We live in a busy and stressful era. In our daily business we can not wait for weeks, months or even longer to get our insights. So we need to deliver faster. Therefor we are working agile to realize continuous delivery of results. The agile approach helps us to deliver small artifacts to the end-user in a continuous flow. But by continuously pushing new content to our end users, the chance for mistakes is growing exponentially.
To avoid mistakes, we need to add some structure to our development process. We can do this by working in a multi-tier developing process. But how do we realize that with a self-service tooling like Power BI?
Integrate multi-tier in Power BI development
You might know DTAP (in the Netherlands known as OTAP), the acronym for Development, Testing, Acceptance, and Production. This multi-tier developing process is very common in IT. But in an upcoming industry of self-service IT, this process is used only a very little. Power BI, which is positioned as a self-service data analytics tooling, does not have a standard integrated capability to apply a multi-tier architecture.
In some cases, the DTAP-street has two additional stages which are Education and Backup. In this blog we will only look at the most common four.
Let’s start with the continuous delivery part. By creating Power BI reports and data modeling in Power BI, we have to do that in Power BI Desktop. In the past, it was possible to do a lot of this work in the Power BI Service as well. But more and more Microsoft pushes all the development work back to Power BI Desktop, which is a good thing in my opinion! Simply because Power BI Desktop has a lot more to offer then the functionalities which are available in the Power BI Service. So Power BI Desktop will be your development environment where you can do your data wrangling, transformations, modeling and of course create your report.
After we are done in Power BI Desktop, we publish the content to the Power BI Service. In case you want to share your content, it is a best-practice to publish to an App-Workspace, instead of publishing to your My Workspace and directly sharing. By publishing to an App-Workspace, only members and owners of a workspace will have access to the published content. So that is our test environment.
To also create an acceptance environment in your App-Workspace, we will use the same content as used for testing. But, we need to add a few more people to the workspace to give them the ability to verify the results for acceptance. So you can add some key-users to the workspace as well and assign them to a member role in the old workspaces. Make sure your workspace settings are set to Members can only view Power BI content. In case your using the new (preview) workspace experience, you have to assign a contributor role to your key-users. The view-only role for workspaces does not exist yet in the new workspace experience, so you are taking more risk here.
Learn all about the new (preview) workspaces by reading the documentation.
Last but not least, the production environment.After your done with testing and accepted by your key-users, it is time to push the content to production. We will do that by publishing the app and grant access to all end-users. Make sure that you have selected all content in the workspace you want to publish in the app. Even more important, check if you have unselected the content you do not want to show to your end-users.
There is one more really important thing to be aware of. Now you have published your report to the end-users, the use and popularity of your report will grow. This may result in access requests of new users. In case you want to grant access to new users, you have to update your published app in the workspace. By doing that, you will also push the last updated content which is available in the workspace. Possibly, this means that you publish content which is still in the testing and acceptance phase. You do not want to show that to your end users! So what to do now?
We have to make sure that we have a separate report which is published in the app to the end-user. To do that, we split each report into at least two separate Power BI Desktop files which are both published to the workspace.
First, we need to have a report which is the one selected to be included in the app. The name of the report will be visible for the end user, so be aware of the naming. Afterwards, we create a second Power BI Desktop file which is published to the workspace for testing and acceptance. Make sure you also give a logical name to the second Power BI Desktop file. Make sure you have unselected the Include in App toggle for all content accept your production content.
Possibly your workspace will look like this:
It is up to you if you want to create separate files for testing and acceptance as well. Personally, I develop in Power BI desktop and only publish my content to the Power BI Service if it is ready for testing and acceptance.
After your done with testing and the results are accepted by your end-users, you can publish exactly this version to the Power BI service with the production name and replace the existing production content. Do not forget to update the app to push the production content to the end-users. If you only want to grant access to additional users, you can simply update the app without unintentionally pushing your test and acceptance content to your end-users.
19 thoughts on “Multi-tier architecture and continuous delivery with Power BI”
This is a great blog post. Just one potential idea is that if you add in an Active Directory Security Group or other type if using the new App experience you shouldn’t need to update the App to Grant people access. The users can get added to the particular group?
LikeLiked by 1 person
First thanks for your compliment.
Regarding your comment, you are totally right. Using an Active Directory Group will do the job as well to add new people to your Power BI App. But in case you want to add another AD-group or change something else, you still need to update the app. So there might be cases that it is unavoidable to update the app and you still do not want to push the changes to your end users. In that case, working with separate reports like I recommend in my blog will help you.
Totally agree that at times it could be a challenge to only add users, but that will mean all the reports will also get updated.
LikeLiked by 1 person
Pingback: Parameterize your data source! – Data – Marc
I am usually to running a blog and i actually admire your content. The article has actually peaks my interest. I am going to bookmark your site and preserve checking for new information.
very nice put up, i certainly love this web site, carry on it
This is a very nice post, thanks.
You could consider to publish the development dashboards too instead of only having them in Power BI Desktop.
This has the advantage that other people in the team can see what was build in a easy manner.
It is nice that now there’s a download report button (in Beta) so that the devellopers can download the latest version here.
I do agree with you that publishing the development report can be useful. In some cases we even have a separate workspace for development.
Pingback: Move dataflows across workspaces with the Power BI REST API – Data – Marc
Pingback: Versioning and CI/CD for Power BI with Azure DevOps – Data – Marc
Pingback: Why you should care about Power BI Deployment Pipelines – Data – Marc
How will this work with direct query to the respectively Data sources in the DTAP street.
Will the Power BI test workspace automatically connect to the Test Data Source (SQL/Analysis server)
and when promoted to the ACC workspace to the ACC Analysis Server?
Nothing will work automatically 🙂 but with DQ you can still leverage parameters. I would advise to use a parameter so you can easily swap the connection string for each stage.
This is a great post, thanks for taking the time to write it. I am still trying to understand the best way of managing this whole process.
One question: will the users not be able to access the DEV file through the workspace?
That depends on the way how you share your content. My best practice is to share via an published app. With that, you can better control where your users will have access to, if you grant workspace level access, they can indeed see everything inside the workspace, which is not what you want.
Hope this helps,
How would you handle such a WoW when using DataSets that are on different workspaces. In our case we have DataSets on a Dev Workspace (With Dev Data) and a Similar DataSet on a Production Workspace with the real production data).
For me this is still a huge problem in PowerBI. Editing each DataSet from Dev to Prod is time-consuming and mostly if you fail and skip one yoru report is going bananas.
I would say, either use XMLA to deploy changes to both workspaces / datasets, or implement variables in you dataset, so you can overwrite production if you deploy a new version and swap the connection string by changing the parameter.
But please know, xmla is a premium feature 🙂
Pingback: Integrate Power BI Deployment Pipelines with Azure DevOps – Data – Marc
Pingback: Power BI Workspace setup – Part 1 – Data – Marc