Power BI usage scenarios: Advanced data model management
Note
This article forms part of the Power BI implementation planning series of articles. This series focuses primarily on the Power BI experience within Microsoft Fabric. For an introduction to the series, see Power BI implementation planning.
This usage scenario focuses on advanced data model management, which is when a Power BI content creator relies on a third-party tool to develop, manage, or optimize data models. Some third-party tools are external tools, which Power BI Desktop supports directly. You can also manage a published a data model (semantic model) by communicating directly with the XMLA endpoint in the Power BI service.
Data models are hosted in either the Power BI service, Azure Analysis Services (AAS), or SQL Server Analysis Services (SSAS). This usage scenario focuses on using the XMLA endpoint in the Power BI service.
Tip
Many people refer to third-party tools as external tools. However, there are distinctions in how different tools can be used. Connecting to a local data model in Power BI Desktop is the most literal interpretation of the term external tool. This advanced data model management usage scenario focuses on connecting to a remote data model (a semantic model hosted in the Power BI service) by using the XMLA endpoint. More details on the different ways to use third-party tools are described later in this article.
You can achieve connectivity to a data model by using the XML for Analysis (XMLA) protocol. The XMLA protocol is an industry standard protocol that's supported by more than 25 vendors, including Microsoft. All tools, including third-party tools, that are compliant with the XMLA protocol use Microsoft client libraries to read and/or write data to a data model. Connectivity is achieved with an XMLA endpoint, which is an API exposed by a data model that broadens the development and management capabilities available to semantic model creators.
Note
This advanced data model management usage scenario is one of the content management and deployment scenarios. For a complete list of the self-service usage scenarios, see Power BI usage scenarios.
For brevity, some aspects described in the content collaboration and delivery scenarios topic aren't covered in this article. For complete coverage, read those articles first.
Scenario diagram
The focus of this advanced data model management usage scenario is on using Tabular Editor to manage the data model. You can publish a data model to the Power BI service by using the XMLA endpoint, which is available with Power BI Premium.
Important
At times this article refers to Power BI Premium or its capacity subscriptions (P SKUs). Be aware that Microsoft is currently consolidating purchase options and retiring the Power BI Premium per capacity SKUs. New and existing customers should consider purchasing Fabric capacity subscriptions (F SKUs) instead.
For more information, see Important update coming to Power BI Premium licensing and Power BI Premium FAQ.
Tip
We recommend that you review the self-service content publishing usage scenario if you're not familiar with it. The advanced data model management scenario builds upon that scenario.
Note
Sometimes the terms semantic model and data model are used interchangeably. Generally, from a Power BI service perspective, it's referred to as semantic model. From a development perspective, it's referred to as a data model (or model for short). In this article, both terms have the same meaning. Similarly, a semantic model creator and a data modeler have the same meaning.
The following diagram depicts a high-level overview of the most common user actions and tools that can help you develop, manage, or optimize data models.
Tip
We encourage you to download the scenario diagram if you'd like to embed it in your presentation, documentation, or blog post—or print it out as a wall poster. Because it's a Scalable Vector Graphics (SVG) image, you can scale it up or down without any loss of quality.
The scenario diagram depicts the following user actions, tools, and features:
| Item | Description |
|---|---|
 |
Model creators develop data models by using Tabular Editor. It's common that the initial design work (like Power Query work) is done in Power BI Desktop before switching to Tabular Editor (not depicted in the scenario diagram). |
 |
The data model connects to data from one or more data sources. |
 |
Some data sources may require an On-premises data gateway or VNet gateway for data refresh, like those that reside within a private organizational network. |
 |
Data model development is done in Tabular Editor. Editing of Power Query (M) scripts is supported. Model creators may use C# scripts to accelerate development. |
 |
When ready, semantic model creators publish the data model from Tabular Editor to the Power BI service by using the XMLA endpoint of the target workspace. |
 |
The data model is published to a workspace dedicated to storing and securing shared semantic models. Access to the workspace by using the XMLA endpoint is only possible when the workspace license mode is set to Fabric capacity, Premium capacity, Premium per user, or Embedded. |
 |
Report creators create reports by using a live connection to the shared semantic model. |
 |
