Wikipedia defines Application Lifecycle Management as a set of pre-defined processes that start somewhere in the business as an idea, a need, a challenge or a risk and then pass through different development phases such as requirements definition, design, development, testing, deployment, release and maintenance spanning across an entire lifecycle of a product. As more and more organization embrace Power BI, one of the challenges that they face is to be able to move Power BI artifacts through various environments (for example, Development->Testing->Production).
To put this challenge in the context of the People-Process-Technology operating model, the good news is that Power BI has the Technology to enable ALM; however, the Achilles’ heel of these initiatives is the fact that strictly speaking Power BI is not natively ALM-aware yet. Therefore, we currently have to complement gaps in Technology with a heavy dose of Process.
Before we get too far in the weeds, let’s think about whether every Power BI initiative should be done in the context of ALM. I would argue that unless the Power BI model in question has been operationalized to the point where it becomes a part of a standard business process, ALM is probably an overkill. However, as models mature so does their adoption by the enterprise and eventually many of them start driving daily activities of large audiences in an enterprise which makes it absolutely necessary to set up governance and ALM processes around them.
We can partition our ALM activities in the following three categories
- Support for multiple data environments
- Support for Development/Test/Production environments for Power BI content
- Dissemination of Power BI content to the end users
Power BI does a very good job with #2 and #3. We can use Power BI groups to partition our content between Development, Test and Production (to support #2) and we can use Organizational Content Packs to package the content up and deliver it to the end users (to support #3). Both of these topics have been widely covered by both Microsoft and the Power BI blogosphere, so there is not much value in covering them in detail here.
Support for multiple environments is a little tricky in Power BI so I wanted to cover it in some depth below.
Let’s think of a common scenario where a company has three data environments: Dev, Test and Prod. As an example, let’s say that each environment is hosted on its own SQL Server:
- DEV: dev.mycompanyIPAddress.com
- TEST: uat.mycompanyIPaddress.com
- Prod: prod.mycompanyIPAddress.com
As a developer, I will connect to my Dev environment (dev.mycompanyIPAddress.com), load all data in my Power BI model and then build necessary measures and visualizations. I will save this model on a network drive or a Dev Power BI group so that I can co-develop it with my colleagues. The challenge arises when I decide that I am ready to promote this model to my Test environment. It is easy to open my PBIX file and re-publish it to a different group (TEST), the issue is that in Test I am supposed to be accessing a different instance of SQL Server – uat.mycompanyIPaddress.com. This means that I have to save my PBIX file as a different file and then go through all of my connections in Edit Queries window and switch them all to this new server. After I do that, I now have two physical copies of my model which means that I now have to keep them in sync. When I promote my file to Prod, I will end up with 3 copies that I have to manage.
What I don’t like about his approach is the fact that the only way to keep these files in sync is through adhering to a strict Change Control Process. I would much rather have Technology assist with the complexities of moving this content through each environment. Turns out that this can be achieved in Power BI Desktop, albeit not in a very intuitive fashion.
In order to make our single PBIX file be aware of all three data environments we need to do some Power Query magic.
The first thing we need to do is to go to the Edit Queries -> Manage Parameters. There we can create a ServerName parameter, specify that it’s a “List of Values” in the Suggested Values dropdown and then list all of our three servers in that list.
Now we need to wire this parameter to our queries. Before we do that, let’s make sure that Formula Bar is enabled for our queries
I can click on the very first step in my query that will usually be named Source and see what my current server name is
I can now replace it with my ServerName parameter (please remember that Power Query is case sensitive!!!)
You need to do this for every query that uses this server as source. Now you should be able to easily switch this single PBIX file from one data environment to another. All you need to do is click on Edit Queries->Edit Parameters
… and select the server that you wish to connect to and then Apply Changes to refresh the model
business intelligist 