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!

SQLUniversity: Introduction to PowerPivot and Mobile Business Intelligence

Here is the second post for SQL University, where we introduce the topic of mobilising PowerPivot data. As an introduction, I thought it might be useful to share the presentations that I did recently. Tomorrow’s blog will talk more about the details of mobilising PowerPivot data using Tableau, but I thought that these articles might provide some introductory material for people to use, and re-use, in order to expose the usefulness of PowerPivot as a data source. Here is a brief overview:

Essentially,the Ordnance Survey and Census data were mashed up together and linked together in PowerPivot. The PowerPivot was then used as a source to Tableau, which was used to visualise the data.  As an introduction to the process, I generated some presentations to use when I was talking around this solution. and I have provided these presentations here.
Here is a very introductory presentation on PowerPivot in Sharepoint. This presentation was designed to be given in around 5 minutes before being supported a series of demos, and the intended audience was people who hadn’t seen PowerPivot previous to the presentation.

The following presentation came from SQLBits, where I gave a discussion on mobilising business intelligence with PowerPivot. Tableau Software was used in order to display the data from the PowerPivot, and the next blog in the series will present more technical detail on how the solution was built.

Here is the presentation that I produced for SQL Server Days in Belgium in November 2011:

I hope that you enjoy these presentations, which I’ve provided for people to use and enjoy as they wish. Tomorrow’s SQLUniversity post will provide more detail on the mobilisation of PowerPivot with Tableau.

SQLUniversity: PowerPivot and ATOM feed mashup Source to PerformancePoint

SQL-University-Shield-268x300This is the first episode of SQL University’s week of PowerPivot! In this episode, we will look at how PowerPivot can be used to mash up PowerPivot and ATOM Feed data, which in turn is then shaped and surfaced in PerformancePoint Services for Sharepoint. All of the images are hosted on Flickr so please feel free to click on them to see the larger versions.

Mashing together PowerPivot with an ATOM feed means that the disparate data sources appear unified, which can give us something business users can use very smoothly. The business users can also see the detailed Powerpivot in Sharepoint, so they are able to go back and check the detail of the data if they require. The ATOM feed means that they can change the target data using Sharepoint Lists, and it will pass through Sharepoint so it is visible in their scorecards. Ultimately, the business users want accurate information as quickly as possible in order to make a decision, whether it is operational or strategic – and mashing together data sources in PowerPivot can help them to do just that!

There are a number of steps involved in this sequence:
  •  Creating a Sharepoint List
  • Create Custom List in Datasheet View
  • Export the Data to an ATOM Feed
  •  Connecting PerformancePoint to a PowerPivot data source
  • Creating your KPIs and Scorecard with your PowerPivot source

Each of these steps will be detailed in the following sections.
Creating a Sharepoint List
On the left hand side of the page, look for ‘All Site Content’.

B Create Sharepoint List

Create Custom List in Datasheet View

Right click on the column called ‘Title’, and change it to something convenient.
Once the columns are added, enter information in the rows. This is done by directly typing into the cells.
Once the table is complete, we need to export the data to PowerPivot via a data feed. The next steps to export the data to PowerPivot are given next.

Export the Data to a ATOM Feed

If you can’t find the custom list, look for the link called ‘All Site Content’ in the browser. You should find your custom list, which will appear half way down the page under the heading ‘List’.
Click on the orange button called ‘Export as Data Feed’. This can be seen in the toolbar below.
Next, you need to save the atom feed output to a workbook. From the box entitled ‘PowerPivot’, choose a workbook.
Click on ‘OK’ to create a dedicated workbook for the PowerPivot model that will hold the target data.




C Export as Data Feed




