In my beginners journey into power Bi, I have been introduces to new concepts such as joins and relationship schemas, all used to store and organise data in a database.
Relationships
schema relationships show how data is related across different tables in a database. To enable the connections to be made, primary and foreign keys are used to define that relationship.
Primary Keys.
These are unique identifiers in a table, for example, in the above table, the customer_id is the primary key.
Foreign Keys.
They refer to a primary key that is in a different table.
In this example above, the same customer customer_id that was a primary key in it's own table becomes a foreign key.
Cardinality.
When the primary and foreign keys are used to join tables together, they bring in an element called cardinality.
There are 4 types of cardinality according to the number of keys interconnecting across the tables
- One to One - means that one column connects to another column in a different table. The primary key will be used to connect them.
- One to many - one column connects to multiple in a different table. when viewed from the other table, that relationship becomes the *Many to one *
- Many to many - multiple columns from one table link to other multiple in a different table.
When tables have been joined and relationships defined, a schema is created. It shows how different tables(dimensions) relate in data model form.
Types of Schema
Star Schema.
A star schema has different dimensions connected to a central fact table, which contains data related to the other dimension tables. The above image is an example of a star schema.
Snowflake Schema
A snowflake schema has different dimension tables interconnecting, related data has been divided further to show more detailed information.
Galaxy/Constellation schema.
A galaxy schema has multiple fact tables that share dimension tables, therefore creating an interconnected data model.
Conclusion.
Different models are used at different occasions according to the data being analysed. For straight forward data, a star schema is recommended. For more complex data that requires a lot of details to be analysed, the snowflake schema will come in handy.
When there are multiple fact tables, we use a galaxy schema.
In my power bi study journey, I continue to learn different concepts and increase to my data analytics tools.
Top comments (0)