Data Modelling And Schemas In PowerBI.

What is data modelling?

Data modelling refers to the process of structuring and organizing data into a logical models (tables), therefore creating relationships that enable;

• Easy understanding of data by aggregating data accurately and correctly.

• Providing realible insights, through proper structuring.

• Maintaining data integrity.

Facts And Dimension Tables:

There are two types of tables in data modelling: i.e Facts and Dimension tables.
Facts Tables
They contain measurable data that is numeric data. Includes: Sales, Amounts, Quantity, Cost etc. Data that contains numbers.
Dimension Tables
They help in describing facts. Instead of having numerical values, they are more of textual content. They contain describe attributes. For example: We can have a product; then have a ProductName, ProductCategory, ProductKey, all these you see are attributes that describe a Product.

Schemas In PowerBI.

A data schema is a logical structure of your data model. Its a blue print that organizes facts and dimension tables together with their relations.

Types Of Schemas

The following are the common types of schemas used in PowerBI:

1. Star Schema. As the name suggests it has a shape of a star. It has a central fact with surrounding dimension tables.

Illustration of Star Schema:

Above is a description of a star schema in PowerBI with:

• The Facts table(Numeric data) in the middle.

• The dimension tables connected from the Fact table.

2.Snowflake Schema.
For the snowflake schema dimensions are split into multiple related tables.
Meaning: In star schema the dimensions are kept in a single table. But in snowflake the dimesions are normalized i.e They are organized to reduce redundacy (repetition of data). The dimensions can relate to other dimensions including the fact table.

For Example:

In the above relationship you can be able see the further sub-division of dimensions.

Relationships In PowerBI:

Relationships are used to link facts and dimensions. They define how tables connect to each other therefore enhancing accurate reporting.
In PowerBI there are several types of relationships they include:

1. One to One. The rarest relationship. One table relates to only one table. For Example (1) dimension relates to (1) Fact:

1. One to Many.
   One record relates to multiple records in other tables.
   For Example: We can have one customer with multiple transactions.
   

2. Many to Many
   Its a type of relationships where multiple records are related to other multiple records in other tables.
   For Example: One student can take many courses and one course can have multiple students.

Importance of a good modelling:

• Performance-By minimizing the number of joints, a clean schema ensures optimized queries and less memory usage therefore ensuring a good responsive dashboard.
• Accuracy-An accurate model ensures the correctness of aggregates. This ensures that there is a consistency across the report.

• Maintability-A good model with a clean schema is easy to maintain by spoting errors and easy troubleshooting. Also its easy to explain to others.

• Scalability- Its easy to add new features, you can easily add more dimensions and supports large growth of data.