What's New in Microsoft SQL Server 2012 PowerPivot

SQL Server 2012 includes a new release of PowerPivot for Excel 2010 and PowerPivot for SharePoint. PowerPivot for Excel is an Excel add-in that lets users gather, store, model, and analyze data. PowerPivot for SharePoint, which is actually an instance of SQL Server Analysis Services (SSAS), lets users easily publish and share their PowerPivot workbooks. I'll discuss the key new capabilities in the SQL Server 2012 version of PowerPivot for Excel and touch on a few of the related enhancements in PowerPivot for SharePoint and SSAS.

Upgrade Considerations

The PowerPivot for Excel 2010 add-in can be upgraded by running the PowerPivot_for_Excel.msi installation file. There's both an x86 and x64 version. You need to download and install the version that corresponds to the version of Excel on your machine. You can download the PowerPivot for Excel 2010 add-in from the Microsoft website.

PowerPivot makes it possible to work with millions of rows inside of Excel. However, the x86 version of Microsoft Office limits Excel, and therefore PowerPivot, to 2GB of addressable memory. Based on my experience, memory errors might occur with data sets as small as 400MB. So, if you need to work with very large data volumes, plan on leveraging the x64 build of Office. (For more information about this limitation, see the PowerPivotGeek blog post "How much data can I load into PowerPivot?")

PowerPivot for SharePoint can be upgraded by running the Microsoft SQL Server 2012 setup program on the SharePoint application servers that run the SQL Server Analysis Services (PowerPivot) service. After the upgrade completes, you use the PowerPivot Configuration Tool to complete the upgrade process. Note that any deployed workbooks won't be upgraded. They'll continue to work using the features available in the initial release.

To upgrade an existing workbook, you must open it in the Excel client. As soon as the PowerPivot window is open (or if a PivotTable or PivotChart is selected), the PowerPivot add-in will ask whether you want to upgrade the workbook. After an existing PowerPivot-enabled Excel workbook is upgraded to the 2012 version, you can't downgrade it back to the earlier version, nor can you use the earlier version of the PowerPivot add-in to work with it. You should consider backing up a workbook before upgrading it, especially if you need to continue sharing the workbook with users who haven't upgraded their PowerPivot add-in.

New Capabilities in PowerPivot for Excel

There are many new capabilities in the SQL Server 2012 version of PowerPivot for Excel. The TechNet web page What's New in PowerPivot 2012 provides a complete list of them. Some of the more notable new capabilities include the following.

An additional place to define measures. In the initial release of PowerPivot, calculated measures are created and managed from the PowerPivot Field List. This option is still available in the 2012 version, but you can also create and manage measures from within the PowerPivot window, as Figure 1 shows. Like in an Excel worksheet, you can create measures in any cell underneath the table area in the PowerPivot window. You can also mark measures as hidden from the UI, which is nice when you have several intermediate measures that you don't want exposed in the PowerPivot Field List.

Figure 1: Creating and managing measures in the PowerPivot window 

Numeric formatting. Although the first release of PowerPivot supported formatting numbers from within the PowerPivot window, these formats didn't carry forward into PivotTables and PivotCharts. Now, the formatting of both columns and calculated measures are recognized and automatically displayed. From my experience, this seemingly minor new feature will save hours of time over the long haul and minimize confusion when analyzing certain data types (e.g., decimals versus percentage values).

Diagram View. When working with a small number of tables, the standard Data View works well. Beyond 8 to 10 tables, it can be difficult to visualize a model and the various relationships between tables. As Figure 2 shows, the new Diagram View provides a visual representation of tables and their relationships and is similar to a Data Source View in SSAS. The Diagram View is also where hierarchies are created and maintained.

Figure 2: Visualizing table relationships in the Diagram View

Hierarchies. Hierarchies are created in the Diagram View of the PowerPivot window by dragging and dropping columns. For users, hierarchies provide an easy way to explore and aggregate measures (see Figure 3) without having to know the relationship between columns.

Figure 3: Creating hierarchies in the Diagram View 

