Fact Tables and Dimension Tables in Power BI
Fact tables store events and numbers (each sale line, each phone-bill payment). Dimension tables store the things you slice by (store, product, calendar). Facts stay skinny; labels live once on dimensions.
Friends! Beginners paste City Name on every sales row. Why is that painful? Rename one city and you edit a thousand lines. How? We separate FreshBasket facts (Amount + keys) from Store and Product dimensions, then relate them. RELATED pulls the label when a column needs it.
मित्रांनो! Beginners प्रत्येक sales row वर City Name चिकटवतात. का त्रासदायक? एक शहर rename करायचे आणि हजार ओळी संपादित करायच्या. कसे? FreshBasket facts (Amount + keys) Store आणि Product dimensions पासून वेगळे करायचे, मग relate. RELATED ला label हवे असेल तेव्हा ओढते.
मित्रों! Beginners हर sales row पर City Name चिपकाते हैं. क्यों तकलीफ़देह? एक शहर rename करना और हज़ार पंक्तियाँ संपादित करना. कैसे? FreshBasket facts (Amount + keys) को Store और Product dimensions से अलग करें, फिर relate. RELATED तब label खींचता है जब कॉलम को ज़रूरत हो.
Quick answer
Vertical path:
- Fact rows = events: keys + Amount + Qty.
- Dimension rows = entities: Store ID + City + Channel.
- Do not copy City onto every fact row if Store already has it.
- Relate fact keys to dimension keys.
- Slice on dimension columns; measures sum the fact.
- Use RELATED on the fact only when you truly need a denormalised column.
Real example: sales slips vs store card
Fact — what happened (grain = one slip line):
| OrderID | StoreID | ProductID | Amount |
|---|---|---|---|
| A | S1 | P1 | 100 |
| A | S1 | P2 | 50 |
| B | S2 | P1 | 40 |
Dimension Store — who / where:
| StoreID | City | Channel |
|---|---|---|
| S1 | Mumbai | Online |
| S2 | Pune | Store |
Dimension Product — what:
| ProductID | Name |
|---|---|
| P1 | Rice |
| P2 | Oil |
Phone-bill cousin (also a fact): Month key, StoreID, BillAmount. Still an event table. City still comes from Store.
What a City slicer = Mumbai does:
- Dimension keeps S1.
- Fact keeps orders A lines (100+50).
- Order B leaves.
- Total 150.
- City was never stored twice on the fact.
What do I need before this guide?
- Basic Get Data (Excel/CSV).
- Pair with star schema.
- Course: Fact and dimension tables.
Before and after (look at the tables first)
Before
आधी (Before)
पहले (Before)
After
नंतर (After)
बाद में (After)
How to recognise each
A fact table holds events: each sale line, with amounts and foreign keys.
Fact टेबल मध्ये events असतात: प्रत्येक sale line, amounts आणि foreign keys सह.
Fact टेबल में events होते हैं: हर sale line, amounts और foreign keys के साथ.
A dimension table holds things you slice by: City, Product name, Calendar month.
Dimension टेबल मध्ये तुम्ही slice करता ती गोष्टी असतात: City, Product name, Calendar month.
Dimension टेबल में वे चीज़ें होती हैं जिनसे आप slice करते हैं: City, Product name, Calendar month.
- Fact — grows forever (new orders every day). Numeric measures live here.
- Dimension — grows slowly (new stores, new products). Text labels live here.
- Date — special dimension with one row per day.
- If a column answers “which / who / when label?”, it is usually dimensional.
- If a column answers “how much / how many for this event?”, it is usually factual.
Facts stay skinny (keys + numbers). Labels live on dimensions. That is why RELATED works.
Facts skinny राहतात (keys + numbers). Labels dimensions वर राहतात. म्हणूनच RELATED काम करतो.
Facts skinny रहते हैं (keys + numbers). Labels dimensions पर रहते हैं. इसलिए RELATED काम करता है.
Why RELATED exists: sometimes a calculated column on Sales wants RELATED ( Store[City] ). The relationship is the bridge. Prefer slicing on the dimension column over stuffing every label into the fact.
Grain (say it every time)
- Fact grain = “one row means what?” — one order line, not one order header if lines differ.
- Wrong grain duplicates totals when you relate poorly.
- Count orders with DISTINCTCOUNT of OrderID, not COUNTROWS of lines, when lines are the grain (count guide).
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| City repeated 10k times | Label on fact | Move to dimension |
| Totals explode | Grain / relationship wrong | Check one-to-many keys |
| Slicer on fact text | No dimension | Build dimension; relate |
| RELATED blank | Missing relationship / wrong side | Model view check |
Ravindra Bagale's Tip
Interview line: “Facts hold events and numbers; dimensions hold labels I slice by. I keep facts skinny and relate on keys.” Got it?
Ravindra Bagale's Tip – मराठी
Interview line: “Facts मध्ये events आणि numbers असतात; dimensions मध्ये मी slice करतो ती labels. मी facts skinny ठेवतो आणि keys वर relate करतो.” समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview line: “Facts में events और numbers होते हैं; dimensions में वे labels जिन्हें मैं slice करता हूँ. मैं facts skinny रखता हूँ और keys पर relate करता हूँ.” समझ में आया?
Practice task
- Take a flat shop export.
- Split into Sales + Store + Product.
- Prove Mumbai total 150 with a slicer.
- Write the fact grain in one sentence.
Got it? Fact = events and numbers; dimension = slice labels. Keep facts skinny. That closes this batch of ten guides — not deployed. Let us go ahead.
समजलं का? Fact = events आणि numbers; dimension = slice labels. Facts skinny ठेवा. या दहा गाईड्सचा बॅच इथे संपतो — deploy नाही. आता पुढे जाऊया.
समझ में आया? Fact = events और numbers; dimension = slice labels. Facts skinny रखो. इन दस गाइड्स का बैच यहाँ खत्म — deploy नहीं. अब आगे बढ़ते हैं.
Frequently asked questions
What is a fact table?
A table of events or measurements — sales lines, tickets, phone-bill payments — with keys and numbers.
What is a dimension table?
A lookup table of entities you filter by: store, product, customer, calendar.
Why not one wide sheet?
Repeating “Mumbai” on every line wastes space and makes renaming a city painful. Dimensions store the label once.
Is Date a dimension?
Yes. A proper Date table is the usual dimension for time intelligence.
Can a table be both?
In messy imports, sometimes. In a clean model you separate them so grain stays clear.
Course lesson?
Fact tables and dimension tables, in the data modelling chapter.