Introduction
This article takes through the ideas that decide whether a model works as intended
Schemas; how table are organized
Relationships; how the model connects tables
Joins; how tables are physically combined in power query
Each section on this article explains the concept, shows with an example and guidance on when to use each one of them.
Data Modelling
Data modelling in power BI is the process of connecting separate data tables together so that they can have a connections with each other and produce accurate answers for business questions and demands.
Importance of Well Designed Data Model
Analytics - The data model is flexible and fully integrated, numerical metrics can be grouped or filtered by categorical attribute(dimensions like date) without hitting broken relationships and rigid hierarchies. User interface is easier to navigate and data fields are logically organized and named so that business user can easily locate metrics and categories without strain.
DAX Calculation - When one has a well designed data model theres no need to write complex formulas and codes to tell measure on how to filter data.
Performance - Having a well designed data model impacts the performance by preventing the model from hitting memory limits and faster data refreshes and a quicker visualization on reports.
Scalability - As the data model get bigger a lean one allows facts, dimensions and data sources to seamlessly integrate into the model without requiring a full report or have a model redesign.
Maintainability - A well designed data model makes it easier to debug, update, and manage over time without breaking existing reports.
Comparisons between Flat Table, Star Schema And Snowflake Schema
1) Flat Table
A flat table stores everything in one wide table, each row is transaction and every descriptive attribute the transaction needs like customer name, product name and category is repeated on that row.
Advantages
Faster to build, is exactly what a CSV export or database view already looks like.
No relationships to configure
Easy to understand and analyze
Disadvantages
Redundancy, storage of exact piece of information or repeated attributes values across multiple tables inside the data model instead of being organized into one relatable table.
Mixed Grain, a data model or table is combined into different level of details.
Hard to extend, adding more facts is hard since there's no shared dimensions to related to.
Flat table are appropriate when dealing with small dataset that will not be reused or combined with other datasets.
Performance and Complexity on Power BI
This model is simple but is heavy to run. Many text columns inflate the memory and perform slow scans. Performing DAX becomes impossible and complicated because counting distinct text columns needs extra logic to escape repeated rows.
2) Star Schema
A star schema has one central fact table surround by dimension table that are connected to the fact table using a single key.
Advantages
Simple and Predictable filtering - Every filter is one hop from dimension to fact
Simple DAX Once a measure is written it against the fact it works with any dimension
Easy to read and extend: User can tell which table hold metrics and which one hold filters. When a new business process is added against the fact table it can be depended onto other dimension tables.
Disadvantages
More tables and relationships than flat table making it to take time to design.
Dimensions have some redundancy
Star schema is appropriate in the day to use for reporting and analytics project
Performance and Complexity on Power BI
Start schema's give the best balance in Power BI, relationship is traversal is minimal, also star schema use very minimal memory and the model is easy to go about
3) Snowflake Schema
A snowflake schema is a version of star schema but in which the dimension are further categorized into sub-dimensions.
Advantages
Less repetition inside the dimensions.
Easier data maintenance, modification can be made one the intended sub-dimensions without altering the data model.
Disadvantages
With more table and relationships it becomes complicated to navigate
Filter must travel through several table before reaching the fact table.
When Its appropriate, when dimensions are large wiith more complex hierarchies.
Performance and Complexity on Power BI
To filter a chart using snowflake schema, power bi must traverse multiple tables through a chain relationship making it not recommended.
Facts and Dimension Tables
A fact table is a central data model that stores numerical number, measurements or metrics of a business like customerID, productID, sales amount. Dimension tables is a supporting table in data model that stores descriptive attributes and background information about a business like product name, category and customer email.
Information Stored in fact tables
Foreign keys - connects to dimensions tables this includes (productID, customerID)
Numeric number - This are numeric columns that contain business transaction such as (Sales amount, Units sold, discount percentage)
Information stored in Dimension tables
Primary Key
Every dimension table consist of unique IDs that acts as master key connects descriptive information and details to the central fact table.Descriptive text attributes
This are columns that contain descriptive that is used to slice, dice, filter and group the data inro charts, tables and slicers.Hierarchies and Categories
Dimension tables hod structural grouping that allow data to be arranged into forms of hierarchies.
Product category-Category-subcategory-product name
Grain of a Fact Table
The grain of a fact table is the level of detail that is show in a single row in a table.
Relationships and Joins Between Tables
Relationship is a rule that indicates how two or more table are connected or linked.
*One to many *
One row matched many rows. Filters flows from the one side to the many by default.
One to One
Each key appears at once on both tables. Filter flow in both direction.
Many to Many
Multiple row in one table are clinked to a different table with multiple rows.
Filter Direction
Filter direction refers to how a filter travels across relationships line to filter in other tables.
1) Single directions filtering
Filtering moves in one direction from one side dimension table to the many fact table.
2) Both directions filtering
Filters flows between the two tables along a relationship line. In setting up the dimension table can filter the fact table and fact table can filter the dimension table.
Why Bi-directions is discouraged
1) Performance
It heavily degrades report performance on large datasets since the engine is constantly evaluating complex bi-directional filtering logic.
2) Ambiguous Filter paths
With more than one route to be used it cannot the determined which path to be used.
Joins in Power Query
A join combines two separate tables horizontally by matching columns in one or more key columns. In power query this is done with merge queries feature, pulling matching details from both the left table and the right table into structured columns that can expand.
Types of Joins
1) Left Outer Join
A left outer joins returns all rows are from the left on the first table and adds matching columns from the second table whenever theirs a match. Unmatched row gets to be highlighted as "null" from the columns on the right.
2) Right Outer Join
Similar to the left outer join it joins returns all rows from the right table and adds matching columns from the second table whenever theirs a match. Unmatched rows gets highlighted as "null" from the columns of the left.
3) Full Outer Join
Matches are combined from both tables and unmatched one get to be highlighted as null.
4) Inner Join
Inner joins only returns rows that have a match in both tables. Those rows that are not matched are dropped and do not return null or blanks.
5) Left Anti Join
Left anti joins returns rows from the left table haven't matched in the right table.
6)Right Anti Join
Right anti joins returns row from the right table that haven't matched in the left table.
Power Query Joins vs Power BI Relationships
The difference is that power query join are physical data transformations that combine columns into tables while power BI relationships are logical data model that creates virtual connections.
1) Does a Power Query merge physically combine columns/data from tables?
Merging in power query evaluates matching keys and copies columns from the secondary table to the primary table resulting into a singled merged table.
2) Does creating a relationship combine the tables?
Creating a relations does not result in a combined table since creating a relationship does not copy or physically move data. The table remain separate. A relationship simply is creates a path that allows power bi DAX to perform filters on one table to another.
3) At what stage of the Power BI workflow does each operation occur?
Power Queries happens before the data is loaded into the model.
Relations happens right after the model is loaded into the model at the data modelling stage.
4) When would I choose a merge instead of a relationship?
I would choose a merge over relationship when i want to convert snowflake schema into easy to use star schema.
5) How can excessive merging affect the structure of a data model?
Excessive merging turn a clean relations structure into a huge and flat table which then takes up a huge storage. Large tables bloats the model size, refresh rate become slower and degrades its report rendering performance.
6) Why might keeping fact and dimension tables separate be preferable in a BI model?
Keeping the fact and dimension tables separate helps write clear DAX measures becomes straightforward since filters flows in a clean 1 to many relationships. Improves performance making rapid computations.
Recommended Power BI Architecture
I would recommend the star schema since for business intelligence project, it separates data into central fact table which contains quantitative measurements and foreign keys that is surrounded by dimension tables that contains descriptive attributes the following are the justifications.
1) Query and report performance
Vertipaq is a columnar database engine that is engineer and optimized for a start schema that can scan and filter compact tables instantly.
2) DAX simplicity
Filter flow from 1 sided dimension table to many sided fact table, this makes DAX measures into simple and readable.
3) Filter Propagation & Model Complexity
In star schema it is recommended to use a one to many relationship that has a single directional filter
4) Scalability and Maintainability
When new data sources are introduced including tables along with existing table there's no need to rebuild the model. The existing dimension tables can link with the a new fact table.
5) Ease of Creating Reports and Model Readability
The start schema creates the best layout for clean and clear structure in that the dimension table hold all the descriptive attributes and the fact table holds the numerical metrics.
Conclusion
Understanding between power query operations and power bi modelling is the defining line. Choosing when to merge tables and when to have then isolated directly dictates the performance, scalability and the long term maintenance of the data model. By applying the data transformations early in power query and highlighting relationships efficiently in the data model this enable reports to be understandable.
Top comments (0)