Tim is a Senior Consultant at ThreeWill. He has 15 years of consulting experience designing and developing browser-based solutions using Microsoft technologies. Experience over the last 8 years has focused on the design and implementation of SharePoint Intranets, Extranets and Public Sites.
PowerPivot for Excel and SharePoint 2010
PowerPivot is an exciting new addition to the Microsoft Business Intelligence stack that provides a lot of power to end-users. For office savvy users who have Excel 2010 and the PowerPivot add-in, analysis against backend datasources is quick and powerful. For users who may have less knowledge of the data or who aren’t as fluent with Excel, Workbooks with one or many Worksheets can be exposed through a browser to broaden the availability to any user who may have a need to analyze the data.
One of the latest additions to the Microsoft Business Intelligence stack is PowerPivot. PowerPivot leverages the comfort and familiarity of Excel with the power of Analysis Services and other data sources to allow business users to analyze mission critical data without the need for IT involvement.
As a business user with Excel 2010 and the free PowerPivot add-in, a connection to a secured datasource can quickly be configured using AD or SQL Server permissions and slicing and dicing of data can be achieved in minutes. However, there are times when personal manipulation and viewing of this data is not enough; the data needs to be shared with other users who may not be able to easily access it for any number of reasons (don’t have Excel 2010, don’t know the data, etc). This is where PowerPivot for SharePoint 2010 comes in. PowerPivot workbooks can be published to a PowerPivot Gallery in SharePoint 2010 where a user-friendly view of the data can be surfaced through the web browser along with the capabilities of slicing and dicing the data to get greater understanding of what is happening in the business.
With PowerPivot, tens of millions of rows can now be imported into Excel and manipulated in real time. This ability to handle massive amounts of data natively from the ubiquitous Excel application empowers business users with the ability to perform true real time analytics that previously required massive servers and tended to be painstakingly tedious.
While a blog doesn’t serve well to share all of the capabilities of this powerful set of tools, I would like to share a quick example of how to retrieve SQL Server Analysis Server (SSAS) data into an Excel 2010 PowerPivot workbook, format the names to be user friendly, and make the workbook available to end-users through SharePoint 2010. Since many of you are probably familiar with the AdventureWorks 2008 database examples, I’ll start out with a very simple example that merely displays Sales totals that can be sliced and diced by Calendar Year, Product Category, Product Sub Category and Product. I’ll then follow-up with something that is more visually appealing by adding a Chart as a second worksheet in the work book.
This walk-through begins with the assumption that you have Excel 2010 and that you have downloaded and installed the free PowerPivot add-in. The plugin can be found here.
Open Excel 2010 and note the new PowerPivot tab that is now available on the top row.
Next, click on the PowerPivot Window button at the top left.
Note that you are presented with several data source options: database, report, Azure DataMarket, etc.
In my example, I want to retrieve data from an Analysis Services database, so I specify the server and database as shown below.
After pointing to the correct database, a wizard will guide you through the steps of choosing the data that will be available in the Excel Workbook.
You can specify an MDX query or alternately select the Design button so that you are presented with a graphical interface to select your data. In my example, I am selecting the Design button.
Now I can use the graphical interface to select the measures and dimensions that I want to see.
For this example, I want to look at Internet Sales Amounts and I want to see them based on Calendar Year, Product Category, Product Sub Category and Product so I drag the corresponding fields onto the design surface as shown in the following screen shots.
Now that I have selected the appropriate data elements, I click OK to continue through the wizard.
The corresponding MDX query is displayed based upon the facts and dimensions that have been selected using the designer. Click Finish to continue.
A “Success” dialog is displayed to indicate the number of rows that met the criteria for the MDX query and the rows that were subsequently imported into the Excel Workbook.
So, now you can see the individual rows that were retrieved. Note that the columns above have all been imported as Text data types. Before you can leverage the numeric data in meaningful ways, you must update the data type to numeric which is shown below.
Select the column that you want to update and specify the data type and the display format.
Now you can close the PowerPivot window and return to the Excel Worksheet where you can begin to build meaningful displays that will help visualize the data.
In this simple example, I’m going to select a PivotTable, but as you can see I have several choices of how to present my data.
I’m going to put the PivotTable on the existing sheet which is Sheet1 in my example.
Now you can begin to build out your PivotTable by specifying the values to display as well as the “Slicers”. Slicers are dimensional data that allow you to “slice” the fact data. In our case, we will be able to slice the sales data by Calendar Year, Product Category, Product Sub Category and Product. As a general rule, each Dimension imported from the SSAS cube will be a slicer. For those who are familiar with SSAS cubes, this makes for a good analogy for what a slicer really does.
Select the Sales Amount field and drag it over to the Values column.
Specify a Custom Name for this amount if you like. Also specify that we will be summing the Sales Amount.
Next, select the Calendar Year field and drag it into the Slicers Horizontal column. Then, select the Product Category, Product SubCategory and Product fields and drag them to the Slicers Vertical column to achieve the display above.
Next, let’s give user-friendly names to the slicers by right-clicking on them and choosing the Slicer Settings.
Now, you can update the Display Caption to “Calendar Year”. You can also change other items such as Sorting or Filtering if desired.
Lastly, we’ll update the Sales Amount field with a new caption and currency display as shown below.
(Note my total Sales Amount of $29,358,677.22.)
When I click the CY 2001 button in the Calendar Year slicer, note the sales amount changes to a much smaller number, $3,266,373.66. This is because we are now only looking at sales for Calendar Year 2001. Note also that the other slicers are updated to reflect what data applies to CY 2001. So, we can see that only the Bikes Product Category was available in 2001.
Next, clear the Calendar Year filter by clicking on the red filter icon next to Calendar Year.
With the Calendar Year filter now cleared, click on the Clothing button in the Product Category slicer to see sales for Clothing. Note that clothing was only available in Calendar Years 2003 and 2004.
Now that we have our workbook functioning, let’s navigate to the SharePoint PowerPivot gallery and upload the Excel Workbook.
First, access the PowerPivot Gallery on your site.
Within the PowerPivot Gallery, click the documents tab and select the upload document option.
Browse to the Excel Workbook and click Open.
Note that Workbook is now uploaded to SharePoint and the hour glass icon indicates that the thumbnail preview is being created. The thumbnail generation process can take several minutes.
When I refresh the page, I can now see that a thumbnail image has been created so that a User can easily see the visual display of the workbook along with a title and other information without opening the workbook. Click on either image to navigate to the first sheet of the workbook in the browser.
Note that the look of the display is very similar to what was experienced in Excel. Now users who access this SharePoint site can slice the data similar to what we did earlier in Excel.
In this scenario, I have picked the Bike Stands Product SubCategory.
While this display is very informative, it is not very visually compelling so let’s go back to Excel and add another Worksheet to this Workbook to make it more visually appealing. Instead of updating the existing Excel Workbook, I have decided to make a copy and update the copy. This will allow me to demonstrate the PowerPivot Gallery display of a Workbook with a single Worksheet and a Workbook with multiple Worksheets. So, make a copy of the first Workbook and open the copy.
Navigate to the PowerPivot tab and select the PivotChart option.
Specify to add the chart to a new Worksheet.
Note that a new chart has been added along with the same field options as I had before. Similar to before, I can now begin to select the fact and dimension values to create an appropriate display.
In this example, I chose to display Sales by Product Category with the ability to filter or slice the data based on Calendar Year. Note that I also renamed the Worksheets to have more meaningful names.
Now, I want to upload my updated Workbook to the PowerPivot Gallery on the SharePoint site
Having navigated back to the PowerPivot Gallery, I navigate to the documents tab and select the Upload document option.
I select the “copy” to upload.
Note that I now have two PowerPivot Workbooks in the gallery. As I hover over each of the smaller thumbnails, the larger thumbnail updates to provide a larger image.
Select the new worksheet to see the PivotChart.
Select the Calendar Year 2001button in the Calendar Year slicer to narrow down the data to only 2001. Note that only bikes were sold during the year 2001.