Report creators develop reports in Power BI Desktop. Other than purposefully separating reports from semantic models, content creators follow the typical report creation process. |
 |
When ready, report creators publish their Power BI Desktop file (.pbix) or Power BI project file (.pbip) to the Power BI service. |
 |
Reports are published to a workspace dedicated to storing and securing reports and dashboards. |
 |
Published reports remain connected to the shared semantic model that's stored in a different workspace. Any changes made to the shared semantic model affect all dependent reports. |
 |
Third-party tools can use the XMLA endpoint to query the shared semantic model. Other XMLA-compliant tools—such as DAX Studio, Semantic Link from Fabric notebooks, or Windows PowerShell—can be used to query or update the shared semantic model. Power BI Desktop, Excel, and Report Builder can also connect by using the XMLA endpoint (not depicted in the scenario diagram). |
 |
Other Microsoft and third-party tools can use the XMLA endpoint to manage the semantic model and provide application lifecycle management. To learn more, see XMLA endpoint-based client tools. |
 |
Fabric administrators manage the tenant setting to enable the use of the XMLA endpoint. The administrator must enable the XMLA endpoint for Fabric capacities, Premium capacities, and Premium Per User settings. |
 |
Fabric administrators oversee and monitor activity in the Fabric portal. |
Key points
The following are some key points to emphasize about the advanced data model management scenario.
Third-party applications and tools
Enterprise BI teams commonly use client tools, such as Tabular Editor (depicted in the scenario diagram and described in the next topic), to help them manage centralized semantic models. However, any semantic model creator that wants to work with advanced modeling capabilities can take advantage of the methods described in this usage scenario.
There are several ways to use third-party applications:
- Connect to a remote data model by using the XMLA endpoint: Some third-party tools can connect directly to a remote data model in the Power BI service (or Analysis Services). Once connected to the XMLA endpoint, all Tabular Object Model (TOM) operations are supported. This approach is the primary focus of this usage scenario.
- Connect to a local data model in Power BI Desktop: Some third-party tools can connect to a local data model that's open in Power BI Desktop (not depicted in the scenario diagram). However, there are some limitations, and not all external tool functionality is officially supported.
- Connect to a template file in Power BI Desktop: Some third-party tools distribute their functionality in a lightweight way by using a Power BI Desktop template file (.pbit) (not depicted in the scenario diagram).
Tabular Editor
Tabular Editor is depicted in the scenario diagram. It's a third-party tool that's achieved widespread adoption by the Power BI community. Some advantages of managing tabular data models with Tabular Editor include:
- Setting up data model capabilities not supported in Power BI Desktop: Tabular Editor provides an interface to set up object-level security (OLS), calculation groups, perspectives, translations, and partitions.
- Support for concurrent model development: Microsoft data model development tools, such as Visual Studio with Analysis Services projects, store the entire data model definition in a Model.bim file. This single file can make it difficult for a team of developers to work together on a single data model. Tabular Editor has a feature called Folder serialization. Folder serialization deconstructs the Model.bim file into separate object-specific files within an organized folder structure. Different data modelers can then work on different files with less risk of overwriting each other's efforts.
- Integration with source control: Folder serialization allows source control system to easily detect data model changes, making source merges and conflict resolution easier to do.
- Improved data model quality and design: Tabular Editor integrates with Best Practices Analyzer (BPA). BPA helps data modelers with a set of customizable rules that can improve the quality, consistency, and performance of data models. You can download a set of best practice rules (provided by Microsoft) from GitHub.
- Increased productivity when developing data models: The Tabular Editor interface makes it well-suited for performing batch edits, debugging, and viewing data model dependencies. Tabular Editor is different from Power BI Desktop in that it works in disconnected mode. You can make data model changes in disconnected mode and commit them as a batch of edits. Working this way allows for faster development and validation, especially for experienced data modelers. It's also possible to create C# scripts and save them as macros. These scripts can help you to improve the efficiency of managing and synchronizing multiple data models.
XMLA endpoint
The XMLA endpoint uses the XMLA protocol to expose all features of a tabular data model, including some data modeling operations that aren't supported by Power BI Desktop. You can use the TOM API to make programmatic changes to a data model.
The XMLA endpoint also provides connectivity. You can only connect to a semantic model when the workspace that has its license mode set to Premium per user, Premium per capacity, or Embedded. Once a connection is made, an XMLA-compliant tool can operate on the data model in two ways:
- Write data and metadata: Read/write use of the XMLA endpoint allows for:
- Data modeling capabilities that aren't supported by Power BI Desktop, like object-level security (OLS), calculation groups, perspectives, translations, and partition management.
- More complex deployments. For example, a partial deployment or a metadata-only deployment that publishes only a single new measure.
- Asynchronous semantic model refresh. For example, refreshing a single table or partition.
- Read data and metadata: Read-only use of the XMLA endpoint allows for:
- Monitoring, debugging, and tracing semantic models and queries.
- Allowing third-party data reporting tools to visualize data sourced from a shared semantic model. This technique is a great way to extend the benefits and investments in managed self-service BI.
Warning
Once you modify or publish a semantic model by using the XMLA endpoint, you can no longer download it from the Power BI service as a Power BI Desktop file.
XMLA settings per capacity
Each Power BI Premium capacity and Power BI Embedded capacity has a setting to control whether the XMLA endpoint is read-only, read/write, or off. This setting is also available for all Premium Per User workspaces in the Power BI tenant. Read/write XMLA access must be enabled for each capacity that contains semantic models that you want to manage with a tool other than Power BI Desktop.
Tip
The XMLA endpoint setting (read/write, read-only, or off) applies to all workspaces and semantic models assigned to a particular capacity. You can set up multiple capacities to decentralize and/or customize how content is managed for each capacity.
XMLA tenant setting
In addition to the XMLA endpoint settings, a Power BI administrator must use the tenant settings to allow XMLA endpoints and Analyze in Excel with on-premises semantic models. When enabled, you can allow all users, or specific security groups, to use XMLA endpoint functionality.
Note
All standard security and data protection features still apply to specify which users can view and/or edit content.
Third-party tools
Power BI Desktop can handle the end-to-end needs for most self-service content creators. However, third-party tools offer other enterprise features and functionality. For this reason, third-party tools, such as Tabular Editor, have become prevalent in the Power BI community, especially for advanced content creators, developers, and IT professionals.
Tip
This blog post describes how third-party tools allow the Power BI product team to reevaluate their development priorities, increase the reach of the Power BI platform, and satisfy more advanced and diverse requests from the user community.
Note
Some third-party tools require a paid license, such as Tabular Editor 3. Other community tools are free and open source (such as Tabular Editor 2, DAX Studio, and ALM Toolkit). We recommend that you carefully evaluate the features of each tool, cost, and support model so you can adequately support your community of content creators.
Data model management
The primary focus of this usage scenario is on the content creator who uses Tabular Editor to manage a data model. For infrequent advanced data model management requirements, like occasional partition management, you might choose to use a tool such as SQL Server Management Studio (SSMS). It's also possible for a .NET developer to create and manage Power BI semantic models by using the TOM API.
Tip
When using the XMLA endpoint for data model management, we recommend that you enable the large semantic model storage format setting. When enabled, the large semantic model storage format can improve XMLA write operation performance.
Separation of data model and reports
For this usage scenario to be successful, you should separate reports from the data model. This approach results in managing separate Power BI Desktop files as described in the managed self-service BI usage scenario. Even if the same person is responsible for all development, the separation of semantic models and reports is important because Tabular Editor doesn't have an awareness of report content.
Gateway setup
Typically, a data gateway is required when accessing data sources that reside within the private organizational network or a virtual network. The On-premises data gateway becomes relevant once a data model is published to the Power BI service. The two purposes of a gateway are to refresh imported data, or view a report that queries a live connection or DirectQuery semantic model (not depicted in the scenario diagram).
Note
A centralized data gateway in standard mode is strongly recommended over gateways in personal mode. In standard mode, the data gateway supports live connection and DirectQuery operations (in addition to scheduled data refresh operations).
For more information, see On-premises data gateway (standard mode).
System oversight
The activity log records user activities that occur in the Power BI service. Power BI administrators can use the activity log data that's collected to perform auditing to help them understand activities that connect through XMLA endpoints.
Related content
For other useful scenarios to help you with Power BI implementation decisions, see the Power BI usage scenarios article.