PowerBI - CICD using Azure DevOps

[Jayendran]

Introduction

Power BI is a business analytics service by Microsoft. It aims to provide interactive visualizations and business intelligence capabilities with an interface simple enough for end-users to create their own reports and dashboards. Currently, there is no direct way of Implementing CICD in Power BI. Using the Publish menu from the Power BI Desktop is the easiest way to deploy/publish a report as of now. In this article, we are going to see how we can implement source control and CICD for Power BI using Azure DevOps.

                                                   

Difficulties using direct publishing from Power BI Desktop

There are some difficulties of publishing a Power BI Report directly from Power BI Desktop, that includes

• Power BI Desktop (Client Tool) needs to be installed in all the Environment (Production,Dev,QA Servers)
• Without having a proper Source Version Control, the changes couldn't be tracked.
• The developer needs access to all the data sources across every environment. For E.g, if we have 2 environments say Dev and PROD and the data source is SQL server, then the developer needs access to both Dev SQL Server and PROD SQL Server.
• The developer needs to manually change the data source settings for every environment

Best Practice

• If you using Cloud Data sources you don't need a gateway, but for on-premises data sources, we should use the gateway. Another Advantage of using the gateway for cloud data sources is your credentials were encrypted and stored in the gateway server and not with Power BI
• Try to reduce hardcoding your datasources in your reports (or) In other words, try to use Parameters for your data source as much as possible, Also keep in the mind updating parameter using Power BI REST API don't support for the datasources like SQL,Oracle,Teradat...
• Organzie your Source Control folder structure based on your requirements/client-specific /data sources.

CICD Process for Power BI Reports 

As soon as we say the automation(CICD) the things that would come to mind be like using API / Cmdlets. Likewise,we can use Power BI Rest API /  PowerBI cmdlets for automating Power BI reports.

Maik van der Gaag created a great (Azure DevOps) extension called Power BI Actions, which makes things easier to handle the CICD for Power BI. We are going to see how this extension would be helpful for us in the CICD process., This extension will be handly in most way but in sometimes we need to do some additional steps that won't support by this extension for now,so we also going to use some PowerShell Scripts.

✔ Sample Power BI Reports and Environment Specification

For the demo purpose here you will see two different powerbi reports deploying in two different environments (workspace), keep this as a reference you can do use your own reports.

Dev Environment Workspace  :  PowerBI_CICD

Prod Environment Workspace :  PowerBI_CICD_PROD

PowerBI Reports:

1. AdventureReports.pbix
2. Products.pbix

Dev Environment Data Sources

AdventureReports => SQL =>devenvironment.database.windows.net;devdb

Products => OData => https://services.odata.org/V3/OData/OData.svc/

Prod Environment Data Sources

AdventureReports => SQL =>prodenv.database.windows.net;proddb

Products => OData => https://services.odata.org/V3/(S(2z5tfelmekddgffwed3sq4ve))/OData/OData.svc/

✔ Power BI Reports in Azure Repo 

The initial step here is to set up a source control version for our Power BI Reports (.pbix files). In this article, we will see on how to setup the Azure Repo as version control for our Power BI Reports. Here you can also use Version control like BitBucket,TFVC,GitHub ,SubVersion etc., instead of Azure Repo.

• Create a new Azure Repo

• Clone the repo in your local and commit your pbix files in the Azure Repo. Here you can see how you can do that using VS Code
  

Commit the changes




Push the Commit

•  Once you pushed the commit, you will see your commit in the azure repo (like below)

✔ Azure Build Pipeline (CI) for Power BI

Now we had our PBI reports in the Azure Repo's. It's time to setup the CICD.Let see how we can setup the Continous Integration

Setup the CI is actually very easy, We just need to include 2 task in the Build pipeline

• Copy Task
• Publish Task

Classic Editor with-out YAML

Copy Task to Staging Artifact



Publish Task

Using YAML

# Starter pipeline
# Start with a minimal pipeline that you can customize to build and deploy your code.
# Add steps that build, run tests, deploy, and more:
# https://aka.ms/yaml
 
trigger:
- master
 
pool:
  vmImage: 'ubuntu-latest'
 
steps:
- task: CopyFiles@2
  displayName: 'Copy Files to: Staging Artifact'
  inputs:
    SourceFolder: Reports
    TargetFolder: '$(Build.ArtifactStagingDirectory)'
    OverWrite: true
     
- task: PublishBuildArtifacts@1
  displayName: 'Publish Artifact: drop'

✔ Azure Release Pipeline (CD) for Power BI

This release pipeline is just a demo purpose, actual release pipeline and tasks may varies depends on the various use-cases based on the end-user.So this section is just gives you a basic understanding on how you can use the existing features in azure devops to implement the Continous deployment of Power BI Reports.

Autentication for Power BI

The autentication for Power BI can be done in 2 ways 

1. Master Account : This basically username and password autentication along you need to reg an application in AAD to access Powerbi API's
2. SPN (Service Principal): This is basically reg you application in AAD and add your app in powerbi portal. Read more 

Kindly do remember that there are many difference between master account vs service principal autentication

Using Power BI Action Extension (Master Account Autentication)

