• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Power BI open-platform connectivity with XMLA endpoints public preview

March 30, 2019   Self-Service BI

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.

CentralizedBI Power BI open platform connectivity with XMLA endpoints public preview

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.

Copy workspace URL 300x282 Power BI open platform connectivity with XMLA endpoints public preview

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.

SSMS connection Power BI open platform connectivity with XMLA endpoints public preview

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.

Capacity admin setting Power BI open platform connectivity with XMLA endpoints public preview

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.

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Connectivity, endpoints, openplatform, Power, Preview, Public, XMLA
  • Recent Posts

    • Accelerate Your Data Strategies and Investments to Stay Competitive in the Banking Sector
    • SQL Server Security – Fixed server and database roles
    • Teradata Named a Leader in Cloud Data Warehouse Evaluation by Independent Research Firm
    • Derivative of a norm
    • TODAY’S OPEN THREAD
  • Categories

  • Archives

    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited