Data typically arrives in Power BI from various sources, and is often in unorganized or inconsistent formats. Data modelling is just the process of cleaning this data and then logically linking it together in such a way that your reports can run quickly and your DAX formula functions. A good model makes your life easier, a bad model will make your Power BI app slow and confusing!
This is a summary of key concepts that you should understand when creating a Power BI model.
1. Data Modelling Approaches
The order of tables is important. You can accomplish this in three ways:
Flat Table: This is a bit like a huge Excel spreadsheet with all sales, customer information, product information and so on stuffed into one large table.
Pros: Easy to view for a rapid scan.
Cons: Lot of repeating data, huge file size and poor performance in Power BI.
You can create a diagram of a One Massive Table with 50 columns.A diagram of a One Massive Table with 50 columns can be created.
Star Schema
: The optimal configuration in Power BI. You have one numbers table, in the middle, with descriptive tables that reference the numbers. It resembles a star.
Cons: Can be very slow when using many different data sources that don't share the same data model.
Cons: It needs a little work up front to correctly partition your data.
Diagram:
Snowflake Schema: Just like a star, but with even more tables such as a product table pointing to a separate category table.
Cons: Offers a little less storage space.
Pros and Cons: Creates a messy model and makes the Power BI tooling slower due to the number of hops it needs to make to filter data.
2. Fact Tables vs Dimension Tables
In order to create a Star Schema, you must divide your tables into two categories: Facts and Dimensions.
Numbers are stored in Fact Tables. Business Events are stored in Fact Tables. They monitor and follow up what has occurred. A fact table's "grain" is how much detail it contains, for example, one row represents one transaction.
Contains: Numbers, dates, and ID keys.
In fact, there are several examples of fact tables, including FactSales , which is a table that contains information about all transactions ,and FactOrders.
Dimension Tables: These are used to hold information about the facts. They give you the who, what, where and when.
The description will include the following: Contains: Text, names, categories, descriptions.
Examples: DimCustomer (names of customers), DimProduct (names of items), DimDate.
Let us take an example of a business which sells spare parts for motorcycles. The FactSales table just logs that on Tuesday, Customer #105 bought Product #50 for KES 2,000. To find out that Customer #105 is John and Product #50 is a brake pad, Power BI looks up those IDs in the DimCustomer and DimProduct tables.
3. Relationships in Power BI
Relationships are simply wires that link your tables together, allowing them to communicate with one another. If they're not in there, the total sales amount won't be filtered by clicking on a customer's name on a dashboard.
One to Many (1:*): Standard and best type. In the DimCustomer table, there can be many Sales in the FactSales table.
One-to-One (1:1): Rare. In Table A, each row corresponds to exactly one row in Table B (one employee to one ID badge).
Many-to-Many (:): Disorderly and typically not done. A large number of students enrolled in a variety of classes. Can cause Power BI to be confused and to generate incorrect totals.
Key concepts here:
Ability to have a unique ID in a dimension table (e.g., CustomerID). No duplicates allowed.
The same ID used in the fact table (Foreign Key). It is repeated because a customer has the ability to purchase items multiple times.
Active vs Inactive: More than one line can be between two tables (such as Order Date and Ship Date) but only one of them can be solid (Active). The other are dotted (Inactive) and require special DAX to get them active.
4. Filter Direction
Filters are passed down the wires when tables are connected.
Single-direction filtering: The dimension table filters the data in one direction only, that is, from the Dimension table to the Fact table. If you filter DimProduct by "Helmets", the dimension will go down and filter FactSales to just show helmet sales. This is the most secure and quickest way.
Both/Bidirectional filtering: Arrows flow in both directions. What's more, the fact table can be used to filter the dimension table. Unless there is a compelling need to use this, you should do your best to avoid using it as it creates “ambiguous paths” and slows down your entire report.
6. Separates in Power Query (Dividing)
Data can be transformed in Power Query prior to it being loaded into the Power BI Model. In some cases, it's necessary to physically join two tables. This is called Merging.
Let's say we have an Orders table and a Customers table.
Left Outer Join: All the Orders are retrieved and only Customer details are retrieved when there is a match.
Right Outer Join: - Retains all Customers; Only orders when they match.
Full Outer Join: Returns all the data from both tables, matching them when they have a matching field, but leaving the values empty when they do not.
Inner Join: Only returns the rows with a match in both tables. If there is no matching customer, then the order will be dropped.
Left Anti Join: Only return the Orders which do not have a corresponding Customer.
Right Anti Join: Remains only the Customers that have not ordered anything.
Expected Output Example (Inner Join): When you Inner Join Orders to Customers, the output table will only contain orders that had a Customer ID on their Order Line item, and not those that were a cash order.
Power Query Joins vs Power BI Relationships
They can be easily confused, but they occur at different times and do different things:
Power Query Merge (Joins): This occurs prior to the data being loaded. It physically "mashes" columns from two tables into a wider table. It will make files larger and refreshing slower.
Power BI Relationships: This occurs once the data has been loaded. The tables remain completely independent in the Model view and are only connected by a virtual wire.
When to use which? Always use tables separately and use Relationship 90% of the time. It's better for BI. Use the Power Query Merge only if you have to get some messy data into a single dimension table before loading it.
7. Recommended Power BI Model
In my opinion, the Star Schema is a very good solution for a typical business intelligence project.
The way it should be set up is:
Data Model: Star Schema (1 fact table as center surrounded by multiple dimension tables).
Relationships: Strictly One-to-Many (1:*).
Filter Direction: Only Single direction from Dimensions to Fact table.
Why? The Star Schema has the "Goldilocks" condition.
Performance: The underlying engine of Power BI (VertiPaq) is actually optimised to read Star Schemas very quickly.
Writing DAX on a Star Schema is easy, DAX Simplicity. No more bidirectional filters or peculiar many to many errors.
Maintainability: When the business adds a new product category or a new store branch, you simply update the dimension table for that category without destroying some big, big flat file. It maintains your model clean, scalable and easy to read.
Top comments (0)