Power BI is a special tool used in management of data via simple ways of manipulating data. Some ways to manipulate data is by use of relationships,joins and schemas. The above are going to be discussed below. Keeping in mind that power BI is not such a hard concept to understand but one has to give it a considerable amount of time and effort so as to ensure a smooth ease into the mind.
TYPES OF RELATIONSHIPS IN POWER BI
Power BI supports multiple relationship types between tables, including one-to-one, one-to-many, and many-to-many, with options for single or both-directional filtering.
One to One
This is where a single row in a table corresponds or rather relates to another singular row in a different table. The two row have to have different values so as to reinforce the 1:1 relationship.
One to Many
This is where a single row in a table corresponds to multiple other rows in another table. For example in a supermarket, assuming that there is a customer row, it may relate to other rows showing the products purchased.
Many to Many
This is where multiple rows in a table corresponds to multiple other rows in a different table. This can be done by creating multiple many to many relationships.
Filtering
Single direction
It filters contexts from the one table to the many table. IT is usually the most recommended one.
Both Direction
It is also called Bi-direction. It filters contexts in both directions as the name suggests. This means that when a change is made in one table it reflects in the other table.
Power BI allows multiple relationships between the same tables, but only one active relationship can be used by default in visuals. Inactive relationships can be activated in DAX calculations.
Types of Schemas in power BI
Star Schema
It is called star because the fact table is usually at the centre with the dimension tables radiating outwards therefore resembling a star. A single fact table only contains quantitative data such as sales. A multiple dimension tables contains descriptive data such as product categories.
Advantages
It simplifies queries and improves performance as most relationships are one to many.
Easy to understand
Optimized for DAX calculations.
It is used sales reporting, inventory management and wherever clear metrics are tied to multiple descriptive attributes.
Snowflake Schema
Dimensions are normalized into multiple related tables, therefore one can say its a variation of the star schema
Advantages
Can be used for complex data
Saves storage space
Disadvantages
Harder to Navigate
If not modelled carefully, complex queries can reduce performance.
Galaxy Schema
Its where multiple fact table are connected as they may share common dimension. Its suitable where businesses have multiple areas of analysis like sales and inventory which share dimensions such as date and products.
Advantages
Flexible for larger enterprises
Enables combining different areas of business metrics in the same model
Disadvantages
Can be challenging to optimize DAX measures
Types of Joins in power BI
Joins are used to merge two tables
Left outer
All rows from the left table and matching rows from the right side table are joined. If they do not match, nulls are returned for the second/right table.
Right outer
Works the same as the left outer but with a little difference. So rows from the right table and matching rows from the left/first table are joined. But this time if there are no matches, nulls are returned to the first/left table as opposed to the left outer join where the nulls are returned to the right/second table.
Full Outer
All rows from the tables are joined with nulls returned for missing values.
inner
Only rows that match from both tables are merged.
Left anti
Rows in the first/left table that do not have a match with any rows in the right/second table.
Right anti
Rows in the second table that do not have a match with any rows in the first/left table.
In conclusion, Power BI is a rather simple tool used in data management but if one does not give it the time and put in the amount of work, it may turn out hard for them to understand it fully. Most of functions, queries, DAX calculations... are to some extent interconnected therefore making them easier to relate to one another.
Top comments (0)