What is data modelling and why is it essential?
Proper data modelling practices are critical to creating profitable and scalable Power BI reports.
In this article, we will be talking about what data modelling is, why it is essential, and the role of the star schema in effective data modelling.
What is Data Modelling?
Data modelling is the process of creating a visual representation of how data is arranged and related to one another in a database or system.
A data modeller develops a detailed plan for how data will be stored and arranged in a database, much like an architect does to construct a building like creating the blueprint of a building.
Why is Data Modelling important?
Data modelling helps to organize data logically and efficiently. The following are reasons for data modelling in Power BI:
- To prepare data for analysis: Before we can start analyzing data in Power BI, we need to ensure that it is in the right format and structure. Data modelling helps to transform raw data into a format that is easier to analyze.
- To combine data from multiple sources: Often, data for analysis is scattered across multiple sources such as Excel files, databases, or web services. Data modelling in Power BI enables us to bring all this data together into a single, unified data model.
- To create relationships between data tables: When data is stored across multiple tables, we need to define relationships between them to analyze the data effectively. Data modelling in Power BI allows us to create these relationships.
- To calculate custom metrics: Data modelling in Power BI also allows us to create new metrics based on the existing data. For instance, we can create a new metric that calculates the total sales for a particular region or product category.
One of the key data modelling techniques employed in Power BI is the star schema, an approach that unlocks the platform’s full potential.
The Star Schema
A star schema is a multi-dimensional data model that is employed to arrange data in a database in a way that makes it simple to understand and analyze. It consists of a central fact table and multiple dimension tables.
A fact table is like a big table that stores important numbers or measurements. Each row in the table represents a specific event or transaction, and the columns contain the details or attributes of that event. Let’s say we have a fact table for sales, each row could represent a sale, and the columns might include the sale amount, the date of the sale, and the customer involved.
The fact table holds the core information that we want to analyze.
A dimension table is a table that provides additional information about the data in the fact table. It helps us understand and organize the data better. For instance, we have a dimension table for customers, it might include columns like customer name, address, and age. Each row in the dimension table represents a unique customer.
Dimension tables contain descriptive attributes or characteristics that help categorize or describe the data in the fact table.
The Relationship between Fact and Dimension Tables:
The fact and dimension tables are connected through a special link. This link helps us combine or relate the data from both tables to get useful insights. We use a common key, which is like a unique identifier, to establish this relationship. Such as, if the fact table has a column called “customer ID,” the dimension table might also have a column called “customer ID.” By matching these common keys, we can bring together the facts from the fact table and the descriptive information from the dimension table.
The image below is an example of a star schema in Power BI: Establishment Survey is the fact table while Time, Age and Industry are the dimension tables.
Conclusion
In Power BI, data modelling is a key component of creating powerful reports and visualisations and there are key points to look out for when data modelling in Power BI, they include:
- Understand Your Data: It’s crucial to have a thorough understanding of your data sources, their structure, and the connections between various tables before you begin modelling your data. Having this knowledge will help in making sensible choices while modelling.
- Keep it simple: it is important to keep your data modelling as simple as possible and not overcomplicate with unnecessary tables or relationships.
- Create Relationships: Identify connections between tables based on shared columns or keys.
About the Author
You can follow Faith Oseghale on Twitter and LinkedIn.