Christian Wade recently announced that read/write XMLA endpoints in Power BI Premium is finally in public preview!

https://powerbi.microsoft.com/en-us/blog/announcing-read-write-xmla-endpoints-in-power-bi-premium-public-preview/

To enable read-write for a capacity

1. In the Admin portal, click Capacity settings > Power BI Premium > capacity name.

2. Expand Workloads. In the XMLA Endpoint setting, select Read Write.

What Is the XMLA Endpoint for Power BI and Why Should I Care?

What exactly is an XMLA endpoint, and what is the benefits of it? what it can do? Reza Rad has a great post here that helped me better understand this!

Power BI Premium uses the XML for Analysis (XMLA) protocol for communications between client applications and the engine that manages your Power BI workspaces and datasets. These communications are through what are commonly referred to as XMLA endpoints.

XMLA is an industry standard communication protocol, introduced in 2005 , for client tools to work with Business Intelligence datasets.

Which is More Promising: Data Science or Software Engineering? | Data Driven Investor

About a month back, while I was sitting at a café and working on developing a website for a client, I found this woman…

www.datadriveninvestor.com

It is the same communication protocol used by the Microsoft Analysis Services engine, which under the hood, runs Power BI’s semantic modeling, governance, lifecycle, and data management.

Optimize datasets for write operations

When using the XMLA endpoint for dataset management with write operations, it’s recommended you enable the dataset for large models. This reduces the overhead of write operations, which can make them considerably faster. For datasets over 1 GB in size (after compression), the difference can be significant.

Large models in Power BI Premium (preview)

Large models in Power BI Premium (preview) - Power BI

Power BI datasets can store data in a highly compressed, in-memory cache for optimized query performance to enable fast…

docs.microsoft.com

Power BI datasets can store data in a highly compressed, in-memory cache for optimized query performance to enable fast user interactivity over large datasets. The large models feature allows datasets in Power BI Premium to grow beyond 10 GB in size. The size of the dataset is instead limited by the Power BI Premium capacity size, which is similar to how Azure Analysis Services works in terms of model size limitations. For more information on capacity sizes in Power BI Premium, see Capacity nodes. You can set up large models for all Premium P SKUs and Embedded A SKUs; but they work only with the new workspaces.

Large models do not affect the PBIX upload size, which is still limited to 10 GB. Instead, datasets grow beyond 10 GB in the service on refresh. You can use incremental refresh to configure a dataset to grow beyond 10 GB.

How to enable the dataset for large models with PowerShell

Must have capacity admin and workspace admin privileges to run the PowerShell cmdlets.

• Install the MicrosoftPowerBIMgmt module.

Install-Module -Name MicrosoftPowerBIMgmt

• Check the dataset storage mode

Login-PowerBIServiceAccount

(Get-PowerBIDataset -Scope Organization -ID <Dataset ID> -Include actualStorage).ActualStorage

• The storage mode is ABF (Analysis Services backup file), which is the default.

• Set the storage mode to Premium Files

Set-PowerBIDataset -ID <Dataset ID> -TargetStorageMode PremiumFiles

(Get-PowerBIDataset -Scope Organization -ID <Dataset ID> -Include actualStorage).ActualStorage

You can check the status of dataset conversions to and from Premium Files by using the Get-PowerBIWorkspaceMigrationStatus cmdlet.

Limitations and considerations when using large models:

• Bring your own key BYOK encryption: Datasets enabled for Premium Files are not encrypted by BYOK.

• Multi-geo support: Datasets enabled for Premium Files will fail on capacities where multi-geo is also enabled.

• Download to Power BI Desktop: If a dataset is stored on Premium Files, downloading as a .pbix file will fail.

• Supported regions: Large models are supported in all Azure regions that support Premium Files Storage. To learn more, see Products available by region, and consult the table in the following section.

Security

The tenant-level Export data setting in the Power BI Admin Portal, also required for Analyze in Excel, must be enabled.

Export Data

Access through the XMLA endpoint will honor security group membership set at the workspace/app level.

Workspace contributors and above have write access to the dataset and are therefore equivalent to Analysis Services database admins. They can deploy new datasets from Visual Studio and execute TMSL scripts in SSMS.

