Using the Excel Data Model for Larger Datasets in Practice

The Data Model is the part of Excel that most users never open and that solves the problem they complain about most: too many rows for a sheet and pivots too slow to use. It is an in-memory column store, the same engine as Power Pivot, built into the workbook. Tables go in through Power Query, relationships join them, and PivotTables read from it. This post covers when and how to use it, how to keep it small and what its limits are. It is a spoke of Excel files that are too big or too slow.

What the model is

The Data Model is a compressed, columnar store embedded in the workbook file. Instead of keeping each row as a unit, it stores each column separately and compresses it by dictionary and run-length techniques. A column with few distinct values (a region, a status) compresses to almost nothing. A column of unique values (an order id, a timestamp to the second) compresses poorly. Queries that touch a few columns read only those columns, which is why a pivot over millions of rows can respond quickly.

It is not a worksheet. It has no cells and no row limit of 1,048,576. A model can hold tables of many millions of rows, bounded by memory and by Microsoft's published limits on the data model (the file size limit and the available memory are the practical ones).

It also has its own calculation language, DAX, for measures. You can use a model for years without writing any, since a pivot can sum, count and average model fields with a click.

Load data into the model

In Power Query, load a query with Load To, choose Only Create Connection, and tick Add this data to the Data Model. Do not also load to a worksheet; a table on a sheet is a second copy and brings the row limit back.

let
    Source = Csv.Document(File.Contents("C:\data\orders.csv"), [Delimiter=",", Encoding=65001]),
    Promoted = Table.PromoteHeaders(Source),
    Kept = Table.SelectColumns(Promoted, {"order_id", "order_date", "customer_id", "product_id", "quantity", "unit_price"}),
    Typed = Table.TransformColumnTypes(Kept, {{"order_id", Int64.Type}, {"order_date", type date}, {"customer_id", Int64.Type}, {"product_id", Int64.Type}, {"quantity", Int64.Type}, {"unit_price", Currency.Type}}, "en-US")
in
    Typed

Two habits in this query pay off. Table.SelectColumns keeps only the columns the analysis needs, because the model stores every column it receives, used or not. And the types are set explicitly, with a locale, so a date is a date and the dictionary encoding works; text columns that should be numbers waste space and break sums.

Load the small lookup tables (customers, products, a calendar) the same way.

Relate the tables

Open the model with Data, Manage Data Model (Power Pivot window), then Diagram View. Drag a key from one table to the matching key in another to create a relationship. The usual shape is a star: one large fact table (orders) with small dimension tables around it (customers, products, calendar), each related on its key.

Relationships go one-to-many: the dimension's key is unique, the fact's is repeated. If the dimension has duplicate keys, the relationship cannot be created as one-to-many, or the totals go wrong; clean it first (finding duplicate rows in an Excel sheet with code). With the relationships in place, a pivot can put customers[region] in Rows and orders[quantity] in Values, and the model does the join. No VLOOKUP is needed, and the lookup column is not duplicated across millions of rows.

A calendar table is worth adding: one row per date with the year, quarter, month name, fiscal period and so on, related to the fact's date column. Date grouping then works the same for every report, and fiscal definitions live in one place (grouping dates by month or quarter in a pivot table).

Build the pivot

Insert, PivotTable, From Data Model. The field list shows all model tables. Choose fields from any of them. To total revenue, you need a column for it. Either compute it in Power Query before loading (quantity * unit_price as a column), or define a measure in the model:

Revenue := SUMX(orders, orders[quantity] * orders[unit_price])

A measure is calculated when the pivot asks, at whatever grain the pivot has, and it does not store a value per row. A calculated column stores a value per row and uses memory. Prefer measures for aggregations and calculated columns only when the value must be per row, for example to filter or group by it. For ratios, measures give the correct answer at each level, where a column of per-row ratios does not (calculating percent of total in a pivot and in SQL).

Distinct count is available in the model (DISTINCTCOUNT), which a plain pivot cannot do, and is a common reason to use it.

Keep it small

Size matters because the model is in memory while the workbook is open, and the file stores it.

• Drop columns you do not use. The biggest saving, every time.

• Reduce cardinality. Split a timestamp into a date column and a time-bucket column (hour or quarter-hour). Round currencies if you can. A column with a million distinct values costs far more than one with a hundred.

• Avoid long text. Descriptions, comments and JSON blobs do not belong in the model. Keep a key and look the text up elsewhere.

• Use integer keys rather than text keys for relationships.

• Aggregate if you can. If the question is monthly by region, load a table summarized to that grain, not the order lines.

• Filter rows to the period you need, in the query.

• Turn off auto date/time. Excel can create hidden date tables for each date column, which adds weight; use your own calendar table.

The Power Pivot window and the workbook file size tell you how it is going. A workbook of a few hundred megabytes is already heavy to share.

Memory and bitness

The model loads into memory when the workbook opens. 32-bit Excel is limited to about 2 GB of address space for the whole application, and a model that large fails to load with an out-of-memory message. Use 64-bit Excel for any serious model, and enough RAM for the model, Excel and everything else. Check bitness under File, Account, About Excel. Microsoft's page on data model specifications lists the limits, including the workbook size limits for models and the number of tables; look there for current figures.

Refresh also needs memory. Reloading a model temporarily holds the old and new data, so a model that fits comfortably may fail to refresh. Close other workbooks and applications before a big refresh.

What it cannot do

The Data Model is a good local analysis tool and has limits as a platform.

• One user at a time. It lives in the file. A shared copy is a copy.

• No row-level security in the way a database has.

• Refresh is manual or scheduled outside Excel. Scheduled refresh needs a service (refreshing Power Query data on a schedule).

• Size ceiling. Past what one machine's memory holds, you need a server.

• DAX has a learning curve. The simple measures are easy; time intelligence and context transition are not.

• Files grow. Emailing a 300 MB workbook is a poor way to share a dataset.

When the model grows beyond a single analyst, the usual path is to publish it as a Power BI dataset or move the data to a database and keep Excel as the front end that connects to it (connecting Excel directly to a database).

Check the model

Compare the model with the source. Add a pivot with the count of the fact table's key and the sum of the main measure, and compare with the source system or a direct query. Check that relationships are active and one-to-many, that no pivot shows a "blank" item with a large total (a sign that fact rows have no match in a dimension), and that date ranges are what you expect. A blank item in a pivot of regions means orders whose customer is not in the customers table.

When the Data Model is the wrong container

The data is hundreds of millions of rows. Use a database or a columnar engine.

Several people need to query it. A shared database or dataset serves that better than a file.

You need full SQL. The model speaks DAX and pivots. For ad hoc SQL on files, DuckDB is more direct (analyzing more than a million rows without Excel limits).

The data is small. A sheet and a Table are simpler under a few hundred thousand rows.

See Microsoft's pages on the data model specification and limits and on creating a data model in Excel. To check a small extract as JSON, use the CSV to JSON converter.

The Power Query documentation covers loading queries to the model.