Combine Excel Online and Load on Demand to power up your Excel data source in Datazen


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 –
image  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.
imageThen 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.
image 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!

Providing context for the shadow – Part 2 of the Powerful Productivity Series

This next scenario was one I was going to tackle at some point, but I decided to do it now because of a couple articles I read this past week.  Interestingly enough, one of the articles was a blog post from Tableau, and the other was a survey done by Domo.  I found them interesting because they both touched on a topic I’ve been intimately involved with for the last several years, and that’s sales reporting.  I’m guessing, however, my reaction to each article was different than other folks who read them because of that.

The Tableau post told the same story that every company that sells a BI solution talks about to their customers – here’s how we use our own product to run our business.  For example, Marc Reguera does a great job telling customers how the finance team at Microsoft uses the Microsoft stack to run the business.  I’d have been shocked if the article was about how the sales teams at Tableau reported on their business using Power BI (although I’m guessing James Phillips would have blogged about that article as opposed to me.  But I digress).  The report they showed had the usual metrics you see every sales organization look at – top opportunities in the pipeline for the quarter, YoY growth rates, Achievement vs. Quota, etc.  There was nothing “wrong” with the post at all – but my first reaction was immediate and made me laugh.  That was because I created a report similar to this four years ago for a VP I supported at the time.  I’d spent months doing the following –

– Getting security access to the right data

– Ordering a VM to host my reports

– Setting up SQL Server on the VM to host my data from these sources

– Creating SSIS jobs to cleanup and bring the data in from various .csv files

– Setting up SQL Server Reporting Services

– Building the report in Report Builder

– Deploying the report

There was no doubt in my mind I was about to be the new BI hero in town.  I presented the report to him and waited for his reaction.  Which was the following –


Shrug re-enactment drawing courtesy of Rob Collie

Yep, that was his reaction.

“You don’t like the report?” I asked.

His response and subsequent explanation really stuck with me.  And it was partially highlighted in the survey results I called out earlier – on slide 17, some specific issues were culled from the responses.  The ones I noticed right away were these –

– inaccurate

– not up to date

– unreliable

– hard to understand

When it comes to sales reporting, the vast majority of data comes out of the Customer Relationship Management, or CRM, system.  This is where the sales organization enters all of the information around their customers and the sales opportunities they have with them.  You might have heard of some of the companies that sell CRM systems – Microsoft, Salesforce, and SAP are three of the largest vendors.  However, every CRM system has the same “flaw” – they all rely on salespeople to enter data in the system.  And the quality of the information entered can vary wildly depending on the salesperson for a number of reasons.    

This isn’t meant to be an blanket indictment of folks working in sales.  Being a good salesperson is HARD.  Having a quota you are responsible for, presenting or speaking to customers most days, making cold calls or traveling – it is a job that comes with a lot of pressure.  And every time you enter something into CRM, it’s going to be immediately tracked, dissected, discussed by your manager, their manager, etc.  Which means sometimes, people are very “selective” about what they put in CRM – some might think to themselves, why bring a lot of extra attention to yourself if you don’t have to?  Or they’ll put the value of the opportunity much lower than it really is for similar reasons.  Because of travel, perhaps they don’t update their data in the system for a few days because their too busy.  Or they hate entering stuff in CRM and put it off as long as possible.  Or they are told to put a bunch of opportunities in the system that they have little confidence in but need to show a “full pipeline” for management.  Combine this with the usual number of data entry errors folks will make, and you have a scenario where you have a sexy dashboard that is showing a pretty picture of a lot of bad data.

Should these folks be doing these things?  Of course not.  But the point my manager made was that since this did happen, he needed a solution that could account for this “shadow pipeline” as well as what came out of CRM so he could make a proper forecast of where the quarter would end up.  And he wanted it structured the way they had it currently in Excel, which looked like similar to this overly simplistic re-creation –


