Power BI open-platform connectivity with XMLA endpoints public preview
Organizations embracing a data culture must find a way to create semantic models that serve as the single source of truth for the enterprise. With the sophisticated data modeling capabilities in Power BI, customers are building enterprise grade semantic models directly into Power BI datasets, which are then visualized on Power BI reports and dashboards.
We are excited to announce the public preview of read-only XMLA endpoints in Power BI Premium. XMLA endpoints enable open-platform connectivity to Power BI datasets. With these capabilities, customers can leverage a single one-version-of-the-truth semantic model across a range of data-visualization tools from different vendors, including many of those covered by the Gartner Magic Quadrant for Analytics and Business Intelligence Platforms.
Analysis Services in Power BI
Azure Analysis Services and SQL Server Analysis Services are based on mature BI engine technology used by countless enterprises for over two decades. The same technology is also at the foundation of Power BI and is powering its datasets. XMLA endpoints provide access to the Analysis Services engine in the Power BI service. With these endpoints, the same Enterprise BI tools that connect to Analysis Services for application lifecycle management, governance, complex semantic modeling, debugging, and monitoring will be enabled with Power BI.
Microsoft’s deep heritage in enterprise BI
XMLA endpoints are rooted in Microsoft’s deep heritage in enterprise BI, which uniquely positions Power BI to converge both enterprise and self-service BI in a single platform. XMLA has long been accepted as the industry standard for data access in analytical systems and enjoys wide adoption from many software vendors.
Client tools that work with read-only XMLA endpoints
The following tools can add customer value with read-only XMLA endpoints to Power BI datasets:
| Tool | Description | Installation/prerequisites |
| Third party data-visualization tools | Non-Microsoft tools to consume reusable semantic models in Power BI. | Install the latest versions of MSOLAP from here. |
| SQL Server Management Studio (SSMS) | SSMS can be used to, for example, view partitions generated by incremental refresh. | The SSMS download is available here. Version 18.0 RC1 or above is required. |
| SQL Server Profiler | Tool for tracing and debugging. | SSMS 18.0 RC1 or above is required. |
| DAX Studio | Open-source, community tool for executing and analyzing DAX queries against Analysis Services. We want to recognize the great work already done in DAX Studio to work with XMLA endpoints in Power BI. | Version 2.8.2 or above. |
| Paginated reports in Power BI Premium, Power BI Report Server and SQL Server Reporting Services | Operational, pixel perfect, paginated reports. | Will be supported in upcoming releases. |
| Excel PivotTables | Traditional interactive analysis. Note this is already provided by Analyze in Excel (see licensing change below). | The upcoming Click-to-Run version of Office 16.0.11326.10000 or above is required. |
The ADOMD.NET client library can be downloaded from here. It provides a programmatic way of executing MDX and DAX queries against Analysis Services for client tools.
Dynamic Management Views (DMV) provide visibility of dataset metadata, lineage and resource usage.
All operations are limited to, at most, Analysis Services database-admin permissions. Some DMVs for example are not currently accessible because they require Analysis Services server-admin permissions. SQL Profiler traces are limited to database-level events.
Addressing a workspace
With the client tools listed above, use the following URL format to address a workspace as though it were an Analysis Services server name.
powerbi://api.powerbi.com/v1.0/myorg/[your workspace name]
myorg can be replaced with your tenant name (e.g. “mycompany.com”).
[your workspace name] is case sensitive and can include spaces.
You can easily copy the workspace URL from the workspace settings dialog.
When using the URL, depending on the tool (for example SQL Profiler), you may need to specify Initial Catalog. How to specify it is shown in the following example using SSMS.
Power BI apps
Users with permissions to read from datasets through published apps can use the same URL format as addressing a workspace, but the app name replaces the workspace name.
Licensing for XMLA
Access to XMLA endpoints is available for datasets in Power BI Premium. Any user and client can connect through XMLA, regardless of whether the user has a Pro license.
Analyze in Excel allows users to create Excel PivotTables connected to Power BI datasets. Behind the scenes, Analyze in Excel uses a private version of the XMLA endpoint. As part of the broader release of XMLA, we are aligning the licensing of Analyze in Excel on Premium datasets to match the overall approach described above for XMLA. This means that any user will be able to use Analyze in Excel on datasets in Power BI Premium. For datasets not in Premium, Analyze in Excel will continue to function as-is, requiring a Pro license to use.
Security requirements
The tenant-level Analyze in Excel setting in the Power BI Admin Portal must be enabled for the current user.
The new capacity-level setting for XMLA endpoints must be enabled by setting the value to 1 for read only. It is enabled by default.
Assuming both the above settings are enabled, access through XMLA endpoints will honor the 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 run database-level traces in SQL Profiler and script out tabular metadata in SSMS.
- App viewers are equivalent to Analysis Services database readers. They can connect and browse datasets for data consumption and visualization, but they cannot see internal metadata.
Further information is available on the Docs page.
Read/write coming soon
We are working on allowing read/write operations through the XMLA endpoint. It will come later. With read/write, the following tools will be particularly useful in addition to those listed above:
| Tool | Description |
| SQL Server Data Tools | Model authoring with a range of enterprise features, integration with source control, and application-lifecycle management processes. |
| SQL Server Management Studio | Perform fine-grain data refresh, scripting and management. |
| Tabular Editor | Open-source, community tool with extensive set of enterprise modelling features, and easy-to-use experience |
| Power BI ALM Toolkit and BISM Normalizer | Application-lifecycle management, incremental deployments and model merging as described in this whitepaper |
| Others | Analysis Services has a rich history of open-source community tools. Various third-party software vendors provide tools for monitoring and managing Analysis Services. |
Dataset-level operations and interfaces exposed by the following programming and scripting languages will work.
- The Tabular Object Model (TOM) client library is available here. It allows programmatic creation of models and administrative functions like importing and refreshing data.
- Tabular Model Scripting Language provides scripting commands that are typically executed from, but not limited to, SSMS.
- PowerShell cmdlets for administrative functions and database-management tasks.
Video
This recording from Ignite 2018 discusses the benefits of XMLA endpoints and includes a demonstration of SQL Server Management Studio connecting to a Power BI workspace to manage datasets.
Final note
Customers value openness and interoperability, and we in the Power BI team continue to make investments to make Power BI the most open BI product in the market.
Over three years ago, we introduced the Power BI custom visualization framework. It has generated hundreds of community visuals and thousands of uniquely customized visuals for specific customers. This demonstrates our commitment to an open, standards-based approach to data visualization.
In November 2018 we shipped Power BI dataflows to bring data in Power BI to an HDFS compatible data lake – Azure Data Lake Storage Gen2 using the Common Data Model (CDM) format. The CDM is an open, standard metadata system for consistency of data and its meaning across applications and business processes. This ensures that data ingested through Power BI dataflows is available to data engineers and data scientists to leverage the full power of Azure Data Services. The CDM continues to evolve as part of the Open Data Initiative.
By now supporting XMLA endpoints we continue our journey of making Power BI more open and extensible, providing access to semantic model in Power BI from any BI tool in the market.