Latest Enterprise DNA Initiatives

Best practices: Deploying reports from development to Production

Hi everybody!

One of the questions that I have been thinking about lately is version management.
How do you make sure that:

  1. The development changes that you make to a report do not “break” the report which is used by stakeholders
  2. You are not working in a report where somebody else is making changes (and thus, by accident maybe overwriting his/her changes!)
  1. You can connect to a development back-end in order to test new functionalities, but connect to a production system once the report is up and running.

I am very curious about your expertise and best practices. I will share my way of working, but I think it is far from ideal:
a) Regarding the Power BI app: Create a seperate work space for “Development” which can be used to test reports in development. Do not give important stakeholders (which are not involved in testing) access to this workspace.
b) Regarding Power BI desktop: Have seperate maps on your shared filesystem (e.g. onedrive) and only work in the development folder by
(1) taking a copy of the prd file if there is no file present.
If there is another file there, check with the developer if you can make changes/hotfixes as well.
(2) adding your initials behind the report to mark that you are working on something.

Deployment to production is a copy paste from dev to prod (where the old prod file is archived in a map) + change connection to prd back-end system (optional) + publishing to Power BI PRD workspace.

I hope there are some insights here, because my way of working still feels very arcane somehow … :slight_smile:

Cheers,
Rens

1 Like

Hi @Rens.

Loaded question, and everybody’s situation is unique, so I can only offer a few comments (in no particular order) which may or may not be suitable for your situation.

So, first off, I always add a version number to my PBIX files while in development (I also include a version number measure and display this on all report pages to ensure users are using the correct version and to check that publishing was successful).

For version management, I create PBIT templates of every version, and upload the templates to a version control system (that way I can easily “roll-back” any breaking changes I made). (Team Foundation Server is the most common VCS my clients use, although they’ve also used GitHub).

As for ensuring that changes you make don’t “break” a report, regression testing is my place of comfort. (And the level of testing, who does it, and how long the testing takes depends on the business requirements, the importance of the report, and the scope of the changes, so can vary.)

Regarding not overwriting someone else’s changes, again, the version number and, as you said, initials are unfortunately probably required until a proper VCS comes out for Power BI files.

Separate workspaces are a good idea, with most of my clients using 3 (DEV, TEST, and PROD) with different users having access to the different “environments”. Permissions are almost always an issue especially with database sources. I don’t have direct experience with different versions of a file, but do have experience with different database servers; as such, my thoughts would be to change the gateway configuration or to setup and use Power Query source parameters.

Finally, if you use Power BI Premium, then you can use the ALM Toolkit external tool to promote changes to DEV, TEST, and PROD.

Hope this help.
Greg

3 Likes

@Rens,

I don’t have much to add to @Greg’s outstanding post above. However, a proper VCS for Power BI files is a major gap. If you saw our writeup from Data Challenge 11, this was the biggest issue we struggled with in the collaborative development process on the challenge.

Thus, I was very excited when I saw that Power BI Tips recently released v1.0 of their VCS built specifically for Power BI. Our plan was to use this in conjunction with Analyst Hub as the coordination framework for the JMAP collaborative effort on DC #12. Unfortunately, while it looks like a very promising tool that fills a critical gap, even after hours of trying neither @greg nor I was able to get it to install and function properly (if anyone here has gotten it working properly, please let us know).

So, instead with Data Challenge #12 right upon us, we built a simple but pretty useful checkin/checkout status report and reservation system. It doesn’t enforce the file checkin/checkout - it’s merely a status indicator so we have to link it with some of the disciplined business rules that you and Greg discuss (version #s, initials, and one we employ of as soon as you check the file out, you move the current PBIX file from the main Sharepoint folder for the project to one called “Prior PBIX”, so that if someone comes into the Sharepoint folder without first checking the dashboard, they won’t find a file and it will alert them to check the status to see who has possession of it.

