Last week I took a stab at creating my first PowerPivot spreadsheet, and I wanted to document the steps. Maybe this post will help someone, but it will help me remember, that is for sure!
Before you get started, you will need to download all the software required. Here is my list:
Of course, you will need and instance of SQL Server 2008. You can just use Express edition if you do not have one installed.
Now that I have everything installed and configured, I can start with PowerPivot. One note – look for Hand shaped cursor in the screens below.s
Step 1 – Start up Excel 2010 and look for PowerPivot menu, then click on PowerPivot Window to launch pivot.
Step 2 – click on From Database in the window, then Select from SQL Server
Step 3 – Fill in SQL Server details. I am choosing to use Data Warehouse DB. Click Next when done. On the next screen select an option to Select for a list of tables and views.
Step 4 – Select dimension tables and fact table to analyze. Here is my list in alphabetical order
- DimCustomer
- DimDate
- DimProductCategory
- DimSalesTerritory
- FactInternetSales
You can select any tables you like of course. Wait while data is imported into PowerPivot, then click Close. PowerPivot window will now have five tabs with data and columns directly imported from the database. Here is what it looks like.
Now click on PivotTable menu and select Chart and Table – Vertical option. We will simply create a chart and an analysis table to go with it. Click on New Worksheet when prompted. This step will return you back to main Excel window. You will see chart, table and on the left hand side Gemini task pane – listing of imported data. Gemini used to be Microsoft code name for this product/functionality.
Step 5 – Building a chart. We are going to analyze sales based on customer marital status by linking it to sales amount. Click on the chart first, then in Gemini pane expand DimCustomer table and select MaritalStatus. Then, expand FactInternetSales and select SalesAmount. PowerPivot will guess what we are trying to do and MaritalStatus as Axis field and Sum(SalesAmount) as Values. Here is what it looks like:
Step 6 – building table with filters.
Click on table first, then check the following tables/fields in Gemini pane:
- DimCustomer – MaritalStatus and Gender
- DimDte – CalendarYear
- DimProductCategory – EnglishProductCategoryName
- DimSalesTerritory – SalesTerritoryCountry
- FactInternetSales – SalesAmount
At this point your bottom of Gemini pane should look like this:
if you have anything else in Values, click on that item and select “Move to Row labels”. What you will see also is that your have a report built for you in table area that uses all the analysis points. You could leave it as is, but I will do more.
Step 7 Now I can analyze the data by “slicing it”. To do this we will create two groups of slices – horizontal and vertical. We will split our analysis categories as follows:
- Horizontal Slices: Marital Status and Gender
- Vertical Slices –English Product Category and Calendar Year.
I will leave Sales Territory country as a column in my table. To perform this I will click on each of my slices and select “Move to Slices Horizontal (or Vertical) as appropriate. Then I click on each slice and choose Move Up or down until my pane looks like this:
Now here is what my table looks:
I will go ahead and move things around to make spreadsheet look better:
Step 8 – Use slices for analysis. This process is super simple – just click on a desired slice(s) to enact filter on rows of a table.
To clear the filter just click on a funnel picture with x across it:
As you do so, you will see the chart and table both updating with your filter values. One thing I did notice at the end – Catergory is lon linked to internet sales, so category filter has no effect on data. If you see similar behavior, your data has the same issue. You can always manually build relationships in PowerPivot by click on Table tab in pivot and choosing Manage Relationships menu:
As you can see, using PowerPivot is extremely easy and the results are very powerful. You can save your spreadsheet, open it later and refresh the data by clicking on a filter! You also have an option to show or hide Gemini panels in Excel under PowerPivot menu. In the same menu you also have an option to show or hide Gemini panels.
Thanks.
