Data Modelling, Relationships & Joins

Data Modelling in Power BI In data analytics and business intelligence the success of a power BI solution lies in it's data model. Exploring data modelling principles, relationships management and join operations equips you with the knowledge to design a robust data model. A well-architected data model ensures accuracy in your analytics as well as simplicity in your DAX calculations. Data modelling is the process of organizing, defining and designing tables, how they establish relationships with other tables and rules that govern data interactions. It becomes the blue print through which data is stored, filtered and queried to answer business questions and give insight. Components of Data Modelling: - Tables: Containers for your data (facts and dimensions) - Relationships: Connections between tables (one-to-many, many-to-one). How many rows in one table can link to rows in another. - Keys: Unique identifiers (Primary Keys and Foreign Keys) - Cardinality: The nature of relationships (1:1, 1:M, M:M). - Filter Direction: How filters flow between tables (single or bi-directional) Flat table It represents the simplest form of data modelling where all data elements exist within a single table structure and with no relationships with other tables. The table is a massive data set which contains data redundancy, mixed granularity example the data contains customer information together with product store data. It's primary advantage is that its simple with no complex logic or relationships to understand. The disadvantage far out way advantages. Flat tables cannot provide any business intelligence solutions as the data is very redundant. If a customer like "Jack" has made fifty purchases, his name, email address, segment classification, and geographical information are duplicated fifty times across the table. This redundancy wastes memory and storage space, leading to bloated file sizes that can be five to ten times larger than necessary. When you need to update customer information, you must modify every single row where that customer appears, creating opportunities for data inconsistency and requiring expensive update operations. This leads to poor performance. Star Schema The star schema represents the industry-standard approach for dimensional modelling in business intelligence and is the recommended design pattern for Power BI solutions. This architecture consists of a central fact table surrounded by multiple dimension tables, creating a structure that resembles a star when visualized in the model diagram. The fact table **sits at the center and contains the measurable, quantitative data about business processes, while the **dimension tables radiate outward and contain the descriptive attributes that provide context for analysis. Example of a star schema approach In a retail sales scenario, the fact table **would be named FactSales and would contain one row for each individual sales transaction. Each row would include foreign key references to dimension tables, such as DateKey, CustomerKey, ProductKey, and StoreKey, along with numeric measurements like SalesAmount, Quantity, Discount, and Profit. **The dimension tables, such as DimDate, DimCustomer, DimProduct, and DimStore, contain the descriptive attributes. DimDate might include columns like FullDate, Year, Quarter, Month, Day, DayOfWeek, and IsHoliday. DimCustomer would contain CustomerName, Email, Segment, Region, and City. DimProduct would include ProductName, Category, Subcategory, and Supplier. DimStore would contain StoreName, Address, City, State, and Region. Advantages - Separating the repetitive foreign keys and having relatively small dimension tables improves query performance ie faster than flat tables. - DAX calculations become remarkably simpler because relationships enable automatic filter propagation. - Data redundancy is minimized because each dimension attribute is stored only once. Customer information exists in a single row in DimCustomer and is referenced by the CustomerKey foreign key in every related sales transaction. - Scalability is excellent because the fact table can grow to billions of rows while dimension tables remain relatively small and stable. Snowflake Schema Approach. The snowflake schema represents a normalized variation of the star schema where dimension tables are further broken down into sub-dimensions, creating multiple levels of related tables that resemble a snowflake pattern when visualized. Advantages - Has reduced storage requirements through additional normalization. Category names are stored only once in DimCategory rather than being repeated for every subcategory and product, and subcategory names are stored once rather than repeated for every product. Disadvantages - Performance degradation is the most critical issue because queries must traverse multiple relationships to reach the descriptive attributes. - DAX calculations become more complex because measures must account for the additional relationship layers. Simple calculations that would be automatic in a star schema require explicit handling of filter context across multiple tables. Relationships in Power BI In Power BI, a relationship is a logical connection between two tables that enables the automatic propagation of filters from one table to another, allowing data from multiple tables to be queried and analyzed together as if it were a single unified dataset.Without relationships, Power BI would have no way of knowing how to connect a customer's name in DimCustomer to their sales transactions in FactSales, or how to associate a product's category in DimProduct with its sales amounts in FactSales. Cardinalities Relationship cardinality describes the numerical relationship between rows in one table and rows in another table, and Power BI supports three primary cardinality types: one-to-many, one-to-one, and many-to-many. Cardinality refers to the uniqueness of data in a column **and is closely related to the **concept of primary and foreign keys. A column with high cardinality has many unique values relative to the total number of rows, such as a transaction ID column where every row has a different value. A column with low cardinality has few unique values relative to the total number of rows, such as a gender column with only two or three distinct values. In relationships, the "one" side typically has high cardinality (one unique value per row), while the "many" side has low cardinality (many rows sharing the same foreign key value). One-to-Many relationships A single row in Table A can match multiple rows in Table B. Example: A Customer table linked to an Orders table (one customer can place many orders).This is the most used relationship. One-to-One relationship Each row in Table A matches exactly one row in Table B. Example: A User table linked to a User_Passport table (one person has one passport). Many-to-Many relationship A many-to-many relationship exists when rows in the first table can be associated with multiple rows in the second table, and rows in the second table can also be associated with multiple rows in the first table. Example: A Student table linked to a Classes table (a student takes many classes; a class has many students). Filter Direction Filter direction determines how filters applied to one table flow through relationships to affect other related tables in your data model. Understanding filter propagation is essential for creating accurate reports and avoiding common pitfalls that can lead to incorrect results or performance problems. Single-direction filtering Single-direction filtering, also known as one-way filtering, is the default and recommended filter direction for most relationships in Power BI. In a single-direction relationship, filters flow from the "one" side of the relationship (typically a dimension table) to the "many" side (typically a fact table), but not in the reverse direction. This means that when you select a value in a dimension table, such as clicking on "Electronics" in a DimProduct[Category] slicer, that filter automatically propagates to the FactSales table, showing only sales transactions for products in the Electronics category. However, filters do not flow from FactSales back to DimProduct, which means that selecting a specific sales amount range in a fact table measure would not filter the products shown in a dimension table visual. Bidirectional filtering Bidirectional filtering, also known as both-direction filtering or cross-filtering, allows filters to flow in both directions across a relationship, from the "one" side to the "many" side and from the "many" side back to the "one" side. When bidirectional filtering is enabled, selecting a value in either table affects the other table, creating a two-way filter propagation. Bidirectional filtering can be useful in specific scenarios, such as when you have a many-to-many relationship implemented with a bridge table, and you need filters to flow from both dimension tables through the bridge table to the fact table. Joins in Power Query A join is an operation that combines rows from two tables based on a related column between them, creating a single unified table that contains columns from both source tables. Joins are performed using the Merge Queries feature in Power Query Editor, which allows you to specify which columns to match between tables and what type of join to perform. Left Outer Join A left outer join, often simply called a left join, returns all rows from the left table (the first table you select in the merge operation) and only the matching rows from the right table (the second table). If a row in the left table has no matching row in the right table, the columns from the right table will contain null values for that row, but the row from the left table is still included in the result Right Outer Join A right outer join is the mirror image of a left outer join, returning all rows from the right table (the second table) and only the m