As a first step you need to install this extension from marketplace (https://marketplace.visualstudio.com/items?itemName=maikvandergaag.maikvandergaag-power-bi-actions). Using Power BI Action Extension we can easily do the operations like below from Azure Pipeline, 

1. Create Workspace
2. Delete Workspace
3. Add a new Admin User to worksapce
4. Refresh a Dataset
5. Update DataSource Connection
6. Upload Power BI Report

As of now this extension is using the legacy approach of autentication using username and password, if you want to use the Service Principal you need to use your own powershell scripting which you can see in the next section.

For this demo we are planning to implement the CD for 2 Environments 1. Dev and 2. PROD

DEV Environment

After adding the Power BI Action Task into our Release Task, we need to configure the Power BI Service connection, as this is using master account approach, we need to provide username,password and clientid

PROD Environment

For the Production Environment we need to deploy the reports as well as need to change the datasources.

Deploy Reports





Update SQL DataSource 



Update Odata DataSource



With this you can able to deploy the reports and update the datasource, but the restriction is you couldn't able to update the credentials for the updated datasource

Current limitation of this extension: (As of 18 Aug 2019)

The following features are not yet supported by this extension, but we can expect this to include in near future.

• SPN Autentication
• Parameter updation
• DataSet TakeOver
• Update Credentials for the datasource

Using PowerShell Scripting in Azure DevOps (SPN Autentication)

Using Powershell scripts we can do all the possible deployments and break the limitations of the Power BI Action Extension.

Here we are not going to see all the possbile actions from Power BI instead we will see how we can use Powershell script to overcome the limitations of the above extension

Install the Required Modules

TakeOver DataSet

Update Datasource Credentials for a DataSet

Update Parameters for a DataSet

Variables Used from Azure DevOps

Download Full Script

You can download the full script from https://gallery.technet.microsoft.com/AzureDevOps-CICD-for-fefd58b8

[pbiembed4]

Excellent Post !

[Anonymous]

Nice Article. I understand the intial Checkin.How can we checkin/commit modified .pbix files in tfvc repo?

Thanks!

[Jayendran]

Hi @Anonymous 

Pls refer 

https://www.fourmoo.com/2019/07/23/setting-up-version-control-for-my-power-bi-desktop-files-pbix-with-no-additional-cost-part-1/

https://www.fourmoo.com/2019/08/06/using-version-control-with-power-bi-desktop-files-check-in-check-out-view-or-revert-to-previous-versions-part-2/

[Sam02]

@Jayendran  Great article! helped a lot, just wondering if we can still use this if we have power BI template file .pbit stored on repo or this valid only for .pbix files?

[Jayendran]

Hi @Sam02 ,

You can store the pbit file just for version control, however for doing the CICD you should have a pbix file

[Anonymous]

Hi,

I am using Service Principal Authentication .

I am trying to create the CICD pipeline for Power BI deployment.

I am using Power BI Action extension. Publish report is working but Update Datasource connection is giving the following error 

##[error]Operation is not supported for selector #5

When I update the connection for one dataset its working finr but when I try to do it for multiple datasets its giving this error.

Please advise .

[Jayendran]

Hi @Anonymous ,

I've responded to your query in the developer forum

[Anonymous]

Hi @Jayendran ,

I need some clarification regarding service accounts/master account.I need to setup CICD using GW as source DB is onpremise.

1)I have created APP in App registration.(Does this app neads to be created by Admin?)

2)I got Grant admin consent on powerbi app in app registration.

3)My userid is added in owners in app registration.

4)My CI is find but when i do CD using  my userid it gives me error.

Can you please help.Do i need to do any settings on PowerBI side as well?

I am deploying .pbix files to workspace.

[Jayendran]

Hi @Anonymous ,

Actually the error message is complaining about either a wrong tenant id / app id is provided. Please cross check that.

Also provide the correct PowerBI API Permissons to your app, then provide the admin consent.

You don't need to include your userid to the owner of the app, that no needed. However make sure that your serviceaccount/master account added as the admin of the powerbi workspace.

Thanks !

Jay 

[Anonymous]

@Jayendran Thanks Mate!I fixed it.One more question:Is there a way by which i can create folder inside workspace to have some kind of segregation for reports?

[kv]

Hi @Jayendran  Will the Update SQL Data Source work for updating the data source connection for an Oracle database?

We have Power BI files with data imported from an Oracle data source that we are connecting/refreshing via a GATEWAY. We were interested in using the extension to automate the update of connection info as we deploy pbix files (import model) from DEV to QA to PROD.

[Jayendran]

Hi @Anonymous ,

No currently there is no way to create folder inside in workspace. The segregation is always on worksapce. But its a good idea to request 

https://aka.ms/PBI_Comm_Ideas

Please provide your feedback in the above 

[Jayendran]

Hi @kv ,

If you are talking about update datasource REST for oracle . It is supported

https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasourcesingroup#restrictions

However I've never tried updating data source credentials for a  oracle data source. You have to try that and let us know 🙂

[shyam7007]

Hi, @Jayendran Thank you so much for this wonderful article.

1. I have parameterised Datasource for my report. I'm unable to change or update the data source for the dataset of Odatatype using RestAPI can you please help me in this regard. I can change the parameters using RestAPI but could not update the data source credentials. I have authentication type as Oauth2. How to change the dynamic 365 crm data source. Is it possible to do it?

2. Is it possible to share a report using RestAPI?

[Anonymous]

Hi Jayendran,

                    I'm trying to publish reports through release pipelines.CI is working fine, but in the CD pipeline, it gets below error with the service principal account as well with the user account. I tested locally, working fine.

In CD pipelines I'm using the below code.

Thanks!