kohera-logo-regular.svg

Avoid headaches when working with separate PBIX files in Power BI

Vector_BG.png

Avoid headaches when working with separate PBIX files in Power BI

Vector_BG.png

Splitting datasets and reports into separate PBIX files is a very common way of managing Power BI reports. This approach keeps data and calculations in a more central repository which acts as a “Single Source of Truth”. But dividing sets and reports can result in a headache or two. Let our blog be your Power BI painkiller.

What causes the headache?

Once you have a Single Source of Truth, you can use it in as many reports as you’d like. The reporting layer only visualizes the data and connects to the repository through a live connection. That makes it very easy to switch data sources. The report file can easily be moved from using test data to using a production data source for example.

Those are convincing arguments for splitting reports, but it is not very practical for the developer. Searing migraines have been known to occur when you need to change measures in your extremely large dataset. The problem is that the report is connected to the dataset in the Power BI Service. When you want to see the updated measures in the report, you’ll have to republish the entire dataset to the service every time. Far from ideal for developing reports smoothly.

The painkiller that solves our challenge

Luckily, there’s a solution for every problem. Yes indeed, this challenge can also easily be tackled. More specifically, by using the Hot Swap Report Connections tool from Steve Campbell. This tool enables you to quickly switch between a local live connection and a live connection to the Power BI service in your report file. In other words, it allows you to set up a local development environment that can be easily deployed to the cloud when you’re ready.

Development overview in Power BI with the Hot Swap Report Connections tool

 

Time to get our hands dirty. I’ll start from a Power BI sample report.

Step 1: Splitting the file

The easiest way to split the Power BI file is by duplicating the PBIX file. I’ll add the suffixes report and dataset to the filenames. In the dataset file, I’ll remove all visualizations and report pages. In the report file I’ll do the opposite. I’ll keep all visualizations, but remove the entire data model such as tables and measures. The result is shown below. We end up with two broken files:

Split the Power BI report into a dataset file and a report file

 

Step 2: Connecting the pieces

Time to fix our report. This is done by publishing the dataset to the service. Once this is done, you can add a live connection data source in the local report file. Tada, all visuals are working again. It’s magic! As you can see on the image below the data view disappeared and Connected live to the Power BI dataset: Sales & Returns Dataset in …. appeared in the status bar. Our split was successful.

Live connection to the dataset in Power BI Service (report)

 

Step 3: Further local development

Suppose we want to create a new measure for one of the visuals in the report. This would mean that we have to:

  1. edit the local dataset file,
  2. publish the dataset,
  3. edit the local report file,
  4. and publish the report.

For one small change, that’s no problem. However, if you want to make some changes iteratively, you’ll have to repeat this process over and over again. In other words, you’ll often have to wait. One solution would be copying some of the visuals to the dataset temporary and test them this way. But again, this is not a proper solution. Aargh, the headache begins. But wait there is a better way!

Step 4: Using the Hot Swap Report tool

It’s time to bring in the Hot Swap Report tool. You can install this with the Business Ops tool. This is an all-in-one external tool installer for Power BI which contains many other blog-post-worthy components.

To install Hot Swap Connections go to Add External tools in Business Ops. Next, click on the checkbox next to Hot Swap Connections in the section of Steve Campbell. Then, click on the Add External Tools button.

Business Ops Tool – Install Hot Swap Connections

 

Once Hot Swap Report tool is installed, we’ll take the following steps:

  1. Open the dataset file locally.
  2. Make a live connection from the local dataset to the local report file.
  3. Continue development in the local report and dataset files.
  4. Publish the dataset file when everything works fine.
  5. Change the data source in the report file (live connection to the dataset in the Power BI Service).
  6. Publish the report.

So let’s go through these steps in a bit more detail.

1. Open the dataset file locally and make a live connection from the dataset to the report

First, open the dataset file and navigate to external tools in the top ribbon. Click on the Hot Swap Connections button (1). In the window that opens, go to the Connect tab (2) and press Overwrite and connect (3). A file selection dialog opens. Select the report file and continue.

If you look closely at the status bar once your file has opened, you’ll see that the text in it has changed. Now it reads Live Connected instead of Connected live to the…. . The report file is now linked to the local dataset.

Remark: you can also choose to create a new file instead of overwriting the report file by selecting the copy and connect button

Create a measure in the local dataset file and update the local report (left: dataset, right: report)

 

It’s now time to visualize our extremely complex measure. I’ll just make a card that covers the entire page and save the report file. Next, I’ll close the file and go back to my Power BI dataset file.

3. Publish the dataset file

The next step is quite simple. We publish the dataset so that the updated version is also available in the Power BI service.

4. Change the data source again in the report file and publish

The modified dataset is ready to go. Time to update the report. In the dataset file in Power BI desktop, open the Hot Swap Report tool. Go to the Remove tab and select Overwrite and remove live connections. This opens a file dialog where we, once again, select the report file. The live connection will be removed and the report opens with errors in the visuals.

Remove live connections from the report file (left: dataset, right: report)

 

Just as we did before, we’ll create a new live connection to our dataset file in the Power BI service.
The visuals will re-appear and in the status bar you’ll see that the report file is connected to the service again.

 

Reconnect the local report file to the dataset in the Power BI service (report)

 

Finally, the last step: Publish the report to the service.  Hurray we’re done! You can check the updated report in the service now.

A final note

As a final note, I would like to mention another tool that can be installed with the Business Ops tool: The ALM toolkit. Among other things, this tool makes it possible to perform deployments of schema changes in Power BI Premium without needing to refresh the entire dataset. A good video about this can be found on the Guy In A Cube YouTube channel. The link to this video is added to the references.

References:

 

 

 

2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
DSC_1388
Aggregaties
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
blog-2303
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...