Downloads

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

What Power BI and Excel cannot do

Relationships

Data types that do not come through cleanly

Size and speed

Features that differ

Where to go next

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.