Integrate Power BI Deployment Pipelines with Azure DevOps

On By MarcIn Azure, Deployment Pipelines, Developing, Power BI REST API, Power BI Service, Powershell, XMLA Endpoints

Back in 2019, Ton Swart and I wrote a blog post on Versioning and CI/CD for Power BI with Azure DevOps. Back then, there were not many options available for versioning and professionalizing your ALM story for Power BI. Till today, this blog post is still my most read blog on Data-Marc.com which makes us proud.

In May 2020, Microsoft announced Power BI deployment pipelines, a native ALM feature as part of the Power BI Service. As Microsoft stated: Deployment pipelines help enterprise BI teams build an efficient and reusable process by maintaining development, test, and production environments. BI creators can incrementally transition new or updated content between environments, reconfiguring them with the appropriate data connections and permissions.

As I wrote in July 2020 in my blog about why you should care about Power BI deployment pipelines, there were some functionalities lacking in Power BI deployment pipelines, which are easy to build and configure yourself in Azure DevOps as you have full flexibility to customize operations to match your organizational processes. In this blog I highlighted some strengths of Power BI Deployment pipelines, like incremental and metadata deployment. Though, versioning was a missing functionality at the time.

As you might have seen, during the recent MS Build conference, Microsoft announced API support for Power BI Deployment Pipelines. My colleague Ton and I had the privilege to get our hands on this in an early stage, which gave us the opportunity to build an Azure DevOps extension (read along!) based on these all-new APIs. In this blog we will run you through the configuration of the DevOps extension, where you can download it and get you up to speed!

Get you up to speed with the ALM topic

In case you are not familiar yet with the topics described in above introduction, I encourage you to first look at below resources that will help you to get up to speed.

DTAP staged approach using Deployment Pipelines

Above topics will help you to better understand the end-to-end story of Deployment Pipelines. The current gaps we identify in the native Power BI Deployment Pipelines are the lack of dataflow support, versioning capabilities and DevOps integration.

Looking at the Power BI release plan, dataflow support for Deployment Pipelines is coming up shortly! Currently it is scheduled for June 2021 to reach the public preview state. Versioning and DevOps integration go hand-in-hand to our opinion. With Azure DevOps Git integration, we can overcome the versioning challenge while integrating with Azure DevOps at the same time, as described in the previous blog in 2019. Today, we release a new version of the DevOps implementation which uses native Power BI functionality. Stay tuned!

As we really like the metadata deployment and the ease of setup a pipeline in the Power BI Service, Ton and I decided to setup an Azure DevOps extension based on the recently released Power BI REST APIs for Deployment Pipelines. Although Microsoft promised to come-up with a native DevOps extension over time, we decided to go for it. Time to bridge the gap!

Setup your ALM story with Deployment Pipelines and Azure DevOps

In this section, we will walk you through all the steps you need to take to integrate Azure DevOps and Power BI Deployment pipelines. Step by step, we will describe each step you have to take.

Setup Deployment Pipelines in the Power BI Service

To not go into much detail, it all starts with setting up your Power BI Deployment Pipeline in the Power BI Service. Without Deployment Pipeline, we will not be able to configure the DevOps extension. To configure the pipeline, we need to have a workspace that we assign to the pipeline. Today, it is mandatory to first create a pipeline manually. As announced in the release blog, the API will support pipeline creation as well in the future.

It all starts with having a Power BI workspace. For this example, we have setup a workspace “Deployment Sample [DEV]” as this will be our development workspace. In this workspace we have a dataset, report and dashboard like shown below.

Sample workspace for deployment pipeline configuration

Notice that the workspace is part of Premium (Premium per User in this example). This is required to use Power BI Deployment Pipelines.

In the top ribbon of the workspace, you will see the button “Create a pipeline”. By clicking this button, you will be able to setup a new Power BI deployment pipeline that includes this workspace. Secondly, you need to assign the workspace to one of the stages in the pipeline. In this example, we assigned the workspace to the Development stage.

Configure Power BI Deployment Pipeline

The Deployment Pipeline in the Power BI Service will automatically be created. During the configuration, the pipeline will contain only one workspace that is assigned to the development stage. For now, this is sufficient as the other workspaces will be generated during deployment and run of the pipeline.

Currently, you are limited to assign only one workspace to a Deployment Pipeline. In case you have your DTAP configuration already set, you must choose which workspace you assign to the pipeline. This will change in the future, when you will be able to assign multiple workspaces to a pipeline. You can find this future plan on the public roadmap.

Result of a configured Power BI Deployment Pipeline

Setup Azure DevOps

