I hadn’t planned on a post today, but after watching the Philadelphia Eagles get their first win of the season, I was inspired to do something I’d first thought about a couple weeks back. Specifically, I wanted to use an Excel document as a data source in my Datazen dashboard, but enable Load on Demand capabilities so I could take advantage of parameters. I also wanted to see how that would work in combination with people just typing in data into the Excel sheets that are the data source. How quickly would I see their changes in my Datazen dashboard?
Out of the box, Datazen allows you to use Excel files in a shared folder as a real time data source. And in the post I did around combining Datazen dashboards with Power BI Q&A functionality, I first talked using the sync capabilities of OneDrive to sync an Excel file from my local drive to the Datazen server. The thought was, that way if I refresh a data source locally, I can have it sync up to the server. The issue I saw with using this as the solution for this scenario were two-fold –
1. I was the only person updating information in this scenario. Ideally multiple people could make updates using Excel Online at the same time.
2. I couldn’t do parameters with the out of the box Excel data source in Datazen.
Well, what if I used the workaround I mentioned about how you could use Excel files as a data source for KPI’s? Since that had you using Excel like SQL, maybe then I could use parameters. Let’s give this a shot.
In my Datazen server, I’m going to create a new data source called Excel as SQL, using the instructions from the blog post I referenced –
I’m going to create an Excel file with two tabs – one has Category, Amount and Target along with some sample data.
The other just has Category and a single sample value.
I then save my file. It’s now available to me either on the server or in a web browser via Excel Online, thanks to OneDrive.
On the Datazen server, I’m going to create two SQL queries to use. The first one, called Data, looks like this –
You see I’ve setup a parameter called @Product to use – here’s what that looks like
I used a default value to make sure I get back at least one value, otherwise the Datazen Publisher won’t be able to build the data view properly. Next, I’ll build my other query called FilterValues. It’s pretty basic –
Now I can build my dashboard. I’ll bring in two elements – a Selection list and a Number with Delta.
Then bring in both of the data feeds and hookup the data to the dashboard elements.
I’ll setup my parameter to be equal to what’s passed into my dropdown list.
Now I publish my dashboard. When I hit it from a web browser or app, I get back what I’d expect to.
This is fine, but I’ll want to add more items on an ongoing basis. If I try to open the document and make the changes directly on my desktop, the data source is locked and not available to Datazen –
But, if I instead make the changes in Excel Online, the changes get synced and my data source isn’t locked! This is true even if I have multiple people adding items at the same time. I’m going to update my Excel Online document by adding a number of new categories (as suggested by my children) –
How quickly these show up can vary – I’ve had it in as little as 5 seconds, and sometimes it’ll take up to a minute. For example, these showed up by the time I finished typing the previous sentence.
And I’ll now update my data in the other sheet as well.
What’s cool here is I don’t need to refresh the page to see this new data, since it’s always going to back to the original data source thanks to Load On Demand. Once my data shows up, my values are automatically updated when I change the dropdown.
And notice I added the Seahawks data – I’ll never see that as a value if unless I add it to the dropdown as well.
This offers some great flexibility for folks to manage data and make updates this way. You could also do multi-select values vs. a single value by changing your SQL statement and allowing multi-select in your dropdown list.
Thanks for reading everyone!
Christopher Finlan 