Understanding the concepts
Relationships, schemas and joins in power BI are key concepts in understanding data structures in power BI
relationships
Are basically connections between tables in power BI that shows how a data in one table is related to a data in another table.
cardinality:
one to one -each value appears once in the two tables eg;
one to many-one value in one table appears many times in another table
this basically means Fatma has placed multiple orders
many to many- multiple values in one table can appear multiple times in another table. A simplified example is a data table for students performance in a school where we find that one student can take many subjects and also one subject can be taken by multiple students.
Primary keys and Foreign keys
A primary keys is a column that acts as a unique identifier in a table while a foreign key is a column that basically referrences a primary key in another table.
From the above table explaining the one to many relationship of a schema, we can conclude that the customerID column is a primary key in the customer's table while the same column acts as a foreign key in the Order table.
Schemas/data structure/data blueprint
This is a model/ structure in which your data sits on. As you feed more data into your table, a schema is how your data will be arranged in your .pbix file.
To understand this concept, you have to first understand the two types of tables involved in your .pbix file namely;
dimensional tables
fact tables
For instance you are working on a table of raw data containing thousands of rows. As a data analyst it is your duty to prepare the data in such a way that it becomes easier to analyse , understand and interpret. One large table can be broken down into multiple tables that are interconnected with each other.
Dimensional tables will contain primary keys while a fact table will house those primary keys as foreign keys in it.
A good example is on the above tow tables explaining the one to many relationships. We can conclude that the Customer's table is a dimensional table while the Order table is a fact table.
There are 2 types of schemas:
- Star schema- has one fact table and several dimensional tables
- snowflake schema- has one fact table, several dimensional tables and sub dimensional tables
Joins
This is a function in your power BI platfrom that now you will use to merge these dimensional and subdimensional tables into one large meaningfull flat table.It is used in power query when transforming your data.
There are types of joins:
Full outer join- combines all values from both tables
right outer join-combines all values from the second table and matching values from the first table
left outer join-combines all values from the first table and matching values from the second table.(note: the arrangement of the table depends entirely on the analyst)
-** inner join**-combines only matching values from the columns of the two tables(dimensional and fact tables)
Antijoins
left Antijoin will only select rows that are in the first table only and are missing in the second table
Right Antijoin only selects rows in the second table alone and are missing in the first
Key take aways:
Schemas are used to break down tables
Joins are used to merge your tables
Antijoins cant be used to merge tables
Foreign keys are in fact tables, in their dimensional tables they are primary keys
Primary keys are unique in their tables
Top comments (0)