A well-designed data model determines how efficiently data can be queried, how easy reports are to build, and how accurate the insights will be. Some of the data modelling concepts described are star schema design, snowflake schema, fact and dimension tables and their relevance to developing models that optimize performance and usability.
Data Modelling
Data modeling is the process of creating a visual representation of either a whole information system or parts of it to communicate connections between data points and structures.
Benefits of Data Modeling
- Visualizes complex data structures, providing a clear roadmap for understanding relationships.
- Acts as a universal language, fostering effective communication between business and technical stakeholders.
- Creates organized databases by defining entities, properties and relationships.
- Enhances data quality and integrity by reducing anomalies and redundancy through normalization.
- Reduce errors in software and database development.
- Increase consistency in documentation and system design across the enterprise.
- Ease data mapping throughout the organization.
1. Fact Table:
The fact table sits at the center of the schema and stores the measurable, quantitative data used for analysis.
A fact table contains dimension key columns that relate to dimension tables, and numeric measure columns. Examples include:
- Sales amount
- Units sold
- Discount
- Profit Each record in a fact table represents a business event (e.g., a sales transaction).
2. Dimension Tables:
Dimension tables surround the fact table and contain descriptive attributes that add context to the facts.
The dimension table contains a key column (or columns) that acts as a unique identifier, and other columns. Other columns support filtering and grouping your data. Common dimensions include:
- Product details
- Customer details
- Time attributes
- Employee or store information These tables allow users to slice, dice, filter, and group the fact data for analysis (e.g., sales by region, by month, by product category).
Star Schema
A Star Schema organizes data into a central fact table linked to multiple dimension tables, forming a layout that visually resembles a star. This structure makes analytical queries fast, simple, and efficient. It is also known as the dimensional data modeling
Features of Star Schema
- Central fact table holding numerical measures.
- Dimension tables storing descriptive attributes.
- Denormalized design, allowing fast joins and simple queries.
- Pre-aggregated and summary-friendly structure.
- High query performance, especially for OLAP workloads.
- Easy to understand, even for non-technical users.
star schema
Snowflake Schema
A Snowflake Schema is a data warehouse modeling technique where dimension tables are normalized into multiple related sub-tables. It is an extension of the Star Schema, designed to handle complex hierarchies and reduce data redundancy.
Features of the Snowflake Schema
- Uses normalized tables to reduce redundancy and improve consistency.
- Has central fact table with connected dimension tables.
- Dimensions can be sourced from different systems and integrated cleanly.
- Requires more joins, which can slow performance on large datasets.
Snowflake schema
Good data modelling directly impacts performance, accuracy, and usability. Either by reducing the number of joins, queries executing faster, DAX measures that are shorter and easier to debug, and filters can be applied.
By organizing data into fact and dimension tables, using star schemas where possible, and defining clear, intentional relationships, you create models that are fast, reliable, and easy to use with better performance, simpler DAX, and accurate, trustworthy reporting that supports confident business decisions.
Top comments (0)