Designing a Schema in Power Bi.

#analytics #beginners #database #microsoft

Before we design a schema there is need to understand what a schema is:
In any relational database data is structured into Rows,columns, and tables.
These are connected to one another using Primary Or Foreign Keys.

A schema is how your tables are structured and connected inside the Model View of Microsoft Power BI.

For easier understanding lets equate schema Designing to A tailor Designing a suit. Below is summary of the process from when the tailor Receives this order to the point of delivering this suit.

Tailor.

Understands client.
Takes measurements.
Cuts fabric.
Stitches pieces.
Final fitting.

Data Modeler.

Understands business.
Analyzes data structure.
Separates fact & dimensions.
Creates relationships.
Tests measures & visuals.

For you to understand the business you need to draw a business flow chart, this will help you understand how data flow in your business.
Understand what does this business really do, what its core activity. What does the management need to measure, what are the key nounsused in the business and what are their relationships?

Secondly we must analyze data structure; this is like understanding the shape of the data before reshaping it.
These are the tables i have, they represent the following, are they transactional or descriptive, are the columns texts, dates,are they keys or numeric? If they are keys are they primary or foreign/secondary keys, does this data have problems? How much detail have you been given as well as cardinality.

Separating facts from dimensional tables entails understanding what each table means.
A fact table these table answers the questions of How Much? or How Many?

A dimensional table on the other hand answer "who?", "what?", "when?", "where?", and "why?"

If the Information is measurableand numeric put it in the fact table.
If the information is descriptive or categorical describing who?,what?,when?,why?,or where put it in the dimensional table.

Lets try this with a real Example

Given such a flat table

Open Power Bi
Load The fLat Table
Navigate to Power Queryby clicking Transform Data.
Start creating your Dimensional tables.

Create a Customer Dim table.

By :Duplicating your flat Table, Rename this to Customer Dimension keep Customer Name And Contact Columns and Remove other Columns, Remove any Duplicates And add a Column titled customer Key See Below:

Now you have your first Customer Dimensional table

Create a Product Dimension table by:

Duplicate the original flat table,Rename it to Product Dimension,InsertIndex Column And Name it As Product keys
See Below:

Your Product Dimension Table

Create a Date Dimension Table By:

Duplicateyour Original Flat Table, Keep the Datecolumn and Remove All other Columns,Remove Duplicates,AddColumns a for Date Month, Day and Year. Finally add an Index Column titled Date Keys

See Below:

Your Date Dimension Table

Now Create your Fact table By

Rename your Flat Table to Fact table
Keep The foreign Key Columns and Numeric Columns, Invoice No ,Product Name ,Customer Name ,Date ,Total Amount, Unit Price, And quantity Columns.