Introduction
Welcome to Power BI, which in my opinion is Excel’s slightly cooler cousin. Once you’re up to speed with Excel, Power BI becomes a breeze and, in this article, I shall take you through data modelling in Power BI, as well as other concepts such as schemas and relationships.
Once you’ve transformed your data by cleaning it, you can load it. Let’s begin with data modelling.
Data Modelling
This is the process of structuring and organizing data to create meaningful relationships, hierarchies and calculations. Data modelling transforms raw data from different source into structured format that supports analysis and reporting.
There are three types of data modelling;
- Conceptual data modelling
This uses the overall structure of data that can e used to organize business processes or objectives into entities. Entities are groupings such as clients, products and they can have relationships with other entities.
- Logical data modelling
It is built on top of the conceptual data model and is one level down from it. This means that it captures the attributes within each entity and the logical relationship between them.
- Physical data modelling
It’s a specific implementation of the logical data model.
Data modelling has a number of benefits. One is that it improves data integrity and reduces errors. It also optimizes performance and speed of analysis. Data modelling defines data needs and users thus improving business processes.
Let's begin with relationships.
Relationships
Relationships are just as the word suggests; connections between different tables. Relationships support measures and calculated columns that reference multiple tables.
They enable cross-table analysis without merging.
Cardinality refers to the type of relationship of which there are a number:
a. One-to-many (1: ) – One record in one table relates to many records in another table.
b. Many-to-One ( :1) – Many records in one table relate to one record in another.
c. Many-to-Many (:) – Both tables have many matching values
d. One-to-One (1:1) – Each row in 1 table matches exactly one row in another.
Cross-filter direction helps you determine whether the filters flow in either a single direction or both directions.
Relationships are established by matching primary keys in dimension tables with corresponding foreign keys in the fact table.
And that brings us to the next section, Schemas.
Schemas
Now we know relationships, so let’s move ahead to find out about schemas. A schema is a specific arrangement or pattern that you choose to help you organize your data tables and relationships.
When creating a schema, it is important to ask yourself what exactly is being measured and how do you want to compare the results.
A schema has two kinds of tables – a fact table, and a dimension table.
Fact Table
A fact table has measurable data and contains records of transactions or events. It usually contains numeric values and grows continuously.
Examples include payment transactions and ID’s.
What’s important to note about fact tables is that they contain the grain of the data.
A grain is the detailing of what unique dimension values each row represents.
The facts table also has both primary and foreign keys (which I will explain in a bit).
Dimension Table
A dim table gives meaning to the facts table.
It is descriptive in nature, answering the who, where, what questions.
Examples are customer, product, location information.
They have primary keys.
Keys
Primary Key - This is a unique identifier and cannot have duplicates i.e ID’s
Foreign Key – This is a key in a table that refers to the primary key in a related table. Mostly found in fact tables.
Types of Schemas
1. Star Schema
This is an arrangement whereby there is a central fact table that is directly connected to multiple dimension tables.
Advantages
• Easy to understand
• Faster query performance
• Simplifies DAX calculations
Disadvantages
• Takes up more storage space
• Data redundancy in dimension tables
• Less suitable for normalized data
2. Snowflake Schema
This schema resembles the star schema except that it has more normalized dimension tables.
Normalized tables are tables that have been broken down into separate tables that are related to each other.
Joins
Finally, we have joins which we perform in power query. Joins combine two tables based on matching values
a. Inner Join – keeps only rows where records exist in both tables i.e below finding customers who placed orders
b. Left Outer Join – keeps all rows from the left table (1st), and matches from the right table, 2nd.
c. Right Outer Join – keeps matches from 1st table and all rows from 2nd
d. Full Outer Join – keeps all rows from all tables
e. Left Anti-join – keeps rows from 1st tables with no matches in 2nd table i.e below, I fetched customers with no orders
f. Right Anti-join – keeps rows from 2nd table with no matches in 1st i.e orders with no customers.
In Conclusion
Learning about data modelling has been quite the interesting journey, and yet it is not over.
I'm excited to be using the insights I've drawn from this into my work as a data analyst and scientist.
Top comments (0)