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!

Plastic Surgery Data Analysis Using PerformancePoint

One area of interest in healthcare data is plastic surgery, and the trends that appear in the operations that individuals are choosing to have done. In order to explore more about this phenomenon, the Guardian Datastore has provided an excellent set of data which has been used here as a basis for further analysis, and the display was created using Microsoft Office PerformancePoint and SQL Server.
As the examples show, sometimes it’s not enough just to have a set of numbers; illustrating the numbers with graphs can highlight patterns to the data consumer that weren’t previously visible.
In order to display some of the capabilities of Performance Point, the plastic surgery data was used here in order to create graphs and dashboards using Microsoft SQL Server as a data source. The data was obtained from the Guardian Datastore has a comma-separated values document, or CSV. This was easily imported into a table in SQL Server using a straightforward wizard. The ‘presentation layer’ was Performance Point, which is part of the Office SharePoint suite of products. Performance Point Services is integrated into Sharepoint, and can take in a variety of data sources, including Excel, standard ODBC connections, SQL Server database sources. It is particularly good with Analysis Services cubes since this allows drill-down and click-through to display data in dashboards, graphs, or even ‘traffic light’ indicators. Performance Point aids analysis by allowing the user a lot of control over the appearance of the graphs, and, as is usual for Microsoft, there are lots of different ways to do the same thing. Whatever is easiest for the user, really! If you would like to know more about how everything hangs together, please see the PerformancePoint Services blog site here for a downloadable Visio or PDF diagram for easy reading.
As a sample, there is a PerformancePoint dashboard below, or click here for the large image:
Overall, the dashboard shows that plastic surgery is a growing area of healthcare, with increases shown in almost every type of surgery except the bottom two: facelifts and abdominoplasty (tummy tucks). In addition to an overall increase in the plastic surgery requested, the type of plastic surgery requested has changed from 2008 to 2009. For example, there has been an over 40% increase in brow lifts, and a quarter rise in rhinoplasty or ‘nose jobs’. However, perhaps surprisingly, the ‘tummy tuck’ operation has shown a nearly 10% drop in operations performed. This is highlighted by the following excerpt of the above graph, or click here for an enlarged version:

There is obviously still a difference in the types of surgery requested by males and females, and this can be seen from the graph below. For example, in females, breast augmentation was a popular choice, whilst the data shows that only 28 males have undergone breast augmentation surgery in 2009. The only choice nearing a 50-50 split was otoplasty, which is an operation to correct ear shape or size. This can be seen from the bottom half of the dashboard, which is reproduced here for ease of use:
One interesting find is that male cosmetic surgery is on the increase; in fact, it increased by 21% in the last year. In particular, the Guardian reports that plastic surgery operations to reduce breast size in men (gynecomastia) have nearly doubled in the last year, and the British Association of Aesthetic Plastic Surgeons (BAAPs) found that operations to correct gynecomastia in men grew by 80% overall over the past 5 years.
To summarise, there are plenty of ways of visualising the nuggets of data held in data sources, and the Microsoft stack allows plenty of different SQL Server based ways for storing and visualising data in accordance with user requirements.