Model data in Power BI

Model data in Power BI

Introduction  

Data model is a collection of

• loaded tables of data
  • with one or more columns that have been loaded for a reason (don’t load any columns from your source into your model unless you have a reason).
  • with one or more rows of data that have been loaded for a reason (don’t load rows from your source into your model unless you have a reason).
• relationships between the loaded tables
• the measures (formulas) written that apply the business rules to the raw data to extract business insights (e.g., sell price minus cost price is profit), including
  • the formulas themselves
  • the formatting applied to the formulas
  • the business names were given to the formulas

All of the above makeup what is referred to as “the data model” in Power BI

The benefits of a data model based tool like Power BI are:

• the author of “the data model” builds a reusable capability (the data model) that can be used to solve the current reporting requirements as well as (often) future requirements without the need to go back and write further queries to retrieve a new subset of data.
• The author can often be a business user (normally with good Excel skills) – they don’t have to be a professionally trained database administrator or SQL professional.
• The model is “conceptual” in nature, supported by a user-friendly interface that lets you “build” the model logically with the minimum of coding. Note: You will need to do some coding (writing formulas) for a good model, but no harder than your average Excel spreadsheet once you have skills.

The Model View in Power BI Desktop

Inside Power BI Desktop you will find the “model-view”. The image below shows the tabular structure of the Adventure Works database I use for most of my demos.

Above you can see:

• The tables
• The columns in the tables (although this is not the best view for columns – better to use the data view for that)
• The relationships between tables (if they exist)
• The types of relationships (the ones above are all “1 to many”)
• The direction of filter propagation between the tables using the relationships

What you can’t see in this model view are the measures (the formulas that generate the business concepts from the raw data).

How to manage your data relationships:-

Relationship means we can connect one or more tables together because they had related data in respected tables. So we can able to perform queries on the linked table according to our needs. The Model view in Power BI Desktop allows you to visually set the relationship between tables or elements. In the Model view, we can see that table with the respected relationships. Adding and removing relationships is straightforward. To remove a relationship, right-click the relationship and select Delete. To create a relationship, drag and drop the fields that you want to link between tables.

A data relationship’s cardinality describes the l relationship between rows of one table and the rows of the other. 

one-to-one: one-to-one cardinality indicates that each row of a table maps to a single row of another table

one-to-many or many-to-one: one-to-many relationships are very frequent in traditional star-schemas. One-to-many relationships occur when a single row of a table maps to more than one row in another table. This is the case of the example relationship studied above

many-to-many: many-to-many relationships are very tricky to deal with. They occur when multiple rows in one table link to multiple rows in another.