Why couldn’t he just force his team do it properly in CRM, you might ask?  Because they were a global team that worked with the regional sales folks, but didn’t directly enter any of the data.  This is hardly unusual – most, if not all, customers I talked to had many, many groups leveraging this data that weren’t entering it directly.  So they needed a way to include this information in the report.

At first glance, this seemed like an easy enough request to add.  I’d follow a similar pattern to what I did in the first post in this series and build a form where his direct reports could enter their shadow pipe, then show it in the format he wanted in the SQL Server Reporting Services report I’d built.  So I went back and did that (which took several hours of work) and re-presented it to him.  Again with the shrug.

“What’s the problem now?” I asked.

“I can’t change the shadow pipe values,” he said.  “I need to be able to do that on the call.”  He was referring to the regular team call this report was used on.

“Well, I can show you how to change them, but I don’t understand.  Why do you want to change values on the call they just typed in for that call the day before?”

In hindsight, this was a really stupid question.  He could have said, “Because I said so, dumbass.”  But he decided to explain anyways.

“There could be a number of reasons.  First off, a lot of stuff can happen around here in a day, so there could be an update that needs to be reflected.  Second, I just hired three new people.  They don’t know the accounts real well and might change their forecast when we talk about it.  Third . . . ”

“Okay, I get it.  But you can change the values – just open the app I built, change the dropdowns, find the value to change, hit save, re . . .”

That was not what he wanted to hear.

“I’m not doing all that, Chris.  I want to change it like I do now, where it takes a second and all the calculations update automatically.  And I also want a way where I can add notes on the call that I can use when I share this information up the chain.  You should never give an executive a sales report without context.  Otherwise you’re just creating a bunch of additional work.  They’ll always want to know why the numbers are what they are, what are we doing to help the teams drive more revenue, do we need to re-allocate funds around . . .”

He was absolutely right – I’d seen that scenario play out time after time.  So I went back and took another crack at it using Excel and this new add-in Microsoft had released called Power Pivot.  However, the way the data needed to be laid out meant people would have to enter data in “holes” in the middle of a pivot table.  There were also a bunch of subtotals in the report currently where I needed to add up data from both the entered data in Excel and the summarized data from SQL Server that also sat in locations in the current report I couldn’t see how to do in Excel.  It also didn’t address the issue of having a central place for folks to go and enter the data because we didn’t have SharePoint setup (this project spurred me to do this for the team on my own, actually).

Eventually, the solution I provided him was a completely custom solution in Visual Studio that took probably three weeks to build, and countless additional hours to tweak over the time I was supporting them using it.  The good news was, he was happy with this and it led to a number of great projects I worked with them on.  The bad news was, making changes was something only I could do, so it died as soon as I left and they needed to make a change. Plus, I never felt comfortable I’d found the best solution.

What if I had the opportunity to do it over again now?  Well, I’m not waiting any longer – let’s redo this baby right now using Office 365 and Power BI and what we can come up with.

I’m going to run through the entire scenario in my head as best as I remember how this used to work, and map it to the functionality in Office 365 or Power BI in each step –

– A meeting invite was created and sent to the participants via e-mail (Outlook and Exchange)

– Data needed to be cleaned up and combined from CRM and another data source that has the quarterly sales targets stored (Power Pivot/Power Query in Excel 2013).

– As opposed to building a custom app, I’ll create the report using Excel as the front-end and taking advantage of Cube Formulas.

If I had simply spent more time reading Rob Collie’s blog post from January 2010 (!), I would have seen I could have provided my manager with the exact functionality he was looking for by leveraging cube formulas.  For the people who’ve never used them before, in essence you have the ability in Excel to blow up a pivot table and layout the cells however you want while still being linked to the data model dynamically.  Rob spends more time talking about cube formulas than I do about Datazen (well, close anyways), so I’d strongly urge you to read his blog to learn more, but here’s how I’d do it for this project –

1. From the Power Pivot window in my Excel 2013/2016 Preview workbook, I’ll create a new PivotTable (or I could create it on the Insert tab in Excel.  It doesn’t matter) –


2. Add to the pivot table the results I need for my report from the CRM system.  I added a slicer as well just to make sure everything is still connected to the live data source after I convert the data –


