to Excel data
Over the past decade or so, corporate managers, eager to turn impossible amounts of data into useful information, drove the business intelligence (BI) industry to innovate new ways of synthesizing data into meaningful insights. During this period, organizations spent lots of time and money implementing big enterprise reporting systems to help keep up with the hunger for data analytics and dashboards.
Recognizing the importance of the BI revolution and the place that Excel holds within it, Microsoft proceeded to make substantial investments in improving Excel’s BI capabilities. It specifically focused on Excel’s self-service BI capabilities and its ability to better manage and analyze information from the increasing number of available data sources.
The key product of that endeavor was essentially Power Pivot (introduced in Excel 2010 as an add-in). With Power Pivot came the ability to set up relationships between large, disparate data sources. For the first time, Excel analysts were able to add a relational view to their reporting without the use of problematic functions such as VLOOKUPS. The ability to merge data sources with hundreds of thousands of rows into one analytical engine within Excel was groundbreaking.
With the release of Excel 2016, Microsoft incorporated Power Pivot directly into Excel. The powerful capabilities of Power Pivot are available out of the box!
In this chapter, you get an overview of those capabilities by exploring the key features, benefits, and capabilities of Power Pivot.
Understanding the Power Pivot Internal Data Model
At its core, Power Pivot is essentially a SQL Server Analysis Services engine made available by way of an in-memory process that runs directly within Excel. Its technical name is the xVelocity analytics engine. However, in Excel, it’s referred to as the Internal Data Model.
Every Excel workbook contains an Internal Data Model, a single instance of the Power Pivot in-memory engine. The most effective way to interact with the Internal Data Model is to use the Power Pivot Ribbon interface (see Figure 2-1).
FIGURE 2-1: The Power Pivot Ribbon interface.
The Power Pivot Ribbon interface exposes the full set of functionalities you don’t get with the standard Excel Data tab. Here are a few examples of functionality available with the Power Pivot interface:
You can browse, edit, filter, and apply custom sorting to data.
You can create custom calculated columns that apply to all rows in the data import.
You can define a default number format to use when the field appears in a pivot table.
You can easily configure relationships via the handy Graphical Diagram view.
You can choose to prevent certain fields from appearing in the PivotTable Field List.
As with everything else in Excel, the Internal Data Model does have limitations. Most Excel users will not likely hit these limitations, because Power Pivot’s compression algorithm is typically able to shrink imported data to about one-tenth its original size. For example, a 100MB text file would take up only approximately 10MB in the Internal Data Model.
Nevertheless, it’s important to understand the maximum and configurable limits for Power Pivot Data Models. Table 2-1 highlights them.
WHERE’S THE POWER PIVOT TAB?
Organizations often install Excel in accordance with their own installation policies. In some organizations, Excel is installed without the PowerPivot add-in activated, so the Power Pivot tab won’t be visible. If you don’t see the Power Pivot tab shown in Figure 2-1, you can follow these steps to activate it:
1 Go up to the Excel Ribbon and choose File ⇒ Options.
2 Select the Add-Ins option on the left.
3 From the Manage drop-down list, select COM Add-Ins and click Go.
4 In the list of available COM Add-Ins, check the box next to Microsoft Office Power Pivot for Excel and click OK.
5 If the Power Pivot tab doesn’t appear on the Ribbon, quit and restart Excel.
TABLE 2-1 Limitations of the Internal Data Model
| Object | Specification |
|---|---|
| Data model size | In 32-bit environments, Excel workbooks are subject to a 2GB limit. This includes the in-memory space shared by Excel, the Internal Data Model, and add-ins that run in the same process. In 64-bit environments, there are no hard limits on file size. Workbook size is limited only by available memory and system resources. |
| Number of tables in the data model | No hard limits exist on the count of tables. However, all tables in the data model cannot exceed 2,147,483,647 bytes. |
| Number of rows in each table in the data model | 1,999,999,997 |
| Number of columns and calculated columns in each table in the data model | The number cannot exceed 2,147,483,647 bytes. |
| Number of distinct values in a column | 1,999,999,997 |
| Characters in a column name | 100 characters |
| String length in each field | It’s limited to 536,870,912 bytes (512MB), equivalent to 268,435,456 Unicode characters (256 mega-characters). |
A WORD ON COMPATIBILITY
Since Excel 2010 was released, Microsoft has made several versions of Power Pivot available. Different versions of Power Pivot are being used, depending on the version of Excel. Be careful when sharing Power Pivot workbooks in environments where some of your audience is using earlier versions of Excel while others are using more recent versions of Excel. Opening and refreshing a workbook that contains a Power Pivot model created with an older version of the Power Pivot add-in will trigger an automatic upgrade of the underlying model. When this happens, users with older versions of Excel will no longer be able to use the Power Pivot model in the workbook.
Power Pivot workbooks created in a version of Excel that is older than your version should give you no problems. However, you won’t be able use Power Pivot workbooks created in a version of Excel newer than your version.
Linking Excel Tables to Power Pivot
The first step in using Power Pivot is to fill it with data. You can either import data from external data sources or link to Excel tables in your current workbook. I cover importing data from external data sources in Chapter 4. For now, let me start this walkthrough by showing you how to link three Excel tables to Power Pivot.