Glossary
What is star schema?
A star schema is a data model with a fact table of events and amounts at the centre, surrounded by dimension tables with descriptions such as customer, product and date. It is the standard set-up for Power BI models and data warehouses.
Star schema explained
The fact table holds one row per event, such as an order line or a ledger entry, with amounts, quantities and keys to the dimensions. The dimension tables hold the descriptions you filter and group by. The key design decision is the grain: what does one row in the fact table represent?
Power BI is built for this set-up. Filters flow from dimensions to facts, measures stay simple and the model is fast. A model with one large flat table or many interlinked fact tables more often leads to slow reports and wrong totals.
A snowflake schema is a variant in which dimensions are themselves split up, for example product and product group in separate tables. That saves some storage but makes the model more complex; for Power BI a star schema is usually recommended.
Example
Acme Groothandel has a fact table of order lines: date, customer, product, quantity and amount. Around it sit the customer dimension (with segment and region), the product dimension (with category) and the date dimension (with month and year), so you can break revenue down by any of those attributes.
Star schema in ENABLE
ENABLE does not prescribe a data model, but a star schema in the PostgreSQL data warehouse works well with the SQL Explorer and the Dashboard Builder (pilot). Data Analytics often models the data this way before setting up the portal.
FAQ
Frequently asked questions
What is the difference between a fact table and a dimension table?
A fact table holds events with measurable values, such as orders with amounts. A dimension table holds descriptive attributes, such as customer name, region or product category, that you filter and group by.
What is the difference between a star schema and a snowflake schema?
In a star schema each dimension is a single table; in a snowflake schema dimensions are split further into sub-tables. A star schema is simpler and faster in Power BI.
Why does Microsoft recommend a star schema for Power BI?
Because filters then flow clearly from dimensions to facts, measures stay simpler and the model is faster. That reduces the risk of wrong totals.
Further reading
- ResourcesWhat is a data warehouse? Explained and comparedA data warehouse is a central database in which you bring together data from different source systems, such as your ERP, CRM and webshop, clean it up and organise it so that you can report on it and analyse it effectively. A source system is built to process transactions; a data warehouse is built for reading, comparing and keeping history. A data lake stores raw data in any format, and a data lakehouse combines the storage of a lake with the structure of a warehouse.Read more
- IntegrationPower BI embedded portal: share dashboards securelyPower BI reports embedded in your own portal, with access by role and RLS.Read more
- SolutionBuild a data platform: layers, choices and approachFrom sources to dashboards and AI: how a data platform is structured.Read more
- ResourcesGlossary of data, AI and Power BI termsWhat is a service principal, row-level security, a semantic model, text-to-SQL or RAG? Short, clear definitions of terms around data, Power BI and AI.Read more
Use AI on your own data. Securely, and without the headache.
In an online demo we show how ENABLE makes your data securely available and how you build on it: describing an app in the AI Builder, plain-language questions, dashboards and APIs. And how we set it up and manage it for you.