In Azure DevOps we need to import the DevOps extension first. You can find the extension via this link. If you are new to DevOps extensions, this documentation will guide you through installing extensions step by step.

Sample of an Azure DevOps Release Pipeline including stages Dev, Test and Prod.

The DevOps task, can be configured in multiple ways. In our example we use Release Pipelines in Azure DevOps. In the pipeline we use the imported extension for the Test and Production stage to trigger the Power BI Deployment Pipeline to run, like shown in the above example. Depending on your configuration, you can configure pre-deployment approvals or other settings if you like.

If we go into edit mode of the pipeline, we can configure different settings for each pipeline stage. The DevOps extension includes multiple tasks that can be set. Below is the setup as we have invented it:

  • Development: Import Task, to upload PBIX files from the GIT Repository to the Development Workspace.
  • Test: Pipeline task, to move content from Development to the Test Workspace.
  • Production: Pipeline task, to move content from Test to the Production Workspace.

Below we describe in more detail what all different settings mean and how you can configure them in specific for the Deployment Pipeline Task. Underneath you will find a screenshot where all these settings can be found.

Configuration Deployment Pipeline task in Azure DevOps

General

  • Displayname: this is the name of the task executed on pipeline stage execution. If you want to combine different tasks to run on the Agent Job, you can give each task an explanatory name for your own reference. The name is not used elsewhere.

Azure Details

  • Power BI service connection: this is the connection that is configured as Service Connection in Azure DevOps and used for authentication to the Power BI Service and Power BI Deployment Pipeline. The service connection can be a Username/Password combination as well as a Service Principal. Please know that Username/Password combination can only be used without MFA configured.

Power BI Details

  • Pipeline name or id: this represents the name or the GUID from the Power BI Deployment pipeline as configured in the earlier section of this blog. Following the example, the name should be ”Demo pipeline for blogpost”.
  • Target stage name: this dropdown represents the name you want to deploy the content to. If you want to deploy content from Development to Test, you have select “Test”, if you want to deploy from Test to Production, you have to select “Production” in the dropdown menu.
  • Deploy Action: this defines what type of deployment you want to execute. In the dropdown menu you can select “Deploy all content from a stage” or “Deploy specific content from a stage”.
    • All content: If you select all content, there are no further fields you must fill in the configuration.
    • Specific content: If you want to deploy that one report or dashboard only, you can choose to deploy specific content. Additional variables will appear on the screen to fill. In these fields you can specify the Dashboards, Reports and/or Datasets you want to deploy. You can specify the GUIDs from these objects or the names, both works. If you want to specify multiple items, you can specify them in order, semicolon ( ; ) separated.
  • Purge data from the target artifact: This checkbox is created in the deployment with which you can allow the deployment to delete the current data from the target artifact in case of a schema mismatch, such as a change in datatype that does not match the current dataset. For more information, you can check the documentation about Power BI Deployment pipelines troubleshooting.

Dependency handling

In the previous section, we explained that the “Deploy specific content from a stage” allows you to deploy selected items from a given stage in a Power BI Deployment pipeline. Included at this configuration, we also have the checkbox to include related artifacts. Enabling this checkbox will investigate artifact dependencies to find all related items and include them in the deployment.

Dependency investigation only works upstream at this moment. Which means as much as, the dependency investigation only flows from Dashboard > Report > Dataset, but not the other way around. Please take this into consideration when you specify the names and/or IDs of the artifacts you want to deploy.

In order to easily investigate the artifacts that will be included with the Include related artifacts setting enabled, I encourage you to use the lineage view in the Power BI Service.

Lineage overview in the Power BI workspace

Taking the above example of the lineage view, the following scenarios could be defined, all with the setting for related artifacts enabled.

  • Specify Dashboard name: in the scenario where the dashboard name “Demo Dashboard” is specified in the DevOps task variables, this will also deploy both downstream reports, “DIAD Final Report” and “Demo Report”, as well as the dataset “DIAD Final Report”.
  • Specify Report name: when the report name “Demo Report” is specified in the deployment, the downstream dataset “DIAD Final Report” will also be deployed. But other report “DIAD Final Report” as well as the upstream dashboard “Demo Dashboard” will not be deployed.
  • Specify Dataset name: the last scenario where the dataset name “DIAD Final Report” is specified in the dataset variable, only the dataset will be deployed. As you might notice, the setting to deploy related artifacts is already included here. This will be useful in the future when dataflows are supported to investigate downstream dataflows that also must be deployed. The dependency investigation already supports this scenario today, but deployment pipelines cannot deploy dataflows yet.

Wrap up and remarks

