There are several ways to replace the flooring in your kitchen, but if you were on a budget and had to choose between modifying the existing kitchen floor or building an entirely new house, the only difference being the kitchen floor, you would probably choose the first option. When making changes to a Power BI Dataset, you can have a similar experience. When making changes to large, complex datasets, you can either republish entire Datasets or modify the existing dataset and push only the differences up to the Power BI Service.Â
In this blog, we will explore a tool called ALM Toolkit that makes this possible. We will cover what ALM Toolkit does, why it’s important for shared datasets, and how to use it to make changes to your Power BI Datasets.
What is the ALM Toolkit, and Why Does it Matter?
ALM Toolkit for Power BI is a third-party tool that modifies the model files for Power BI Datasets, Template, and BIM files and allows for changes to be made to existing Datasets published in Power BI Service on Premium or Premium Per User (PPU) workspaces.Â
This means transferring the model and data from your desktop to Power BI Service. You can update the model and kick off a refresh in Power BI Service, meaning less attended time, less watching to make sure your large datasets publish to Power BI Service, less strain on your PC, and moving on to the next task a few minutes quicker.Â
This is especially important when working with enterprise datasets and Power BI Deployment Pipelines. These large datasets can take 10 minutes or more to deploy with Power BI Desktop, but ALM Toolkit can merge changes in under a minute using the same dataset, all while having minimal strains on your PC memory and CPU.Â
How to use the ALM Toolkit for Power BI
To get started with ALM Toolkit for Power BI, you will need the following:
ALM Toolkit for Power BI – available for free here
Power BI Desktop
Either
A Power BI Pro License and a Workspace on a Premium Capacity
A Power BI PPU License and a PPU Workspace
A Power BI Dataset with one or more data sources, published to Power BI Service on a Premium or PPU Workspace
We will walk through an example of adding and deleting a data source and updating fields in a data source, but first, we need to configure the ALM Toolkit. Note that XLMA Read/Write permissions must be enabled on the capacity before connecting with the ALM Toolkit.Â
Additionally, once a dataset is published using XLMA, it cannot be downloaded from Power BI Service, so be sure to have a spare copy saved (if you republish from Power BI Desktop, this limitation goes away).
Open the Power BI file in Power BI Desktop, and ensure it is published to a Premium or PPU Workspace.Â
You can tell if this workspace is Premium or PPU by finding the gem icon next to the workspace name.
Navigate to the Premium or PPU Workspace and open the Workspace Settings
Open the Premium Tab and identify the Workspace Connection at the bottom of the screen. Copy this by clicking the clipboard icon in the bottom right corner.
Note: The URL will be powerbi://api.powerbi.com/v1.0/myorg/ followed by the Workspace name, substituting %20 for spaces in the name.
Open ALM Toolkit. The Connections Pane will pop up automatically.
Ensure your Source is selected as Power BI Desktop and the correct Power BI dataset is available in the drop-down.
Paste the copied URL into the Workspace box in the Target Section.
Using the dropdown on the Dataset option in the Target Section will begin an authentication process. Log in using your Microsoft Account Credentials to allow for access to Power BI Service.
Once authentication is complete, select the appropriate dataset from the dropdown.
Select OK to begin the comparison of the Desktop and Service models.
Review the comparison
You will see the Source Model components on the left and, on the right, the Target Model components. In between, you will see a status that shows if the source component exists in the target, is missing from the target, is missing from the source, or has a different definition in the target from the source.
Using the Select Actions drop-down, you can filter the display to show a number of different options, but you’re mostly going to Hide Skip objects with the same definition. This will show only the differences between the two models.
We will navigate back to Power BI to create a new table to see how this appears in the ALM Toolkit.Â
Note: if you keep the ALM Toolkit program open during this, your authentication should persist, and you won’t have to re-authenticate.
In Power BI, go to Get data > Blank Query. This will navigate to Power Query, where you can name this query as you please.
Hit Close and Apply, Save the File, and Navigate back to ALM Toolkit.
Go to Compare in the Top Left, and select OK to refresh the comparison.
Notice the ALM Test Table in the Source Table that is missing from the target, and we will push this file to Power BI Service. Ensure the Action Column has Create Selected.
Under the Select Actions Tab, you have several options to filter or unfilter the fields that may be updated, skipped, added, or deleted in the update to the target. Be sure to review all changes. I usually start by selecting Hide skip objects with the same definitions, which allows me only to view objects that will change when I update the target.
Select Validate Selection, and a list of all changes, warnings, and potential errors will appear. Warnings and Errors can come when you attempt to update a Power BI Desktop File. More details will be provided in the limitations section later on.
Select OK and Hit Update to push the changes to the Power BI Server.
When you refresh the comparison, you will see that all objects have the same definition again.
Updating and deleting objects with ALM toolkits has the same steps, so we will instead look at the comparison screens for both options:
Updating an object using ALM Toolkit
When an object has the same name but different definitions, you will see an Update action assigned to make the target match the source. The above image shows where a field, formerly a whole number field, has been changed to a string field.Â
In the comparison pane on the bottom, the scroll bars will have green sections indicating where the source contains sections different from the target and will be inserted into the target. Red sections where the target contains sections different from the source will be removed from the target upon updating.
Deleting an object using ALM Toolkit
When the object is present in the target but missing in the source, the default option will be set to delete to make the target dataset match the source. Ensure you want to delete tables extensively before making this change, as they may take a lot of work to recover.
Deployments and Release Management
So you built that new kitchen, but now you hate it. The sink is installed on the wrong side of the counter, and all cabinet doors open vertically. How do you go about reverting changes? After running a comparison, you can go to File > Save As in ALM Toolkit to save the comparison. However, you need help maintaining the PBIX or PBIT file version history.Â
To make sure that your Power BI changes can be reverted by more than memory, utilize tools like SharePoint folders, which maintain a version history of files that allow for historical files to be retrieved after they have been overwritten, or establish a folder system where your Power BI Desktop files live, and save versions of the model file in a folder whenever changes are made.
5 Tips and Reminders for ALM Toolkit for Power BI
ALM Toolkit has some limitations – while you can run a comparison with a Power BI Desktop file as the ‘target’ file, most changes cannot be made to a Power BI Desktop or Template file through ALM Toolkit. Power BI has limitations to these files. Forcing changes to the model can break these files. You will receive a warning preventing you from making changes unless a setting is checked otherwise. Don’t burn down your newly renovated house without saving a copy elsewhere first.Â
Once a Power BI Dataset in Power BI Service has been altered using the XMLA endpoint, the dataset can no longer be downloaded from Power BI Service. While you’re learning the tool, make sure to keep copies of the original files saved separately.
Because of the above limitations, best practices state you should save copies of the file before committing any changes to Power BI Service.
After you push a change to a Power BI Dataset using an import model that alters the tables’ structure, kick off a refresh to load appropriate data into the dataset.
You don’t need to use PBIX files as the source! You can use datasets that are already in Power BI Service, BIM files saved from SSRS or Tabular Editor, or Power BI Template files!
Closing
ALM Toolkit for Power BI is a great, free tool to add to your Power BI environment. You can make iterative changes to enterprise datasets and troubleshoot any issues that may arrive in the development lifecycle, all while freeing up your computer from being locked down and pushing large volumes of data up to Power BI Service.Â
If you need additional help or are curious about leveraging Power BI, reach out to the experts at phData today for help, guidance, and best practices!
FAQs
ALM Stands for Application Lifecycle Management, referring to the process of designing, implementing, and improving these Power BI Datasets in an enterprise environment.
No! When using the ALM Toolkit, you can easily copy tables and fields with the same definition that are needed across multiple datasets. Commonly, we can use the ALM Toolkit to copy fiscal calendars that may be complex across multiple datasets, and it makes building a new dataset so much easier when you can grab some of your standard tables and immediately have them set up, including relationships.
