Power BI is a suite of business analytics and data visualization tools developed by Microsoft. It allows you to connect to hundreds of data sources, clean and model raw data, and transform it into interactive, shareable dashboards and reports.
Data Modelling
A data model is a collection of tables, relationships and calculations that represent the underlying structure of the data. The data model defines how data is stored, how different data entities relate to each other and how calculations are performed.
Reasons for data modelling
- Supports Data Exploration - Data modeling enables users to create hierarchies and drill-down paths, which support efficient data exploration.
- Affects Performance - How a data model is designed directly impacts the speed and efficiency of data retrieval.
- Promotes More Accurate Reports - When a data model is designed correctly, it can guarantee the accuracy, consistency, and dependability of the data used in reports.
- Easier Future Maintainability - Creating reusable components, improving documentation, and standardizing data can also ensure that reports are easy to maintain and update over time.
There are three common data models:
1. FLAT TABLE
This is a data model where all the all the data is contained in a single table.
This is the most basic data model, and only recommended for one-off analysis.
Using a flat table can result in functional issues especially when the data scales in volume.
2. STAR SCHEMA
The star schema is a well-established data modeling technique. This approach requires breaking down the data into a facts table and related dimension tables. All of the dimension tables must have a direct relationship to the fact table.
Facts table is the table that stores the transactions that occurred in the data set.
It answers the question "What happened?"
Dimension table is a table that contains explanatory data of the information being presented through the transactions.
The facts table and dimension tables are linked through primary and foreign keys.
A primary key is a unique, non-blank column that identifies the rows in a table, whereas a foreign key is a column that references the primary key of a different table.
The star schema is considered better than a flat table since:
- It reduces repetition of data therefore less resource intensive.
- Clearly outlines relationships among the data entities thus easier to understand and perform operations on the data.
3. SNOWFLAKE SCHEMA
This model is a further breakdown of the star schema where not all dimension tables have a direct connection to the facts table.
The dimension tables where data is further repeated are broken down further into logical sub-dimensions.
I have mentioned that the facts and dimension tables are connected and it is now time to explore what these connections are and how they work.
HOW RELATIONSHIPS WORK IN POWER BI
Relationship is the connection between tables through common columns, i.e. foreign keys.
Relationships in Power BI are illustrated through cardinality i.e. how rows in one table relate to rows in another table.
These are:
ONE TO ONE (1:1)
This is where column in one table has only one instance of a value and the other related table has only one instance of the value, such that each row in a table matches exactly one row in the other table.
ONE TO MANY (1:*)
This is the most common relationship. It occurs when one value in the first table can be related to many records in the second table, such that one value in a dimension table matches to multiple values in the facts table.
MANY TO MANY (:)
This relationship occurs when multiple records in the first table can be related to many records in the second table. This relationship indicates the data model needs to be restructured properly.
Cross-filter direction
Cross-filter direction determines how filters move between tables.
Single direction means filters flow from the dimension table to the fact table.
Both directions allow filters to flow both ways between tables.
Single direction is recommended in most models because it is:
- easier to understand
- less error-prone
- aligned with star schema logic
Bidirectional filtering can be useful in specific scenarios, but it also increases the risk of ambiguous filter paths and unexpected results.
Active vs inactive relationships
Power BI allows multiple relationships between the same tables, but only one can be active at a time.
Active relationships are used automatically by visuals whereas inactive relationships exist in the model but are ignored unless explicitly activated in a measure.
MERGE QUERIES
Data modelling also involves operating on the structure of the tables in order to end up with an efficient model. This can be achieved through merge queries.
A Power BI merge query is an operation that joins two existing tables together based on matching values from one or more columns.
Types of merge queries
INNER JOIN
This operation returns only the matching values form both tables based on the foreign key.
FULL JOIN/FULL OUTER JOIN
This operation returns all the rows from both tables and fills null values where there is no match.
LEFT OUTER JOIN
This operation returns all rows from the table placed on the left and returns only the matching rows from the table placed on the right.
RIGHT OUTER JOIN
This operation returns all the rows from the table placed on the right and returns only the matching rows from the table placed on the left.
LEFT ANTI JOIN
This operation brings in only the row form the table placed on the left that do not have any matching rows from the table placed on the right.
RIGHT ANTI JOIN
This operation brings in only the row form the table placed on the right that do not have any matching rows from the table placed on the left.
CONCLUSION
Data modelling is the foundation for any data analysis. A proper model simplifies the data into an easily understandable structure that can be used to perform calculations, perform accurate analysis and generate reports.
One has to master the foundations such as differences between data models, how to select the proper model for a dataset, how cardinality works and how relationships can be best used within a model and how to use merge queries in order to properly utilize data.
Top comments (0)