Deploy a Power BI dataset via a DevOps - Setup

Case
I want to develop my Power BI dataset separately from my Power BI reports. So we started using Tabular Editor. This also allows us to work on the model with multiple people at the same time because every object is a separate JSON file. But how can we automatically deploy these files using Azure DevOps?

Deploy your Power BI datasets with DevOps

Solution
In this serie of posts we will deploy the Tabular Editor project as an Power BI dataset via DevOps. We will deploy this to the development workspace. For the deployment from Dev to Test and Test to Prod we will use Power BI Deployment pipelines.

1. Setup DevOps and security
2. Building the project
3. Publishing as dataset

In this first post we are setting up the project in Azure DevOps as a preparation for publishing Power BI Datasets. We will show you

• the folder stucture of the repository
• which addon is handy in combination with Power BI Premium 
• how to authorize the Service Principal and create a Service Connection
• which variables we use in the variable groups

1) Repository

You need to store those JSON files from your Tabular Editor project in a DevOps (or GITHUB) repository. For this example we created the following folder structure where we separated de models from the reports. Within the models folder we have one or more subfolders for each PBI workspace we want to target and then an other subfolder level for each dataset/project.

Repository folder structure

2) Power BI Automation tools addon

We will use Tabular Editor to deploy from the respository to the Dev workspace in Power BI, but for this example we will use Power BI Deployment pipelines to deploy from Dev tot Tst(/Acc) and then to Prd. This is optional because you need Power BI Premium for this, but it is saving you a lot of time writing and maintaining code.

To call the Power BI Deployment pipelines from within Azure DevOps we need a (free) add-on from Microsoft called: Power BI automation tools. Requesting to add the extentions is just a few clicks, but for approving the installation of the extention you need enough rights on organization level in DevOps.

Power BI automation tools add on for DevOps

3) Service Principal

To do the release of the dataset to the Power BI workspace we need an Azure Active Directory Service Principal (SP).

1. This SP needs to be in an Azure Active Directory Group that will used within the Tenant Settings of Power BI to allow the usage of Power BI rest APIs.
2. This SP needs to get Contributor access in the workspace to do the deployment of the dataset
3. (optional) This SP needs to get access to an Power BI Deployment pipeline (=Power BI premium)

3a) Allow service principal to use Power BI APIs

For this first step you need access to the Power BI admin portal to give the Service Principal access to the Rest APIs from Power BI. 

• Log in to Power BI online and click on the gear icon in the upper right corner
• Click on Admin portal in the menu to open the portal
• Then click on Tenant settings (if you don't see this, continue after the next image)
• Scroll down to the Developer settings and locate 'Allow service principal to use Power BI APIs'

Either allow the entire organization to use the Rest APIs (not recommended) or specify an Azure Active Directory Group and make sure your Service Principal is a member of that group. 

PBI Admin portal - Tenant Settings

If you couldn't find the tenant settings in the Admin Portal from Power BI then you need an Power BI administrator role. Your Office 365 administrator can either give you the role or do the steps above for you. If you are able get this role then you should probably use Azure AD Privileged Identity Management (PIM) to first get approval from a colleage to temporarily get this role and also use the PBI audit log to record all logins to the admin portal and record all changes in the tenant settings. 

Not a PBI admin

3b) Give permissions in Power BI Workspace

Next step is to give your Service Principal contributor rights in the PBI Workspaces where you want to deploy the datasets.

• Go to your Power BI workspace and click on Access in the upper right corner
• Search for your Service Principal by its name (ours is called PowerBI)
• Select the right permission (contributor) and click on the Add button
• Verify the changed list of permissions and then click on the Close button

Give SP permissions in your workspace

3c) Give permissions to Deployment pipeline

The Power BI Deployment Pipelines are a premium feature to release Power BI components from a Development workspace to a Test Workspace and then from Test to Production. These pipelines will save you writing (and maintaining) a lot of PowerShell code for Azure DevOps pipelines, but they also have 2 major disadvantages. They don't use a repository to store versions of your work and the can't deploy to the Development workspace. This is where the combination DevOps and Power BI can help.

For this you need to create a Power BI Deployment Pipeline, connect it to your PBI workspaces (dev, tst & prd) and at last give the Service Principal from above access to your newly created Deployment pipeline. At the moment the only role is admin.

Give SP access to PBI deployment pipeline

4) Service Connection Power BI

Create an Azure DevOps Service Connection. The type should be a Power BI Service Connection which can only be added once the add-on of step 2 is installed. Use the Service Principal of step 3 for this.

• Go to the Project Settings of your DevOps project (bottom left)
• Find Service connections under Pipelines
• Click New service connection (upper right)
• Choose Power BI and click next
• Fill in the details of your Service Principal of step 3

Power BI Service Connection (via add-on)

5) Libarary - Variable groups

Before we start writing the YAML (and some PowerShell) code we need to create some Variable groups under Pipelines - Libary. We have a general variable group with a couple of general variables that don't vary per environment like the download URL for Tabular Editor or the SP properties. We also have three variable groups (one for each environment) that contain variables that do have different values per environment.

Create Variable groups (1 general and 3 for enviroments)

General Variable Group

General Variable Group

• DeploymentPipelinePBI
  The name of your Power BI Deployment Pipeline for publishing from Dev 2 Tst and Tst 2 Prd
• DownloadLinkTabularEditor
  The url of the zipped portable version of tabular editor. See all versions.
• ServiceConnection
  The name of the Azure DevOps Service Connection created in step 4
• SpApplicationId
  The application id of the SP from step 3
• SpClientSecret
  The client secret of the SP from step 3 (change type to secret)
• SpTenantId
  The tentant id of your Azure Actice Directory. You can also find this in Power BI  online in the ?-menu and then About Power BI and in the popup you see the Tenant URL with the ID at the end
• ReposWorkspaceFolder
  This contains the name of the workspace folder in the repository ("Cloud Team" in the repository picture) and it should also be the (start of the) workspace name in Power BI. We will add something behind it to distinguish dev from tst and prd. So in the repos picture you see "Cloud Team" and for Development the actual workspace name will be "Cloud Team - dev" (see next variable group)

Development Variable Group

Development Variable Group

• PowerBiWorkspaceSuffix
  The respository contains the name of the workspace, but for this example we added a suffix after it to distinguish dev from tst and prd

If you don't want to use the Power BI Deployment Pipelines then just Clone this variable group for test/acceptance and production. If you do want to use the Power BI Deployment Pipelines then you don't need variable groups for test/acceptance and production.

Conclusion

In this first post we did all the preparation work for DevOps and for the Service Principal. In the next blog post we will build the project and make it available as an Artifact in DevOps to release it to the Development workspace in Power BI. In the third post we will show two different ways for deployment.