3. Under PivotTable tools, select OLAP Tools, and Convert to Formulas in the dropdown  –

You’ll see this has converted the cells to individual formulas in the formula bar that looks something like this, depending on which cell you have highlighted –

4.  Cut and paste the last row of values down one from it’s current location so your report looks like this –


5.  If I click the slicer, my values update to the proper results

I now can lay out the report any way I need to, allowing data entry in certain fields, add formulas that properly sum the fields that are entered and tied to the data source, add an area for comments to be entered for each row, etc.  It would have met the requirements he had for his frontend experience.  Single tear . . .

– Central place to collect shadow pipeline and notes from users for the report (OneDrive for Business Access Web App)

As I thought about how I’d handle this piece, I initially considered the idea of just saving my report to OneDrive for Business, sharing a link with the users and allowing them to enter the data in the shadow pipeline field accordingly since the report was small enough it could be used with Excel Online.  He could simply overwrite that on the call with them, or add additional notes and call it a day. There were a couple issues there I had concerns about –

1. Anyone could potentially overwrite the fields that contained cube formulas when editing in the browser.  It wouldn’t be the end of the world, since I’d have multiple backups of the report, but still wouldn’t be ideal.

2. Instead, I looked at adding protection to the sheet while leaving certain cells unlocked and available for editing.  Problem was, this only would work when folks opened it in Excel locally.

For the record, if I had offered this second idea to my manager on how to handle this, he would have said that was fine and not to overthink it.  I know this because we ended up doing something similar for a different project once I got SharePoint stood up.

A better answer in my mind would be to let his team enter the value and notes via an Access app in Office 365 like I showed in the last post, but give him a field where he could enter a value as well, and supersede any entry folks made in the calculations if he entered a value.  Why bother doing this extra work if I know he would have accepted the previous option as a solution?  Because I know he’ll want to capture these values in a way that he could see historically what people were forecasting and how accurate they were vs. the actual sales amount (I know because he did ask for it later on, by the way).  My proposed solution would allow him to do just that, and then he’s the only one ever updating data directly in the report.

– Share the report with my manager and his team (OneDrive for Business and Power BI)

By loading the report to OneDrive for Business and then accessing it through Power BI, I get the best of both worlds.  I now have a way to share a link to the Excel document with my manager so he could view and edit it, provide a link to anyone else so they can view it only, and leverage the Power BI personal gateway to refresh the data on a schedule basis.

This is pretty easy to do.  I can save the file right to OneDrive for Business from Excel directly.  I just go to File –> Save As, then add an Office 365 SharePoint location


Enter my credentials and my OneDrive for Business information is added.


Once I save it there, I flip to my Power BI area and add data from my OneDrive for Business folder


then choose my Excel file I want to connect to.  I want to see and use the file just like I would in Excel Online, so I choose that


Then I can view or edit it as an Excel document AND leverage scheduled refresh.  No conversion to a new Power BI report necessary.


– Run the online meeting for the global team where they review the report and talk about their part of the business (Skype for Business)

There, I was able to redo everything using Power BI and Office 365 and meet his specific requests asks around being able to provide context for the shadow pipeline figures that were captured as part of this process.  I wonder if I could connect the data in that Excel sheet to the PowerPoint presentation he wants to give at the end of each quarter.  I bet I can . . .

Hmm – I’ll save that for another post in this series down the line.  So remember, the next time you’re asked to build a sales dashboard or report, see if can you provide context to the shadow with it.  Because if your solution doesn’t have that level of flexibility, people looking at your report might be “in the dark” when it comes to having the right answers (Yes!  I was looking for a terrible pun to end this post with, and I succeeded.  In your face, good storytellers.)

Until next time!

Powerful Productivity using Power BI and Office 365 – Part 1 in a series

Alright the long weekend is here (and sadly over by the time I finished this post)!  And since Monday is was a celebration of the worker here in the US and Canada, I wanted to do something special for this post.  Microsoft Office is the go to productivity suite for 1.2 billion people every day.  That translates to 1 in 7 people on the planet earth using Office to get things done, both personally and professionally.

