Conflicting developments in multi developer scenarios, hotfixes done in production, time consuming and manual deployment processes for datasets and reports or forget to change parameter settings when deploying from test to production.
Does that sound familiar to you? Well, it does for a lot of Power BI developers. But what options do you have to fix this? What can you do to implement a secure, automated and reliable process of development and deployment in Power BI? This series tries to give an overview on what is possible as of today.
What is the purpose of DevOps?
The term DevOps (Development Operations) incorporates all practices, principles and tools to deliver high quality software products in a frequent and reliable way. It supports coordination and colaboration in the process of development and delivery. This becomes even more crucial in complex enterprise scenarios with big Power BI deployments and many developers.
The implemented methods are aiming to speed up, optimize and automate all phases of that process. Some of the main goals are:
- Version Control - The ability to manage your solution in versions. Tracking changes and maintain the history for review and recovery. In some scenarios this includes not only your Power BI reports and datasets, but also the ability to set back the datasources (e.g. DWH) to a corresponding status.
- Branching - The ability to support multi developer scenarios with each developer working on his own copy of the solution to enhance it with features. This often includes enabling of extensive user testing for that specific feature.
- CI/CD - Continuous Integration and Continuous Delivery targets an incremental metadata merge of new features from all branches back to the master solution while preventing the risc of overwriting changes made by other team members. Frequent delivery, even of small changes, to the end user is a main aspect of CI/CD.
- Environments - Support for work in several separated development stages (e.g. DEV, TEST and PROD) enabling secure development and testing in environments with different characteristics. This might include different versions of your datasource or simply reduced amounts of data in the different environments.
- Automation - The ability to provide secure, reliable, fast and repeatable development and deployment processes while minimizing the risk of manual interventions by people.
These aspects represent some kind of ideal. Whether you really need some or all of those methods to be implemented and to which extend depends on your very specific situation and how complex it is. This might include aspects of how large your models are, how many models you have, the number of developers involved or the frequency of changes made.
What is the problem with standard Power BI?
It is unfortunate that, at least at the time of writing, Power BI does support near to none of the aspects mentioned above out of the box. In most organizations the development process still is about Power BI files, each containing a model and the associated reports, simply published manually by hitting the publish button in Power BI Desktop before manually updating the Power BI App on top of that workspace.
This seems to be fine as long things do not get too complicated. If you only have a small number of reports. If the models are not too big and will not slow down your workflow or if you are able to work one developer at a time, to avoid the risk of overwriting the changes of your team members.
Standard Power BI especially falls short regarding the following aspects:
- Any kind of automated integration and deployment as long as your are not using Power BIs REST API programmatically
- There is no integration to Git or any other platform targeting version control and supporting branching strategies
- Only limited support for different development and deployment stages in form of Deployment Pipelines which are not capable of managing deployment of changes to the underlying datasources if necessary
- No support for incremental metadata deployment to drive continuous integration of changes in multi developer scenarios
In a nutshell a solution built in Power BI Desktop consists of the follwing three layers:
- ETL/Pipeline - PowerQuery imports to fetch data from sources, transform them on the go and load them into the Power BI datamodel (assuming using Power BI`s import mode)
- Datamodel - Model containing imported tables, their relationships and business measurements in form of DAX code
- Visual Layer - Report canvas including the visuals, buttons and drill functionality to create the user experience
Especially the last one, the visual layer, (as off today) lacks any capabilities to support multi developer scenarios, merging or incremental metadata refresh. So the first advice to give to enhance your development process is to always separate the report from the datamodel into different files and assign a "one developer per report"-policy if possible. In that way you enable at least the datamodel and the ETL layer to be object of other tools that bring all the capabilities we would like to have.
Options for improvements and their prerequisites
There are a couple of tools and features involved to bring the benefits of DevOps to Power BI. For some of them you need to fullfill some or all of the following prerequisites:
- Separation of reports and datamodel in separated files
- Power BI Premium or Premium per User to utilize XMLA read/write Endpoints as well as Deployment Pipelines
- Availability of Azure DevOps or a DevOps platform of your choice
- Availability of Visual Studio, Tortois Git or any other tool of your choice for integration of your codebase
Hereafter the list of tools that enable some or all of the aspects I discribed at the beginning of this post. I will have a closer look at all of them in upcoming articles or videos. In that posts we will take a look on what those tools are, on specific features they have, how they work, which of the aspects each of them caters and how you can combine some of them to apply different degrees of DevOps processes to your Power BI projects.
Those tools are:
- OneDrive/Sharepoint - In the context of Power BI these tools mainly provide capabilities for version control as well as the ability to automatically synchronize or deploy your Power BI files to your workspaces in the Cloud Service
- Deployment Pipelines - The only Power BI inherent feature in this list. It enables manual as well as automated deployments of datasets, reports and dataflows across different stages and environments
- ALM Toolkit - A third party tool that supports multi developer scenarios. It enables schema compare between datamodels and incremental metadata deployments to merge changes into the master model
- Tabular Editor - A third party tool that provides a wide range of features for manual and automated development of Power BI datamodels and tabular SSAS models. Together with Power BI Premium or Premium per User it also enables to develop your changes while the model is running in the Power BI Service
- Azure DevOps - A platform to support the whole process of developing and deploying BI (Software) solutions. In conjunction with other tools it is capable of providing all the aspects of version control, branching, CI/CD and environments in an automated way
I would be glad if something interesting for you was in that list!
Hope you enjoyed this article. Looking forward to welcome you back for the upcoming posts on this topic. You can also visit my YouTube-Channel for more content on Power BI, SSAS, Power Pivot, Power Query, DAX und M!
STAY IN TOUCH...
LATEST ARTICLES ON BI BLOG...
>> mehr lesen
DevOps for Power BI - What is it? Do you need it? And which options are available? (Fri, 26 Nov 2021)
>> mehr lesen
Kommentar schreiben