POWER BI -DATA MODELLING ,RELATIONSHIPS AND JOINS

INTRODUCTION

Power BI is a powerful platform where raw data is collected from various sources , cleaned, analyzed and presented in interactive dashboards and reports to support decision making. Concepts like Data modelling ,relationships and joins are what make these reports interactive and insightful.

## 1.DATA MODELLING
In Power BI , data modelling is used to organize data into tables and define the relationships between them. This allows to filter, analyze and summarize your data in a more meaningful and elaborate manner. A well- designed data model helps to:

• Create well structured and meaningful reports easily

• Improve data quality by reducing errors, repetitions and other inconsistencies

• Organize data that helps in better reporting

*### Data modeling approaches *

1. Flat Table
This is one single table that contains everything .For example , customer order, customer id, dates ,regions, groceries, sales .
It has no relationships ,no separtion.

Advantage: it is simple for beginners ,it has no joins.

Disadvantage: there is huge data redundancy and repetition .It shows poor performance

2.Star Schema
This is the most recommended and standard approach for Power BI.
It is one fact table at the center surrounded by dimensional tables .'

Advantages:

• Easy for users to understand

• It has low model complexity

• simple DAX

• minimal joins

• very fast

Disadvantages:

• data redundancy in dimensions

• limited flexibility since it bult for specific ,pre-determined and analytical use cases and queries.

1. 3.Snowflake Schema

It works just like a star Schema with the dimensional tables further broken down to several smaller relatable tables. Example: Dimlocation split into Dimcity->Dimcounty->Dimreligion.

Advantages: It is normalized there there is less data redundancy
Disadvantages:It has so many joins, complex DAX and slower queries therefore it could be complicated for business. It IS slower(increases complexity).

Snowflake schema is used in power BI when source system is highly normalized and cannot be denormalized.

**

Facts Tables and Dimensional Tables**

1. Facts Tables
This tables hold specific events transactions that are to be analyzed and used to establish relations.

Characteristics:

• holds data in quantities,

• it contains numerical records or events

• it contains foreign and primary keys

• holds data metrics

• helps understand the grain of the data

Grain is the description of what each record in a table consist of

2. Dimensional Tables
This tables give a description of events that are held on the fact tables.

Characteristics:

• uses descriptive context .Who,where,when
• All characteristics have a primary key.

Relations in Power BI

A relationship is a logical connection between two tables using keys(primary keys and foreign keys). It allows filters to flow and avoids physically merging tables.
Primary Key (PK):is a unique identifier in dimension tables. Eg. CustomerID in DimCustomer is unique.

Foreign Key (FK): is a primary key in a fact tables. Eg. CustomerID appears many times in FactOrders.

Unique Values: PK must be unique, FK can have duplicates.Referential Integrity: Every FK in fact should have a matching PK in dimension
.Active vs Inactive: Only one active relationship between two tables. Second relationship (e.g. Order Date and Delivery Date both to DimDate) becomes inactive, activated with USERELATIONSHIP() in DAX.

Cardinalities:it refers to how data in one table can relate to data in another table based on numerical count of matching rows.

Types of Cardinalities:

1. One-to-Many (1:_): Refers to one row matching to many rows inanother table.it is Standard and recommended. One customer in DimCustomer can have many orders in FactOrders. Example: DimCustomerCustomerID -> FactOrdersCustomerID.

2. One-to-One (1:1): One row matches exactly one row. Rare. Use for extension tables or security. Eg. DimCustomer and DimCustomerDetails. Avoid unless necessary.

3. Many-to-Many (:): refers to multiple rows that can link into multiple rows in another table. Example: Customers buying multiple Products and Products bought by multiple Customers. In modern Power BI, it works but requires careful handling. Avoid if you can create a bridge table. Do not use as default.

Filter Directions

They help to show how filters flow through relations.
Examples of filter directions

• Bidirectionalthis allows filters to flow from both ways ,example; Fact->Dim and Dim->Fact. We can use bidirectional for some cases like many-to-many bridge tables or when you need a dimension to filter another dimension through the fact.

-** single Direction** this is the most recommended filter direction ,the filters flow from dimension to fact (from DimCustomers to FactSales).Single direction filter is more efficient and predictable.

Joins In Power Query (merge query)

Joins help us to combine two tables into one using matching columns by using primary keys and foreign keys.

1. Inner joins We use inner joins to keep only records that match on both tables.

2.Left Outer joins
We use left outer joins when we want keep all the rows from the left table (first table )and the matching rows from the second table .

3.Right Outer joins
We use this when we want to to keep all the rows from the second table and the matching rows from the first table

4. full outer join
We use full outer joins to keep all the rows from both tables whether they match or not
**
5.Left anti-join**
It is used to keep only rows in the first table that have no match in the second table.

6.Right ant-join
We use this type of joins to keep only the rows from the second table that have no match in the first table.

###Distinction Between Power Query Joins and Power BI Relationships

• power Query Joins increases tale size ,reduces tables which my cause redundancy while Power BI Relationships keeps normalized and is more efficient.

• Power Query Joins is used during data loading and data transformation before data modelling while Power BI Relationships are used after loading data, in the model view.

Conclusion

In conclusion, the most recommended Power BI Model is the STAR SCHEMA, since it provides the balance across all criteria:

• Dax formulas are short ,clean and very performant since filters from from dimension to fact naturally

  • Star Schema minimizes joins at query time hence easy and fast query and report performance.
  • Developers and business users can be able to read and understand the visuals created easily with this model since the fact are at the center with the dimensions around.
  • In star schema data scales cleanly because data can grow to as many fact rows as possible with the dimension tables remaining smaller.
  • Data redundancy in star schema supports performance by de-normalizing dimension tables by keeping eg. cities, states and countries in one single table
  • When making changes in a star schema only single dimension tables are updated leaving the fact table and historical tables untouched, this helps to reduce risk of breaking existing reports during updates
  • Star schema results to ease of creating reports since dimension tables can be reused severally and the user does not need to take time to find tales, this helps to reduce mistakes and creates visuals that are clear and valid.
  • Filter propagation in star schema ensures predictable filtering without ambiguity since it uses single direction filters.
  • Star schema has low model complexity where complex data is broken down into modular and repeatable blocks .