The Power BI Workflow

In this chapter, we delve into the process of transforming raw data into a comprehensive report through a structured workflow of activities using Power BI. As a Power BI developer, you will find yourself regularly engaging in these activities to construct various types of reports for your stakeholders. Nevertheless, the amount of time dedicated to each task may vary depending on the data’s complexity and quality as well as the report’s requirements.

Data for your report may originate from an array of sources, including databases, spreadsheets (Microsoft Excel), document libraries (Microsoft SharePoint), surveys (SurveyMonkey), cloud-based business applications (Microsoft Dynamics 365 Business Central), and other different types of systems. Your role as a developer will involve cleaning, shaping, converting, and sometimes combining the data into suitable structures, analyzing and visualizing the data, and, finally, building, deploying, and sharing...

Technical requirements

• Power BI Desktop
• SharePoint Online (https://www.microsoft.com/en-us/microsoft-365/sharepoint/collaboration)

Bringing data into Power BI

When you are ready to create a Power BI report, the first activity is to bring data into Power BI. Despite being a rather small and seemingly insignificant step, getting data is important. You will need to determine how you get the right data and where it comes from. There are a few questions that you would need to ask before you dive in:

• What is the original source of the data?
• How close to the original source can I get?
• Is the data consistent and standardized?
• Is there a single existing repository of data and/or analytics?

Let’s explore these questions.

Imagine you are tasked with creating a sales report. You will ideally want to connect to the sales system or the sales database. You would also want to go with the production database because that is where the real and latest data live. However, if organizational policy does not allow developers to connect to production databases, you would need to look for alternatives...

Cleansing and transforming data

Data, in their raw form, are usually not structured for analysis and may have undesired elements in them as well. Hence, it is important that the data are scrubbed of what you do not desire and are shaped into an analytics-friendly form.

Cleansing

Data stored on systems often have problems that don’t make them reporting-ready. Some of these problems, which are quite common across organizations, are the following:

• Inconsistency: This is where data in the same table are entered in different ways by users, or different formats are used to enter the data; for example, dates are entered using the YYYYMMDD format by some and MM/DD/YYYY by others.
• Incomplete data: This is where data are entered only into some fields for most records or where entire important fields are left empty.
• Incorrect data: This is where the wrong information is entered by the users.
• Data duplication: This is where the same data is entered multiple times...

Combining data from multiple sources

Combining data from multiple tables from within a single system and across systems is a common data preparation activity. It is advisable and is a general rule of thumb to first have the data from each table cleansed as much as possible before you attempt to combine it with another. Of course, there can always be exceptions to this rule, such as when you want to combine multiple similar files, in which case you must first combine and then apply the same cleansing rules across all these files in one go.

In the Cleansing and transforming data section, while cleansing and transforming, we inadvertently combined data from multiple tables: Product, ProductSubcategory, and ProductCategory. Having related tables from a transactional database, such as SQL Server, provides the option of automatically combining the tables based on foreign keys (Figure 3.1). Hence, we did this without having to think too much by selecting columns from the related tables...

Modeling data for analysis

Modeling is where you structure your Power BI semantic model to represent the business processes from an analytical standpoint. In essence, modeling actually starts before development. It is part of designing your Power BI solution.

We shall dive deeper into the details of modeling in Chapter 5. Here, let us look at what you, as a developer, would do. But remember, at every point of modeling data, you are, or should be, following a design that has been thought out. Even the data that you cleanse, transform, and combine are driven by what your model design dictates.

To model data effectively, you will need to use the right modeling technique. Let us look at the star schema, a popular analytics modeling technique.

Star schema

The recommended design technique for Power BI models is the star schema modeling approach. Regardless of your requirements, building your model according to this technique gives users a versatile, performant, and intuitive...

Exploring and analyzing data

This is where you start exploring the data that you have modeled, try to make sense of it, and look for insights.

We have two measures that we can play around with in a model: Amount and Quantity. Let’s start out our analysis by putting up Amount broken down by Type, just to check how big the Direct sales are compared to the main channels, and then also break it down further by Year to double-ensure that it is indeed the case every year:

Figure 3.28: Verifying direct sales’ makeup for total sales

The hypothesis that we tested is true. Hence, we can safely disregard direct sales and analyze them separately later if required. Let’s turn our attention back to the main sales channels by filtering out direct sales.

Let’s continue by looking at the sales amount by year (Figure 3.29); they show an increase each year and a dip in the last year. What if we break it down by product category to see if...

Visualizing data on a report

Power BI reports are not the conventional type of report that most businesses are used to. Conventional, pixel-perfect, print-ready reports are called paginated reports and are indeed part of the Power BI offerings.

Let’s get back to the default analytical reports in Power BI. Due to its functionality that allows for different types of visuals to be put together and paint an analytical picture, the word dashboard is quite commonly used by business users to refer to Power BI reports. Dashboards in Power BI are, however, a somewhat similar yet different artifact.

I will not disagree with users who want to call Power BI reports dashboards every time. Very often, we build Power BI reports to the effect of dashboards to fulfill the requirements of users wanting to see their KPIs and business measures at a high level. Keeping this in mind, we shall use the correct technical terminology in this book unless explicitly noted.

Reports are created...

Publishing and sharing Power BI content

The content you create on Power BI Desktop cannot be shared while it is on Power BI Desktop. You can forward files around, but that will not be practical at all, especially with the data size and potential for multiple versions of the truth. Power BI Desktop is the only authoring tool for Power BI and needs to be treated as such. The Power BI service is where all Power BI content goes live, is shared, and is used.

Publishing Power BI content

All Power BI content published to the service is housed within workspaces, and so is content that is created on the service. Workspaces are where you would collaborate with your peers on the content before making it available to a wider audience.

Workspaces are usually created for different types of requirements. Think of the various types of analytical requirements that you would need to cater to. Then, think of how you can organize the content that you create for the requirements into workspaces...

Q&A

Here are a few questions related to the Power BI workflow that may arise during the interview.

Question 1: What are the steps you would take from end to end when developing a Power BI report?

Answer: Here are the steps that I follow:

1. I start with choosing the data sources and connecting to them.
2. I then cleanse and transform the data to match a model that I have in mind for the report.
3. Next, I model the transformed data, ensuring that fact tables and dimension are designed right, and then I create the required measures and make the necessary enhancements to the dimensions.
4. Finally, I visualize the data on a report and publish it to Power BI.
5. Once published I would configure connectivity to the data, share the report, and allow users to collaborate on it.

Question 2: What do you usually consider when choosing a data source for your Power BI projects?

Answer: The first thing I usually consider is how much is the data already processed. If...

Summary

In this chapter, you learned about the workflow of the steps that need to be carried out to implement a Power BI report. During its course, you will wear multiple hats: that of a developer and that of a data analyst.

You start by determining where to obtain your data from and look at the right methodology for connecting to and extracting data. You may not have the experience of connecting to every source supported by Power BI. It is not a realistic expectation. However, as a developer, some due diligence is encouraged to conduct research on the probable source systems that the organization uses and be prepared with overall knowledge about connecting to those systems.

You then embark on a journey of cleansing, transforming, and combining the data into an analytical shape. Power Query has many functionalities for this purpose, meaning that you rarely must clean data at the source before bringing them into Power BI. You learned that this is not done ad hoc but that there...