How to implement Source control and CI/ CD for Power BI?

One of the challenges I was facing recently was about a requirement to implement source control and CI/ CD pipeline with existing Bitbucket and Bamboo architecture without using deployment pipelines for Power BI. I would recommend this approach only if deployment pipelines is out of question for your team.

Obviously, we cannot have source control with Power BI files (pbix/ pbit) as they are binary files but we can have source control on “.bim” files generated using Tabular Editor. Tabular Editor is available in Power BI as External tools. The Developers will need to update the bim file whenever they implement any changes in Power BI.

Below are some high-level steps for Source control and CI/ CD in Power BI.

• Separate Power BI Dataset and Report files

This is one of the best practices and highly recommended approach. You can first create and publish dataset then use that dataset for creating the report (visual side).

• Create “.bim” file using Tabular editor (External Tools) for Power BI dataset file

The bim file is a readable file that should be updated whenever there are any changes implemented in the Power BI dataset file. This will help in tracking the changes implemented in the Power BI model. It captures queries, tables and measures information. You can install Tabular Editor from SQLBI website.

• Git/ Bitbucket repository

The repository should contain dataset, report and bim files. It should look something like below image.

• CI/ CD tool

A trigger can be configured in CI/ CD tool (like Bamboo) that executes Powershell scripts when changes in Power BI files are merged into the master branch of the git repository (i.e. Pull request). There are lot content on setting-up the pipeline that triggers and executes scripts. As it’s a topic in itself, I won’t be showing that implementation in this blog post. For reference, you can visit Bamboo website here that explains the same implementation.

• Powershell script

The Powershell script can contain Power BI Powershell commands and REST API calls to publish and configure the dataset/ report to the desired workspace. Below are some of the links to Powershell commands and REST API calls you use to set-up dataset automatically when a CI/ CD pipeline runs.

1. Publish Dataset/ Report
2. Bind to Gateway in Group
3. Update Parameters in Group
4. Update Refresh schedule

To completely automate this process, you may need to use Service principal for authentication in the Powershell script. Please refer to my blog posts Power BI REST API using Service Principal – Part 1 and Power BI REST API using Service Principal – Part 2. It covers the Service principal setup part and provides a sample Powershell script.

Conclusion

Power BI has Deployment pipelines and it’s improving everyday but in terms complete DevOps it isn’t there yet. Hence, the approach mentioned in this blog works especially if you already have a DevOps infrastructure setup using Git and CI/ CD tools. The drawback of this approach is obviously maintenance of the Powershell code and complexity due to lot of moving parts. There are some recent announcements related to integration of Git, Deployment pipelines and REST API but until it becomes a reality we have to implement some workarounds in this area. There are some pipelines REST API that can also be used to automate Power BI deployment.