This 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’.
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.
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.
Click the Create tab, and then click Data Source.
In the Category pane of the Select a Data Source Template dialog box, click Multidimensional and then click Analysis Services. Click OK.
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.
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.
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.
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.
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:
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:
The Scorecard PowerPivot data source appears the same as an Analysis Services 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:
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!
Nice. I will be trying this out later today.