Category: Power BI Desktop

Business Intelligence - BI, Data Warehousing - DW, Power BI, Power BI Desktop, Self-Service BI

Slowly Changing Dimension (SCD) in Power BI, Part 1, Introduction to SCD

Slowly changing dimension (SCD) is a data warehousing concept coined by the amazing Ralph Kimball. The SCD concept deals with moving a specific set of data from one state to another. Imagine a human resources (HR) system having an Employee table. As the following image shows, Stephen Jiang is a Sales Manager having ten sales representatives in his team:

SCD in Power BI, Stephen Jiang is the sales manager of a team of 10 sales representatives
Image 1: Stephen Jiang is the sales manager of a team of 10 sales representatives

Today, Stephen Jiang got his promotion to the Vice President of Sales role, so his team has grown in size from 10 to 17. Stephen is the same person, but his role is now changed, as shown in the following image:

SCD in Power BI, Stephen's team after he was promoted to Vice President of Sales
Image 2: Stephen’s team after he was promoted to Vice President of Sales

Another example is when a customer’s address changes in a sales system. Again, the customer is the same, but their address is now different. From a data warehousing standpoint, we have different options to deal with the data depending on the business requirements, leading us to different types of SDCs. It is crucial to note that the data changes in the transactional source systems (in our examples, the HR system or a sales system). We move and transform the data from the transactional systems via ETL (Extract, Transform, and Load) processes and land it in a data warehouse, where the SCD concept kicks in. SCD is about how changes in the source systems reflect the data in the data warehouse. These kinds of changes in the source system do not happen very often hence the term slowly changing. Many SCD types have been developed over the years, which is out of the scope of this post, but for your reference, we cover the first three types as follows.

SCD type zero (SCD 0)

With this type of SCD, we ignore all changes in a dimension. So, when a person’s residential address changes in the source system (an HR system, in our example), we do not change the landing dimension in our data warehouse. In other words, we ignore the changes within the data source. SCD 0 is also referred to as fixed dimensions.

Continue reading “Slowly Changing Dimension (SCD) in Power BI, Part 1, Introduction to SCD” →
DAX, Power BI, Power BI Desktop, Power BI Documenter, Self-Service BI

Thin Reports, Real-world Challenges

Power BI Thin Reports, Real-world Challenges

I previously explained in a blog post what thin reports are and why we should care about them. I also explained Report Level Measures in another blog post. In this post, I try to raise some real-world challenges we face when developing thin reports. I also provide a solution to those challenges.

Report Level Measure Related Challenges

Creating and using Report Level Measures is relatively easy, but there are some challenges that we face from time to time, such as:

  • Distinguishing Report Level Measures from Dataset Level Measures
  • Report Level Measure dependencies

Determining Report Level Measures from Dataset Level Measures

One of the challenges that Power BI Developers face is creating many report level measures. Unfortunately, Power BI Desktop currently uses the same iconography for both types of measures, making it hard to distinguish the actual measures created within the dataset from the report level measures. It gets even more challenging if we need to write technical documentation for an existing thin report. We have to open the PBIX file of the thin report in the Power BI Desktop and click every single measure. If the expression bar appears, the selected measure is a report level measure; otherwise, it is a dataset level measure.

So unless we use third-party tools, which I explain in this post, we must go through the manual process.

Report Level Measure dependencies

Another pain point related to the previous challenge is finding the dependencies between the report level measures. It is crucial to be aware of the interdependencies when doing impact analysis. We need to understand how a change in a report level measure impacts other report level measures. Again, Power BI Desktop does not currently have any options supporting that, so we have to click every measure and read through the DAX expressions to identify the dependencies or use the third-party tools to save development time.

Dataset and Thin Reports Dependency Challenges

The other challenges are even more difficult to overcome relate to interdependencies between datasets and thin reports. Power BI Service provides a lineage view that shows the dependencies between a dataset and its connected thin reports. But the challenges can get more complex to overcome manually. The following are some real-world examples of more complex situations:

  • What if we need to analyse the impact of changes in a dataset measure on all report level measures of the connected thin reports?
  • How do we analyse the impact of changes on a dataset measure on all connected thin reports, including the visuals, filters, etc…?
  • What if we need to tune the performance and we want to find a list of all unused tables or unused fields?

As you can see, the situation can get pretty complex, so manual operations are virtually impossible.

