PowerPivot – Part 2: Creating A Basic Report

Continuing the PowerPivot series, I’ll now attempt at creating my first PowerPivot report. After opening Excel, first thing we need to do is getting ourselves a data set to work with. To do so, I’ll click on the “data” tab and choose “From Other Sources” button and connect to my local SQL Server instance, to the “AdventureWorksDW2008” database. Running through a familiar set of wizard screens (similar to the Excel 2007 wizard), where I’m prompted to choose a database, and get the option to choose a specific table or leave the connection “table-agnostic”. I’ll choose the option to be able to pick later which tables to use and the next screen prompts me to save the connection settings and choose a friendly name to it (similar to  the option you may be familiar with when you create a DSV in Analysis Services). Note that you do not have to use data sources from an Excel sheet like this, you can directly connect to a data source from a PowerPivot window as well.

Next, I need to choose the tables I’ll be using to create the PowerPivot report with. I’m choosing DimDate, DimCustomer and FactInternetSales. Each table resides on a separate Excel sheet and this is how my Excel file looks like after this operation completes:

Now I need to get these tables into my PowerPivot report. I’m clicking on the “PowerPivot” tab and choosing the “Create Linked Table” button. Doing so for each of the three Excel sheets I’ve created will eventually link the three tables to a PowerPivot window where I could start creating my report.

Before I’ll create my report, I want to let PowerPivot know the relationships that exist between the three tables. I’m clicking on the “Table” tab and the “Create Relationship” button in order to define the relationships. Then I create the relationships between the two dimension tables (customers and date) and the fact table (Internet Sales).

Now we’re ready to create our first PowerPivot report. I click on the “PivotTable” button in the PowerPivot window and choose the “Single PivotTable” option. This opens up a new Excel window with the option to choose slicers, filters and values for my report:

I create a trivial report which sums order quantity by date and gender on the vertical and horizontal slicers, and add yearly income groupings as the row labels. The report looks like this:

I can choose to filter by a specific date, a date range, and the same goes for each slicer I picked to participate in the report. It all looks very user friendly and easy to use, yet has the new slicers feature which wasn’t there in the “old” Excel 2007 pivot table interface. So we have created a basic PowerPivot report, but haven’t even begun to scratch the surface of what PowerPivot is really all about. On the next few blog posts I’ll drill down further and look at the more advanced PowerPivot options, using the new DAX expression language and examine performance aspects of the product.

-Yaniv

(This is post number 8 for week number 7 of my SQL blog challenge)