We believe the integration between Azure DevOps and native Power BI Deployment pipelines eases the configuration and setup of DT(A)P story for Power BI and at the same time professionalizes the end-to-end ALM story. With this integration it will be easier and more visual for end-users to configure their pipeline in the Power BI service while still being able to trigger pipeline runs in Azure DevOps when Power BI artifact development is part of a larger data platform deployment.

Besides being able to trigger the Power BI Deployment Pipeline, versioning capabilities can be professionalized in this way. Combining the previous version of DevOps deployments as described in an earlier blog, combined with this new extension, allows you to version your Power BI files through DevOps GIT integration and deploy from stage to stage using native deployment pipelines. You can configure the previous extension for your development stage in DevOps Release Pipeline to upload new artifacts from your repository to the Power BI Service and use the newly added Deployment Pipelines tasks to deploy to Test and Production workspaces.

As API support for Power BI Deployment Pipelines is still in a public preview state, please know that things might change over time. Ton and I will do our best to keep the DevOps Extension updated according to the most recent changes of these APIs. If there is anything you bump into, feel free to leave a comment underneath or in the DevOps Marketplace.

Finally, while dataflow deployment is still a missing piece (early June 2021) in the native Power BI Deployment Pipelines, you might have spotted dataflow support as preview in the earlier screenshots. With permission of the responsible Program Manager in the Microsoft Power BI team, we were allowed to share these screenshots, which also means that it will not be long before dataflows will be supported!

With that, I also want to do a shout out to the team we worked with at Microsoft and thank them for their close collaboration, feedback and allow us to build this extension in such an early stage. Those involved know that it concerns them. Thanks a lot!

Links

Co-author

This blog and solution are co-authored by my colleague, Ton Swart. Ton is a Data & Analytics DevOps Engineer at Macaw and has over many years of experience in IT. This includes roles as BI Consultant, Data warehouse specialist and IT consultant. His focus is on Business Intelligence. Ton his expertise is CI/CD with Azure DevOps, PowerShell as automating Power BI deployments.

