Splitting Data Models and Reports into separate .pbix files should be standard for any Power BI implementation.

Thank you for coming to my TED talk.

Ok, fine. Details forthcoming.

I’ll Have Two .pbix, Please

The premise of this design is that each file is the owner of different components of the combined solution. It’s very common because many times a Data Model will be used across many Reports.

The first file is the Data Model. This file contains Connections to data sources, Power Query transformations, Relationships, Security, and Calculations. Writing ad-hoc visuals in this file within Power BI Desktop is convenient for testing purposes. However, when published to PowerBI.com, the corresponding Data Model Report that is also published should be removed. This .pbix file could be named something like “Dynamics Production Data Model”.

The second file is the Report. This file always connects to a “Power BI dataset” as the data source. This file contains all the tabs, visuals, slicers, etc that the end user will interact with. The Data Model needs deployed to PowerBI.com to start work on building a Report using this configuration.

Single Source of Truthiness

The two file approach keeps data and calculations in a more central repository when many reports are written off similar data sources. A key component of BI is leveraging a “Single Source of Truth”, and that can be very challenging if every Report essentially has its own queries.

Keep queries, transformations, and calculations centralized in as few places as possible.

What’s All This “Testing” I Keep Hearing About?

Another advantage of the split report approach is to the ability to do Test and Production reports with roughly the same files. For example, perhaps reports are being deployed like this:

• Production Data Model | Report 1, Report 2, Report 3.

No matter how you organize things, making copies of the .pbix files and repointing them to the Test database is required. Generally, updating connection strings in the Data Model is most time consuming part of this process. Having fewer Data Models reduces difficulty of update. In the Report files, simply point to either “Test Data Model” or “Production Data Model” in the Power BI Service connection settings.

Calculations Need A Home

Technically speaking, calculations can belong in both the Data Model file and/or the Report file. There are advantages and disadvantages to both. Having commonly used calculations in the Data Model file is ideal.

“But there are ad-hoc reporting use cases where putting calculations in the Report file is preferred.”

“Those ad-hoc reports might turn into forever reports.”

“We need to make things easy for our users!”

“What about the single source of truthiness you wanted?”

“It’s too complicated!!”

“Those ad-hoc calculations will break when the Data Model gets updated!!!”

These arguments go on forever. Find some middle ground.

Previous Posts

Check out my other posts in the “Power BI Concepts I Wish I Knew When I Started” series:
Table Relationships
OneDrive > GitHub
Make It Pretty