If you are a BI developer, you might be wondering if XMLA endpoints in Power BI support out-of-line (OOL) bindings, an advanced Analysis Services capability to temporarily change the data bindings of certain metadata objects in a dataset for the duration of a refresh command, such as data sources, partitions, and expressions. The good news is that you can now use OOL bindings in Power BI through XMLA endpoints, yet there are some restrictions. For example, you can override a table partition’s M expression, a partition’s query expression, or a named expression that might be shared across multiple tables or partitions, as explained later in this article, but you cannot directly override a data source definition when processing a partition or update the credentials or privacy settings for a data source that a Power BI dataset uses. For more details about data source dependencies, refer to the blog post Using XMLA endpoints to change data sources in a Power BI dataset.
One of the primary scenarios for OOL bindings is adding data to an existing table partition. The basic idea is to issue a refresh command of type “add”, also known as a ProcessAdd command, together with a custom M expression or source query that fetches a few new rows from an existing database table in order to add these new rows to the data that is already in the table partition, as in the following figure. In the depicted example, the new transactions are added based on an OrderDateKey of 20201231, but it could be any timestamp or other filter criteria, of course. This “ProcessAdd with OOL bindings” approach enables fine-grained incremental refresh scenarios without having to alter the dataset metadata. It can open the door for advanced refresh patterns that go beyond the incremental refresh capabilities that Power BI provides out of the box.
A very similar scenario is based on a refresh command of type “full”. The TMSL script is almost identical to the one depicted above. You just need to replace “type”: “add” with “type”: “full” and Power BI will process the referenced table partition thereby replacing the existing data with the data fetched through the custom M expression or source query. This could be useful if you wanted to validate calculated columns and DAX measures by using a specific known set of test data rows, again without having to alter the actual dataset in Power BI. You might consider this a form of A\B testing based on two data sources in the dataset, data sources A and B, and switching between them by using OOL bindings. Data source A might contain test data and data source B contains the production data, for example. Just process the dataset with OOL bindings, verify that all calculations produce correct results, and then process the dataset again but this time without OOL bindings so that Power BI pulls in the full production data, and the job is done.
Similar things can be achieved via two Provider data sources and switching data sources in partition override (we support this as well, for SDS, it is implicit since partition won’t reference to data source directly).
The above examples demonstrate how to override a partition’s M expression, but you can also specify a custom query expression or named expression, as mentioned earlier. The following screenshot shows a TMSL script to override a query expression. This is very similar to an M expression, except that the type is “query” and that the query itself is in the native dialect of the data source, such as SQL. You could call this a native query. Native queries expect an explicit data source definition in the dataset (as opposed to a data source referenced inline in an M expression). The dataSource property of the query expression references the name of this data source definition. As mentioned in the beginning of this article, Power BI does not support directly overriding a data source definition, so the data source definition must already exist in the dataset metadata for this OOL binding to work.
A TMSL script to override a named expression that might be shared across multiple tables or partitions is equally uncomplicated, as in the screenshot below. This time, the OOL binding does not reference an individual partition, yet it still impacts the partition being processed because the partition uses the named expression as part of its M expression. In this concrete example, the named expression defines an M parameter called dbName which the table partition’s M expression uses to specify the database name. Essentially, this OOL binding temporarily points the partition’s M expression to a different database.
When trying to switch to a different database via OOL bindings, be aware of the data source dependencies covered in the blog post Using XMLA endpoints to change data sources in a Power BI dataset. Note also that OOL bindings add another layer of complexity because they exist only during the requested refreshing operation. The changed bindings are not persisted in the dataset, which implies that you cannot provide missing credentials or privacy settings after the fact. These dataset settings must already exist in Power BI before you issue a refresh command with OOL bindings so that Power BI can auto-bind to the given source. For cloud data sources, this typically means that you must already be the owner of another dataset that connects to the same source and have configured the credentials and privacy settings.
In summary, OOL bindings in Power BI support changing a partition’s M expression to another M expression that references the same or different data source provided the data source settings exist in Power BI. It is not supported to change a partition’s M expression to a query expression. You can override a query expression, however, by using another query expression, but the referenced data source must already exist in the dataset. You cannot provide the data source definition in an OOL binding together with the custom query expression. You can also override a named M expression with another M expression. But be aware that you might be changing a data source if the shared M expression defines a parameter that is used to specify a server name, database name, URL, or other connection parameter. Again, and as always, when switching to a different data source, the data source settings must already be available in Power BI so that the refresh command with OOL bindings can succeed.