But there is a third party tool we can use which provides heaps of capabilities with a couple of clicks.

Continue reading “Thin Reports, Real-world Challenges” →
Data Visualisation, DAX, Power BI, Power BI Desktop, Power BI Service

Thin Reports, Report Level Measures vs Data Model Measures

Thin Reports, Report Level Measures vs Data Model Measures

The previous post explained what Thin reports are, why we should care and how we can create them. This post focuses on a more specific topic, Report Level Measures. We discuss what report-level measures are, when and why we need them and how we create them.

If you are not sure what Thin Report means, I suggest you check out my previous blog post before reading this one.

What are report level measures?

Report level measures are the measures created by the report writers within a Thin Report. Hence, the report level measures are available within the hosting Thin Report only. In other words, the report level measures are locally available within the containing report only. These measures are not written back to the underlying dataset, hence not available to any other reports.

In contrast, the data model measures, are the measures created by data modellers and appear on the dataset level and are independent from the reports.

Why and when do we need report level measures?

It is a common situation in real-world scenarios when the business requires a report urgently, but the nuts and bolts of the report are not being created on the underlying dataset yet. For instance, the business requires to present a report to the board showing year-to-date sales analysis but the year-to-date sales measure hasn’t been created in the dataset yet. The business analyst approaches the Power BI developers to add the measure, but they are under the pump to deliver some other functionalities which adding a new measure is not even in their project delivery plan. It is perhaps too late if we wait for the developers to plan for creating the required measure, go through the release process, and make it available for us in the dataset. Here is when the report level measures come to the rescue. We can simply create the missing measure in the Thin Report itself, where we can later share it with the developers to implement it as a dataset measure.

Continue reading “Thin Reports, Report Level Measures vs Data Model Measures” →
Data Visualisation, Power BI, Power BI Desktop, Power BI Service

Thin Reports, What Are They, Why Should I Care and How Can I Create Them?

Thin Reports in Power BI

Shared Datasets have been around for quite a while now. In June 2019, Microsoft announced a new feature called Shared and Certified Datasets with the mindset of supporting enterprise-grade BI within the Power BI ecosystem. In essence, the shared dataset feature allows organisations to have a single source of truth across the organisation serving many reports.

A Thin Report is a report that connects to an existing dataset on Power BI Service using the Connect Live connectivity mode. So, we basically have multiple reports connected to a single dataset. Now that we know what a thin report is, let’s see why it is best practice to follow this approach.

Prior to the Shared and Certified Datasets announcement, we used to create separate reports in Power BI Desktop and publish those reports into Power BI Service. This approach had many disadvantages, such as:

  • Having many disparate islands of data instead of a single source of truth.
  • Consuming more storage on Power BI Service by having repetitive table across many datasets
  • Reducing collaboration between data modellers and report creators (contributors) as Power BI Desktop is not a multi-user application.
  • The reports were strictly connected to the underlying dataset so it is so hard, if not totally impossible, to decouple a report from a dataset and connect it to a different dataset. This was pretty restrictive for the developers to follow the Dev/Test/Prod approach.
  • If we had a fairly large report with many pages, say more than 20 pages, then again, it was almost impossible to break the report down into some smaller and more business-centric reports.
  • Putting too much load on the data sources connected to many disparate datasets. The situation gets even worst when we schedule multiple refreshes a day. In some cases the data refresh process put exclusive locks on the the source system that can potentially cause many issues down the road.
  • Having many datasets and reports made it harder and more expensive to maintain the solution.

In my previous blog, I explained the different components of a Business Intelligence solution and how they map to the Power BI ecosystem. In that post, I mentioned that the Power BI Service Datasets map to a Semantic Layer in a Business Intelligence solution. So, when we create a Power BI report with Power BI Desktop and publish the report to the Power BI Service, we create a semantic layer with a report connected to it altogether. By creating many disparate reports in Power BI Desktop and publishing them to the Power BI Service, we are indeed creating many semantic layers with many repeated tables on top of our data which does not make much sense.

On the other hand, having some shared datasets with many connected thin reports makes a lot of sense. This approach covers all the disadvantages of the previous development method; in addition, it decreases the confusion for report writers around the datasets they are connecting to, it helps with storage management in Power BI Service, and it is easier to comply with security and privacy concerns.

Continue reading “Thin Reports, What Are They, Why Should I Care and How Can I Create Them?” →