Data modelling organises data into tables that are easy to query and hard to misuse. For analytics, dimensional modelling is the classic approach.
Facts and Dimensions
- Fact tables record events or measurements: orders, payments, website visits. They contain numeric measures (amount, quantity) and keys to dimensions.
- Dimension tables describe the context: customers, products, dates, stores. They contain descriptive attributes used for filtering and grouping.
The Star Schema
A central fact table connected to surrounding dimension tables looks like a star. Queries join facts to the dimensions they need. It's simple for analysts and efficient for databases.
Grain
Define the grain of each fact table precisely: one row per order line, per daily account balance, per page view. Mixing grains in one table causes double counting.
Slowly Changing Dimensions
Attributes change — a customer moves city. Decide whether to overwrite (type 1) or keep history with effective dates (type 2), so past facts are analysed with the attributes valid at the time.
Other Approaches
- Wide denormalised tables: convenient for specific use cases and BI tools.
- Data vault: flexible modelling for integrating many sources, with more complexity.
Good Practice
- Consistent naming and definitions.
- Shared, conformed dimensions (one customer table used everywhere).
- Documented tables and columns.
- Tests for keys, uniqueness and relationships.