How to manage Dev & Live Environments in Power BI

Februar 19, 2018 / DevOps / Power BI / Tipps & Tricks May 26, 2019


Introduction

Power BI is built to retrieve data from multiple sources into a single Data Model. This is cool. But it also means that you need to prepare at least one query per table – and at least 1 datasource definition per datasource.

From several years of practice in business intelligence projects, I can assure that even simple test data from our clients typically contain a minimum of 10-20 tables from 1 to 10 different datascources, which requires a lot of work to do this upfront work.

This post show you how to avoid tedious manual re-work of all your definitions when switching the Power BI Model from a test environment to an integration or production environment.

Test environment

As DataScientists should not make any changes in production environments, it is obvious that the upfront work needs to be done in a kind of transport chain, starting at a test environment

Test data for test environments are typically stored in different locations as production data – and data are likely to be not 100% in sync with the production environment.

Each datatable, whether it comes from a file, a database, a webservice, your SAP system or your machine learning entity, requires a separate query definition.

Who followed me so far should start to see the upcoming point: As each query definition relies on the attached datasource, a change of the environment requires a manual change of the datascource for each query definition, the number of tables and datasources multiplies manual re-work – and of course increases risk of errors.

Mini DevOps trick for solid transports

To automate the modification of queries with the change of a datasource, we need a smart solution to ensure a proper transport from one environment to another. This is what we recommend:

Prepare at least 2 environments, e.g. Development and Production, in your Data Model using Parameters.

A once defined parameter can be used in many places. Its modification is immediately visible in every parameter occurrence.

Let us assume, that our source data are stored in OneDrive under a specific folder as Excel files. We know the names of the Excel files (Products.xlsx and Sales.xlsx), but we want to parameterize the URL of the OneDrive Tenant and the name of the folder, where the files are stored. Something like:

{OneDriveUrl}{EnvFolderName}/Products.xlsx
where {EnvFolderName} can be either Production or Dev

Here is the guidance how to create such parameters step-by-step

1. Open Edit Query Editor

2. Select Manage Parameters -> Create a new parameter

3. The first parameter will contain the name of the folder depending on whether we want to use test or production data. Set:

  • name
  • description which in the future, when the complexity of the report will increase, will allow you to quickly recognize the function of the parameter
  • parameter type – in our case text
  • suggested values – in our case, available folder names
  • current value

4. The second parameter is the url referring to the location of files, so that if they are moved to a different location, the change will be required in one place.

5. Now, get the data from the Excel files.
Note: How to download data from OneDrive files using the Power BI desktop, read the documentation from Microsoft.

6. Choose advanced settings and build our URL to the file in 3 parts. First, choose the parameter – OneDriveUrl.

Then we add the EnvFolderName parameter.

7. Add an additional part of the url and give the file name preceded by the „/“ sign. In the URL preview, we can see what the URL will look like:

8. Choose the data

9. Add the second data file (Sales.xlsx) in the same way

10. Click Close & Apply

11. The result of the data loaded:

12. To switch to production data, you only need to change the parameter!

13. Select Production from the available list

14. Click button Apply changes

15. The data will refresh. Here is the result from the production file:

In addition, we could simply change the URL to the files by modifying the OneDriveUrl parameter.

Summary

In this simple way, we have saved many clicks and time.

I want to thank my colleague Łukasz Klimczuk for his support in preparing this Blog Post.

What is your best practices to manage the Dev & Live environment in your Power BI solutions?