The ‘Table Import Wizard’ will appear. Assign a name to the PowerPivot model that is appropriate to your network.
Keep the Data Feed as-is for the moment, and click ‘Next’.
Next, the Table Import Wizard offers you the opportunity to filter the table. For this exercise, this is not necessary.  We will click ‘Finish’.
Once you have clicked ‘Finish’, you should hopefully get a ‘Success’ feedback window. Choose the ‘Close’ option.
Once you have done this, your PowerPivot model of the Target data appears. Save it to your preferred Sharepoint location for the PowerPivot models. This is URL to the PowerPivot gallery on your Sharepoint site.
Now you have a PowerPivot model that contains your target data.  Now we want to mash it together with your actual data to give us a complete model of merged Actual and Target data in PowerPivot. This ‘mashed together’ model will give us a nice data set for visualising Actual versus Target data, and this is something that we are going to do in the next step.
PerformancePoint is a very nice way of displaying Actual versus Target data that is intuitive to the user. We will now look at some of the different ways that PerformancePoint allows us to display Actual versus Target data. As we proceed, we will look at the best ways of visualising the Actual versus Target data so that business users can understand the message of the data.

To use PerformancePoint, you firstly need to open Sharepoint in a browser since it may not be obvious where PerformancePoint is hiding! If you haven’t used PerformancePoint Services before, then it needs to be installed from the Business Intelligence Center in Sharepoint. The install process is straightforward, but if it does not work, then there may be an issue with your permissions.
In the Business Intelligence Center, click a section, such as Monitor Key Performance, Build and Share Reports, or Create Dashboards.
Additional information about each area is displayed in the center pane.
In the centre window, click a link that says, “Start using PerformancePoint Services.”
The PerformancePoint site template opens.  Next, you need to click the orange button that says ‘Start Using PerformancePoint Services’. The Dashboard Designer should start to install itself and run.
You do not need to install the Dashboard Designer every time. Instead, you can go to Start – All Programs  –  Sharepoint – PerformancePoint Dashboard Designer.


Connecting PerformancePoint to a PowerPivot data source
To create a PowerPivot data source connection, the following steps need to be followed:


 Click the Create tab, and then click Data Source.

3a Create then Data Source


In the Category pane of the Select a Data Source Template dialog box, click Multidimensional and then click Analysis Services. Click OK.


2. SSAS Data Source for PowerPivot

In the left navigation pane (workspace browser), type the name of your data source.


In the center pane, click the Editor tab. In the Connection Settings section, select Use the following connection.


Type the connection string to the PowerPivot data source by using the following format: 


PROVIDER=MSOLAP;DATA SOURCE= http://testsp01/sites/powerpivot_test/ABC/PowerPivot Base Data Hub/Operations PowerPivot Book.xlsx


Select the cube from the drop-down menu. The cube name for a PowerPivot model will always be Sandbox.


3b Connections Details for PowerPivot Source



In the Data Source Settings section, select the method on which to authenticate to the data source.


From the Formatting Dimension list, select desired dimension formatting needed for the report.


From the Cache Lifetime list, type the refresh rate (in minutes) for the cache. Data from this data source will update at this interval.

Here are the details below:


Click Test Connection to confirm that the connection is configured correctly.
Note: at the time of writing, Time Intelligence is not supported for PowerPivot data sources in SQL Server 2010. You should always test your connection, and hopefully you should get this result:


4. PowerPivot Successful Connect
The next step is to create a Scorecard that will display the performance of the Actual data compared to the Target data.  The Actual data will come from the PowerPivot Actual source, and the target data will come from a separate table in the same PowerPivot source.


The Scorecard PowerPivot data source appears the same as an Analysis Services data source:


5 Scorecard - select a data source


Technet offer an excellent walkthrough of the PerformancePoint scorecard creation here. The key difference is in selecting the data source. Note that the data source comes up as ‘Analysis Services’ rather than PowerPivot, but do not worry: remember PowerPivot is powered by Analysis Services, so that’s ok. Here is an example:
When we set up a KPI source, the source also appears to be Analysis Services. Here is what the next step looks like:


6 Select a KPI Source


Once you have created your scorecard containing your actual and target KPIs, it appears as a unified scorecard rather than coming from different mashed-up data sources.

To summarise, mashing together PowerPivot with an ATOM feed can give us something business users can use very smoothly since the disparate data sources appear unified. The business users can also see the powerpivot in Sharepoint, so they are able to go back and check the detail of the data if they require, which is something that they can find reassuring. The ATOM feed means that they can change the target data if they like, and it will be visible in their scorecards. Ultimately, the business users want accurate information as quickly as possible in order to make a decision, whether it is operational or strategic – and mashing together data sources in PowerPivot can help them to do just that!