31 thoughts on “Integrate Power BI Deployment Pipelines with Azure DevOps”

  2. Pingback: Integrating Power BI Deployment Pipelines with Azure DevOps – Curated SQL

  3. Pingback: Versioning and CI/CD for Power BI with Azure DevOps – Data – Marc

  4. MartinSpalding

    Hi Marc,

    Your Power BI extension for this article doesn’t work. When used in my release my artefacts deploy from Dev to Test but not from Test to Prod. The error is: “[DeployTargetStage] Stage ‘StageProduction’ not found”. I think this is a UI issue as when looking at the backend YAML the configuration of Test doesn’t specify a “targetStageName” but Prod does: “targetStageName: StageProduction”. I think “StageProduction” needs to match the Power BI Deployment pipeline stage name of “Production” to fix this error.

    Like

    Reply
      1. Martin Spalding

        Any joy repeating my issue Marc? I’ve used the API via Powershell to get round this but I like how much easier it is to configure your extension and it seems to run much quicker.

        Thanks.

        Like

      3. Hi Martin and Filipe,
        First thought it had something to do with a YAML pipeline, but spotted a typo in de extension code. Will try to update the extension next week. So thanks for reporting this bug.

        Liked by 1 person

  5. Matt Picca

    Hello Marc,

    Will this method work while retaining report GUIDs? We are using Power-BI Embedded and have found in the past that deployment pipelines changes the GUID’s. For context we work with many live connected reports to a single model.

    Like

    Reply

    1. Hi Matt,
      It won’t, every new object will get a new GUID. If you deploy a new version, it will overwrite the existing one and in that case it will continue to have the same GUID.

      Hope this helps,
      Marc

      Like

      Reply
  6. Martin Spalding

    Hi Ton/Marc,

    Am I right in saying that your extensions or the “Power BI Actions” extensions cannot publish .rdl files? No errors are flagged in my release pipeline but my paginated reports are not publishing into my stage workspaces? If this is the case, is the only way via the API for this?

    Thanks.

    Like

    Reply

    1. Hi Martin,

      We never tested / used this in combination with deployment pipelines. Can you elaborate on the case? Do you try to publish it to the workspace or move it from one to another?

      Cheers,
      Marc

      Like

      Reply
      1. Martin Spalding

        Hi Marc,

        I’m using your “Power BI Report Task: CreateReport” task in my release pipeline sourcing from my build pipeline artifacts which contains a number of .rdl files as well as .pbix files. When I run the release initially to my DEV workspace, only the .pbix file artifacts are created, the .rdl files are simply ignored.

        Perhaps your extension code only accepts .pbix files? If so, could this easily be extended to accept .rdl files also as I love how easy your extension is to make the API calls.

        Thanks.

        Like

      2. Thanks for explaining your use case. Ton (co-author + dev) and I discussed this today and we would love to support RDLs in the future to.

        We will investigate the impact and changes necessary this week and discuss how to move forward.

        Keep you posted!
        –Marc

        Like

  7. Martin Spalding

    Thank you for considering this Marc. Paginated reports (within my organisation anyway) are very popular so this addition would help massively.

    Like

    Reply
  8. Ben Arendse

    Hi Marc,

    First off thnx for the awesome blog.

    But regarding this post, what about workspaces/apps?
    We can controle and some what of version controle on the reports that go in to the workspace, but what about the workspace/apps itself.

    – manage workspaces or apps
    — create a workspace
    — what is the name of the workspace
    — what is the description of the workspace
    — when is it the workspace created,modified,deleted
    — does the workspace have an App
    —- create an app in the workspace
    — who can see the workspace (roles)
    —- what kind of roles do the users have
    — Which report can i add to which workspace.
    —- which report can i push to the app.
    — what are the admin settings in powerbi.com.
    — which datasets are connected in which workspace.

    Etc.

    A lot of these things can be done with devops build en deploy, but my question is more on the version control of the workspace.
    The process before you deploy reports or create new workspaces in powerbi.com.
    Where do you define/model your workspaces?

    Kind regards,
    Met vriendelijke groet,

    Ben Arendse

    Like

    Reply

    1. Hey Ben,
      Thanks for your comment. I see a lot of relevant questions, but not all in the context of content deployment. Some are more related to the governance strategy in your organization.

      Of course I’m more than happy to share my thoughts on these aspects, but I think that best fits in another blogpost.

      –Marc

      Like

      Reply
      1. Ben Arendse

        Hi Marc,

        Wow that was quick 😊
        You are right maybe it doesn’t fit this post.

        I’m in my exploring fase on how to do the full enterprise model from A to Z ( B to I 😆) and your post is a good view on the deployment of everything.
        But I’m searching for the part where i design the workspace and set it up as an template so I can use it to build and deploy the workspaces and manage those workspaces as well or even other powerbi.com environments.

        Kind regards,

        Met vriendelijke groet,

        Ben

        Like

      2. Well, the cool thing is that the Devops extension already supports workspace creation, as well as adding/updating workspace user and associated permissions.

        Though, I’ll pick up this input to conduct another post on workspace naming convention, dos and don’ts etc.

        Give me some time, but stay tuned!
        –Marc

        Like

  9. Martin Spalding

    Hi Marc,

    I’m using your “Power BI Report Task: CreateReport” task, I’ve only noticed this now but the task doesn’t overwrite existing datasets of the same name, I receive “The property ‘datasetId’ cannot be found on this object. Verify that the property exists.”. However, if I clear out the destination workspace no error flags and the dataset gets created? Is this the correct behaviour?

    Like

    Reply

  10. Pingback: Power BI Workspace setup – Part 1 – Data – Marc

  11. Thanks guys for the great content. One specific question, do you know of any update regarding this:
    “Finally, while dataflow deployment is still a missing piece (early June 2021) in the native Power BI Deployment Pipelines, you might have spotted dataflow support …”

    Do you know of any programmatic method to handle Dataflows via CI/CD Tool such as Azure DevOps, please?

    Like

    Reply

    1. Hey Nico,
      Thanks for your comment!

      Dataflows are now supported in deployment pipelines. So that should work to move them from Dev to Test to Prod. Though, there is one thing to keep in mind. Since dataflows are made in the Power BI Service, and there is no way to build them in Power BI Desktop or anything, there is not really a file that you can commit to your DevOps repository. What you could consider to do, is build the dataflow, then export as json file and put that in your repository. If you then want to publish the dataflow as part of a CI/CD pipeline in DevOps, I encourage you to check out this blogpost: https://data-marc.com/2020/12/02/update-move-dataflows-across-workspaces/ which includes a PowerShell script that you can integrate in Azure DevOps to deploy your dataflow from repo to Dev.

      I personally think you should only use this binary upload from Repo to Dev, and support deployment from Dev to Test to Prod using the native pipelines. With that setup, you can benefit from incremental deployment that comes with native deployment pipelines, but still orchestrate the deployment process from Azure DevOps.

      Hope this helps you!
      Cheers, Marc

      Like

      Reply

  12. Pingback: Sharing individual Power BI dataflows, but how? – Data – Marc

  13. Pingback: What, How, When and Why on Power BI Deployment Pipelines [Hill Sprint] – Data Ascend

  14. Pingback: Source Control in Power BI – What are your options? – Data Ascend

  15. Pingback: why you should not build one gigantic super dataset in power bi.

Leave a comment