XMLA endpoints
XMLA is the communications protocol used by Microsoft SQL Server Analysis Services instances. XMLA is a widely used connectivity option supported by many BI tools as a source for visualization.
Power BI is built off the foundation of Analysis Services technologies, and as part of Power BI Premium, it is possible to connect to a Power BI Premium workspace using XMLA. This happens through an XMLA endpoint that is part of the Premium service.
Common uses of XMLA endpoint clients used include the following:
- SQL Server Data Tools (SSDT)/Visual Studio with Analysis Services – Some organizations standardize developer tooling. SSDT has been around a long time and is widely used.
- SQL Server Management Studio (SSMS) – Another tool that has been around a while and is widely used, SSMS allows you to create DAX, MDX, and XMLA queries.
- Power BI Report Builder.
- Tabular Editor.
- DAX Studio.
- ALM Toolkit.
- And the perennial favorite data tool: Microsoft Excel.
Many times, an XMLA endpoint will be used for dataset management with write operations. When this is the case, it’s recommended to enable large model support in Power BI Premium. Large model support in Power BI Premium will allow data models to grow larger than 10 GB of compressed data size. When write operations need to take place, be advised that XMLA endpoints are enabled as read-only by default, so write capability will need to be enabled.
Tenant-level settings are enabled by default to allow XMLA endpoints and analyzing in Excel with on-premises datasets. This setting is enabled for the entire organization by default. Some organizations will choose to disable this default setting, so it’s important to know that this may need to be enabled sometimes.
To enable read-write capability for XMLA endpoints, this option needs to be changed in the Power BI admin portal:
- Select Capacity settings.
- Select Power BI Premium, then select the name of the capacity.
- Expand Workloads and select Read Write under the XMLA Endpoint setting.
The XMLA endpoint connection URL can be seen for each workspace deployed to Power BI Premium by viewing the workspace settings and clicking on the Premium tab.
In addition to client connectivity to Power BI Premium workspaces, XMLA endpoint capability also enables fine-grained data refresh capabilities. By setting up data partitioning, it becomes possible to refresh selected historical partitions without having to reload all data. This is useful for organizations who want to maximize the data stored in the Power BI service (for best report performance) while using large historical datasets.