Use cases of the XMLA endpoint

There has been a lot of fuss around XMLA endpoint in Power BI since the announcement by Christian Wade at Ignite 2018. Firstly Microsoft opened up the endpoint for read access and finally now, as of March 26th 2020, we have write enabled in public preview. This is definitely a game changer and opens up some very interesting use cases.

Migrate an existing SSAS or AAS model to the Power BI service

Deploy your single-version-of-the-truth semantic Analysis Services models directly to a Power BI Workspace. Lift’n’Shift your existing investment to the platform Microsoft is committed to maintain – the superset of Azure Analysis Services. Some clear advantages:

• Power BI report developers get easy access to your Enterprise models. No need to remember long connectionstrings.
• Cut the cost to Azure Analysis Services or an on-premise SQL Server (hardware and license).
• Keep access to the latest and greatest modeling features.

Your model should at least be on compatibility level 1500. Meaning SQL Server 2019 or Azure Analysis Services. It should be an easy task to upgrade if your models are on a lower level.

Promote a Self-service data model

This a personal favorite of mine – making a ownership transfer of a Power BI solution. Going from the Business-Led Self-Service BI to IT-Managed Self-Service BI, where the Business Users “only” create and maintain the reports. It’s a simple three step process:

1. Split the report from the dataset. A best practice in any case.
2. Handover the dataset (Power BI Desktop file) to IT.
3. IT maintain the model (dataset) in a more IT-friendly tool like SQL Server Data Tools (SSDT) or Tabular Editor.

You would be surprised how easy it is to “move” the data model and maintain it in one of the two tools. I would recommend you to use Tabular Editor where you have two options:

1. Connect to the published dataset in the Power BI workspace.
2. Connect to the open PBIX file.

You can then save the metadata (bim-file) and put it under source control and setup DevOps. All while the business users continue creating and maintaining reports as nothing has changed. Pretty powerful !

Get access to additional modeling capabilities

Under the hood Microsoft updated the Analysis Services engine in Power BI to what is known as version 3 or simply enhanced metadata format – based on the Tabular Object Model. It was released in Power BI Desktop as a preview feature in the March 2020 update. This is a prerequisite if you want to maintain your dataset with Tabular Editor or SSDT. It will raise the compatibility level of your model to 1520. You can then use some cool next-generation features thats not yet supported in Power BI Desktop:

• Calculation groups – grouping common measure expressions.
• Translations – support multi-language reports.
• Perspectives – business-domain specific views of dataset.

Be aware that there is no going back when you first upgraded your model (dataset) to the enhanced metadata format. Please make a backup of your files before you try it out. At least until the feature is out of preview 🙂

Use additional tools from Microsoft and third-party

If your familiar with Analysis Services, this shouldn’t be new for you. But if you come from the Power BI side, then you should definitely pay attention and try out some of the new possibilities in regard to authoring, deployment, debugging and monitoring. As mentioned a couple of times, the XMLA write endpoint lets you use tools like SQL Server Data Tools from Microsoft and the open-source Tabular Editor to maintain you models. Both giving you more power in your developing as well as deployment scenarios. Regarding deployment you should also take a look at:

• ALM Toolkit – Open-source tool to manage Microsoft Power BI datasets. Database compare, Code merging, Easy deployment, Source-control integration, Reuse definitions, Self-service to corporate BI. Created by Christian Wade.
• Azure DevOps – Plan smarter, collaborate better, and ship faster with a set of modern dev services.

For debugging, tracing and monitoring, then check out:

• DAX Studio – The ultimate tool for working with DAX queries. A new and exciting feature is the integration of VertiPaq Analyzer where you can analyze the storage structures and data model size.
• SQL Server Profiler – An interface to create and manage traces and events. Chris Webb has a great blog post on “Analysing Dataset Refresh In Power BI Premium Using SQL Server Profiler“.

Limitations in the preview

Please beware that the write endpoint is in preview. It means there still are some limitations:

• Role memberships cannot be specified by external tools. You should specify users for dataset roles in the Power BI service.
• Object level security (OLS) is not supported.
• Connecting using a service principal for automation scenarios is not supported.
• Download back as a PBIX file from the service is prevented.
• A range of data source connectors is not support when upgrading to the enhanced dataset metadata.

And yes, XMLA endpoint is a Premium only feature. Or actually it just need capacity – meaning it also works with the Power BI embedded SKUs. Stay tuned for my next blog post – where I unveil a trick to use XMLA endpoint pretty cheap 🙂