Excel Power Pivot & Power Query For Dummies
Book image
Explore Book Buy On Amazon
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, which becomes available when you activate the Power Pivot Add-In.

The Power Pivot Ribbon interface exposes the full set of functionality 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 Pivot Table 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.

Object Specification
Limitations of the Internal Data Model
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).
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, no hard limits on file size exist. 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).
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.

About This Article

This article is from the book:

About the book author:

Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years’ experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community.

This article can be found in the category: