SQLUniversity: PowerPivot, Tableau and Jedi Knights

This blog will show an overview of how I mobilised PowerPivot using Tableau. I’ve previously given this session at SQLBits, NEBytes Microsoft Technology User Group, and SQLServerDays in Belgium but thought it would also be useful to supply the files for you. The steps are very simple since I intended to show the end-to-end solution simply as a proof of concept, as follows:

• creation of a PowerPivot which mashed up UK Census data with geographical data
• creation of the report in Tableau
• deployed to Tableau Public for consumption by mobile devices such as the iPad

The example was deliberately kept simple in order to prove the concept of PowerPivot being mobilised. 

The data sample involved mashing up two sources:

• Jedi Knight census, data, which can be downloaded from here This is a basic file but the final PowerPivot can be downloaded from a link later on in this article
• Geonames offer an excellent free download service, which you can access here

The Jedi Knight data, along with the geographical data, were joined using the outcode of the postcode data. If you need more definitions of the UK postcode system, I’ve previously blogged about this here.  

Essentially, a very simple RELATED formula was used in order to look up the latitude and longitude from the UKGeography table, and put it into the Jedi Knights data, and produce the necessary data in a simple Excel table. The formula looks like this:

=RELATED(UKGeography[Latitude])

=RELATED(UKGeography[Longitude])

Once these very simple formula were put in place, it was time to load the data into Tableau.

Tableau can take both PowerPivot and Excel data – which driver to use?  I used version 6 of Tableau. Whilst this version of Tableau does see the PowerPivot correctly as an Analysis Services cube, it does not always read the date as a ‘date’ type, but instead as an attribute. There is a forum posting on the Tableau website which tells you how to fix this issue, which involves changing the date so it appears as a measure, which means it can then be used for trends and so on. 

However, I wasn’t comfortable with this solution because I like dates to be in date format. I’ve also run into this issue at customer site, where the customer wanted to use SSAS as a source and Tableau as the presentation layer. They were data-warehouse savvy and didn’t like the ‘measures approach’ fix. 

On customer site, I got around it instead by using the Excel data source, and importing all of the PowerPivot columns into an Excel 2010 sheet. By doing it in this way, date formats were preserved. In this example, I didn’t have date format so it didn’t matter – but this is a useful tip for the future if you are using PowerPivot with Tableau. The final data, in an Excel PowerPivot, can be obtained in zip format here or if you can’t access it, please email me at jenstirrup [at] jenstirrup [dot] com.

Once the data was accessible by Tableau, I used the Tableau Desktop version to upload the data into Tableau’s memory. I did this so that I could eventually upload the Tableau workbook to Tableau Public. The instructions to save to Tableau Public are given here. 

Once the data was in Tableau Public, I just needed to access the data using the Safari browser on the iPad. In case you are interested, the demos are publically accessible and you can access the final result by clicking on the hyperlinks below.

I hope that’s been a useful overview of PowerPivot, and the ease of which it was mobilised. This blog forms a use case of how it might be useful to use PowerPivot, since I think that people sometimes need examples of how PowerPivot can benefit them. In this case, the clear benefit of PowerPivot is to provide an easy way of mashing up different data sources.

I look forward to your comments and thank you for sticking with me for the PowerPivot SQLUniversity discussions!