POWER BI?

Power BI is another visualisation tool that depending on what data you have, figuring what you want to do will dictate on whether it is the right tool to use.

Relationship, schemas and joins.

While working with a workload especially when analysing Power BI models, relationship schema and joins can be considered to be "patterns" that connects everything. The main type of schema includes;

1. Star Schema.

This is a multi dimensional data model that is used to organise data to make it easy to understand and analyse. For example in the table below, there are two kinds of tables, one is a Fact table which stores quantitative data while the other table ids the Dimensional table which stores descriptive data and where all generally have a common column.

Advantages

• Star is usually built for rapid analysis as it is easy to understand and navigate and that is why most business users use it.

• It is generally faster and more efficient.

2. Snowflake schema.

This is mostly used for more complex, detailed dimensions with many hierarchies.
When dimensions are incorporating all the fields into a single table for example which would make it hard to use then snowflake schema is ideal.

Here is a table that shows an example of a snowflake schema

Snowflake is usually built for rapid transactions making it sometimes hard for users to understand and navigate.

3. Galaxy schema

With Galaxy schema we usually have multiple fact tables.

Relationships

Relationships defines how tables interact.

1. One-to-One. This is when the tables have a common ID or unique values for the joined column.
2. One-to-Many. This is when you are connecting a dimensional table which has the unique value to another table which is a fact table that has many sides.
3. Many-to-One. This is the opposite of one-to-many.
4. Many-to-Many. This is where the relationship is broken down to two one-to-many relationships.

There is also filter directions where in a single direction, the filter that connects the tables flows in one direction, while in a bi-directional, the filter flows in both directions.

Joins

Joins are used to merge data from two tables based on a common column. The different types are;

1. Left Outer Join. This joins all rows from the left tables and the matching rows from the right side.
2. Right Outer Join. This joins all the rows from the right tables and the matching rows from the left side.
3. Full Outer Join. This includes all rows from both the primary and secondary tables.
4. Inner Join. It includes rows where there is a match in both the tables based on the join key.
5. Left Anti Join. This returns only the rows from the primary table that does not have a match in the secondary table.
6. Right Anti Join. This returns only the rows from the secondary table that do not have a match in the primary table.

Conclusion

Power BI is a great tool for visualisation as it requires one to create more insightful dashboard elements from data.