If you've ever wondered how data should be organised to make it easy to analyse, or if you've worked in relational databases, you have likely come across fact and dimension tables. These are fundamental building blocks of a star schema, a common design pattern in data warehousing. Think of them like the building blocks of your data model, in that they fit together perfectly to help you build something useful.
What are Dimension Tables?
These are all about context. They provide the "who, what, where, and when" for your data. Dimension tables contain descriptive attribute that provide meaning and context to the data in your fact table.
For example a Product Dimension table might include columns like:
The key here is that dimension tables are generally smaller and do not change very often, they're often like a lookup table for your data, holding the static, descriptive information.
What are Fact Tables?
Fact tables are where the action happens as they contain the measurement metrics, and facts from a business process. Think of these as the "how much" and "how many". Fact tables are typically very large and contains foreign keys that link back to your dimension tables.
For example, a Sales Fact Table would contain measurements like:
it would also have foreign keys like:
that connect it to the corresponding dimension tables.
The thing to remember with fact tables is that they contain quantitative data that you want to aggregate, sum up or average which is the information used for analysis or reporting.
Why split your data into separate fact and dimension tables?
It's all to do with efficiency. The star schema structure, helps build a clean, powerful and understandable data model. It does this by:
- Reducing Redundancy: storing descriptive information only once in a dimension table instead of repeating it in every row of the large fact table.
- Improving Performance: when querying the data, a BI tool can quickly join the small dimension tables with a large fact table.
- Enhancing Usability: star schemas are intuitive, analysts can easily see the relationships, understanding that the fact table is the "what happened" and a dimension table is the "what it happened to".