1.Data Modelling
Data Modelling refers to the process of structuring and organising tables,columns and relationships in Power BI so that they work together logically.
It typically involves
Deciding which tables you need and their relationships
Managing relationships
Creating schema
Setting data types ,hierarchies and naming conventions
Think of data modelling as essentially the foundation of the house where reports,dashboard and DAX functions are built on top.A weak foundation will ofcourse not sustain whats on top
What are the importance of data modelling ?
Data modelling has various importance but first we have to acknowledge it forms the basis of what basically can be done with our data
A well structured data model ensures filters can propagate correctly across visuals.Filtering an dimension like date or product correctly flows into fact tables giving correct aggregation.A messy data model might silently produce wrong number and you know whats worse? Wrong analysis.A data analyst can make right insights from the wrong results and this will majorly affect business performance
Data Analysis Expressions(DAX) majorly depends on the relationships and filter context.If at all your model isn't structured well than DAX expressions might give wrong outputs.It is even harder to debug if your data isn't structured correctly
Business continue to grow and with growth comes new tables,measures and reports.A proper structured model will make it easy to add such tables, measures and report builders
Before diving deep into data modelling let's discuss some concepts that will make our understanding even better
Dimension Table
Dimension tables holds descriptive,qualitative details about the entities.Think of it as a table that describe entities,entities in this case being the things you model.Dimension tables like DimCustomer,DimProducts or DimLocation hold text,dates or descriptions.A dimension table usually contains column or columns that act as unique identifier that connects it to a fact table
Fact Table
A fact table records numeric events or transactions that happened in your business e.g FactSales,FactOrders or FactTransactions. Usually contains number you can add,sum,count or average.A fact table contains dimension key column that relate to dimension table
A dimension key determines the dimensionality of a table where as dimension values determine the granularity of a fact table.It is almost impossible to state the granularity of a table without knowing the values in a dimension table.Take for instance we have Date and Product Key as our two dimensions table,it is until we know the date values that we can state the granularity of a fact table
There are no table properties modellers set to set table as either dimension or fact.It is infact determined by model relationships.A common relationship type is one to many where the one side is set to dimension table and many side set to fact table
Normalization and Denormalization
Normalization is simply the act of storing data in a way that reduces repetition.Consider a sales table with product key,product characteristics,product name,color and size.A sales table is said to be normalized if it has the product key only as a field.However if it contains products details apart from the key the table is denormalized
Data Modelling Approaches
Flat Table
All data is combined into one single simple table.Here there are no relationships whatsoever as everything lives in one place.It is extremely simple to understand
Where it may be appropriate
Even though a flat table is a single unnormalized table containing all attributes in one place BI tools like Excel,PowerBI process single tables faster than complex joins across multiple relationships
Aggregations like sum,count,group by run across such datasets without expensive JOIN operations
It is also the best model for Low-Volume datasets where relational normalizations adds unnecessary setup overhead.Standalone projects and small projects don't need relationships whereas you can have all information in one place
Advantages & Disadvantages
Advantages
Extremely simple to understand
No relationships or filter propagation issues
Easy and fast to build initially
Disadvantages
Massive Data Redundancy
Data Redundancy It usually refers to data stored repetitively like for example descriptive attributes being repeated across many rows.Redundancy always poses high risk of updating anomalies not forgetting how wasteful of space it can beRepeating data across many rows makes it very difficult to maintain because updating just one piece of information may require changing thousands of rows
It is impossible just to reuse dimensions across multiple fact tables since all information is baked in one table
At some point the file becomes very large because combining data enlarges the model
Power BI Performance & Model Complexity
Flat tables are generally the worst performing structure in Power BI at any meaningful scale.Even though VertiPaq compresses columns having all columns in one table reduces compression compared to separating them into fact and dimensional tables
The irony is the model is harder to analyze meaningfully and yet all characteristics are in one table.This blurs analytical clarity even though they are no relationships to manage
With a flat table you miss on DAX time intelligence functions
Snowflake Schema
A snowflake schema is a data warehouse design where dimension tables are split into sub tables that are normalized. In this design we have multiple related tables as opposed to one flat table.Name comes from its diagram that resembles a snowflake with one central fact table branching out its dimensions
Above is a structure of a snowflake model with DimProductcategory as a subtable from DimProduct. Just to elaborate on how it is structured here is an example of its schema
DimProduct: Product ID,Product Name,Category Name,SubCategory ID
DimSubCategory: SubCategory ID, SubCategory Name,Category ID
DimCategory: Category ID,category Name
Where it may be appropriate
When dimension tables have million of rows,normalizing reduces redundancy saving on significant disk space
When managing complex category trees,a normalized structure provides a single source of truth so updates can only happen in one place
Sometimes you inherit a schema that exists in a normalized source system
When building a central data warehouse that serves multiple BI platforms beyond Power BI,normalization ensures data consistency across all reporting tools
Advantages & Disadvantages
Advantages
Reduced data redundancy eliminating risk of update anomalies
Easier maintenance of hierarchical data
Clear representation of hierarchical relationships
Disadvantages
- More complex joins or relationships - Queries and filters need to go through many hops thus resulting in slower time in execution
Slower query performance - Commands must transverse multiple hops inorder to get to descriptive attributes
With more tables,more relationships the model becomes larger and complex to navigate and maintain.It also becomes harder for report builders to understand at a glance
Power BI performance & Model complexity
DAX Complexity - Snowflake dimension may require multiple related calls making formulas more complex
More tables and more relationships make it harder to read ,maintain and troubleshoot
Filter context has to flow through additional relationships increasing chances of misconfigured cross filter directions
Star Schema
A data warehouse design model where a single central fact table connects directly to multiple surrounding dimension tables usually forming a star shape.A fact table usually sits at the centre of the model consisting of foreign keys that connect dimension tables.The dimension table is usually denormalized into single tables to reduce on joins unlike the snowflake design
Where it is may be appropriate
In almost every Power BI model,it is the most appropriate model structure for reporting and analytics
Where simplicity and performance are priorities over storage optimization
When building models that are DAX heavy
When end users need to self serve with visuals and slicers without navigating a complex web of relationships
Advantages & Disadvantages
Advantages
Simple intuitive structure making it easy for report builders to navigate and understand
Fewer relationships/joins - Each dimension is a single hop from the fact table,filter propagation is simply just direct
Faster query performance
Disadvantages
Data Redundancy - Descriptive attributes are repeated across many rows
Less 'pure' from a normalization standpoint
Power BI Performance & Model Complexity
Far simpler to read,maintain and debugg. Filter context flows from dimension to fact tables without the need of transversing multiple hops
DAX formulas and measures are easy to execute because the related attributes are just one relationship away
Star Schemas are the optimal structure for VertiPaq,because dimension tables are flattened ,Vertopaq compresses repeated columns efficiently,the storage penalty you'd expect in a traditional database barely applies
2.Relationships
A relationship in Power BI is a defined link between two tables based on one common column.It essentially tells Power BI that this is how these tables are connected and here is how to match them.When data is distributed across multiple tables,Power BI needs to know that Customer ID in Sales table refers to Customer ID in Customer table
We establish relationships by matching primary key in the dimensional table to the foreign key in the fact table.Creating relationships between tables allows calculations and aggregations to work across tables so that multiple columns can be used from separate tables
Before we discuss the different types of relationships we need to understand various terms associated with relationships
Cardinality is basically how you establish relationships based on numerical count of matching rows.There are four different types of cardinalities
One to one relationship
One to many relationship
Many to one relationship
Many to many relationship
Primary key is a unique identifier column in a table.It has no duplicates,one key usually represent one value where as foreign key is a primary key in a different table
We can take for example CustomerID as a primary key in DimCustomer.Each value in the CustomerID column represents a unique customer
Above is an image demonstrating primary keys.Basically no other customer apart from Amina Hassan can be identified with customerID 1.Now we need to understand that even though primary key appears once ,foreign keys can appear more than once in a table.Suppose we have a sales table showing the number of sales transactions, we can have Amina Id's appearing more than once,twice because yes Amina can make more than one transaction
In a nutshell a foreign key will just refer you to the primary table
Active & Inactive relationships.Power BI allows only one active relationship between two tables a time.If you have more than one relationship between two tables then only one will be active and the other will be inactive .You will need to activate the other relationship by your own
Referential integrity is a relational database concept that ensures relationships between tables remain valid and consistent.It ensures every foreign key in a fact table matches primary key in the referenced table
Knowing all these concepts we can go one and discuss the different type of relationships
One to One Relationship
For a one to one relationship both columns connected have common and unique values.Take for instance an employee table and an employee details table. You can only have one employee with his/her details,you cant have one employee having different details
One to Many Relationship
A single row in one table matching multiple rows in a different table.Suppose we have a Customer table and Sales table,one customer can make two,three different transactions
You can clearly see from the above image that we can have one product being sold severally in the sales table
Many to One Relationship
It is usually the reverse of one to many relationship,many rows from one table having a match with one row in another table
Many to Many Relationship
Here we have many rows from one table matching many rows from another table.We can have one specific doctor having very many different patients he/she treats and one patient having many doctors he/she visits
2.1 Filter Direction
Now,you need to understand that when you creating relationships you invoke different types of filtering.How filtering behaves depends on the type of relationship
When you do create a one to one relationship or one to many,you do apply single cross filter direction which filters in one direction where as both cross filter filter in both directions
Lets say you do filter a product from DimProduct which is connected to FactSales then you are going to have one aggregated result showing the total sales made from that product.This is a classical example of single cross filter direction
Bi-directional relationship/Both filter direction could result in ambiguous filter propagation.Power BI requires there be exactly one path for a filter to travel.Filtering in both directions create multiple paths and Power BI wont know which one to use.Even if we do have Power BI filtering in both directions successful incase the numbers look wrong,debugging becomes such a hustle because instead of checking a single filter path,you'll need to check in both direction
3.Joins
Joining basically in layman terms can be defined as combining two datasets.Merge is the same as join in relational databases,for merging datasets with each other you need to have joining fields.Unlike relationship tab in Power BI which doesn't allow you to create relationships based on two columns,merging do allow
What are the different types of join
NOTE: The type of join is actually very important because you get very different result set based on different set of join.
When you join two datasets together first table is considered the LEFT table and the second table is considered the RIGHT table
Left Outer Join
It is usually the default type of join.If you don't specify the type of join,your PC will always default to Left outer join.For this kind of join it returns all rows from the first/left table and returns all matching rows from the right table.For the non matching rows it returns null
Right Outer Join
It returns all rows from the right table and matching rows from the left table
Full Outer Join
A type of join that returns all rows from both the left and right table whether they do match or not.It acts as a combination of both Left and Right Join.Matching rows are shown as a single row and for non matching the rows have nulls
Inner Join
Keeps records that match from both tables.It drops any rows that don't match
Left ANTI
Keeps records from the left table that have no matching rows with the right table
Right ANTI
Keeps records from the right table that have no matching rows with the left table
How do i perform these joins
In Power BI,under home tab choose transform data
After launching Power Query,still under home tab on the farmost right side click on Merge Queries
From the dropdown select on Merge Queries as New
You can choose your the tables you want to join and from the tables choose the joining fields
Choose the kind of join from the Join Kind dropdown
3.1 Power Query Joins VS Power BI Relationships
Although both power query and power BI relationships let you "connect" tables, they work at completely different stages and they do give different results.A power query join will let you combine this tables physically because we have seen that when you join these tables you end up with one combined table but for a relationship the tables remain two distinct tables that have been connected to a special key
Power Query joins are performed during the ETL(Extract,Transform & Load) process or simply before you load data,this might be because you might need to transform this 'combined' table before loading it.On the other hand Power BI relationships happen in the model view when you have already loaded data
When do you choose merging over relationship ?
When you want a combined flat table where you can create visuals or report from this one table
Sometimes you just can't afford to have other dimensional tables whereas you can just merge them
Merging allows joining two separate fields especially when a single column is not enough.For example let's have region & year
How can excessive merging affect the structure of a data model
Merging almost everything into one combined table destroys your schema and result to one bloated flat table.Here you lose the independent filtering because the dimension tables that were on there own have now been merged.It also becomes very hard to maintain because now you have data being combined into one and so correcting errors must be done on the one big table as opposed to one single dimension table
Some Power BI tools do have their autodetect new relationships on and so when you do merge tables maybe perform left join,right join,inner join together with the anti joins your Power BI will try to relate them even when the merged tables are very different from related tables.In such instances you see you going to have relationships even when tables are not related
A perfect description is shown above
Why should we keep fact and dimensional tables separate?
By now one should clearly understand that having these two types of tables separately,Power BI is going to have the DAX functions working way much better and easier.Filtering from one table is also going to more efficient and easier and to expound more on filtering,you can reuse tables such that you can have one dimension table used to filter two different tables
Imagine a situation where by you just to correct one table and other related information becomes updated.It is very easy to maintain when you do have dimension tables separated from fact tables
Power Bi performance is also great when you have these tables separated.You are having VertiPaq working super great with star schemas compressing these repeated files making querying way much efficient
4.Recommended Power Bi Visual
For a business intelligence project,I'm going to have to go with
star schema because with a star schema your query and report performance is going to simpler and faster because relationships are closer as compared to a snowflake schema where there are many hops
Just for the same reason of having relationships one step away from each other,DAX functions perform better than the snowflake schema.When talking about querying and DAX simplicity we don't even need to include flat tables because it is the worst hands down
Apart from data redundancy that snowflake schemas performs better,star schema ace all other aspects of a good model,i.e maintaining a star schema is way much simpler,its model performance and complexity is way much better and even for the issue of data redundancy Power Bi vertipaq compresses these repeated values making it better than the traditional ones
5.Conclusion
Good data modelling isn't about the clever transformations,it is about designing something Power BI engine can optimize. It is not how complex your model looks but how efficient it runs.From choosing the best modelling approach to knowing when to use different filter directions,different kinds of joins you need to ensure your system runs perfectly
Top comments (0)