Organizations need semantic models that serve as the single source of truth for the enterprise to help in intelligent decision–making. Power BI through XMLA endpoints provides open-platform connectivity for Power BI datasets in premium capacities at the 1500 and higher compatibility level. The powerful read-only XMLA endpoint is already generally available since 2019. Using that, the rich semantic Power BI models can be used in other tools and the customer solution implementations became highly scalable. Now, Microsoft has enabled write operations, in addition, to reading in XMLA endpoint as a public preview feature and this brings in more potential to Power BI in terms of capabilities.

This allows customers to leverage semantic models compatible with a wide range of tools like Visual Studio with Analysis Services projects extension, SQL Server Management Studio, PowerShell cmdlets, DAX Studio, Microsoft Excel, and other third-party tools. These tools through read/write XMLA endpoints provides a rich set of advanced semantic modelling capabilities, dataset management, debugging, and monitoring. In this blog, we will mainly discuss the write operations supported through XMLA endpoints.     

Supported Write Operations 

The read/write XMLA endpoint enables Visual Studio, Tabular Editor and other editors to provide additional semantic modelling capabilities supported by the Analysis Services engine, but not yet supported in Power BI Desktop. These powerful functionalities introduced to Power BI Premium datasets include: 

• Calculation groups -which allows reducing the number of redundant measures created by grouping common measure expressions as calculation items. This way, it provides calculation reusability.  
• Metadata translations– to support multi-lingual reports. The tabular model objects can have multiple translations of its name/description. 
• Perspectives– to define more refined views of model specific to the business domains. By this, the users who need a limited part of the model can be given a perspective instead of deterring them with complex models. This enhances user experience too. 
• Deploying models from ‘Visual Studio with Analysis Services projects extension’ with a rich set of semantic modelling capabilities 
• Fine-grain refresh capabilities –using SQL server management studio. 

We can see in detail how to utilize XMLA endpoints to implement the above-listed features. 

1. Read/write XMLA endpoint

Prerequisites 

• Enable XMLA read/write: By default, it’s read-only for premium capacity. In the admin portal, change it to read/write in capacity settings.

• Enhanced metadata: XMLA write operations on datasets authored in Power BI Desktop and published to a Premium workspace requires enhanced metadata. Explore the enhanced metadata in detail here. In Power BI Desktop preview features, enable the “store datasets using enhanced metadata format” option
• Copy the Power BI premium workspace connection URL from ‘Settings’ ->’Premium’ -> ‘Workspace Connection’.

2. Calculation groups

Calculation groups work with explicit DAX measures. ‘DiscourageImplicitMeasures’ model property should be set ‘true’ to create calculation groups. Here we are using calculation groups for reusable time intelligence calculations. In Visual Studio, create ‘Timecalgroup’ Calculation group and its individual calculation items and edit its DAX formula accordingly.

Calculation groups can be used after deploying to the workspace. We can reuse it with any DAX measure to get its time intelligence calculations. We can see the calculation group in action below for ‘sales’ DAX measure.

3. Metadata translations

Multiple translated strings can be given to objects like table, columns, measures etc. for its name and description. In Visual Studio, we can create, manage, and import multiple translations based on the requirement. We can see the metadata translations in action below for Spanish, French and Portuguese languages.

4. Perspectives

We can create perspectives of a group of fields from our model for each department in the organization to make the work focused. In Visual Studio, select create and manage perspectives.

Select the required fields and deploy it. We can see how to access the created perspectives in Power BI below.

5. Fine-grain refresh capabilities in SQL server management studio

We can access the Power BI premium datasets from SQL server management studio by connecting to its workspace URL. With this, we can do an incremental refresh for specific partitions alone by selecting it based on need whereas in Power BI workspace only refresh for the entire set of partitions is possible. Refresh operations through the XMLA endpoint are not limited to 48 refreshes per day, and the scheduled refresh timeout is not imposed. The incremental refresh by partitions is in action below.

Thus, with XMLA read/write enabled endpoint, Power BI Premium datasets have more parity with Azure Analysis Services enterprise-grade tabular modeling tools and processes. By this, the Power BI platform is nearly converging both enterprise and self-service BI in a single powerful platform.

Read more about  XMLA Endpoint-Read Capabilities in Power BI Premium from the blog titled ‘Demystifying the XMLA – Read Capabilities in Power BI Premium’.

Learn more about Microsoft Power BI services offerings from Visual BI solutions here.