There are a few limitations with hierarchies in PowerPivot 2012 compared with those in SSAS. In PowerPivot, all the columns in a hierarchy must come from a single table. If you want to create a hierarchy from two related tables (e.g., Country and Province), you first need to combine the tables before loading them into PowerPivot. In addition, only user-defined hierarchies can be created. Parent-child hierarchies can't be defined. (A common example of a parent-child hierarchy is a financial chart of accounts in which each child account is related to a parent account through a self-referencing foreign key.) With that said, PowerPivot 2012 provides a new set of Data Analysis Expressions (DAX) Path functions to query child nodes in a parent-child hierarchy and calculate parent-child related measures. Alternatively, you can flatten parent-child hierarchies into an explicit user-defined hierarchy before importing them into PowerPivot. For an example of this technique, take a look at the USGDP_Accounts table in my sample 2012 workbook at the Understanding the United States Debt  website.

Key Performance Indicators (KPIs). You can create KPIs from an existing measure by defining them from either the Excel ribbon or the PowerPivot window. As shown in Figure 4, the measure becomes the base value for the KPI. A target is specified using either an absolute (i.e., static) value or an existing measure.

Figure 4: Creating a KPI 

Unlike SSAS, PowerPivot doesn't let you use a trend value in the KPI definition. The KPI status threshold, which compares the actual KPI value against its target to calculate a status, can be set up in one of four ways:

In the Excel client, a KPI is denoted in the PowerPivot Field List with a multi-color indicator, as Figure 5 shows. The original measure is turned into an expandable container.

Figure 5: Reviewing KPIs in the PowerPivot Field List 

DAX functions. In addition to the DAX Path functions I already discussed, several new statistical, filtering, and informational DAX functions are introduced in PowerPivot 2012, including the highly requested Rank function. Some of the DAX functions have direct Excel function counterparts (e.g., IsError), whereas others originate from SSAS MDX functions (e.g., ParallelPeriod).

Show Details option. You can drill down from a PivotTable cell to a new worksheet displaying the underlying data that contributes to the value of the specified cell. To access this capability, you just need to double-click the cell, or right-click the cell and select the Show Details option.

Advanced tab. From the PowerPivot window, you can access the new Advanced Tab by selecting the Switch to Advanced Mode option on the File menu. The Advanced tab exposes several new capabilities, including perspectives. Just like an SSAS perspective, a PowerPivot perspective filters a cube to display only a specific set of tables and table columns within the Pivot Field List. Two sets of reporting properties -- default field set and table behavior -- are also available. Neither of these property sets have any impact within Excel, but they can be leveraged if an Excel workbook is published to SharePoint. I cover default field sets and table behavior in "Introducing Microsoft Power View" (June 2012). Power View is a new reporting tool in SQL Server 2012.

Enhancements in PowerPivot for SharePoint and SSAS

As I mentioned previously, PowerPivot for SharePoint is an instance of SSAS that runs as an integrated service within a SharePoint farm. The major enhancement in PowerPivot for SharePoint 2012 is the ability to support the new PowerPivot 2012 workbooks. Another important enhancement is the addition of the PowerPivot Configuration Tool. Used for both upgrades and new installations, this tool greatly simplifies the setup process, even in existing SharePoint farms. If you tried in the past to install PowerPivot for SharePoint into an existing farm or had to troubleshoot a failed installation, you'll greatly appreciate this new tool.

The major change in SSAS 2012 is the introduction of a deployment option called Tabular mode. An instance of SSAS running in Tabular mode effectively leverages the same in-memory cube architecture of PowerPivot, but it's geared toward SQL Server pros. Instead of using Excel, SQL Server pros use SQL Server Data Tools (i.e., the new name for Business Intelligence Development Studio, or BIDS, in SQL Server 2012) to build and deploy cubes. Because the cubes are deployed straight to an instance of SSAS, they can be much larger than the current limit of 2GB imposed on Excel workbooks.

In addition to scalability, the Tabular mode is a game changer in that it makes building cubes much more accessible to the average SQL Server pro. (The traditional multi-dimensional mode of building cubes is still supported.) Plus, with the tabular mode, workbooks can be converted into Tabular mode cubes, which provides an easy migration path if a PowerPivot workbook needs to be taken over by a SQL Server team.

Explore PowerPivot Further

I just covered what's new in PowerPivot 2012. If you're not too familiar with this technology and I've piqued your interest, you can learn more about it in "A Walkthrough of PowerPivot for Excel 2010" (September 2010) and "Introducing PowerPivot for SharePoint" (July 2011). In addition, you can see a working example of PowerPivot 2012 at the Understanding the United States Debt website.