PowerPivot is a powerful tool which gives users the ability to pivot data on the fly from multiple data sources. However, using PowerPivot can be a little daunting at first, especially if you haven’t used pivot tables before. Here are 5 simple tips for users new to PowerPivot.
Tip #1. Client Software required to build PowerPivot Cubes from Excel
Excel 2010 or Excel 2013 is required to connect to an Analysis Server. However, If the user wants to build and run PowerPivot from Excel, and the machine in question has Excel 2010 installed, then an add-in is also required.
Cube designers that have Office 2010 (or Excel 2010) must also install an add-in to use PowerPivot: Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010 (http://www.microsoft.com/en-us/download/details.aspx?id=29074)
Tip #2. Connecting to the Tabular cube on the Analysis Server
PowerPivot can be configured primarily to run with two different architectures. It can be configured to run on the client machine from Excel or it can be deployed to an Analysis Server where the user uses Excel to communicate with the server. A user that is new to PowerPivot can follow these steps when connecting to the Analysis Server:
1. Open up Excel.
2. Choose Data Tab and then From Other Sources choose “From Analysis Services” menu item.
3. In the Data Connection Wizard enter the <Server Name> in the Server Name text box and press Next.
It is recommended to use the fully qualified machine names when connection to the server on a different domain or using a remote connection (e.g. YourSSASServerName.YourNetworkName.com)
Also, if the Analysis Server is running on a specific port you will append this to the end of the server name (e.g. SomeServerName.YourNetworkName.com:PortNumber). Usually, your system administrator will supply this information if a port number is required.
4. Select the database that contains the data you want and select the specific cube or table & press Finish.
Tip #3. Pivot on Dimension Fields
Dimension fields are used to slice-and-dice (pivot) the data. Dimensions help categorize measurements so that the reporting user can pivot the data and gain a better understanding of their business. The dimension fields can be found under the Dimension Tables in the “PivotTable Fields” section on the right-hand side of Excel as noted by the following icon .
A reporting user can add dimension fields to the report by dropping the PivotTable Fields in the ROWS or COLUMNS section of the Pivot as indicated in the lower right hand quadrant of the image below.
When building a report from scratch, all dimension values will display in the report until a measure is added to the VALUES section. The data in the PowerPivot report will not be filtered until a measure is added. As displayed in the screen shot above, all resource names will be displayed at first.
Tip #4. Filter the Dimension Values
If there are a lot of Dimension values, or you don’t want to see some values, just drop down the list box for the Columns Labels or Row Labels and deselect the values you don’t want to see. All of the data associated with these Resource names will not be shown.
Tip #5. Add a Measurement to your Report
The Fact tables primarily store fields that can be quantified (measured) in terms of numbers, like a Percent, Count, Distinct Count, Sum, Average, Dollar Amount and Decimal. To add a measure, drag the field configured to be a measurement field from the PivotTable Fields section and drop it in the VALUES area as noted by the following icon
These are just a couple of tips for new PowerPivot users. Using PowerPivot is fun and easy, and users can quickly pivot reporting data on the fly once the machine can establish a connection to the reporting server. With a little practice, a reporting user can quickly gain better insight into their business.
More questions about PowerPivot, or business intelligence in general? Contact us today at firstname.lastname@example.org.