Introduction
PowerPivot builds on existing Excel features and extends their functionality. Those familiar with Excel are aware of Pivot tables. Excel typically involves creating a pivot chart with one single data source. PowerPivot allows users to extract data from various sources. These data sources can also be linked to one another with in a PowerPivot workbook. This feature allows you to create sources which maybe from a broad list of sources and link them all in your work book.
Slicers are also new in PowerPivot. They are familiar territory for users of Excel who have worked with filters. Slicers are useful for filtering the data in Pivot charts and tables. The advantages which make slicers different from filters are the visual display of the data which is being filtered and the ability to link slicers to multiple charts and pivot tables with a single slicer if that data is linked in PowerPivot. Hence they are easier to use because they allow single point of control for filtering data from multiple sources.
We will walk through these features of linking data sources with in PowerPivot. In addition we will link slicers through the slicer options to have ability to slice our data sources from one slicer.
Installing PowerPivot
Establishing a PowerPivot environment requires deploying the PowerPivot for Excel® 2010 add-in on workstations running Microsoft Office 2010 and PowerPivot for SharePoint on SharePoint application servers.
These tasks are not dependent on each other. Users with only a web connection don’t need Excel 2010 on their machines to view PowerPivot work books deployed on the SharePoint server. However users that do need to create and deploy work books to the SharePoint infrastructure do need to have the Excel 2010 client.
Linking data within PowerPivot
My source in this example is an SSAS cube. I am pulling information on a hospital which wants to track the Average time a Surgeon will take to spend on a procedure in a year by their specialty. I am calling this table the AverageSurg_Timewith the following columns listed below:
You can see the sample data which I pull and the columns below:
We pull a second data set which is called Vendor_Cost. The VendorCost table will enable a hospital to view the Total Cost for a component which is purchased by a hospital for that particular Year and Procedure Type. The Vendor_Cost table is going to be pulled with the sample data and columns below:
Both tables have a field which we can use to filter data on for a Procedure Year. But there is no link between the two tables to know which Year we want to see both these by. If we have a slicer which we want to filter then we will need to add a separate slicer for each source. Hence we will need to link the Procedure Year in both tables to another dimension which will act as our intermediate table for linking the two sources. Both our AverageSurg_Time and the Vendor_Cost will link to this intermediate dimension. This is a ProcedureYeartable with a single column and it lists all Year values as listed below:
Procedure Year
We will create a link between the three tables in the PowerPivot Design options. First define a link with Vendor_Cost and the ProcedureYeartable as shown:
Next we define a relationship with the ProcedureYear table and the AverageSurg_Timetable.
Linking Slicers to the Pivot Tables
In the next step we will create our pivot charts. I create a few simple bar graphs which are listed below which sources data from Vendor_Cost and AverageSurg_Timeas two Pivot Charts:
The first one is Average Surg Time and the second is the Vendor Cost. Above, I have added a slicer and have used the Procedure Year as a source from the ProcedureYeartable. At this point the data in the Pivot table is linked in the underlying source properly. However we need to perform the link in the Excel presentation layer as well. The Slicer I defined is not linked to the Pivot tables in Excel. In order to link the slicer to the pivot tables we will do the following steps:
1. Select the slicer which you create above the Pivot charts and you will select the ‘Slicer Tools’ in the menu listing.
2. Select the ‘Options’ selection.
3. Then click the ‘PivotTable Connections’ option:
4. Link your Pivot tables in the PivotTable Connection screen to the slicers which you want to define the relationship for.
In my example I just have two Pivot tables which I want to link. which are being populated with the Sheet listed as
Data for PowerPivot Chart 1 and Data for PowerPivot Chart 7. The Sheet is the actual sheet in Excel which will host the PowerPivot data into Excel to build the PowerPivot table.
5. Select ‘Ok’ and you will have your data linked in the Presentation layer.
Now when you select the Procedure Year slicer it will filter down the data in the Pivot tables which I define in the slicer option.
Conclusion
The ability to link sources from a number of areas within the organization with relative simplicity is useful in many scenarios. In addition the ease of using slicers and linking them provides additional functionality to give your users ease of use in consuming content provided via Excel. This post illustrates just some of the ways we can leverage PowerPivot to extend our Excel workbooks. There are many other new features which extend the uses of Excel which can be delved into with more detail.
PowerPivot builds on existing Excel features and extends their functionality. Those familiar with Excel are aware of Pivot tables. Excel typically involves creating a pivot chart with one single data source. PowerPivot allows users to extract data from various sources. These data sources can also be linked to one another with in a PowerPivot workbook. This feature allows you to create sources which maybe from a broad list of sources and link them all in your work book.
Slicers are also new in PowerPivot. They are familiar territory for users of Excel who have worked with filters. Slicers are useful for filtering the data in Pivot charts and tables. The advantages which make slicers different from filters are the visual display of the data which is being filtered and the ability to link slicers to multiple charts and pivot tables with a single slicer if that data is linked in PowerPivot. Hence they are easier to use because they allow single point of control for filtering data from multiple sources.
We will walk through these features of linking data sources with in PowerPivot. In addition we will link slicers through the slicer options to have ability to slice our data sources from one slicer.
Installing PowerPivot
Establishing a PowerPivot environment requires deploying the PowerPivot for Excel® 2010 add-in on workstations running Microsoft Office 2010 and PowerPivot for SharePoint on SharePoint application servers.
These tasks are not dependent on each other. Users with only a web connection don’t need Excel 2010 on their machines to view PowerPivot work books deployed on the SharePoint server. However users that do need to create and deploy work books to the SharePoint infrastructure do need to have the Excel 2010 client.
Linking data within PowerPivot
My source in this example is an SSAS cube. I am pulling information on a hospital which wants to track the Average time a Surgeon will take to spend on a procedure in a year by their specialty. I am calling this table the AverageSurg_Timewith the following columns listed below:
You can see the sample data which I pull and the columns below:
We pull a second data set which is called Vendor_Cost. The VendorCost table will enable a hospital to view the Total Cost for a component which is purchased by a hospital for that particular Year and Procedure Type. The Vendor_Cost table is going to be pulled with the sample data and columns below:
Both tables have a field which we can use to filter data on for a Procedure Year. But there is no link between the two tables to know which Year we want to see both these by. If we have a slicer which we want to filter then we will need to add a separate slicer for each source. Hence we will need to link the Procedure Year in both tables to another dimension which will act as our intermediate table for linking the two sources. Both our AverageSurg_Time and the Vendor_Cost will link to this intermediate dimension. This is a ProcedureYeartable with a single column and it lists all Year values as listed below:
Procedure Year
We will create a link between the three tables in the PowerPivot Design options. First define a link with Vendor_Cost and the ProcedureYeartable as shown:
Next we define a relationship with the ProcedureYear table and the AverageSurg_Timetable.
Linking Slicers to the Pivot Tables
In the next step we will create our pivot charts. I create a few simple bar graphs which are listed below which sources data from Vendor_Cost and AverageSurg_Timeas two Pivot Charts:
The first one is Average Surg Time and the second is the Vendor Cost. Above, I have added a slicer and have used the Procedure Year as a source from the ProcedureYeartable. At this point the data in the Pivot table is linked in the underlying source properly. However we need to perform the link in the Excel presentation layer as well. The Slicer I defined is not linked to the Pivot tables in Excel. In order to link the slicer to the pivot tables we will do the following steps:
1. Select the slicer which you create above the Pivot charts and you will select the ‘Slicer Tools’ in the menu listing.
2. Select the ‘Options’ selection.
3. Then click the ‘PivotTable Connections’ option:
4. Link your Pivot tables in the PivotTable Connection screen to the slicers which you want to define the relationship for.
In my example I just have two Pivot tables which I want to link. which are being populated with the Sheet listed as
Data for PowerPivot Chart 1 and Data for PowerPivot Chart 7. The Sheet is the actual sheet in Excel which will host the PowerPivot data into Excel to build the PowerPivot table.
5. Select ‘Ok’ and you will have your data linked in the Presentation layer.
Now when you select the Procedure Year slicer it will filter down the data in the Pivot tables which I define in the slicer option.
Conclusion
The ability to link sources from a number of areas within the organization with relative simplicity is useful in many scenarios. In addition the ease of using slicers and linking them provides additional functionality to give your users ease of use in consuming content provided via Excel. This post illustrates just some of the ways we can leverage PowerPivot to extend our Excel workbooks. There are many other new features which extend the uses of Excel which can be delved into with more detail.
