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.
Nevertheless, it's important to understand the maximum and configurable limits for Power Pivot Data Models.
| 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). |
| 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. |
