[Explained] How Data Modeling is Done in Power BI
New to Power BI or want to know it better? You should start with data modeling in Power BI and then work your way through data mining, and finally, extract actionable insight from Power BI reports.
Data modeling is at the core of any data analytics project. It stays beneath the actual data visualizations and dashboards that the general audience sees.
Just like any other data analytics tool, Power BI also needs data modeling in the backend for data integration, data transformation, creating relationships between various tables of the input dataset, adding calculations, and so on.
Keep reading to learn everything you need to know about data modeling in Power BI, including effortless step-by-step guidance on how to create a Power BI data model.
What Is Data Modeling?
In data modeling, you create a simplified representation of data structures, relationships, and attributes by linking the underlying blocks of datasets like tables, rows, columns, etc.
It helps in organizing and understanding data for efficient storage, retrieval, and analysis. The backend data model aids in designing databases, software, and systems.
Data modeling involves identifying entities, defining their properties, and showing how they connect with each other to generate patterns and actionable insights. It’s crucial for data analytics, ensuring data accuracy, and supporting decision-making processes.
How Essential It Is for Data Analysis in Power BI
Data modeling plays a pivotal role in data analytics tools like Power BI. This business intelligence tool relies on well-structured data models to create meaningful visualizations and insights.
By using data modeling principles, you can efficiently connect, transform, and visualize data from several sources within a large dataset. Data modeling in Power BI also helps you to ensure that the data presented in these analytics tools are accurate and relevant
Finally, it facilitates informed decision-making and unlocks the full potential of data-driven insights. In essence, it also acts as the foundation upon which powerful data analytics tools like Power BI can deliver valuable business intelligence.
Types of Data Modeling
#1. Conceptual Data Modeling
It focuses on the big picture of the data. So, conceptual modeling represents data at a high level without considering specific database structures or technical details. This concept aids in aligning data with organizational goals and helps stakeholders understand the data’s purpose and significance.
#2. Physical Data Modeling
When you model your dataset based on the technical implementation aspect for storage and retrieval, that’s physical data modeling. It specifies the data storage structures, like tables, indexes, and constraints, that you can use in a database system.
#3. Logical Data Modeling
Logical data modeling bridges the gap between conceptual and physical modeling. Such modeling serves as a blueprint for database design and facilitates communication between business stakeholders and technical teams while maintaining data integrity and consistency.
Data Modeling Techniques in Power BI
#1. Star Schema
The Star Schema is a highly used data modeling technique in Power BI. In this approach, there is a central Fact Table that contains key metrics and measures, surrounded by Dimension Tables that provide context to the data. It’s called a “star” because the Fact Table is at the center, connected to Dimension Tables like the points of a star.
#2. Cardinality
Cardinality defines the relationship between tables in Power BI. It specifies how many unique values in one table match the values in another. Cardinality options include One to One, One to Many, and Many to One. Choosing the right cardinality is crucial for accurate data modeling.
#3. Cross Filter Direction
Which way a filter applied on data in one linked table will impact the data of the other table is known as the cross filter direction. There are two types of cross-filter direction and these are as below:
Single
Single cross-filter direction is a relationship option where filters can flow in only one direction, typically from the Dimension Table to the Fact Table. It ensures that filtering the Dimension Table affects the Fact Table, but not the other way around.
Bi-Directional
The bi-directional cross filter allows filters to flow in both directions between the Fact and Dimension Tables. It can be useful in some scenarios but should be used cautiously, as it can lead to unexpected results if not properly managed.
#4. Snowflake Schema
The Snowflake Schema is an extension of the Star Schema, where Dimension Tables are further normalized into sub-dimensions. This creates a more structured but complex schema. While it can reduce data redundancy, it may require more effort to navigate in Power BI.
#5. Lookup Tables
Lookup Tables are Dimension Tables that hold reference information, like product details or geographic data. They are used to enrich the data in Fact Tables, providing additional context for analysis.
#6. Data Tables
Data Tables are a term sometimes used interchangeably with Fact Tables. They contain the primary data, including measures and foreign keys, and serve as the foundation for building Power BI reports and visualizations.
Benefits of Data Modeling in Power BI
- Power BI data modeling enables you to link many data tables so you can source data from these tables when analyzing the whole dataset, creating reports, or building dashboards.
- Well-structured data models enable compelling and meaningful visualizations in Power BI.
- The general audience can easily interact with the visual side of the data when there’s an underlying data model.
- Optimized Power BI data models help you query data faster from a large database containing millions of rows.
- You can perform an in-depth drill down into a large dataset by analyzing all of its tables when there is a structured data model in the backend.
- Also, you can easily link one data model to another or refer to a data model in a different report to cross-link datasets for high-level insight extraction.
- You can control and protect sensitive parts of the data and keep the public part of the data open by creating Power BI data models and securing them with role-based access.
- If you work on a dataset that keeps growing in real-time by collecting data from retail POSs, apps, and marketplaces, you must create a data model first before analyzing the whole dataset as it grows each day.
How to Do Data Modeling in Power BI
Creating a data model in Power BI is really simple. Here are the steps you must follow:
Import Your Dataset to Power BI
First, you must import your datasets to Power BI. You can import as many datasets as you want or import a dataset that has many data objects like tables, columns, rows, etc.
Power BI will automatically analyze the imported dataset or data objects and create a primary data model based on the names of column headers or table headers.
To import datasets, follow these steps:
- Open the Power BI desktop app.
- It should show the Power BI workspace along with a splash screen.
- Click Get data.
- On the Get Data dialog box, you can choose what data category you want to import in the left-side navigation pane and sources or types of data in the right-side navigation pane.
- The default data source type selection is All.
- Since my dataset is in an Excel workbook, I’ll select the Excel Workbook option on the right and click Connect.
- On the Open dialog box, navigate to the Excel file you want to import on the PC and double-click the file to import it into Power BI.
- Now, you should be in the Navigator dialog box.
- There, checkmark the data objects you want to import to the Power BI report.
- Click Load to complete the data import process in Power BI.
Create a Power BI Data Model
When you click the Load button on Navigator, Power BI starts analyzing the dataset, creating data object models, creating data models, and finally takes you to the Report view tab.
If you click the Table view menu on the left side panel, you should get the tabular views of all the data objects you just imported to Power BI.
Power BI won’t save your work so far automatically. So, you need to click File on the ribbon menu and then click Save on the left side panel to save the file in a location on your PC.
Now, we dig deeper into the data modeling process in Power BI. Follow these steps:
- Click the Model view tab on the left-side panel of the Power BI workspace.
- You should now see the default data model created by Power BI.
- In the current example, it’s a star schema since there is a fact table in the middle, and the fact table is surrounded by the dimension tables.
- Now, Power BI may not always accurately create the data model connections you need.
- In the current example, it couldn’t connect the fact table with the dimension Table24 though there are similar column headers Geography and Geo.
- It couldn’t do it because the column headers don’t match as is.
- Two connect Table24 with the fact table Main Data, simply drag the Geo object of Table24 on the fact table’s Geography element at the center.
That’s it! You’ve successfully created a data model on Power BI. Find below the meaning of various objects on the relationship lines:
- A star at the edge of the fact table and the numerical 1 at the edge of the dimension table represent a one-to-many connection.
- An arrow on the connecting line between two tables on Power BI indicates the direction of the filter. When the sharp edge of the arrow faces the fact table, any filter applied to the dimension table will affect the fact table.
- When there are two arrows, the filter impacts the table of both sides, that the fact and dimension tables.
Visualize Your Data
Here’s how you can now visualize your dataset to extract actionable insights:
- Click the Report view tab.
- Choose any chart from the Visualizations section on the right.
- I selected a Stacked column chart.
- Expand the datasets on the far right of the Power BI workspace.
- Drag and drop items on the X-axis and Y-axis fields on the chart editor.
- Power BI will automatically create the chart for you.
Use Cases of Data Modeling in Power BI
Create Holistic Reports
To create a multi-dimensional report or dashboard on Power BI, you must use Power BI data model functionality. Data in one table may not make enough sense. However, when you bring data from different datasets and objects, you can visualize data in a better sense.
Integrate Data
Suppose there are many datasets or data tables in the source Excel workbook or SQL database. Instead of making different Power BI reports, you can connect all of them in a star or snowflake schema and visualize them as a whole. You can do better data storytelling by unifying all the data objects.
Data Cleansing and Transformation
It only becomes apparent whether you need to clean and transform a raw dataset when you attempt to create Power BI data models.
For example, if the tool is unable to link related data columns or tables in the data model, then there could be a mismatch in column headers. So, you know what to fix.
Another example could be more than one element in the tables. Suppose there are only 4 geographies in the dataset, namely US, CA, UK, and DE. But the data model is showing an extra, which is Blank. That’s because some of the geography names contain unnecessary spaces, and hence Power BI classifies them in a new geography element.
Control Data Visibility
You can restrict who can view what in your Power BI report or dashboard by using the Model View tab. By clicking the eye icon on each data card on the Power BI data model workspace, you can hide or unhide the table.
If there is more than one column in the data card, you can customize the visibility of those columns as well.
Manipulate Selected Data Tables
Each data table on the Power BI data model interface comes with More options or a three-dot icon. There, you can discover the following activities that you can perform on the selected data table:
- New measure or column
- Refresh data
- Manage relationships
- Incremental refresh
- Manage aggregations
- And many more
Conclusion
So, that’s all about creating a Power BI data model to visualize large and distributed datasets in one logical report or dashboard.
So far, you learned about the different data modeling techniques in Power BI. Also, you found the steps to create a Power BI data model from scratch. Follow the steps and create your own data model in no time.
Next up, the best data analyst courses to kickstart your data science or data analyst career.
