How to use Modeling view in Power BI Desktop
Data modeling is an essential aspect of data analysis. It allows us to define relationships among our data sources and have a comprehensive and optimized view of the model. This helps prevent redundancy, promotes performance, and aids in the efficient retrieval of data attributes.
In Power BI Desktop, modeling is done in a user-intuitive way. The SaaS application lets users build data models visually by dragging and dropping individual attributes within their data. Data models here can also be easily built using the star schema.
What is the star schema?
The star schema is an intuitive way to modulate data from dimension tables around a central fact table.
Fact tables: These contain our most crucial data and are used to answer business queries and generate metrics. An example of a fact table is the sales transaction table.
Dimension tables: These give context to the information in the fact table. An example of a dimension table is the branch data table that houses all of our branch's information.
The Modeling view
The Modeling view has three sub-views:
Model/Diagram view: This shows the various tables and any relationships between the data. It also allows us to visualize relationships.
Properties pane: This displays information about the selected attribute and allows us to modify its properties.
Fields/Table pane: This displays the various measures, columns, and tables within the queries (data).
Working with Power BI's Modeling view
Let's assume we work at a manufacturing company called "Benny Inc," which produces various kinds of cereal. For this project, we'll make use of three tables, as follows:
Sales Order
Product Name
Customer Name
To get started, we'll do the following:
Launch Power BI and import our dataset.
Head over to the Model view, which we can find on the left side of the Power BI Desktop.
Create model relationships
Power BI Desktop, by default, identifies and defines relationships between tables once imported. However, it is recommended that we turn this off from the setting before getting to work.
To do this, we click "File," then click “Option and setting,” and then click “Option” to turn off the "Autodetect new relationships after data is loaded" option.
Modeling data
From our data, the Sales Order table is our fact table, while the Date, Product, and Customer tables are our dimension tables. Now that we are on the Modeling view, let's do some data modeling.
We need to modulate the data from the dimension tables around our fact table based on the star schema. We can achieve that through the following steps:
Join the Customer and Sales Order data tables by finding a column they have in common, i.e., "Customer index (customer index table)" and "Customer name index (Sales Order table)."
Drag the "Customer name index" column from the Sales Order table to the "Customer index" column in the Customer Data table. This creates a line that indicates that a relationship has been created.
We can also manage and create relationships via “Manage relationships” on the ribbon tab. To do that, we must carry out the following steps:
Click “Manage relationships” to see the Customer Data and Sales Order relationship we created earlier.
Click "New" to create a new relationship.
Select the Date table from the first drop-down list in the "Create relationship" window.
Select the Sales Order table from the second drop-down.
Select the column shared by both tables. This is the Date column for the Date table and the OrderDate column for the Sales Order table.
Note that the "Cardinality" drop-down shows that the "One to many (1:*)" option was selected. This means that one table has unique values (the Date table has unique dates) hence "1," while the other has multiple instances of values (the Sales Order table has multiple values for the same date; that is because multiple sales can take place in a day). "One-to-many" relationships is the most common cardinality; all relationships we create in this article will be of this type. The "Single" option in the "Cross filter direction" drop-down means that it filters from the "one" side to "many" sides.
Click "Ok."
We should do the other columns within different tables.
Now that we are done with modeling, we can start visualization, data reporting, and dashboard building.
