Data Modelling

Fact tables record events at a consistent grain, such as one row per sales line. Dimension tables describe the products, customers and dates attached to those events. Define the grain first so totals and relationships remain predictable.

A star schema places a fact table at the centre, connected to descriptive dimensions. Use unique dimension keys and keep descriptive attributes in the dimensions. This structure makes filters easier to follow and measures easier to maintain.

Relationships allow filters to travel between tables. Connect matching key columns with compatible data types. Prefer an active relationship for the usual analysis path; an inactive relationship can be used explicitly in a DAX calculation with USERELATIONSHIP.

Cardinality describes whether each side of a relationship contains unique or repeated keys. A common pattern is one-to-many from a dimension to a fact table. Check the one-side key for duplicates and blanks before creating the relationship.

Single-direction filtering normally carries dimension filters into the fact table. Bidirectional filtering can support particular scenarios but can also create ambiguous paths. Keep the default model simple and add bidirectional behaviour only when it is needed.

Use a dedicated date table with one unique row per day and a continuous date range covering your facts. Add year, quarter and month attributes, and sort month names by their month numbers. Mark it as a date table when required for your time-intelligence approach.