It’s also a key part of every successful business intelligence implementation I’ve seen – and I’m not just talking about the ability to export to Excel.  In my previous life in shadow IT, the VP I supported used to hammer this point home to me.  He and his team lived in the Office suite day in and day out to do their jobs, so whatever I was providing to his team, I needed to always keep that in mind.  They had jobs to do that didn’t involve learning a complicated new report or app – if it wasn’t easy to adopt, show immediate value, and work well with things like PowerPoint, Excel and Outlook, then it was dead on arrival.

While I’d like to think I did this pretty well, looking back now, I can’t believe how much additional work I had to do simply to achieve the same results I could get just a few years later.  And I’m not talking about me being more skilled (or balder) – the level of productivity someone can achieve using tools like Office365 and Power BI is STUNNING.

You’re probably thinking – “Yeah, it’s “stunning” a Microsoft employee is telling us how amazing their new stuff is,” as you roll your eyes and switch to Jen Underwood or Rob Collie’s blog.  I’ll admit, that isn’t a terrible idea, but hear me out.  I just want to see what my old job would have been and how much more I could have accomplished.  I used to spend my days building apps, setting up databases, building reports, etc.  With the way things have been automated and simplified in Office 365 and Power BI, could they be done now entirely using those tools?

“OH MY GOD – HE’S NOT BLOGGING ABOUT DATAZEN!  THE PRODUCT MUST BE DEAD, etc.” – Thought by at least one person reading this blog post.

To this person I say, yeah, um, no.  No, the reason I am not including Datazen is because one of the rules I need to follow for this self-imposed challenge is that I can’t use IT to setup a VM for me.  I have to do everything in the context of Office 365 and Power BI for this particular challenge.

So, without further adieu, here’s a typical project request I might have gotten 5-6 years ago –

There’s a request in my inbox from someone whose team needs a line of business app built.  They need a simple app built to capture ten fields of data for a one-off project.  A couple temps will be entering the data for a week or two, and they’ll need to do it via a web interface.  There are some reports I’ll need to build off of the data, and I’ll need to show the final numbers in PowerPoint deck I provide back to them so they can add some additional commentary.

Requests like this I used to eat for breakfast (along with Honey Nut Cheerios, usually).  Let’s assume this was asked of me in 2009.  I probably completed the request this way (FYI – SharePoint wasn’t set up and available to me) –

– Ordered a virtual machine (VM) from IT then waited until it was finally provisioned for me.
– Installed SQL Server 2005/2008 on the VM to host the database
– Setup Internet Information Services (IIS) on the VM to host the website
– Built the app using ASP.Net maker (which I had purchased myself) to do these little forms over database projects.
– Deploy the app to the server from my local machine
– Built a couple reports for them in SQL Server Reporting Services using Report Builder.
– Used SnagIt to grab screenshots of the reports and app I could use in the PowerPoint deck.
– Shared the files via e-mail to the folks for them to add commentary and present out.

I can’t believe that’s really how I used to do a project like that then.  At least in 2011 I could have used Visual Studio LightSwitch and Power Pivot.  Let’s try redoing that in Office 365 and Power BI –

1.  Starting with Office 2013, Microsoft Access can be used to make line-of-business apps that you deploy to Office365 sites.  These can be accessed via the web by users to enter data that’s saved in a SQL Azure database that Microsoft provides (as long as you have no more than 1 GB of stored data).

So I can open my team site in Office 365 and select Site Contents –

