Making Sense of Power BI: Data Modelling, Relationships, and Joins

Power BI, a data visualization tool, requires effective data modeling to ensure reports run smoothly and DAX functions operate efficiently. Data arrives in unorganized or inconsistent formats, necessitating the data modelling process to clean and logically link the data together. A well-designed model enhances efficiency, while a poorly structured one leads to slow performance and confusion.

Data modelling in Power BI can be approached through three main methods: Flat Table, Star Schema, and Snowflake Schema. The Flat Table method resembles a large Excel spreadsheet with all sales, customer, and product information in a single table. This approach is easy to view but results in repetitive data, large file sizes, and poor performance in Power BI.

The Star Schema is considered optimal in Power BI. It consists of a central numbers table with descriptive tables that reference the numbers. This star-like structure offers a balance between performance and storage but requires initial effort to correctly partition data. The Snowflake Schema is similar to the Star Schema but includes more tables, such as a separate category table for a product table.

Although it saves storage space, it creates a messier model and slows down Power BI tooling due to the increased number of hops required to filter data.

Fact Tables and Dimension Tables are crucial components of the Star Schema. Fact Tables store numerical data, business events, and monitor what has occurred. Their grain defines the level of detail, such as one row representing one transaction. Examples include FactSales and FactOrders. Dimension Tables hold information about the facts, providing details about who, what, where, and when.

They contain text, names, categories, and descriptions. For example, a DimCustomer table might include customer names, while a DimProduct table lists item names.

Relationships in Power BI connect tables, allowing them to communicate and share data effectively. Without these connections, key metrics like total sales may not be filtered correctly. The most common relationship type is One to Many (1:*), where a dimension table can have many related rows in a fact table, but not vice versa. One-to-One (1:1) and Many-to-Many (:): relationships are less common and can cause confusion in Power BI, potentially generating incorrect totals.

Filter Direction is another important concept. Filters are passed down the wires when tables are connected. Single-direction filtering allows dimension tables to filter fact tables in one direction, ensuring a secure and fast filtering process. Bidirectional filtering, where arrows flow in both directions, can cause "ambiguous paths" and slow down reports unless absolutely necessary.

Data can be transformed in Power Query before being loaded into the Power BI Model through processes like merging tables. Left Outer Join, Right Outer Join, Full Outer Join, Inner Join, Left Anti Join, and Right Anti Join are different types of joins that serve specific purposes in data manipulation. Power Query joins occur before data is loaded, while Power BI Relationships take effect after loading, connecting tables to enable data sharing and efficient filtering.