Operations that require Analysis Services server admin permissions (rather than database admin) such as server-level traces and user impersonation using the EffectiveUserName connection-string property are not supported in Power BI Premium at this time.

Other users who have Build permission on a dataset are equivalent to Analysis Services database readers. They can connect to and browse datasets for data consumption and visualization. Row-level security (RLS) rules are honored and they cannot see internal dataset metadata.

Model Roles

Dataset metadata through the XMLA endpoint can create, modify or delete model roles from a dataset, including setting row-level security (RLS) filters. Model roles in Power BI are used only for RLS. Use the Power BI security model to control permissions beyond RLS.

The following limitations apply when working with dataset roles through the XMLA endpoint:

• During the public preview, you cannot specify role membership for a dataset by using the XMLA endpoint. Instead, specify role members on the Row-Level Security page for a dataset in the Power BI service.

• The only permission for a role that can be set for Power BI datasets is the Read permission. Build permission for a dataset is required for read access through the XMLA endpoint, regardless of the existence of dataset roles. Use the Power BI security model to control permissions beyond RLS.

• Object-level security (OLS) rules are not currently supported in Power BI.

Setting data-source credentials

Metadata specified through the XMLA endpoint can create connections to data sources, but cannot set data-source credentials. Instead, credentials can be set in the dataset settings page in the Power BI Service.

Service principals

During the public preview, connecting with the XMLA endpoint by using a service principal for automation scenarios is not yet supported.

Supported write operations

Dataset metadata is exposed through the client libraries based on the Tabular Object Model (TOM) for developers to build custom applications. This enables Visual Studio and open-source community tools like Tabular Editor to provide additional data modeling and deployment capabilities supported by the Analysis Services engine but not yet supported in Power BI Desktop. Additional data modeling functionality includes:

• Calculation groups for calculation re-usability and simplified consumption of complex models.

• Metadata translations to support multi-language reports and datasets.

• Perspectives to define focused, business-domain specific views of dataset metadata.

Object level security (OLS) is not yet supported in Power BI Premium datasets.

Data modeling and management tools

Client tools that work with the XMLA endpoint

Tools for Analysis Services (SQL Server Data Tools) — SSDT is a model authoring tool for Analysis Services tabular models. Analysis Services projects extensions are supported on all Visual Studio 2017 and later editions, including the free Community edition. To learn more, see Tools for Analysis Services.

SQL Server Management Studio (SSMS) — Supports DAX, MDX, and XMLA queries. Perform fine-grain refresh operations and scripting of dataset metadata using the Tabular Model Scripting Language (TMSL). Requires version 18.5 or above. Download here.

SQL Server Profiler — Installed with SSMS, this tool provides tracing and debugging of dataset events. While officially deprecated for SQL Server, Profiler continues to be included in SSMS and remains supported for Analysis Services and now, Power BI Premium. To learn more, see SQL Server Profiler.

Analysis Services Deployment Wizard — Installed with SSMS, this tool allows deployment of an SSDT tabular model project to a Power BI Premium workspace. It can be run interactively, or from the command line for automation. For example, this tool can be invoked from Azure DevOps pipelines. To learn more, see Running the Analysis Services Deployment Wizard.

PowerShell cmdlets — Use the Analysis Services cmdlets to automate dataset management tasks like refresh operations. To learn more, see Analysis Services PowerShell Reference.

Power BI Report Builder — A tool for authoring paginated reports. Create a report definition that specifies what data to retrieve, where to get it, and how to display it. You can preview your report in Report Builder, and then publish your report to the Power BI service. To learn more, see Power BI Report Builder.

Tabular Editor — Enables BI professionals to easily build, maintain and manage tabular models using an intuitive, lightweight editor. A hierarchical view shows all objects in your tabular model. They are organized by display folders, with support for multi-select property editing and DAX syntax highlighting. To learn more, see tabulareditor.github.io.

DAX Studio — A complete tool for DAX authoring, diagnosis, performance tuning and analysis. Features include object browsing, integrated tracing, query execution breakdowns with detailed statistics, DAX syntax highlighting and formatting. To learn more, see daxstudio.org.

