Skip to main content

Using XMLA endpoints to change data sources in a Power BI dataset

Headshot of article author Kay Unkroth

If you’re an advanced Power BI developer, you are most likely already aware that you can work with Power BI datasets via XMLA endpoints as if they are Analysis Services data models. The XMLA APIs are almost fully compatible. This compatibility provides tremendous flexibility and broad support for common data modelling tools previously only supported with Azure Analysis Services and SQL Server Analysis Services. There are, however, some key differences regarding the management of data sources in Power BI. The purpose of this article is to provide Power BI developers with a deeper understanding of the dependencies when updating data sources in a Power BI dataset through XMLA endpoints.

Most importantly, Power BI maintains security-related data source information such as credentials and privacy settings outside the dataset, whereas Azure Analysis Services and SQL Server Analysis Services maintain these settings in the data model. This can be seen in Power BI Desktop when you open the File menu, click Options and settings, and then Data source settings, as shown in the following screenshot. When you edit the credentials or change the privacy settings for a data source, the updated settings will be used by all your Power BI Desktop (PBIX) files that use this data source when you refresh them on the current computer. It might seem that you’re updating these parameters in the context of a single PBIX file, but this isn’t really the case because the data source settings are separate from the PBIX file.

Figure 1: Editing a data source in Power BI Desktop

You can also verify this by opening one of your PBIX files on a second computer. Because the security-related data source settings aren’t part of the PBIX file, they don’t travel with it. Accordingly, Power BI Desktop will prompt you for credentials and privacy settings again when you try to connect to the data source on the second computer. When provided, all your PBIX files on the second computer can use these settings to connect to that data source without having to prompt you yet again. Power BI Desktop stores this security-sensitive information encrypted in your local user context and locates these settings for each data source based on its connection parameters, such as server name and database, or URL, or any other connector-specific connection parameters.

The same is also true when publishing a PBIX file to the Power BI service. The security-related data source settings are not published with the PBIX. You must provide the missing information on the dataset settings page in the Power BI UI if your dataset uses a cloud data source directly, as the following diagram illustrates. You don’t need to re-enter these settings again when uploading a second PBIX file that uses the same cloud data source. Power BI can automatically bind the second dataset to the data source definition that already exists in your user context as the dataset owner. If your dataset uses an on-premises data source instead, you must explicitly bind it to a predefined data source on a data gateway. That gateway’s data source definition then provides the security-related data source settings.

Figure 2: Data source definition and datasets in Power BI

Like Power BI Desktop, the dataset settings page (in the upper left area of the above diagram) might give you the impression that you are configuring data source settings for an individual dataset, but this isn’t the case. Just as in Power BI Desktop, when you edit the credentials or change the privacy settings for a cloud data source on a dataset’s settings page, you change these parameters for all your datasets that use this cloud data source. Note that the data source settings are not scoped by workspace but instead by dataset owner across all workspaces in a tenant. The Power Platform admin center provides a clearer picture (in the upper right area in the above diagram). If you look at your data sources on the Data page in the Power Platform admin center, you can see there is only one data source definition per data source and all your datasets that use this data source use these same settings.

The way Power BI handles the data source settings has a profound impact on a dataset’s metadata structure. Looking at the following screenshot depicting the relevant metadata sections of a dataset created in Power BI Desktop, you won’t be able to find any explicitly defined data sources. There’s no need for an explicit data source definition. Power BI parses the M expression on the table partition, recognizes the data sources, and attempts to locate the data source settings outside of the dataset, as described above. You can verify this by looking at the metadata of a dataset. To script out a dataset, use SQL Server Management Studio (SSMS) or one of the common Analysis Services modeling tools, or a small Tabular Object Model (TOM) code snippet as shown in the following screenshot.

Figure 3: Metadata of a dataset as generated in Power BI Desktop

Because the data source settings are not part of the Power BI dataset, you cannot retrieve the credentials or privacy settings by downloading a dataset and examining its metadata, or by using any other means. This also means you cannot provide security-related data source settings through metadata updates via XMLA endpoints. You can switch a table partition to a different data source by altering the partition’s M expression, but you cannot provide the credentials or privacy settings directly. For cloud data sources, Power BI might be able to auto-bind the dataset to an existing data source definition or you must provide the missing information on the dataset settings page. If you are switching to a different on-premises data source, you must bind the dataset to an appropriate gateway with the corresponding data source definition on the dataset settings page, or by using the Power BI REST API.

Of course, you can also use tools such as SSDT or Tabular Editor to create datasets in Power BI thanks to the flexibility of XMLA endpoints. These datasets might contain explicit data source definitions. You could also use a small TOM code snippet, as shown in the following screenshot, to add an explicit data source definition to a Power BI dataset and replace a partition’s M expression with a query partition source that uses this data source definition.

Figure 4: Metadata of a dataset as generated or modified using a third-party tool

Power BI supports the metadata arrangement shown above, but it has no impact on how Power BI manages the data source settings. In other words, Power BI ignores any data source settings that you might want to provide in the metadata. For example, you might be tempted to include a user ID and password in the connection string of the ProviderDataSource object listed in the above screenshot, but Power BI is not going to use these parameters. Power BI ignores this information and always looks for a matching data source definition in the Power BI service. You can’t connect if Power BI can’t locate the data source settings, as described earlier.

M parameters provide yet another option to switch a Power BI dataset to a different data source, which provides an efficient way to make these modifications without having to republish the dataset. For example, the M expression for the FactInternetSales table in the following screenshot doesn’t refer to the AdventureWorksDW database directly. Instead, it uses a static parameter named dbName, which is assigned the value AdventureWorksDW so that the M expression connects to the AdventureWorksDW database. Having published this dataset in the Power BI service, you can then change the parameter value on the dataset settings page or by using the Power BI REST API.—When changed, Power BI then uses the connection parameter to find the corresponding data source settings. You must also make sure there is a matching data source definition in the service or provide the credentials in the Power BI UI.

Figure 5: A parameterized data source definition in Power BI Desktop

As you might expect, the story doesn’t change if you use XMLA endpoints to modify such a static M parameter used to define the connection details for a data source. The code snippet shown in the screenshot below locates the M parameter called dbName in the shared Expressions node and replaces its M expression with a new one that changes the parameter value to Staging_AdventureWorksDW. Assuming you haven’t yet provided any settings for this data source in Power BI, Power BI prompts you on the dataset settings page to enter the missing credentials for this new cloud data source.

Figure 6: Modifying a data source parameter using the Power BI user interface or a custom tool

And that’s it! No matter which approach you choose to point a dataset to a different data source, either by modifying the M expression of a table partition, by using a query partition source that points to a different data source definition in the dataset, or by using a shared expression that defines the data source details or a source query, you always must make sure a matching data source definition with the required credentials and privacy settings exists in Power BI, or bind the dataset to a data gateway that has the required settings. If the data source settings don’t yet exist in Power BI, you can provide them after you’ve made the changes through XMLA endpoints by using the Power BI user interface or with Power BI REST APIs if you want to update data sources on a data gateway.