Elishiba Muigo

Posted on Jul 2

Designing Effective Data Models in Power BI: Schemas and Relationships

#powerplatform #analysis #modeling #schemas

Discover how data modeling helps organize and structure data for efficient storage, management, and analysis.

What is Data Modeling?

Data modeling is a detailed process that involves creating a visual representation of data and it's relationships. It serves as a blueprint of how data is structured, stored and accessed to ensure consistency and clarity in data management.

Visualize data modeling as a conceptual view of data elements or the way we structure data for building business objects.

Types of Data Models

There are three main types of data models:

Conceptual data model

The model provides a high-level view of data, it defines keys, attributes, and relationships.
Logical data model

The model defines how the data will be structured, it defines the structure, properties, and relationships of data elements in a way that is independent of any specific database or
BI tool
It serves as a blueprint, mapping out how data entities (e.g., customers, products, sales) relate to one another through primary and foreign keys.
Physical data model

The model represents how data is actually stored in a database. It is an actual implementation in Power BI.

Understanding Dimensional Modeling

Dimensional modeling is a logical approach to organizing data for reporting, making it easy to query and analyze.It is widely used in data warehousing and analytics, where data is often represented in terms of facts and dimensions, it simplifies complex data by organizing into star and snowflake schemas.

Fact Tables: These tables store measurable attributes such as sales amount and transaction counts. These tables represent business processes, transactions, or events.

Dimension Tables: They store descriptions of the contexts around the fact tables. They describe and categorize fact table data, answering key analytical questions such as “who, what, when, where, why, and how.”

STAR SCHEMA

It's a data-modeling technique where a central fact table is surrounded by related dimension tables, visually resembling a star.

SNOWFLAKE SCHEMA

It extends the star schema but it allows relationships between dimensions.

MERGING

Merging tables in Power BI means combining two or more tables into a single table.
You can use merge tables based on a related column:

Click on Merge Queries under the Home tab under the Combine group

Select the first table and the second table you would like to merge

Select the columns you would like to join the tables on by clicking on the column from the first dataset, and from the second dataset

Select the Join Kind that suits your operation. Merge tables when you need a simplified dataset for a specific analysis or when dealing with smaller datasets where performance impact is minimal. Merging can also be good when you need to perform extensive data cleaning and transformation.

When merging tables you can use several types of joins.

We refer to the following two tables to illustrate joins below.

Datacamp: [https://www.datacamp.com/tutorial/power-bi-merge-tables]

1. Inner Join - Returns only rows that match in both tables.

In the Sales table is reduced to 5 rows since Product ID ‘P020’ is not found in the Products table. Additionally, two rows from the Products table are not even included in the Sales table since no sales are recorded for them (that is, Product ID ‘P010’ and ‘P001’).

2. Left Outer Join- The join returns all records from the left tables and the matched records from the right tables. If there is no match, the result is NULL on the side of the right tables.

In our example, Product ID ‘P020’ has no associated product name because this product is not found in the Products table, so we just get a null value in the Product column.

3. Right Outer Join - Right join is the opposite of the left join. It returns all rows from the right table and common rows from the left table. If no match is found in the left table, NULL values will be returned for those rows. It is like the mirror image of LEFT JOIN.

In our example, two products (Binder and Paper) can be found in the Products table but do not have any associated sales in the Sales table. Therefore, we have two extra rows in our Sales table that are completely blank for all columns except the Product column.

Here are the different types of joins you can use:

CARDINALITY

Cardinality it the relationship between rows of data in separate tables within a data model. Each model relationship is defined by a cardinality type.

The four options, together with their shorthand notations, are described in the following list:

One-to-many (1:*)
One-to-one (1:1)
Many-to-many (:)

One-to-many (and many-to-one) cardinality
The cardinality relationship is a connection between two tables in a data model where one unique value in "one" table can be associated with multiple values in the "many" tables.

One-to-one (1:1) cardinality
The one-to-one relationship means both columns contain unique values. This cardinality type isn't common.

Many-to-many (:)
The many-to-many relationship means both columns can contain duplicate values. This cardinality is frequently used.

Create relationships with Power BI

If you have different datasets you want to connect. First, upload them into Power BI

Click on the Model View from the left-hand pane

Connect key columns from different datasets by dragging one to another (e.g., EmployeeID to e.g., SalespersonID)