ALM Toolkit — A schema compare tool for Power BI datasets used for application lifecycle management (ALM) scenarios. Perform easy deployment across environments and retain incremental refresh historical data. Diff and merge metadata files, branches and repos. Reuse common definitions between datasets. To learn more, see alm-toolkit.com.

Excel PivotTables — Use Excel PivotTables to summarize, analyze, explore, and present summary data from Power BI datasets. Click-to-Run version of Office 16.0.11326.10000 or above is required.

Third party — Includes client data visualization applications and tools that can connect to, query, and consume datasets in Power BI Premium. Most tools require the latest versions of MSOLAP client libraries, but some may use ADOMD.

Power BI Premium

XMLA endpoint benefits for incremental refresh

Refresh operations through the XMLA endpoint are not limited to 48 refreshes per day, and the scheduled refresh timeout is not imposed, which can be useful in incremental refresh scenarios.

Refresh management with SQL Server Management Studio (SSMS)

With XMLA endpoint read-write enabled, SSMS can be used to view and manage partitions generated by the application of incremental refresh policies.

Refresh historical partitions

This allows, for example, to refresh a specific historical partition not in the incremental range to perform a back-dated update without having to refresh all historical data.

Override incremental refresh behavior

With SSMS, you also have more control over how to invoke incremental refreshes from using the Tabular Model Scripting Language (TMSL) and the Tabular Object Model (TOM). For example, in SSMS, in Object Explorer, right-click a table and then select the Process Table menu option. Then click the Script button to generate a TMSL refresh command.

The following parameters can be inserted into the TMSL refresh command to override the default incremental refresh behavior.

• applyRefreshPolicy — If a table has an incremental refresh policy defined, applyRefreshPolicy will determine if the policy is applied or not. If the policy is not applied, a process full operation will leave partition definitions unchanged and all partitions in the table will be fully refreshed. Default value is true.

• effectiveDate — If an incremental refresh policy is being applied, it needs to know the current date to determine rolling window ranges for the historical range and the incremental range. The effectiveDate parameter allows you to override the current date. This is useful for testing, demos, and business scenarios where data is incrementally refreshed up to a date in the past or the future (for example, budgets in the future). The default value is the current date.

JSON

{ 
  "refresh": {
    "type": "full",    "applyRefreshPolicy": true,
    "effectiveDate": "12/31/2013",    "objects": [
      {
        "database": "IR_AdventureWorks", 
        "table": "FactInternetSales" 
      }
    ]
  }
}

Custom queries for detect data changes

You can use TMSL and/or TOM to override the detected data changes behavior. Not only can this be used to avoid persisting the last-update column in the in-memory cache, it can enable scenarios where a configuration/instruction table is prepared by ETL processes for the purpose of flagging only the partitions that need to be refreshed. This can create a more efficient incremental refresh process where only the required periods are refreshed, no matter how long ago data updates took place.

The pollingExpression is intended to be a lightweight M expression or name of another M query. It must return a scalar value and will be executed for each partition. If the value returned is different to what it was the last time an incremental refresh occurred, the partition is flagged for full processing.

The following example covers all 120 months in the historical range for backdated changes. Specifying 120 months instead of 10 years means data compression may not be quite as efficient, but avoids having to refresh a whole historical year, which would be more expensive when a month would suffice for a backdated change.

JSON

"refreshPolicy": {
    "policyType": "basic",
    "rollingWindowGranularity": "month",
    "rollingWindowPeriods": 120,
    "incrementalGranularity": "month",
    "incrementalPeriods": 120,
    "pollingExpression": "<M expression or name of custom polling query>",
    "sourceExpression": [
    "let ..."
    ]
}

Video

Here is Christian Wade from Ignite 2018 discussing the benefits of XMLA endpoints and includes a demonstration of SQL Server Management Studio connecting to a Power BI workspace to manage datasets.

When will this be available and released to GA?

XMLA read/write - Power Platform Release Plan

Important Some of the functionality described in this release plan has not been released. Delivery timelines may change…

docs.microsoft.com

September 2020 (subject to change)