With the inclusion of PowerPivot and Power View, Microsoft Excel 2013 is a first-class business intelligence (BI) tool that's capable of storing, processing, and visualizing vast amounts of data. I'll show you how PowerPivot and Power View work inside of Excel 2013. I'll also touch on the APIs that are available for automating PowerPivot tasks.
PowerPivot and Power View in Excel 2013
PowerPivot is an add-in that lets end users gather, store, model, and analyze large amounts of data in Excel. Power View provides intuitive data visualization of PowerPivot models and SQL Server Analysis Services (SSAS) tabular mode databases. If you're unfamiliar with either PowerPivot or Power View, I encourage you to first review my past SQL Server Pro articles listed in the Learning Path (at the end of the article) to understand the basics of how these technologies work.
In Excel 2013, PowerPivot and Power View are no longer separate add-ins that need to be downloaded and installed. These add-ins are natively included. PowerPivot in Excel 2013 is functionally very similar to the PowerPivot add-in for Excel 2010. Likewise, the version of Power View in Excel 2013 provides similar capabilities to the version of Power View available for Microsoft SharePoint 2013 or SharePoint 2010 (by means of SQL Server 2012 SP1).
With that said, there are a few differences to point out. First and foremost, there are architectural differences, which Colin Banfield illustrates in "Excel 2013: Implications for PowerPivot and Excel Data Import Users, Part I." Figure 1 shows the architecture of Excel 2010.
Figure 2 shows the architecture in Excel 2013, with the differences noted in red text.
As you can see, some parts of the PowerPivot architecture is embedded inside of Excel 2013. For example, there's the new Data Model, which I'll cover in-depth shortly. Other differences include:
- The PowerPivot version in Excel 2013 no longer uses a separate PowerPivot Fields list. Instead, the built-in PivotTable Fields list is used. This means that some capabilities from the Excel 2010 add-in (e.g., searching for fields by name, creation of slicers from the field list, surfacing of column descriptions when hovering over a field) are no longer available.
- Workbooks with PowerPivot models are no longer limited to 2GB in size in Excel 2013. However, the 2GB limit still applies to workbooks that will be published to SharePoint. For more information, see the sidebar "How Much Data Can PowerPivot Handle?" (located at the end of the article).
- In Excel 2013, a refresh of a PivotTable or PivotChart will, by default, initiate a refresh of the underlying data connections in the Data Model. This is very different from Excel 2010, where a PivotTable refresh only re-queries the model. The new refresh behavior can be changed by clicking Connections on the Data tab, selecting Properties, and clearing the Refresh this connection on Refresh All check box.
- In Excel 2013, a Power View "report" is a worksheet rather than an .rdlx file. There's no concept of multiple report views. Instead, multiple Power View worksheets can be created within a single Excel workbook.
PowerPivot and the Data Model
In the Create a Data Model in Excel web page, Microsoft describes a Data Model as "a new approach for integrating data from multiple tables, effectively building a relational data source inside an Excel workbook." To illustrate this approach, I'll show you how to use a standard PivotTable to analyze data from an Excel table, then pull in an additional table from an external source using the PowerPivot add-in. Assuming that you have Microsoft Office Professional Plus 2013, you can follow along with this example by downloading the sample workbooks. Note that in a default installation of Office 2013, the PowerPivot add-in is disabled. For now, don't worry about enabling the PowerPivot add-in. You'll enable it later.
Begin by opening the workbook named PP2013_Article_Example1_Start.xlsx, which contains a table of data representing the U.S. historical gross domestic product (GDP). Click the PivotTable button on the Insert tab to bring up the Create PivotTable dialog box. In it, enter GDP as the table to analyze and specify that you want to place the PivotTable in a new worksheet, as shown in Figure 3.
Notice the new Choose whether you want to analyze multiple tables option. Under it, select the Add this data to the Data Model check box.
Figure 4 shows the resulting PivotTable, which displays GDP by fiscal year (columns) and accounts (rows).
The accounts are useful for analyzing GDP areas, but there are also several accounts with duplicate names. Fortunately, all these accounts roll up into various account groups. The account groups aren't in the original table, but there's an external table containing them. How can you merge these two tables? Prior to Excel 2013, you would need to either manually copy and paste the accounts as new columns in the GDP table or copy the chart of accounts to a new Excel worksheet, then use the VLOOKUP function. In Excel 2013, this task is much easier because of the Data Model.
To begin, click somewhere outside the PivotTable. This is necessary when getting external data. If you don't do this, the necessary options will be disabled.
Next, in the Get External Data section of the Data tab, click the From Other Sources drop-down list and choose the From OData Data Feed option, as shown in Figure 5.
This will bring up the Import Data wizard. On the wizard's first page, enter the following data feed from my website—http://www.understandingtheusdebt.com/odata/WcfDataSvcUSDebtv2.svc—and select the table named USGDP_Accounts. On the last page of the wizard (shown in Figure 5), select the Only Create Connection option in the Select how you want to view this data in your workbook section. When you select this option, Excel will establish a connection to the OData feed and load the data into the Data Model, without displaying the actual table in the workbook. After the Import Data wizard has completed, click somewhere in the PivotTable to make its field list visible. There are now are two tables from which to select fields: the original GDP Excel table and the USGDP_Accounts table from the OData feed.
Drag the GDP_Level1 column from the USGDP_Accounts table to the Rows area in the PivotTable Fields list. The Relationships between tables may be needed notification will appear, as Figure 6 shows.
Because you have columns from two unrelated tables being used in the PivotTable, Excel is unable to properly calculate GDP. As you can see in Figure 6, all the AccountDescription values are displayed under each GDP_Level1 value, which is effectively an outer join. To fix this problem, click the Create button in the notification. In the Create Relationship dialog box, specify the relationship between the two tables, using the values shown in Figure 6. Click OK to save this relationship. The PivotTable now correctly groups the AccountDescription values into the appropriate GDP_Level1, as Figure 7 shows.
In case you're having trouble following along, I also included a finished sample workbook named PP2013_Article_Example1_Finish.xlsx.
Next, click Options on the File menu and click Add-Ins. Select COM Add-ins from the Manage drop-down list, click the Go button, and select Microsoft Office PowerPivot for Excel 2013. While you're here, also select Power View. Click OK to enable both the PowerPivot and Power View add-ins.
From the PowerPivot tab, click the Manage icon to launch the PowerPivot add-in. As Figure 8 shows, the GDP and USGDP_Accounts tables are already loaded.
You're probably wondering why. They're loaded because the in-memory, columnar compression storage engine (i.e., xVelocity) first introduced in PowerPivot for Excel 2010 is now the Data Model in Excel 2013. In other words, PowerPivot in Excel 2013 is an optional add-in (albeit a built-in add-in), but the Data Model uses the xVelocity engine regardless of whether the add-in is enabled.
This raises a few questions, the first of which is "Which approach should I take when loading and relating multiple tables: the new options on the PivotTable tab or the PowerPivot add-in?" The answer depends on your prior experience with Excel and PowerPivot. If you have a "classic" Excel background (and no experience with PowerPivot), leveraging the Data Model using the new options on the PivotTable tab along with the Import Data wizard should be a natural extension to your existing skills. If you're a seasoned PowerPivot professional, these options will likely seem circuitous. In this case, using the PowerPivot add-in will be a better and more direct route.
When deciding on your approach, you also need to take into consideration that there are several tasks that can only be accomplished through the PowerPivot add-in. They include:
- Creating calculated columns and measures (note that measures are now referred to as calculated fields)
- Creating hierarchies
- Defining Key Performance Indicators (KPIs)
- Hiding columns
- Adding column and measure descriptions
So what happens if you leverage some of these add-in only features and your co-workers open your workbook on their machines but they don't have the add-in enabled? They'll be able to take full advantage of these features, but they won't be able to make feature modifications. I believe this opens up some interesting and beneficial scenarios. Imagine a user who leverages the full capabilities of PowerPivot to create a rich Data Model, complete with measures, hierarchies, KPIs, and other features. The Data Model can then be consumed by other users through PivotTables, PivotCharts, and Power View without them having to see the Data Model's underlying complexities.
Power View and the Data Model
As I mentioned previously, Power View provides visualization of PowerPivot Data Models and SSAS tabular mode databases. Power View doesn't work directly on top of relational data or local Excel data, but Excel 2013 can create a data model on the fly when needed. To see this in action, follow these steps:
- Use the PowerPivot add-in to remove the tables from the Data Model in the sample workbook PP2013_Article_Example1_Start.xlsx.
- In the GDP worksheet, highlight somewhere inside of the GDP table, and click Power View on the Insert tab. A new Power View worksheet will be created, as shown in Figure 9.
- Click the Manage button on the PowerPivot tab to open the PowerPivot window. Notice that Excel added the GDP table to the Data Model.
As this demonstration shows, from an end-user perspective, Power View is simply a new visualization on top of local Excel data. However, there's always a Data Model behind the scenes.
To insert an additional Power View worksheet, you can simply click Power View on the Insert tab. The Data Model will automatically be loaded into the Power View fields list. (If you select the GDP table prior to inserting the worksheet, it won't be added to the Data Model a second time.)
Inserting a Power View worksheet that connects to an SSAS tabular mode database is also simple. In the Get External Data section of the Data tab, click the From Other Sources drop-down list and choose the From Analysis Services option. On the Import Data wizard's first page, enter the connection information to the SSAS database. On the last page of the wizard, choose the Power View Report option.
Note that a Power View worksheet is a standalone object—in other words, the Power View visualizations can't be placed in a traditional Excel worksheet alongside other items like PivotTables and PivotCharts. In addition, unlike the version of Power View hosted in SharePoint, a Power View worksheet can't be exported to Microsoft PowerPoint. If you want to play around with a comprehensive Power View example, you can download my Excel 2012 U.S. Debt Workbook.
PowerPivot Automation
I've always been a fan of Excel macros for task automation, so I was excited to hear PowerPivot was a "native component" of Excel 2013. (PowerPivot for Excel 2010 doesn't expose any supported APIs.) However, only certain automation scenarios are currently available—primarily those relating to the storage aspects of the xVelocity engine. According to the documentation in What's New for Excel 2013 Developers, the "new DataModel object model (an addition to the existing Visual Basic for Applications object model) enables you to load and refresh data sources programmatically." DataModel introduces four primary objects: Model, ModelChanges, ModelRelationship, and ModelTable. In addition, the existing Connections object in the Excel object model has been updated to allow for integration with the DataModel object model.
For example, suppose you want to:
- Refresh the existing Data Model.
- Add a new table to the Data Model.
- Create a relationship between one of the existing tables and the new table.
Listing 1 contains a macro that uses the Model object in the DataModel object model to accomplish these steps. Specifically, the macro calls Model.Refresh method, adds a connection to the workbook, then uses the CreateModelConnection argument to add the DateTbl table to the Data Model. Finally, the macro creates a relationship between the DateTbl and GDP tables. A working copy of this macro is available in the sample workbook PP2013_Article_Example1_WithMacro.xlsm.
Sub AddDateTableToModel()
On Error GoTo The_Error
' Refresh the existing model.
ActiveWorkbook.Model.Refresh
' Add a connection to the Data Model.
Dim oWorkbookConn As Excel.WorkbookConnection
Set oWorkbookConn = ActiveWorkbook.Connections.Add2( _
Name:="DateTable", Description:="Date Table from " & _
"www.UnderstandingTheUSDebt.com", ConnectionString:= _
Array("DATAFEED;Data Source=http://www.understanding" & _
"usdebt.com/odata/WcfDataSvcUSDebtv2.svc/;Namespaces" & _
"to Include=*;Max Received Message Size=439804651110" & _
"4; Integrated Security=SSPI;Keep Alive=true;Persist" & _
" Security Info=false;Service Document Url=http://" & _
"www.understandingtheusdebt.com/odata/WcfDataSvcUS" & _
"Debtv2.svc/ "), CommandText:=Array("DateTbl"), _
lCmdType:=6, CreateModelConnection:=True, _
ImportRelationships:=False)
' Add a relationship between the Date and GDP tables.
With ActiveWorkbook.Model
.ModelRelationships.Add .ModelTables("GDP"). _
ModelTableColumns("Date"), _
.ModelTables("DateTbl").ModelTableColumns("Date")
End With
Exit Sub
The_Error:
MsgBox "Error Encountered: " & Err.Description & _
vbTab & Err.Source, vbCritical, "Error."
End Sub
You could also use the Model object to provide "smart" refresh of workbook data. For example, you could write a macro that would evaluate the date when a particular data connection was last queried, then initiate a refresh based on whether new data is available from the source connection.
In the future, I hope other parts of the PowerPivot add-in are exposed in the Excel object model. There are several additional functionalities (e.g., auto-creation of time-based calculations such prior-period, period-to-date, and moving-average calculations) that could benefit from automation.
A Big Step Forward
Excel 2013 is a big step forward with respect to BI. The native integration of PowerPivot and Power View provides a whole new level of scalability and functionality. These two technologies also provide a greater degree of consistency in terms of how IT professionals and business users gather, store, model, analyze, and share information.
Sidebar: How Much Data Can PowerPivot Handle?
A question I often get asked is "How much data can PowerPivot handle?" My answer is "It depends." The number of rows is no longer the limiting factor. The limiting factor is the amount of memory available to Microsoft Excel. PowerPivot uses columnar compression to efficiently store data on disk, but a PowerPivot workbook will consume more memory than what is used for on-disk storage.
To avoid memory errors, my rule of thumb with a 32-bit version of Excel (which is limited to 2GB of addressable memory) is to make sure the workbook is less than 250MB when saved to disk. Larger workbooks will likely need the 64-bit version of Excel.
Learning Path
SQL SERVER PRO RESOURCES
For more information about PowerPivot, see
"A Walkthrough of PowerPivot for Excel 2010"
"What's New in Microsoft SQL Server 2012 PowerPivot"
For more information about Power View, see
"Introducing Microsoft Power View"
"Getting the Most Out of Microsoft Power View"
OTHER RESOURCES
For an example of content that leverages both PowerPivot and Power View, see
Tyler Chessman's Excel 2013 U.S. Debt Workbook
For more information about Excel 2013, see
"Excel 2013: Implications for PowerPivot and Excel Data Import Users, Part I"
What's New for Excel 2013 Developers
