After 14 years using Microsoft On Premise BI Tools (SQL Server, Reporting Services, Integration Services and Analysis Services) Its time to embrace Business Intelligence in the cloud.
In a previous blog we looked at setting up a Pipeline
However, we have found an issue, when you Deploy to Test or Production. the issue resulted in getting a better understanding of the permissions you need in order to create and control the process
The Deployment hangs and doesn’t continue on to creating a new Premium App Workspace. If you click refresh, a workspace gets created but it is none premium. In other cases nothing gets created at all.
This is due to the person who is setting up the Pipeline. They may be Admin in the initial Power BI App Workspace but they may not be able to continue on to actually create the Premium Workspaces
In our example, the Power BI Administrator set up the Premium App workspace and then assigned myself as admin. I didn’t set it up.
there are two ways of doing this, especially when working against Dev and Test. They can be set up as Power BI Embedded in an A SKU
Or you can have a Premium capacity Workspace (You must have this if you want the Production area)
Example using the A SKU
We are using Power BI Embedded created in Azure
I am able to create the Premium test and Premium Production environments. But after testing with a colleague, the issue happened.
Lets have a look at what we have set
Power BI App Workspace
We are both Admin
Azure Power BI Embedded
In Azure. Search for Power Bi Embedded. We already have this set up.
Go to Access Control (IAM)
We have User 2 (Rebecca) set as Owner. We also tried this at contributor level but the issue still occurred.
Contributor – Lets you manage everything except access to resources
Owner – Lets you manage everything including access to resources
You need to go to Power BI Capacity Administrator. I was previously set as the capacity administrator to the Power BI Embedded Capacity. Once Becka was added here we were able to successfully move through the Power Bi Pipeline steps without anything hanging.
Therefore, If you are a user in charge of setting up Power BI Pipelines, you must be a Power BI capacity Administrator
To Test. Do you have to be Owner or Contributor in order to use the Power BI Pipeline once it is set up?
Azure Power BI Premium capacity
Power BI Premium is not created as an Azure Service.
Power BI Premium is Managed by the Power BI administrator within Power BI service. Settings and Admin portal
You actually don’t have to be a Capacity Admin in Premium, but do need Capacity assignment privileges.
The Capacity or Power BI Service Admin can arrange for this to be sorted And you need to be able to create new workspaces,
In May 2020 a new pipeline appeared. Deployment Pipelines
Having worked with Devops, It looks like it is identical to the Pipeline that allows you run builds, perform tests and release code to the various production environments.
Power BI deployment Pipelines are a new way for BI teams to manage the content lifecycle.
It is only available in Power BI Premium
Another current restriction is that it doesnt work with dataflows
There are some great reasons to use deployment pipelines
To Improve your productivity
Faster delivery of your updates
Reduction of manual work and errors
Lets see how it works with an example
Requirements
The Workspaces must be in Premium capacity (You can set up the Dev and test areas on an A SKU to save money)
The Developer must have a power BI Pro License
The developer must be an owner of the data sets in the target workspace
Setting up the pipeline Example
In this example, I want a development Test and Production area.
Each of these areas has a separate data source. One each for Dev Test and Prod (But this might not be the case for your own development)
The first instance we will create a dataset that contains the dataflow.
You need a workspace with a diamond against it (Which signifies its in Premium capacity)
You will need to be Admin in the workspace to carry out the task of creating the Pipeline
At some point I want to check against Member access
The Report
A test report is created over the development data (Which is a table in the Dev Azure Database)
At this point, make sure you change the current value of the parameter to check they are all working. This report has been published up to the Premium workspace
You don’t have to do this. You can use Rules within the Pipeline if you wish.
Start a Power BI Deployment pipeline
The Look and feel of the Deployment Pipeline is great
Create a Pipeline
Now we are into the actual main Pipeline area. You can only assign one workspace to the pipeline. When we move through the pipeline it automatically creates the other workspaces for you.
Our Pipeline testing workspace is going to be set up as the development area
Assign a Workspace
You dont need to start with development. You can start with test or Production but in this case we are going straight to the Dev area. Also you don’t need to have all three environments. This now gets assigned to the Development pipeline
At this point you can see what will be part of the Dev Pipeline. Show more shows you the content within. At this point you can visibly select items that you want to deploy up to the next stage, but in this example all three of them is required.
Rename Pipeline Testing to Pipeline testing (Dev). Click on … and go to Workspace settings
Publish App
Your report consumers and testers will want to use your reports and dashboards as apps. You can create Apps at every stage. To do this click on … and Publish app
Deploy to test
Clicking Deploy to test creates a copy in your Test area
It will copy your Reports, Datasets and dashboards into the test area. If you have dataflows you should note that these currently don’t get included
Rename to Pipeline testing (Test) if required
At this point we may want the test area to be connected to a different data source than the development environment. Because we set up Parameters in the pbix file to change to different databases, we can use parameter rules. If if you dont have parameters set up you can create a data source rule.
Dataset Settings
At this point. go back to your New Test Premium Workspace.
Against the data set click … and Settings
I have changed the Parameter to the correct one
Now refresh the credentials
And repeat when you get to Production App Workspace
Deployment Settings (Back in the Pipeline)
Get to Deployment Settings when clicking on the lighting bolt
Parameters have been set in the pbix files so these should be used in this example.
You can use rules(Below) if you don’t have parameters but remember to check your Data set settings first.
Because the source is a database, the pipeline knows to ask for a server and a database. Make sure your Database is set up correctly first within Service.
Deploy to Production
Clean up and set up the same rules (Ensuring after deployment you check your Data set Settings before setting up the rules).
Updates to Development Area
You can also just deploy specified items that you have worked on
For this test, go back to the desktop and add a couple of visuals. Then Publish into the development workspace.
Look to compare the changes
The comparison shows that two items have changed. Deploying into test will copy the new information across
You will also see icons for new and deleted.
Now we can see that production is still using the previous datasets, reports and dashboards. we wont copy across until we are happy the changes are correct.
These are now three individual Workspaces with individual data sets and reports. You will need to set up scheduled refresh for each area.
You can also publish downstream if required by clicking … If the item is available
Limitations
Not for Dataflows or Excel items
Premium Only
The Production workspace must be in premium. You can use A SKU or Power BI Embedded to save money. (A Sku’s can be set up within Azure and are Test environments. they can be paused)
Still looking at the reports that use the dataflow. Lets see if it can compare. The pbix files is opened and amended. then published to the dev Workspace.
At least it tells you which items have changed
With the push to move to dataflows to separate transformation to the actual creation of DAX analytics, it seems like an urgent requirement.
Hopefully this will be resolved soon.
Best Practice
When this is all up and running it is recommended to separate the datasets from reports and dashboards. to do this use the selective deploy
Plan your Permissions model
Who should have access to the pipeline?
Which operations should users with pipeline access be able to perform in each stage?
Who’s reviewing content in the test stage and should they have access to the pipeline?
Who will oversee deployment to the production stage?
Which workspace are you assigning and where in the process are you assigning it to?
Do you need to make changes to the permissions of the workspace you’re assigning?
Debbies Microsoft Power BI, SQL Fabric and Azure Blog 