PowerPivot 2012, Part 1

With the release of SQL Server 2012, we also got a new version of PowerPivot for Excel.  There have been many features added, too many to cover in one sitting at least in any detail.

One of my favorites is the new diagram view that let you visually look at not only the tables that you are bringing into your solution, but also the relationships between those tables.  If you only work with a small number of tables, you may not appreciate the benefit this feature brings.  However, when you have models that include more than a half dozen tables or so, the diagram view of the tables helps visualize and understand the relations between them.  As I’ve often said in presentations, one of the keys to understanding your pivot table model is understanding the relations between the tables that make it up.  This capability has existed in SSAS, so I’m glad to see it brought to PowerPivot.

Another feature I really like is the Calculation Area that allows you view measures in a free form data grid.  This feature allows you to create, view, and otherwise manage your measures and Key Performance Indicators (KPIs).   Yes, KPIs are now a part of PowerPivot allowing you evaluate the performance of selected measures and display their status creating more of a BI dashboard feel for management to quickly determinate the status of the organization.  I will take a look at both of these new features in a future blog.

Perspectives, another SSAS feature now brought to PowerPivot, allows you to define a subset of a larger model to focus on specific area.  In addition, because a Perspective works with a subset of data, interaction and navigation with the data in the pivot table is enhanced.

One of my SQL Saturday presentations has been how to create a date table dimension from your existing fact tables.  In PowerPivot 2012, you can now mark this table specifically as a date table and take advantage of some built in Date Filters that were sadly missing from the earlier version of PowerPivot.  For example, you can now filter on dates after, before or between a date range.  In the past, you could do this only by creating additional calculated fields in your date table to identify which rows to use for special filters.  You no longer need these workarounds.  I’ll get back to this one in a minute.

A cool feature is the Show Details option that appears in the popup menu when you right-click in a pivot table cell.  It pops open a new spreadsheet that shows the underlying data used to create that cell.  This can be extremely useful for not only validating that pivot table, but also researching why specific cells in the pivot table have certain values.

There is a lot more, but I want to get into at least a small demo today of one of the features.  I am going to give you a look at using date tables and the built in filtering.  I’ll specially look at why you might want to use this feature.  Before we start, here are some of the pre-requisites for PowerPivot 2012.

If you already have PowerPivot for Excel 2010 installed on your machine, you can simply upgrade it using the .msi file from the PowerPivot Site.  If you have never used PowerPivot before, but you have Microsoft Office 2010 installed with Excel, you can install PowerPivot using the same .msi download.  There are two versions of PowerPivot 2012, just like in 2010.  There is a x86 version and a x64 version.  The version of Microsoft Office you have installed determines the version of PowerPivot 2012 you should use.  If you have the 64-bit version of Microsoft Office, you must use the x64 version of PowerPivot.  Similarly, the 32-bit version of Microsoft Office can only use the x86 (32-bit) version of PowerPivot.  The 64-bit version supports substantially larger pivot tables but if you are using the Data Mining Add-in for Excel, you must stay with the 32-bit version.  You also need Microsoft .NET Framework 4.0 and the Visual Studio 2010 Tools for Office Runtime and Office Shared Features.

Using the Contoso sample dataset for PowerPivot 2012 found here, I selected the DimDate table and then select Mark as Date Table from the Design ribbon as shown in the following figure.

A Date Table must have a column that is of the date data type and is unique for each record.  I happen to know that the DateKey field in DimDate fits this requirement.

Now when I build a pivot table and use data from the DimDate table (and it does not have to be the DateKey either.  It can be any of the dimension attributes.  I can use the dropdown menu associated with the field.  In the example below, I did use DateKey, but remember that it can be any attribute in the dimension.  In the menu is a new option: Date Filters.  Click on this field to display the fly-out menu.  From this menu, you select a variety of different date filter criteria.  Some of these will not work with the Contoso sample data because for some reason, Microsoft decided not to update the dates in the Contoso data set.  Therefore, filters like Tomorrow, Today, Yesterday, Next Week, etc. will have no meaning and will return no results.  However, we can use the Before, After or Between options.

One of the options near the bottom of the list also includes some interesting filters for dates within a quarter or month.  Feel free to try any of these filters on your own date data.

For the Contoso data, I chose the Between filter which prompts for the date range using the following dialog.

When you press OK, the resulting pivot table will only display data for that date range.  If you are displaying a date attribute like week or month, only the first couple of weeks in 2007 or the month of January in 2007 appear.

In the following figure, I am displaying sales by week beginning with the year 2007.  Suppose I wanted to see the details that make up the sales for the first week of the year.  Right click on the SalesAmount sum and from the popup menu, choose the new option Show Details as shown in the next figure.

This menu command opens a new sheet in the Excel workbook and displays all of the rows from FactSales for the first week of 2007.  You can review the data to evaluate why the sales for the first week appears lower than other weeks.

Before ending this week, return to the PowerPivot window and select the Diagram view from the Design ribbon as shown below.

The following diagram appears to show graphically the relations between the tables.  While not shown in this diagram, go ahead and right click on any of the relationship lines connecting any two tables.  The popup dialog has three options: Delete, Mark as Inactive, Edit Relationship.  The first option’s action is obvious, it deletes the relationship.  The second option is used when you have more than one relationship defined (I will talk about having multiple relations between tables in a future bog, but think of Fact Tables with multiple dates with each needing a relationship to the DimDate table.  Finally, the Edit Relationship option allows you to edit the relationship using the same dialog used when creating new relationships.

Well, I am going to stop there for today, but there are plenty of other new features in PowerPivot 2012 to cover in future blog entries.

Oh, one last thing.  If you open existing PowerPivot files from 2010 in 2012, PowerPivot 2012 automatically converts them to 2012.  That’s great, but you may want to keep a backup of the PowerPivot Excel file in 2010 for those users who have not yet upgraded.

C’ya next time.