PowerPivot – Personal BI on your desktop

What do you think about business intelligence (BI)? A lot of people believe BI is only for large enterprise because it is expensive, takes long time to implement and requires special trained consultants. The worst part is that it does not often deliver what it promises to do – real time, self-service information delivery. Even there is BI within the organization, many analysts still heavily rely on their most trusted software, Excel to do their analysis because their corporate BI just does not provide everything they want or they are not familiar with the new BI environments. We want a BI system that we can manage and ready to use tomorrow.

PowerPivot may be the solution that we have been looking for; it is a free Excel Add-in from Microsoft and if you know pivot table then we can start using it right away. And it is much more powerful than Excel. We can download it from http://www.microsoft.com/en-us/download/details.aspx?id=29074. Once we install PowerPivot, we can download an example workbook from Microsoft, http://www.microsoft.com/en-us/download/details.aspx?id=28572. There are 7 tabs in the workbook. Let’s examine the first one; SalesByCountry which you will find a very simple pivot table.

You can also see a slicer that you can use to select sale channels:

And a PowerPivot Field List (it is very similar to pivot table field list):

When I showed this workbook to my colleagues, they often said “Hold on here! Is this just a regular Excel Pivot Table?” Well, this is the beauty of PowerPivot; it is an extension of Excel Pivot Table. If you know Pivot Table, then you can start using PowerPivot. The major difference is the data behind it. You can analyze much, much more data with PowerPivot. How much more? There are 2 fact tables and 10 dimension tables in this workbook. FactSales has 3.4 million records and FactInvesntory has just over 8 million records. MILLIONS! PowerPivot can handle millions of records; this is the beauty of PowerPivot. One of the news features in PowerPivot 2012 is Diagram View which allows us to easily add and change relationships of the tables. I like this new feature because BI often includes hundred or even thousands of tables; it is great to visualize them.

You can see the relationship between the tables if you click on the arrow line.

 

i.e. FactSales and DimStore are connected by StoreKey. Let’s go back to our first PivotTable; it is a very simple PivotTable which only has 4 elements.

• Slicers Vertical – ChannelName (DimChannel)
• Column Labels – CalendarYear (DimDate)
• Row Labels – RegionCountryName (DimGeography)
• Values – Sales (FactSales)

If you right click on the first 3 elements, you will see their Field Settings:

 

 

The last one, Sales has its own Edit Measure dialogue because it is a Measure Field:

 

 

It has a very simple formula: =SUM(FactSales[SalesAmount]), i.e. sum the SalesAmount from FactSales. There is a fundamental difference between PowerPivot and Excel; the basic element in PowerPivot is column so all the formulas in PowerPivot are against columns rather than cells. Let’s review the data behind this report by clicking PowerPivot Window button.

 

 

PowerPivot for Excel will be displayed and we can see all the data behind all these reports. DimDate has 2,556 records and many data columns that describe the date information.

 

 

FactSales has over 3.4 million records; it has many data columns and other important information. Have you noticed the first half of the screen has all the data columns but there is something else at the bottom:

• Sales
  
• StoreSales
  
• PctAllRegions
  
• YOYGrowth
  
• And more….

What are these rows?

 

 

If we go back to our first report SalesByCountry and look at the PowerPivot Field List under FactSales, you would notice some of the fields have a calculator icon next to them. These are the measure fields; each has its own formula.

 

• Sales:=SUM(FactSales[SalesAmount])
  
• StoreSales:=CALCULATE([Sales],DimChannel[ChannelName]=”Store”)
  
• PctAllRegions:=[Sales]/CALCULATE([Sales] ,ALL(DimGeography))
  
• YOYGrowth:=[Sales] – [Sales](PREVIOUSYEAR(DimDate[Datekey]))
  
• PrevYearSales:=IF( COUNTROWS(VALUES(DimDate[CalendarYear]))=1 , [Sales] (PARALLELPERIOD(DimDate[Datekey],-12,MONTH)), BLANK() )
  

These are not Excel formula; they are something new that we need to learn if we want to maximize PowerPivot’s potential. They are called Data Analysis Expressions (DAX) Language which I will discuss it in near future. But even without DAX, PowerPivot allows us to handle analysis on data volume that is impossible on Excel before and it is much faster! If you would like to find out more about PowerPivot, please stay tuned, I am going to discuss more about DAX, how to import data into PowerPivot. PowerPivot offers us the opportunity to have our own personal BI on our desktop. It has been available since the release of Excel 2010 and recently Microsoft has a new version, PowerPivot 2012 which includes many great new features that will make our analysis better and faster!