CI/CD & DevOps for Power BI… Are We There Yet?

The answer – finally – is a resounding YES! It has been a long road and the path to implementing Continuous Integration & Delivery (CI/CD) in all of its forms for Power BI is still a journey, but the capabilities are now a reality. This broad category includes a range of features and capabilities related to managing project files with version control and the ability to share and collaborate with other development team members. Additionally, we also have the ability to assign individual components of a solution to different team members who then check-out files and develop features, check them back in and merge changes into a feature branch. In enterprise-class projects, having these capabilities enables true Development Operations or DevOps: a process for managing iterative development, testing and deployment with automated builds, using a central process management tool. Implementing full-fledged DevOps is a big lift for most Business Intelligence professionals but the core concept of code version control is essential for any durable project.

Here’s the rub: self-service BI is largely about just getting things done quickly – without asking for permission or waiting to be assigned a task. It’s a make-it-up-as-you-go kind of creative process with exciting, immediate results. When something doesn’t work, you quickly try something else until you create a masterpiece. BI professionals are artists and not garage mechanics. As the artist in a room full of engineers, analytic report designers have an ever-important role in the larger scope of modern data platform solutions.

The Power BI CI/CD Journey & DevOps Enabling Features

In earlier posts, I talked about getting started with basic CI/CD & version management for small projects. Nathan also walked through setting-up a full DevOps solution before the luxury and recent advent of file-based Power BI projects. The process is still quite relevant. Recently, things have been moving very quickly so let’s catch-up. 

Following is a brief timeline of features over the past few years that have brought Power BI from being a pure self-service desktop tool to an enterprise-class platform with DevOps capabilities:

• Parameterized M queries – Reduced PBIX file size for deployment convenience & scaling in the service
• Incremental Refresh – Semantic model table partitioning enables scaling & refresh optimization
• XMLA Endpoint – exposed the full functionality of Analysis Services in the Power BI service
• Community & third-party tool support – ex: Tabular Editor, DAX Studio, …
• Deployment Pipelines – Environment promotion in Premium Capacity (DEV, TEST, PROD)
• Workspace Git & Azure DevOps integration – enables true DevOps & API automation
• Power BI Project – stores semantic model & reports in files & folders as editable text
• Transition MODEL.BIM to TMSL format – coming soon, currently possible with Tabular Editor

The last item on this list has been underway for several months as of this post. The TMDL format will store all objects in the Power BI project to separate JSON files in a similar manner that app dev projects are managed in a source code repository. Currently, the semantic model is described in a single JSON file, MODEL.BIM, inherited from Analysis Services Tabular projects authored in Visual Studio.

Who Done It?

The Tabular Model Definition Language (TMDL) is largely based on the impressive work of Microsoft MVP Mathias Thierbach, primary author of TMDL and PBI-Tools. The feature Principal Program Manager, Rui Romano, was instrumental in bringing this capability into Power BI both before and since being recruited into the Fabric product team organization, both working closely with Christian Wade, Principal Group Product Manager and long-time “semantic model boss” on the product team. I was recently reminded of these contributions by Swiss Microsoft MVP Denis Selimovic at the Data Community Austria Day in Vienna, who referred to these gentlemen as the “Syntax Syndicate”.

Is DevOps Required for All Projects?

No. DevOps adds tremendous overhead to a BI project. A project manager must setup a DevOps project with a backlog and board to manage the epics, features, user stories and tasks for the development work. Team members estimate their work with story points and account for their progress in daily stand-up meetings. Arguably this is a lot of work that can slow the pace of an agile project and stifle the creative process. However, it also helps to avoid risk and provide visibility to the development work and overall health of the project.

For complex projects, DevOps is a necessity, but it is a paradigm shift for Analytics and Business Intelligence projects. The trick is to find the right balance of creative freedom and formal process for your team and organization. The bigger the project, the more important it is to exercise discipline and to use formal methodologies to manage project work. In my experience, the greatest risk to a BI project is when we convince ourselves that our project is the exception, and we are only reminded after things go sideways and we must account for our work, time, and priorities. Managing project work to a central feature and task backlog can solve this problem regardless of the project size or scale.

Is Version Control Important?

Yes…. hell Yes! Some form of file version control and recovery is essential on any project. We all have stories to tell about losing our work and the complexities involved in recovering it – or the grief of not recovering countless hours of work. Compound the task of keeping track of your own changes with tracking and managing many features created by multiple developers and the potential for disaster is high. The self-service nature of Power BI makes it easy to forget how important it is to maintain a master copy of all files with historical versioning. The Power BI service does not provide this capability and downloading files from the service is not a good solution. Fortunately, there are relatively simple methods to do this.

But I’m the Only Developer. Why Does it Matter?

It matters. If you play the lottery, there is a one in four billion chance that you won’t be at your job tomorrow. Things change and we must be prepared. Unfortunately, being hit by a bus is more likely to happen than winning the lottery; and if that were to happen, you would have to talk your replacement through recovering your project while in a body cast at the hospital. Version control and shared file storage are important. Period.

What About the Rest of Fabric?

Power BI is the first Fabric experience to have workspace DevOps integration since the product team have been working on that for years. As of this post, the objects in a Fabric workspace that are supported by Git integration are:

• Power BI semantic models
• Power BI reports
• Paginated Reports
• Notebooks
• Lakehouses

When will we see Git support for pipelines, the new Gen2 dataflows, machine learning experiments and other assets? No doubt that these are priorities, but we don’t know for sure.

Right-sizing Your Solution

In my view, projects and teams of different sizes have different needs. In this post from December 2022, I described DevOps maturity as a pyramid, where most projects don’t require a sophisticated DevOps implementation, and the most complex solutions do. The DevOps maturity is a progression, but only for projects of a certain scale. One of the following options might simply be the best fit for a particular project.

Unless you are throwing together a simple Power BI report that you don’t plan to maintain and add features to, the first and most basic managed project should start with a PBIX file or Power BI Project folder stored in a shared and cloud-backed storage location. OneDrive for Business is a convenient option because it sits on SharePoint Online, which has the option to enable file versioning. With version history enabled, every time you save a copy of the file, a new version is saved in SharePoint with a time stamp and version number. This is great if you can remember what changes you made and when you made them. This is a simple fail-safe option for disaster recovery, but it lacks the ability to explicitly decide when to create a new version, or to annotate and branch your changes.

GitHub is great option for even simple projects, with version control and shared team development. It’s free and easy to get started. You can graduate to enterprise features if needed.

Deployment Pipelines were introduced in Power BI Premium to manage deployment workspace targets and promotion from DEV to other workspace environments for testing, quality assurance (QA) and production. This feature blends with Azure DevOps if you intend to automate the build and deployment process with script and API calls.

Finally, workspaces in a Power BI Premium or Fabric Capacity can be integrated with Azure DevOps as a synchronized deployment target from a Git code repository.