Before we get to the finished product of an interactive report or dashboard, we first need to connect to our raw data source or sources. In addition to that, we need to build a robust data model through analyzing data with Power BI Desktop.
In short, the data model is the foundation of a Power BI report. When configured correctly, a data model is what allows us to slice data at any angle. It drill down from the high-level overview to the granular level data and gives us the ultimate flexibility to view our data.
From there, we can analyze our data and perform any calculations necessary to convert our raw data into real, valuable business outputs. If you recall from our previous article (What is Power BI?), Power BI Desktop is a free application that is compatible with Windows.
It has three components: a data connector, a data modeler, and a report builder. Let’s start with the data connector.
Power BI Desktop lets you connect to a variety of data sources. These include raw files, databases, Azure, and a plethora of others. You can connect to multiple sources at one time, but for this exercise, we’ll use the Retail Analysis sample Power BI workbook available for free download from Microsoft.
Once the data is loaded, we can view our entire dataset. This dataset has five tables: Sales, District, Item, Store, and Time. The Sales table is the largest with almost one million rows.
Power BI Desktop is equipped to handle a sizable amount of data, but for very large datasets, or Big Data datasets, it may be best to use DirectQuery instead of importing all of your data into Power BI Desktop. This method queries the data from its data source and only pulls in the query results at the time of its execution. We’ll examine DirectQuery in-depth in a future installment.
As we further examine our dataset, we can make changes if necessary. For example, we can clean up the dataset by deleting the columns that we don’t need for this report. Note: this does not impact the original data source. Whatever changes we make in Power BI will not affect the original Excel spreadsheet, including the creation of new columns and formatting existing ones. For our sample, we have converted the Sum_Regular_Sales_Dollars column from number to a currency format. After we’ve completed our data cleansing and transformation tasks, we’re ready to create our data model.
Data modeling requires a working knowledge of database and data warehousing techniques, but once you get a hold of the basics, Power BI Desktop provides a simple and intuitive visual interface that makes data modeling a breeze. The Retail Analysis dataset has one fact table, or the dependent variable outputs that will appear on a chart’s y-axis, called Sales and four dimension tables, the independent variables that will appear on a chart’s x-axis.
The Sales table is the central component of our data model. The Store, Item, and Time tables feed into the Sales table, and the District table feeds into the Store table, which indirectly feeds into the Sales table. These table relationships can be created by using the drag-and-drop interface or by utilizing the Manage Relationships tab.
Also, we can modify the properties for each table and each field. Options include renaming the field, changing the data type or format, or giving a description of the field. Below we can see the Buyer field is selected within the Item table.
Power BI data models are meant to be revised and tinkered with. It may take many iterations to nail down your analysis and built the optimal report. You can switch back and forth between your data tables and data model. Updating the data tables will update your model, so plan accordingly. In your data model, you can add or delete facts and dimensions, or show or hide certain fields to keep your report clean. You can start small with a few tables from one data source then add more as you go along. Power BI Desktop offers the ultimate combination of flexibility and functionality.
The data model is the iceberg beneath the surface. It’s the foundation for insight. The data model uses the relationships you created between facts and dimensions to slice and pivot the data and present it in new and different ways. Power BI’s clean and interactive reports help you spot trends, key data points, and new insights. We’ll cover Power BI’s visualizations in the next installment.