Data structures and Power BI limits
Which relational structures exist, which sample database teaches each, and what Power BI and Excel cannot model or load.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Name the relational structures you will meet in finance data, and where to practice each one.
- Say which structures Power BI and Excel can model directly, and which they cannot load.
- Pick the sample database that teaches a given structure.
- Plan a model that stays within what Power BI and Excel can handle.
Almost every database you will meet is one of a handful of shapes. Knowing the shape tells you how to join it, how to model it in Power BI or Excel, and which numbers are safe to add up. This page lists the common relational structures, which sample database teaches each, and what Power BI and Excel cannot do with them.
The structures, and where to practice each
| Structure | What it looks like | Practice with |
|---|---|---|
| Normalized (transactional) database | Many small tables, each fact stored once, joined by keys. Built for entering data, not for reporting. | northwind, classicmodels, adventureworks, world |
| Header and line | An order or invoice row plus one row per line. Totals live on the header; quantities and amounts live on the lines. | northwind (Orders and Order Details), cpafirm (invoices and invoice lines), bigbear (permits and inspections) |
| Star schema | One fact table in the middle, flat dimensions around it. The best shape for Power BI. | contoso, retailwh |
| Snowflake schema | A star whose dimensions are split into sub-tables (product, subcategory, category). | retailwh (product and store) |
| Slowly changing dimension (type 2) | A new dimension row, with valid-from and valid-to dates, each time an attribute changes. | retailwh (dim_customer, dim_product_price) |
| Role-playing dimension | One date table used for order date, ship date and more. | retailwh, northwind |
| Degenerate dimension | An identifier kept on the fact table, such as an order number. | retailwh (order_id) |
| Bridge table (many-to-many) | Two tables linked through a third that holds the pairs. | retailwh (product tags), nonprofit (donor interests) |
| Parent-child hierarchy | A table that points at itself: employee and manager, account and parent account, wholesaler and retailer. | cpafirm, nonprofit, bigbear (employees), the charts of accounts, mfgorders (dim_customer) |
| Bill of materials | A recursive parts-in-parts structure several levels deep. | mfgorders (bom) |
| Periodic snapshot | A balance recorded at regular intervals, such as month-end inventory. Semi-additive. | retailwh (fact_inventory_snapshot) |
| Accumulating snapshot | One row per process instance with a date for each stage; dates fill in over time. | retailwh (fact_order_pipeline) |
| Factless fact | Keys only, recording that something happened or was possible. | retailwh (fact_promo_coverage) |
| Facts at different grain | Budgets by month and category, actuals by transaction. | retailwh (fact_budget), bigbear (budgets) |
| Fund accounting and the general ledger | Balanced journal entries by fund, department and account. | nonprofit, bigbear, cpafirm, qbo, sage100 |
| Multi-currency | A local amount, a daily exchange rate and the converted amount. | retailwh (fx_rates) |
| Time series and event logs | Very many timestamped rows, often with quality flags. | smarthome, bigbear (weather, dispatch calls) |
| Messy and semi-structured | Wide tables, key-value pairs, JSON, text logs and inconsistent codes. | pqlab |
| Geography | Addresses with latitude and longitude, and boundaries. | Every lab database; polygons in retailwh (dim_region.boundary_geojson) |
| A very large fact table | Tens of millions of rows or more, kept small with columnstore compression. | mfgorders |
Not yet covered: a graph of relationships between people or companies, and a stock-movement ledger where the balance is a running total. If you need either, say so.
How to model each in Power BI and Excel
- Star schema: load each table as is, relate each fact key to its dimension (one-to-many, single direction), hide the keys, and write measures on the fact table.
- Snowflake: either keep the chain of relationships (it works) or merge the sub-tables into one flat dimension in Power Query. Flattening is faster and simpler for report authors.
- Slowly changing dimension: relate the fact to the surrogate key (the version). To report on today's attributes instead of the attributes at the time, add a column with the current value to each version, or relate through the business key to the current row.
- Role-playing dates: one active relationship and others inactive. Switch with
USERELATIONSHIPin a measure, or load the date table once per role. - Bridge table: a many-to-many relationship, or a relationship from each side to the bridge with filter direction set towards the fact. Expect a product to be counted once per tag.
- Parent-child hierarchy: Power BI does not follow a self-join. Add
PATH([id], [parent_id])in a calculated column and flatten it withPATHITEMinto level columns. - Snapshots: never add a balance across time. Use the last value in the period (
LASTNONBLANK, orCLOSINGBALANCEMONTH) or an average. - Facts at a different grain: relate both facts to shared dimensions (category, month) rather than to each other, and compare in measures.
- Multi-currency: store the local amount and the rate on the day, convert in the fact table or with a rate table related on date and currency.
What Power BI and Excel cannot do
Relationships
- A relationship uses one column on each side. For a composite key, build a combined column (or an index) first.
- Only one active relationship can exist between two tables; others are inactive until a measure activates them.
- No circular relationship paths and no ambiguous paths. Bidirectional filters make ambiguity easy to create.
- A relationship does not follow a self-reference by itself (see
PATHabove). - The Excel Data Model has only the one-to-many relationship type, so a many-to-many needs a bridge table. Power BI supports many-to-many relationships, with caveats.
Data types that do not come through cleanly
- Spatial columns (
geography,geometry),hierarchyidandsql_variantare not loaded. Convert them in the query:STAsText()for spatial,ToString()for hierarchyid, and aCASTfor variants. - Decimal numbers in Power BI are stored as 64-bit floating point (about 15 significant digits) or as fixed decimal (four decimal places). A SQL
decimal(38,10)loses precision, and money with more than four decimal places is rounded in fixed decimal. - Dates before about 1900 are unreliable in DAX, and a date table must have no gaps for time intelligence to work.
- JSON, XML and delimited lists arrive as text; parse them in Power Query.
- Images, documents and free text are stored as binary or text but cannot be analyzed as such.
Size and speed
- An Excel worksheet holds 1,048,576 rows, but the Data Model can hold more, limited by memory (32-bit Excel is limited to 2 GB for the whole process).
- A Power BI Import model in the Pro service is limited to 1 GB compressed; Premium and Fabric allow far larger models. Check your license for the current limits.
- DirectQuery returns at most one million rows from any single query, is slower, and restricts some DAX and Power Query steps. Excel does not offer it.
- High-cardinality columns (order numbers, timestamps with seconds, long text) compress poorly and dominate model size. Keep integer keys, split a timestamp into a date and a time, and drop columns no report uses.
- Pre-aggregate a very large fact table, or use incremental refresh and Power BI aggregations, so that most visuals read thousands of rows instead of millions. See Big data: manufacturer orders.
Features that differ
- Row-level security exists in Power BI, not in a shared Excel workbook.
- Calculation groups are authored in Power BI Desktop (or external tools), not in the Excel Power Pivot window.
- Excel PivotTables can query a Power BI semantic model through "Analyze in Excel", and then use its measures.
Where to go next
- A walk through every warehouse pattern, with queries: Retail data warehouse (retailwh).
- Very large tables and the bill of materials: Big data: manufacturer orders (mfgorders).
- Model design: Fundamentals and Time intelligence.
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.