Here’s a screenshot of the status dashboard. Ideally, it would be integrated with a Powerapps app, so that you could add a reservation right from within PBI, rather than having to go to Excel and then refresh the report.

Great questions - thanks for initiating this discussion. Eager to hear others’ responses.

  • Brian
3 Likes

@Rens There are other ways than what I use as my process, but this is what I do.

Summary is:

  1. Communicating the plan for updating in a shared environment
  2. Separating into “data model” pbix and “visualizations” pbix files. The “visualizations” pbix live connect to the published “data model” pbix. This allows for a division of labor, re-usability, and single source of truth.
  3. DevOps Repo, or some sort of master source control that gives merge conflicts, commenting, version history.
  4. Workspace/App
  5. Dev Workspace/App and Prod Workspace/App (especially when your “data model” pbix has connections to many reports!)
  6. Power BI release pipelines (premium), this automates and gives excellent options on parameterization of servers and what you want to move from one environment to another. Syncs between Dev, Test, and Prod Workspaces/Apps.

Communicating is key in working in a shared Power BI environment. Make sure everyone knows the established process, and when working on specific files let everyone else know. You can talk in person in say a daily stand up, create a channel in teams or slack, whatever works best with your team.

My typical process is:
Get changes from source control > let others know I’m in x file making y changes > open file in synced folder > make changes > publish to workspace > validate > publish app > sync file back to source control > let others know I’m done.

What is critical is that if someone, for example, chooses to simply edit the report in the browser and save it, I will overwrite all those changes with this process. If someone deletes a file in the workspace on accident, I’m covered and can have the report back up in a few minutes.

Separating the pbix into a “data model” pbix and “visualization” pbix. This is automatically done actually whenever you publish a file too (content and dataset on the service), and I’m not advocating running every single report this way. It makes sense when you create a main data model that will feed multiple different reports. I usually start off in one pbix and then when am not making as many data model changes (creating measures, bringing in data) then I publish it. Then create a new pbix live connected to published dataset, then simply copy the visuals I may have started into the new one. If you have bookmarks and such that don’t copy that way, then copy the original file (or save as) and then remove all the tables of data. Then you can connect to the published dataset. Now you can have people working on new reports or editing existing reports, and have someone maybe making new measures on the data model all in parallel at the same time. You do have to be careful now with your shared data model, as it will have impact on many downstream reports. Table name changes, column name changes should be done as translations not renames for example.

For Source Control I prefer devops repo as we have to be working out of devops for user stories anyway. You just need somewhere to store “master” copies of the pbix files that you work in then publish. Ideally with merge conflict notification, version history, and commenting. And a process for syncing. This is also super important for incremental refresh models. When I modify the pbix file for incremental refresh I utilize alm toolkit to push changes to the service instead of publishing from power bi desktop as to not mess up partitioning.

When publishing from power bi desktop you do so to a shared workspace (not your my workspace) that multiple people can access and publish to. This really for content creators. You can also give read access here to other people if you want to, maybe for internal team only reports. This can be thought of as “draft”. You can then pick and choose which reports you want to publish to an app. This can have many more people as readers that aren’t allowed to see the workspace versions of the reports. It can also be entire organization. Then a copy is published to that app. They are two versions with different GUIDs in the urls.

If you have many reports you can take that another step further as you said, and have a dev and prod version of the workspace/app. First going to dev, checking everything or giving stakeholders a preview for feedback, then pushing to prod.

And if you have premium there is a formalized release pipeline for that dev/test/prod workspace/app scenario. It has many very cool features and easy to use. Power BI Application lifecycle management (ALM) deployment pipelines overview - Power BI | Microsoft Docs

I use this process even when I am a team of one because things happen on the service sometimes and computers can fail.

Hope this helps!

Respectfully,
DataZoe

3 Likes

Thanks for starting this discussion @Rens and to all contributors to this post. We appreciate users sharing varied updates and infos relevant to Power BI.

We are tagging this post as “Solved” due to the inactivity if the thread. For further questions related to this post, please make a new thread.

1 Like