Then I choose Access App, and enter the title of the app I want to build.
I’m then greeted with a webpage that says I’ve successfully created the app.  I can start adding tables and screens in Microsoft Access on my desktop to finish the application.
About 7 minutes later (no seriously, I timed it), I have created my table and form, and it is now available online, via browser, for folks to start entering data –
I also need to run reports.  No problem there, since if I look back in Microsoft Access on the Info tab, I am provided information around how I can connect to my database and run reports –
Once I’ve created the read-only connection, I can open Power BI and use the direct connect with SQL Azure functionality to connect to the database and create my dashboards and reports.  To get my connection information, I grab the details from Access for the read-only connection –
and then plug it into Power BI –
This allows me to create a report in a web browser against the dataset immediately and share it out with individuals or a group.
Now I can add the report to a PowerPoint slide by using some newish, built-in PowerPoint functionality (remember, I can’t use the default Snipping Tool in Windows because it isn’t in Office 365).

In Office 2013 and Office 2016 Preview, under the insert tab, I can insert a screenshot right from PowerPoint.


If I select Screenshot, I see all the open windows on my desktop, and I can simply select the Power BI browser window that’s open to insert the screenshot.  I then resize my screenshot so they can add commentary to the slide accordingly.image
Once I’m finished my deck, I can save my PowerPoint deck either directly to an Office 365 site, or my personal OneDrive for Business site and share the file securely via a link in my web browser to approved users only.

Once folks get the link I send via Outlook and Exchange, they can review the deck together if they  wanted and even make the changes in real time.

So I was able to meet the goal of only using Office 365 and Power BI to complete the project.  How much more productive was I?  The amount of time I estimate it would have taken me to complete the project previously was –

– Minimum 2 weeks (336 hours) for a VM to be provisioned for this project.
– 2 hours to setup SQL server
– 1 hour to setup IIS
– 3 hours to build and test the app
– 2 hours to deploy the app to IIS and test (this never worked right away)
– 2 hours to build and test the Reporting Services reports
– 1 hours to build the slide deck and send via e-mail

So 348 hours minimum were needed to get everything done on my side, and this is assuredly being generous with the estimate.  How long did the same thing take using Office 365 and Power BI?

– 0 hours for VM setup (not required)
– 20 minutes to build, test and deploy app
– 0 hours to setup IIS
– 0 hours to setup SQL server
– 20 minutes to build report in Power BI
– 1 hours to build the slide deck and share the link for review

So I went from needing 348 hours to needing about 2 (let’s round up).  That’s a productivity improvement of over 150x!  Just think about that – this project went from something I had to do a fair amount of additional work around just to request the VM, do all the setup and configuration, etc. to something I could have back to them for the initial data entry piece within an hour of the original request.  Plus, the entire project can be done or reported on from every mobile device.  Yeah, that wasn’t a requirement for the project originally, but it certainly would be now, and that’s all included with no additional development work required on my part.

I really enjoyed doing this first post in this series – it’s easy to forget just how powerful the tools like Office 365 and Power BI are (I know I often take this stuff for granted) and how much value they bring to our customers.    I’ll do more of these now and again that I sprinkle in between my normal Datazen posts, which I know my dozens of loyal readers eagerly await each week.

Thanks for reading!

Combine Datazen dashboards with Power BI Q&A functionality

Happy Friday, everyone!

Did you know that Power BI now allows you to directly link to individual dashboards?  This got me thinking – I love the Q&A functionality in Power BI.  Could I easily put together a solution where I used Datazen for my dashboards and jumped into the Power BI service to ask additional questions?  Why yes, yes I can.  Here’s how –

For this example, we’ll use a simple Excel workbook as the data source.  But let’s leverage the capabilities of OneDrive for Business to make this solution a bit more elegant.


Why?  Couple reasons –

1. Power BI will automatically refresh datasets sitting in OneDrive every hour, so anytime you update your Excel data, it’ll get grabbed automatically by Power BI shortly thereafter.

2. You can leverage the OneDrive for Business sync client to save Excel files locally on your PC and have them automatically sync to other machines running the sync client.  This makes it easy to get my Excel files onto my Datazen server after I’ve installed my sync client on there – I can refresh my files locally, save them to my OneDrive folder on my PC, and they’ll show up on the Datazen server within minutes!  I could even use a tool like Power Update to automate the data refresh of my Excel files.

Let’s get started –

