30.3 Data Modelling

Q17. What is a star schema and why is it recommended?

A central fact table connected directly to dimension tables with one-to-many relationships. It gives simpler DAX, better performance, and an easy-to-understand model for report users.

Q18. Fact table vs dimension table?

A fact table stores events/transactions and numeric values (e.g. Blinkit order lines with Amount, Quantity, Delivery Time Mins) with foreign keys. A dimension table stores descriptive attributes (Product, Customer, DarkStore, Date) used to filter and group, with a unique key.

Q19. What cardinalities are possible for relationships?

One-to-many (and many-to-one), one-to-one and many-to-many.

Q20. What is cross-filter direction?

The direction in which filters propagate across a relationship: Single (from the one side to the many side) or Both (bi-directional). Single is recommended; Both can cause ambiguity and slower performance.

Q21. What is an inactive relationship and how do you use it?

When two tables have more than one relationship path, only one can be active; others are inactive (dotted line). You use an inactive relationship in a measure with CALCULATE(..., USERELATIONSHIP(col1, col2)), e.g. analysing orders by Delivered Date instead of Order Date.

Q22. Why do we need a separate date table?

Time intelligence functions need a continuous, unique date column covering full years. A dedicated date table, marked as date table, provides consistent attributes (Year, Month, Fiscal Year) and avoids hidden auto date/time tables for every date column.

Q23. CALENDAR vs CALENDARAUTO?

CALENDAR(start, end) builds dates between two dates you specify. CALENDARAUTO([fiscal_year_end_month]) automatically scans the model's date columns and builds complete years – convenient but can create an unexpectedly large range if a column has outlier dates.

Q24. What is a role-playing dimension?

A single dimension used in multiple roles, like Date used as Order Date and Delivered Date. Handled with inactive relationships + USERELATIONSHIP, or by duplicating the dimension.