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.

image

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.
image
Now, I’m going to save the file to OneDrive for Business.  Once that’s done, I’m going to my PowerBI.com site and creating a new dashboard called “StoreQA”.
image
Now, I’m going to add my data to the dashboard.  So I click “Get Data”
image
Then I select “Files”
image
Now I want to select the “OneDrive for Business” tile
image
And then find the file I just saved

image
and hit “Connect”.  It’ll then bring my data into the dashboard and I can leverage Q&A with it.
image
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).

image

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 – https://app.powerbi.com/dashboards/xxxx

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 – https://app.powerbi.com/dashboards/xxxx/qna
You can test it out and see how it takes you right to the question and answer area for this dashboard –
image
Simply copy that link and select an element on your dashboard you’d like to link to the Q&A piece from
image
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
image
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 –

https://app.powerbi.com/dashboards/f31f7d2f-13ba-4e21-8b22-754ac62d39d7/qna?q=what%20were%20total%20sales%20for%20grocery%20by%20year

and gives me the following result

image

I can simply change it to use a parameter instead, so it would look like this –

https://app.powerbi.com/dashboards/f31f7d2f-13ba-4e21-8b22-754ac62d39d7/qna?q=what%20were%20total%20sales%20for%20{{ 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!

image
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!

image 
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.

DatazenMap

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 – http://1drv.ms/1H1PtLp).

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 –

image

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

image

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 –

http://sample.cloudapp.net/viewer/public/dashboard?name={{ 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!
image

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!