First, I’m going to open Datazen and draw my dashboard first so I know how I should lay out the data in my Excel workbook.

Screenshot (34)
With that done, I’m going to create my workbook from my live data source using Power Query to make sure it’s laid out the way I need.
Now, I’m going to save the file to OneDrive for Business.  Once that’s done, I’m going to my site and creating a new dashboard called “StoreQA”.
Now, I’m going to add my data to the dashboard.  So I click “Get Data”
Then I select “Files”
Now I want to select the “OneDrive for Business” tile
And then find the file I just saved

and hit “Connect”.  It’ll then bring my data into the dashboard and I can leverage Q&A with it.
I’m going to leave this open for now, and jump back to my Datazen Publisher App to bring in the Excel data to my Datazen dashboard.  You’ll need to setup the OneDrive for Business folder on your Datazen server as the location for the Excel data (this assumes you setup the sync agent on the box already).


With that done, you can bring in the Excel data to your Datazen dashboard
Screenshot (35)
 Screenshot (36)
and hook back in the elements accordingly.

Screenshot (39)

Once that is done, you’ll want to create a link from the Datazen dashboard to the Q&A in Power BI for this dataset.  Go back to your browser and grab the URL for the dashboard from the address bar.  It’ll look like the example here –

To go directly to the Q&A functionality for this dashboard, just add ‘/qna’ at the end of the url, so it then looks like this –
You can test it out and see how it takes you right to the question and answer area for this dashboard –
Simply copy that link and select an element on your dashboard you’d like to link to the Q&A piece from
by using the Drill-Through Target functionality to point to a custom URL
Screenshot (37)
Now when I click on the element, I get right to the Q&A page in Power BI with the same dataset so I can ask it more questions about my data
If you really wanted to get clever, you could even use the parameter functionality.  See how when I ask a question in Power BI, the URL changes to include the question text –

and gives me the following result


I can simply change it to use a parameter instead, so it would look like this –{{ SelectionList01.SelectedItem }}%20by%20year

and when I run my dashboard, I can change the dropdown
Screenshot (38)

and the question will dynamically change when I click on my element with the link to Power BI!

Remember, you have to publish the dashboard to your server before you can test the URL drillthrough in Datazen!

And with Power BI now having the ability to setup custom links with it’s elements, you could even pin the tile for the question you just asked so you could jump back to your Datazen dashboard!

Hopefully this helped open up a number of possibilities with the tools for you to explore.  Have a great weekend, and don’t forget to download the Windows 7 publisher app currently in preview from here and give it a spin.

Thanks for reading!

Drillthrough from one Datazen Map Dashboard to another dynamically

Tonight’s post came out of a conversation I had with my friend Chris here at Microsoft (yes, we’re both Chris).  I was looking to see if I could drillthrough from a map of the United States into each individual state, and see the county-level information of another Datazen dashboard.  The answer is – yes, kindof!

Why the caveat?  Because I want to dynamically have the state change based on which one I select for drillthrough.  You can’t do that by drilling from one dashboard to another inside the Datazen app.  But you can do it by using the web viewer functionality in Datazen for your lower level dashboard.  Let me show you what I did.

First, in the Datazen Designer, I dragged a single Range Stop Heat Map onto the canvas and renamed it accordingly.


Next, I used Power Query in Excel to search online and find the state by state results for the 2012 Presidential Election.  I brought the results into an Excel file, and loaded the file into my Datazen dashboard under the “Data View” tab by choosing “Add Data” and “Local Excel File”.

Screenshot (27)
I made “Obama” the Values selection and “Romney” the Targets selection.  It doesn’t really matter which one you choose for which for this example, however.  Then I flipped back to my layout view.

