Creating a Time intelligence “External Tools” app for Power BI Desktop

With the release of the July 2020 version of Power BI desktop we can create tools that can interreact with the Power BI Desktop model directly using an external tool. With this interaction you can make changes to the model in a programmatic way. There are so opportunities things this will open up. You can think of a tool that would allow you to edit the model like Tabular Editor or query like DAX Studio (which both already work as external tools). But we could also have tools that allow you to add translations to the model in a straightforward way or even generate a full model from your favorite CRM \ Sales product. Initially not all actions are supported, you can see the full list here.

In this blog post I will create a tool that will create a calculation group containing time calculations to the model based on a template. Since most time calculations are mostly the same you can apply this to most models. I plan to extend the tool further at a later stage, but this first version is already a good MVP. Also the focus of this blog post is not on the tool itself but how to make an “External Tools” tool yourself. I will cover my tool in a later blog post.

Getting the External tools to show up and connect

The first thing we need to do is to make sure we can connect to the model that is hidden inside Power BI desktop. This is easy as the Power BI desktop model is the same as SSAS and you can use all the tools it has like XMLA and TOM to connect to it. In this blog post I will use TOM as it has a nice object model I can use from C#. To make sure TOM and other external tools can talk to the model you need to have the preview feature “Store datasets in enhanced metadata format“ enabled (warning this is a preview feature so use with caution).

Now there is one problem, the connection details of Power BI desktop change every time the desktop starts. There are ways to detect it but with this release of Power BI desktop there is a new “trick”. You can get your tool to show up in the Power BI menu bar (like below) and at the same time tell your app how it can connect to the model.

Let’s start with this. You need to create a new folder “External Tools”. Here we can place a JSON file for each “External Tool” that configures the icon and what to launch. The folder should be placed here “C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools”

The JSON file is straightforward and contains just a few items, version nr, name, description, path of the executable, the arguments to tell your tool where to connect to and finally the Icon as a base64 PNG.

{
	"version": "1.0",
        "name": "Calculation wizard",
	"description": "Adds a time intelligence calculation group to your model.",
	"path": "C:\\\\CalculationWizard\\CalculationWizard.exe",
	"arguments": "\"%server%\" \"%database%\"",
	"iconData": "image/png;base64,........."
}

That’s it 🙂 now what about the app itself?

Changing the model

To create the app I am using a simple win forms .NET application. To connect to the model, I am using TOM. I add the NuGet packages to my project that will include this code into my project so I can use the Tabular Object Model.

To start I need to retrieve the parameters send to me from Power BI desktop so I can connect to the server and make changes. This code allows me to retrieve the arguments.

string[] args = Environment.GetCommandLineArgs();
string DesktopConnString = args[1];
string DesktopModel = args[2];

Next, I will use these arguments to connect to the model and then get going 🙂

try
{
    using (Server server = new Server())
    {
       server.Connect(DesktopConnString);
       Model dekstopModel = server.Databases[DesktopModel].Model;
    }
}

Now that is all there is too it, from here on it is whatever you make from it. You can see a lot of examples of what you can do in the documentation here.

You can find my External tool here , feel free to help and extend it. I will write another blog post explaining the tool itself in detail at a later point.