Star Schema Data Modelling in Power BI
A star schema puts one fact table (events and numbers) in the centre and dimension tables (Date, Store, Product) around it. Relationships on keys make filters and DAX behave. It is the default shape for clear Power BI models.
Friends! A flat sheet feels easy until City names repeat a thousand times. Why move to a star? Because filters and CALCULATE follow relationships. How? We draw FreshBasket Sales in the middle, Store / Product / Date around it, relate on IDs, and keep snowflake for later. Simple star first.
मित्रांनो! Flat sheet सोपी वाटते जोपर्यंत City नावे हजार वेळा पुन्हा येत नाहीत. Star कडे का जायचे? कारण filters आणि CALCULATE relationships ने चालतात. कसे? FreshBasket Sales मध्ये, Store / Product / Date भोवती, IDs वर relate, snowflake नंतर. आधी सोपा star.
मित्रों! Flat sheet आसान लगती है जब तक City नाम हज़ार बार दोहराए न जाएँ. Star की ओर क्यों जाएँ? क्योंकि filters और CALCULATE relationships से चलते हैं. कैसे? FreshBasket Sales बीच में, Store / Product / Date चारों ओर, IDs पर relate, snowflake बाद में. पहले आसान star.
Quick answer
Vertical path:
- Fact = Sales lines with Amount + foreign keys.
- Dimensions = Date, Store, Product (labels and attributes).
- Model view: relate Sales[Store ID] → Store[Store ID] (many-to-one).
- Same idea for Date and Product.
- Hide key columns from report view.
- Prefer star over snowflake while learning.
Dimensions filter → Fact rows stay or leave → Measures sum what remains
Real example: shop star with the 190 total
Fact Sales:
| SalesKey | Date | StoreID | ProductID | Amount |
|---|---|---|---|---|
| 1 | 2025-01-10 | S1 | P1 | 100 |
| 2 | 2024-06-01 | S1 | P2 | 50 |
| 3 | 2025-02-01 | S2 | P1 | 40 |
Store dimension:
| StoreID | City | Channel |
|---|---|---|
| S1 | Mumbai | Online |
| S2 | Pune | Online |
Product dimension:
| ProductID | Name |
|---|---|
| P1 | Rice |
| P2 | Oil |
What the star does when City slicer = Mumbai:
- Store dimension keeps S1.
- Relationship keeps Sales rows 1 and 2.
- Row 3 (Pune) leaves.
[Total Sales]shows 150 (100+50).- City name lived once on Store — not copied onto every fact row.
What do I need before this guide?
- Fact and dimension tables.
- Auto date/time off when you bring a real Date table.
- Course: Star vs snowflake.
Before and after (look at the tables first)
Before
आधी (Before)
पहले (Before)
After
नंतर (After)
बाद में (After)
Draw the star
A star schema puts one fact table in the centre and dimension tables around it.
Star schema मध्ये एक fact टेबल ठेवतो आणि भोवती dimension टेबल्स ठेवतो.
Star schema बीच में एक fact टेबल रखता है और चारों ओर dimension टेबल्स रखता है.
Relate fact to each dimension on a key (Store ID, Date, Product ID). Hide the key columns from report view.
Fact ला प्रत्येक dimension शी key वर relate करा (Store ID, Date, Product ID). Report view मधून key कॉलम hide करा.
Fact को हर dimension से key पर relate करें (Store ID, Date, Product ID). Report view से key कॉलम hide करें.
- Fact in the centre — many rows.
- Dimensions around — fewer rows, rich labels.
- Many-to-one from fact to each dimension.
- Single filter direction (dimension → fact) for beginners.
- Hide IDs; show City, Product Name, Month.
Snowflake splits dimensions further. Prefer star for Power BI learning: simpler filters, clearer DAX.
Snowflake dimensions आणखी तोडतो. Power BI शिकताना star निवडा: सोपे फिल्टर, स्पष्ट DAX.
Snowflake dimensions और तोड़ता है. Power BI सीखते समय star चुनें: आसान फ़िल्टर, साफ़ DAX.
Snowflake splits Store into Store + City + State tables. Valid, but more joins and harder DAX for learners. Put City on Store until you must normalise.
Why flat files hurt later
- Renaming “Bombay” to “Mumbai” means rewriting every sales row.
- Date intelligence wants a continuous Date dimension.
- RELATED and clean slicers expect dimensions.
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| Slicer does nothing | Missing / inactive relationship | Model view: create active many-to-one |
| Numbers duplicate | Relationship wrong grain / many-many mishandled | Fix keys; check cardinality |
| Authors use IDs | Keys visible | Hide keys in model |
| Over-normalised early | Snowflake too soon | Collapse attributes onto dimensions |
Ravindra Bagale's Tip
Interview line: “I model a star: facts in the centre, dimensions around, many-to-one on keys, hide the keys from report view.” Got it?
Ravindra Bagale's Tip – मराठी
Interview line: “मी star model करतो: मध्ये facts, भोवती dimensions, keys वर many-to-one, report view मधून keys hide.” समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview line: “मैं star model करता हूँ: बीच में facts, चारों ओर dimensions, keys पर many-to-one, report view से keys hide.” समझ में आया?
Practice task
- Split a flat shop sheet into Sales + Store + Product.
- Relate on IDs.
- Slice City and prove totals 150 / 40.
- Hide StoreID.
Got it? Star schema = fact centre, dimensions around, keys relate them. Next: fact vs dimension tables in detail. Let us go ahead.
समजलं का? Star schema = मध्ये fact, भोवती dimensions, keys relate करतात. पुढे: fact vs dimension tables. आता पुढे जाऊया.
समझ में आया? Star schema = बीच में fact, चारों ओर dimensions, keys relate करते हैं. आगे: fact vs dimension tables. अब आगे बढ़ते हैं.
Frequently asked questions
What is a star schema?
A fact table in the middle related to dimension tables. The diagram looks like a star.
Star vs snowflake?
Snowflake normalises dimensions further (City → State tables). Star keeps attributes on the dimension for simpler DAX.
Why does modelling matter?
Filters and measures follow relationships. A messy model makes every CALCULATE harder.
Can I use one flat table?
For a tiny demo, yes. Real reports grow painful: repeated city names, weak date intelligence, slow changes.
Both directions on the relationship?
Beginners should keep single direction (dimension filters fact) unless a teacher asks for both.
Course lessons?
Why modelling matters, and star vs snowflake, in the data modelling chapter.