I changed the range stop values to show a very narrow range for Neutral Start/Neutral End (99.98 and 99.99), so unless you had another Florida situation, you’d only see two colors for our map.  At this point, I saved it and created a new map dashboard.  But instead of using the out of the box maps, I loaded a new custom map from my local hard drive by choosing “Custom Map From File” from my map selection list.  Here I loaded the Pennsylvania map files I had (here they are if you would like to use them.  Just save them locally and extract them to somewhere on your machine –

Screenshot (28)

Then I created a new map dashboard, doing all the same setup items I did for the map of the US.  I called it “Pennsylvania” and published it to my Datazen server.

I want everyone to see this dashboard, so I need to make it public, which is something that Datazen server supports.  To do that, I need to add a guest account to my Datazen server, and then give it rights to that dashboard.  So I add a guest Server User, like so –

image  Then I add Ol’ Guesty to my hub as a user –


Then I make sure I assign Sir Guest-A-Lot to my public dashboard (as you can tell, I’m kinda punchy right now).


Now I can link directly to this dashboard by using the following pattern as described in the Datazen documentationhttp://local.server/viewer/public/dashboard?name=DashboardName

With that complete, I can go back to my original map dashboard and set up the drillthrough to the url of this state dashboard.  Since I want it to drillthrough to the selected state, and not just Pennsylvania, I’ll use the parameter value so my url looks like this –{{ RangeStopHeatMap3.SelectedItem }}

Now I publish my dashboard, and when I click on the state of Pennsylvania:

Screenshot (29)

A web browser opens up and I get this!

So yeah, there’s obviously the drawback I need to do this 50 times in this example.  But it DOES work (kindof), just like Chris and I discussed.

Thanks for reading!

How to use an Excel data source for KPI’s in Datazen

Happy weekend, folks.  As mentioned in the guest post I did with Rob Collie, a folder of Excel files can be used as a data source for dashboards with Datazen.  However, when it comes to KPI’s, there isn’t out of the box support to use Excel as a data source for those.  But you can still use Excel if you setup a custom data connector to do so.  One caveat with this approach – what’s we’re basically doing is allowing Datazen Server to use Excel like it would a normal SQL Server data source.  This means you’ll need to write at least some basic T-SQL statements to actually bring back data from your Excel workbook.

Assuming you know how to do that, here are the steps you need to follow to enable this –

1. You need to download and install the Microsoft Access Database Engine 2010 Redistributable (choose the AccessDatabaseEngine_x64.exe option) on your Datazen server.

2. Create a new folder named OLEDB in the following location (this assumes you setup a custom data provider with my previous post.  If you haven’t, do that first.) –

C:\Program Files\Datazen Enterprise Server\service\dataproviderschemas


3. Download the following file – and extract the XML file to the folder you just created.


4.  Now login to your Datazen Control Panel and go to “KPIs” for the hub you want to setup with your KPIs.


5.  Click “Create New Group”


6.  You should see the new provider you setup in the previous steps listed as “Microsoft ACE OLEDB 12.0 Excel 2007-Newer” –


Enter a name for your KPI group, and leave the HDR=Yes item under Extended Properties if your Excel file contains a header row.  Otherwise, change it to “No”.


Next, in the data source area, put the file path of the Excel document you wish to you use.  Make sure it is some place the Datazen service account has access to.  Test the connection to make sure it works, and hit next if you see the “Connection Successful” message –


Select the groups who should have access to the data source, then hit Finish.  This will bring you back to the KPI menu with your new KPI hub showing –


If I click “Create New KPI”, I’m presented with the KPI Creation screen.  On the right hand side of the screen, I see my KPI Value/Goal/Status fields I can use my Excel data source for.


If I select the dropdown for “Value”, I can choose “Execute Query” and a “Define Query” link appears.  I need to click it to write my query –


Now I can write my query for this KPI Value.  The table name equates to the name of the tab in the Excel file, but you need to have a ‘$’ appended to it.  A sample query would look like this –


If successful, it should bring back a single result to be used with that KPI –


Hit ‘Update’ to save your query and return to the KPI setup screen.  You can now repeat the process for the Goal/Status/Trend Set items if you wish, or you may set any or all of those manually. 

If you want to learn more about KPI’s and how to set them up in Datazen, refer to the Datazen documentation.  Hope this was helpful, and enjoy the rest of your weekend!