POWER BI:DATA MODELING

Introduction

Power BI is a business intelligence and data visualization tool developed by Microsoft that allows user to connect to multiple data sources, transform and model data, and create reports and dashboards.

In this article we will go through the various types of tables,schemas,data relationships and data models in Power BI.

Lets start with TABLES

To support BI analysis, the data is organised into multiple tables, each serving a specific purpose,ie fact table and dimensional table.

FACT TABLE

The Facts table is the central table in the dataset,stores transaction data and measurable business information.Let's give an example of a dataset that represent information about students,teachers,subjects,

schools, locations,and other related records,making it the larger table that contains all the information in one place.

So the fact table has the following characteristics:

• usually has many columns,it combines both:
  
  Descriptive columns - students_name,school_name,assesment_tpye,gender and etc




• has numeric columns - numeric values that can be aggregated ie:age, fees, performance_score we can find the SUM,AVERAGE using DAX measures




• has repeated values - Country, regions,teachers,schools




• has ID columns(which are Foreign keys) - Students id, teachers id,
  
  subject id, school id

foreign keys links fact tables to dimension tables

• Fact Table is a central table in a star schema or snowflake schema of a data warehouse.

Dimension Table

Dimension tables help us filter, group, and describe the fact table.

Dimension Table are originally connected to the fact table and it is located at the edge of a star schema or snowflake schema.

The foreign key is mapped to the facts table an example:

From our student data we can create multiple dimensional table that is student table, subject table, school table and others

So the student table will have student id, student name, student region, student age... basically all the student's details needed hence we can say dimensional tables usually answer questions like: Who? What? Where? When? Which category? Which subject?and Which teacher?.

We can be now be able to introduce Schemas after knowing facts and dimensional tables

*Schema Definition *

A schema refers to the overall structure of how tables are organized and related in a data model.

It defines how many tables exist,_ how they connect_, and how data flows between them. The main schema types we are focusing on are:_Star schema Snowflake schema _

Star Schema

A star schema is a data model where you have:One central fact table several dimension tables around it.It is called a star schema because it looks like a** star*. The fact table sits in the middle. The dimension tables surround it.

Star schemas reduce data duplication, improve performance, and make models easier to understand.

**Snowflake Schema*

A snowflake schema is a variation of the star schema where dimension tables are further normalized into additional related tables.

A schema cannot exits without understanding relationships thus we can go through the relationships that exist.

RELATIONSHIPS

Relationships connect fact table to dimensional tables, the fact table is on the many side while dimension tables are on the one side. This relationship turn schemas to a working model

There are keys that help to build a relationships. In this dataset, each dimension table has a primary key ie: Student data has student id, subject table has subject id, school data has school id.

These ids uniquely identify each row in their respective dimension tables and they are called primary key

For example

Subject id should only appear once in the subject id

While a foreign key is the matching key in another table.

For example: fact table [subject id] in the fact table, subject id _can repeat _because one subject can be done many times by different student.

Cardinality

Cardinality describes how rows in one table relate to rows in another table.

The main relationship types are:

One-to-many:

One-to-one

Many-to-many