DATA MODELLING 101 IN POWERBI

WHAT IS DATA MODELLING IN POWERBI.

It's a process of setting up tables, relationships, calculations and access for data analysis scenarios.
Data modelling also define the data structure of single or many tables and there relationships.
A well defined model delivers fast performance, accurate results, simple DAX, and an intuitive experience for data analysts.
This topic will cover the essential principles of data modelling in PowerBI, with strong focus on recommended star schema, relationships and joins(both in PowerQuery and data model).

Why Data Modelling matters.

Good modelling delivers:

• Faster query performance and lower memory consumption.
• Simple and more reliable DAX.
• Easier maintenance and scalability.
• Better usage for business us

Model Schemas

We will focus on two types of schemas:

Star schema

The data model in this schema demonstrates the relationship between one fact table and many different dimension tables.
Figure below shows components of star schema.

Fact Table:
The fact table sits at the center of the schema and stores the measurable, quantitative data used for analysis. Examples include:

• Sales amount
• Units sold
• Discount
• Profit Each record in a fact table represents a business event (e.g sales transcation).

Dimension Tables:
Dimension tables surround the fact table and contain descriptive attributes that add context to the facts. 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).

Features of Star schema.

• Fact table contains numeric measures.
• Dimension table storing descriptive attributes.
• High query performance.
• Easy to understand, even for non-technical users.
• Allows fast joins and simple queries.

Example of a star schema:(sample-chocolate-sales-data-all)

To demonstrate how a star schema works, consider a sample chocolate data where each shipments details is stored in a fact table and is analyzed through sorrounding dimension tables.

• Shipments fact table contains numeric measures such as amount, boxes along with foreign keys to each dimension such as product id, geographical id and sales person id.
• Sorrounding dimension tables provide descriptive attributes that allow the facts to be examined from different perspectives.
• Locations dimensions describes the shipment location and product dimensions describes the product being shipped.
• Calendar dimension shows the date in years, months the product was shipped.

Advantages of a Star schema

• Easier to perform simple queries.
• Simplified business reporting logically.

Disadvantages of a Star schema

• Denormalized data can introduce duplicates.
• Not as adaptable as normalized models when analysis needs change frequently.
• Weak support for many to many relationships.

Snowflake Schema

Is data model technique where dimension tables are normalized into multiple related sub-tables.
The snowflake schema applies only to dimension tables, not the fact table.
Figure shows the snowflake schema.

• Dimension tables are normalized into multiple related tables, creating a hierarchial structure.
• Fact table is still located at the center of the schema, surrounded by dimension tables.
• Each dimension table is further broken down into multiple related tables. Example: A customer dimension may contain a cityID that links to a separate city dimension tables storing city, state, country.

Features of a Snowflake Schema

• Uses normalized tables to reduce redundancy and improve consistency.
• Are built around a central fact table with connected dimension tables.
• Dimensions can be split into multiple levels, allowing detailed analysis.
• Requires more joins which can slow performance on large datasets. -Scales well for large data, but its complexity makes it harder to manage.

Example of Snowflake schema.

• Employee diemnsion includes atrributes like employeeID, Name, DepartmentID, Region, and Territory.DepartmentID links to the department table, which holds department details like Nmae and location.

Advantages

• Improves data integrity through normalization.
• Reduces redundancy and storage usage.
• Supports detailed hierarchical drill-down.

Disadvantages

• Increased schema complexity.
• More joins, leading to slower query performance.
• Normalization may offer minimal storage savings compared to the entire warehouse.
• Not recommended unless the hierarchy is essential and widely used in queries.

Difference Between Snowflake and Star Schema

Feature             Star schema      Snowflake schema
Dimension structure- Denormalized     - Normalized
Query performance -Faster(few joins) -Slower(more joins) 
Storage requirement- Higher          - Lower
Complexity         - Simple          - More complex
Use case - Simple analytics      - Complex hierarchies.

What are Table Relationships?

A relationship described between two or more tables via a common attribute is termed a table relationship. It is very crucial as it enables users to access data from two separate tables with ease.

Types of Table Relationships

There are four types of table relationships

1. One-to-One (1:1): One row in a table that can only link to one row in another table.

1. One-to-Many (1:*): One row matching to many rows in another table.

1. Many-to-One (*:1): Many rows in a table, matching one unique row in another table.

1. Many-to-Many (:): Multiple rows that can link into multiple rows in another table.

Cross filter direction

Cross filter considers the columns from both the tables that join them together and allows the user to tell the direction of filtering allowed.

• Single: It represents a single direction filter. In this filtering choices in connected tables work on the table where values are being aggregated.
• Both: It represents a bi-directional filter. In this, for filtering both tables connected are considered as one single table.

We need to identify key fields for Relationships

A table relationship works by matching key fields.

• They can be fields with the same name in both fact and dimension tables.
• Mostly we use the primary key of one table as a foreign key in another table and helps in establishing a relationship between two tables. Example: Table below shows primary keys like GeographicalID in the location table.

Impact of table relationships in powerBI

• Incorrectly defined table relationships can lead to performance issues, such as slow query execution.
• Complex relationships with large datasets may result in increased memory.

How to optimize table relationships

• Avoid unnecessary relationships.
• Keep the number of relationships to a minimum.
• Use Bi-directional filtering only when necessary.
• High cardinality relationships can impact performance.

Merged Tables in PowerQuery

This merges data from two or more tables into single table by using PowerQuery.
It's based on common columns between tables.

Importance of merging tables.

• Enables user to perform analysis, reporting, and visualization on combined datasets without the need for complex joins/relationships between tables.

Merging Tables using Merge queries command

First load the data sets into powerbi as shown below:

Click on Transform data -> click on merge queries command

Select the left table for merge and next right table for merge.

Select type of join you want perform, the join operations are used to join the tables.

Types Of Joins

1. Left outer join:- Merges all the rows from the left table and matching rows from right table.
2. Right outer join:- Merges all the rows from the right table and matching rows from left table.
3. Full outer join:- Merges all the rows from the both tables.
4. Inner join:- Merges only same rows from the same tables.
5. Left anti:- Merges only rows from left table.
6. Right anti:- Merges only rows from right table.

Fuzzy Matching

Fuzzy matching is a technique used to identify and match similar strings or text values within a dataset by measuring their similarity based on various algorithms and criteria.

Impact of Merged tables on performance in powerbi

• Tables can impact performance during data refresh.
• Large merged tables can increase data model size and memory usage thus affecting performance.

How to make Merge queries work:

• Limit the number of transformations applied to merged tables.
• Apply filters and aggregations to reduce the number of rows and columns in merged tables before loading into data model.

CONCLUSION

For most business intelligence work, I would consider a star schema with one-to-many relationships, single-direction filtering, and a dedicated Date dimension.

• Star schema is easier to perform simple queries in DAX and has simplified business reporting logically compared to a Snowflake schema which offers complex operations.
• One to manyrelationships because it keeps filter propagation predictable: dimensions filter facts, not the other way around.
• Date table which keeps time reporting possible for time series data analysis and reporting.