Data Modelling in Power BI
Extracting reliable insights from a dataset requires a structured framework designed to guarantee accuracy, optimize performance, and streamline reporting. Developing this framework comprises the core process of data modeling.
A structured data model serves as the foundation for comprehensive analysis, driving success in crucial areas such as:
1. Scalability and Maintainability
A scalable data model facilitates the seamless integration of new data without requiring a structural rebuild. Because a well-designed model is inherently modular, it easily adapts when reporting requirements expand. When new metrics or data sources are introduced, new data can easily be added in new tables and relationships established with existing tables.
By structuring data into separate, logical tables, new data can be added in new tables and relationships established seamlessly. These updates automatically flow through the rest of the model as defined by the relationships. Eg. The addition of a new customer will only require the addition of all the customer details in the table set aside for that while the customer ID will be used in the other tables to represent the entire information.
2. Performance
Proper data modeling minimizes table width, eliminates redundant text columns from high-cardinality fact tables, leading to faster visual rendering and quicker dashboard interactions
3. DAX Calculations
DAX relies heavily on active relationship paths and filter propagation. A suitable data model ensures a predictable filter context, making it far easier to write formulas because users can mentally visualize how filters flow through the data.
4. Analytics
Choosing the right data model, with properly configured relationships, granularity and cardinality, drastically reduces logic errors to ensure all derived metrics yield accurate insights.
Data Modelling Approches in Power BI
Flat Tables
Flat table combines all attributes and transactional data into a single, unnormalized table. This structure introduces heavy data redundancy, increases model size, and makes writing clean, efficient DAX calculations significantly harder.
To keep the data model organized, clean, and efficient, datasets are separated into distinct fact and dimension tables. The logic behind how and why this data is split relies on:
- Entity-Relationship: Descriptive attributes belonging to a specific subject (e.g., combining Customer_Name and Customer_City) are grouped into a dedicated dimension table. This ensures the data model is logically organized and meaningful.
- Data Granularity: Granularity defines exactly what a single row represents. This dictates the lowest level of detail the fact table will store before connecting out to the dimensions.
- Column Cardinality: Cardinality serves as the primary performance metric for the split. Columns with low cardinality (highly repetitive data) are moved into dimension tables to eliminate redundancy, while high-cardinality unique event identifiers remain in the fact table.
Fact tables are the tables in a schema that store quantitative data, while dimension tables store the descriptive data that gives context to the data in the fact tables. The combination and relationship between these fact and dimension tables make up the core architecture of a schema.
Characteristics of tables in schemas
Fact Tables
- They hold quantitative numerical data which can be computed using DAX fnctions.
- They have the foreign keys which are used as the connectors to the dimension tables.
- Defines the grain of the data, which is the level of detail of the dataset. Eg. Each row holds a transaction of a single product to a specific client.
- They are normally updated frequntly because they hold the data for recurring events.
Dimension Tables
- They hold descriptive data which give context to the dataset in the fact tables.
- They hold the primary keys which are the unique identifiers used to connect to the foreign keys in the fact tables.
- The descriptive data rarely changes so these tables are not updated frequently.
The arrangement and relationship pattern between the fact table, dimension tables, and sub-dimension tables determine the type of schema:
A. Star Schema
In a star schema, all dimension tables are connected directly to a central fact table.
In the illustration, the customers, store and product dimension tables are connected to the central sales table, which is a fact table.
Advantages of a star schema
- Intuitive and Easy to Navigate: Because there are no complex sub-tables, the model is easy for users to visualize.
- Simple formulas are required for computations.
- DAX performs better because it has less relationships to navigate.
Disadvantages of a star schema
- Storing all data in a single flat table leads to disorganization, as it creates overwhelming visual clutter, mixes different levels of data granularity, and increases the risk of data entry inconsistencies.
- Data Redundancy: Text values that would otherwise appear only once in a normalized snowflake schema are repeated across multiple rows.
In conclusion, the star schema is suited for use cases where the requirements are fast computation and easy interpretation and visualization.
B. Snowflake Schema
In a snowflake schema, the dimension tables are connected to a central fact table and the dimension tables themselves have subdimensional tables connecting to them.
Advantages of a snowflake schema
- Allows storage of higly comprehensive detail with no repetition. For example, data for different mugs and umbrellas can be normalized further to have a separate table hold the category details, rather than repeating the details for each type of mug and each type of umbrella on the same table.
- Data model maintainability is improved because updates can be made directly within their respective tables, rather than editing thousands of duplicate rows.
Disadvantages of a snowflake schema
- Error propagation: If for instance an error was made in one sub dimension table, the error will affect the data to a larger extent as it will be passed on to the related tables and troubleshooting would be complex for such a case.
- Complexity: the visual layout is more complex and therefore more difficult to visualize, and more complex formulas are required for computations.
- When performing computations, DAX is slower because it has to navigate through complex relationships between the tables.
In conclusion:
The snowflake schema is ideal for managing massively complex and rigid data hierarchies, as it normalizes the data to completely avoid the repetition of entries.
Relationships in Power BI
Relationships refer to logical connections between tables that share a column with common data. They serve as bridges to enable Power BI to understand how the tables in a model relate to one another.
Characteristics and properties of relationships
1: Relationships are formed by linking the primary key of in the dimension table to the foreign key in the fact table. For example, sales transaction have customers IDs to represent the customer involved. This can be used to link to the table which holds the data about customers.
2: Cardinality of relationships: This refers to the numerical interaction between primary and foreign keys.
- One-to-many: A single instance of a primary key connects to multiple instances of a foreign key. The use case for this is when the primary key in the dimension table corresponds to multiple instances of the foreign key in the fact table.
- A unique primary key in one table links to a non-repeating foreign key in another table. This occurs when two tables share the same primary key structure or when an aggregated fact table contains exactly one record per foreign key.
- Many-to-Many: Multiple instances of a foreign key connect to multiple instances of another foreign key, where both foreign keys represent the same underlying entity or attribute across both tables.The use case for this is when bridging two tables that both contain non-unique, repeating values, such as relating a fact table directly to another fact table at a shared grain.
3: Crossfilter direction
- Single: Filters flow in one direction. Filtering the dimension table propagates to the fact table, but the inverse is not true. Eg, for two related tables, product table and sales table, if the product table is filtered to just display a single product, the sales table will also filter to show one product.
- Both (Bi-directional): Filters flow in both directions simultaneously. Filtering the dimension table propagates to the fact table, and the inverse is also true. Although necessary for certain complex data models, bi-directional filtering can slow down performance and introduce ambiguity into your DAX calculations.
4: Active and inactive relationships
- Power BI enforces a strict limit of one active relationship path between any two tables at a time. This constraint prevents ambiguous, conflicting filter arguments that would otherwise produce unpredictable calculation results in DAX. Any additional relationships established between those same tables are set to inactive but can be invoked using DAX.
Joins in Power Query
Joins in Power Query combine two separate tables horizontally into a single table based on matching columns.
The types of join is based on the rows that are retained:
- Left outer join keeps all rows from the left table and only matching rows from the right table.
In this example, the join returns all the sales with the details of the corresponding clients who made the sales. The sale C5 returns nulls because it has no corresponding customer on the customers table.
- Right outer join Keeps every row from the right/second table and matching rows from the first table.
In the example (same left and right tables as above), the join returns all the customers with their corresponding sales. Customer 4 has nulls because they have no corresponding purchases.
- Full outer join keeps all the rows from both tables, regardless of matches.
This returns all the records from both tables and fills out nulls where clients have no corresponding sales and sales have no corresponding clients.
- Inner join keeps only rows that have exact key matches in both tables.
The inner join returns all records which have their customer IDs in both tables. The sales that have no corresponding clients are removed and the clients with no corresponding clients are also removed.
- Right anti join keeps only rows from the right table that have no match in the left table.
This returns the client Diana because they have no corresponding entry on the sales table.
- Left anti join keeps only rows from the left table that have no match in the right table.
This returns the sale 104 because it has no corresponding customer (The customer ID C5 is not assigned to any client)
Conclusion
For a typical business intelligence project, the recommendation is to use a Star Schema.
- Balanced Normalization: In a typical business scenario, the number of data categories is manageable. There is rarely a need to over-normalize and break tables down into multiple, deep hierarchies. Keeping a single tier of dimension tables is highly efficient and reduces unnecessary complexity.
- Visual Clarity and DAX Simplicity: The star structure is easy to visualize. This visual clarity makes it straightforward to establish relationships, trace exactly how filters propagate from dimensions to facts, and write accurate DAX functions without getting lost in a web of tables.
- Maintainability and Scalability: As the business grows, appending new data is simple. Because the separation between descriptive attributes and transactional metrics is straighforward, it is obvious where new data belongs, making long-term maintenance effortless.
- Model Readability: Because typical business data is not overwhelmingly complex, the Star Schema ensures the model remains highly readable. It is sufficient to avoid the clutter of over-segmented tables.