Replicate Multiple Power BI XMLA Accounts



Replicate multiple Power BI XMLA accounts to one or many databases.

CData Sync for Power BI XMLA is a stand-alone application that provides solutions for a variety of replication scenarios such as replicating sandbox and production instances into your database. Both Sync for Windows and Sync for Java include a command-line interface (CLI) that makes it easy to manage multiple Power BI XMLA connections. In this article we show how to use the CLI to replicate multiple Power BI XMLA accounts.

Configure Power BI XMLA Connections

You can save connection and email notification settings in an XML configuration file. To replicate multiple Power BI XMLA accounts, use multiple configuration files. Below is an example configuration to replicate Power BI XMLA to SQLite:

Windows

<?xml version="1.0" encoding="UTF-8" ?> <CDataSync> <DatabaseType>SQLite</DatabaseType> <DatabaseProvider>System.Data.SQLite</DatabaseProvider> <ConnectionString>AuthScheme=AzureAD</ConnectionString> <ReplicateAll>False</ReplicateAll> <NotificationUserName></NotificationUserName> <DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString> <TaskSchedulerStartTime>09:51</TaskSchedulerStartTime> <TaskSchedulerInterval>Never</TaskSchedulerInterval> </CDataSync>

Java

<?xml version="1.0" encoding="UTF-8" ?> <CDataSync> <DatabaseType>SQLite</DatabaseType> <DatabaseProvider>org.sqlite.JDBC</DatabaseProvider> <ConnectionString>AuthScheme=AzureAD</ConnectionString> <ReplicateAll>False</ReplicateAll> <NotificationUserName></NotificationUserName> <DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString> </CDataSync>

By default, use Azure AD to connect to Microsoft Power BI XMLA. Azure AD is Microsoft’s multi-tenant, cloud-based directory and identity management service. It is user-based authentication that requires that you set AuthScheme to AzureAD.

For more information on other authentication schemes, refer to the Help documentation.

Configure Queries for Each Power BI XMLA Instance

Sync enables you to control replication with standard SQL. The REPLICATE statement is a high-level command that caches and maintains a table in your database. You can define any SELECT query supported by the Power BI XMLA API. The statement below caches and incrementally updates a table of Power BI XMLA data:

REPLICATE Customer;

You can specify a file containing the replication queries you want to use to update a particular database. Separate replication statements with semicolons. The following options are useful if you are replicating multiple Power BI XMLA accounts into the same database:

You can use a different table prefix in the REPLICATE SELECT statement:

REPLICATE PROD_Customer SELECT * FROM Customer

Alternatively, you can use a different schema:

REPLICATE PROD.Customer SELECT * FROM Customer

Run Sync

After you have configured the connection strings and replication queries, you can run Sync with the following command-line options:

Windows

PowerBIXMLASync.exe -g MyProductionPowerBIXMLAConfig.xml -f MyProductionPowerBIXMLASync.sql

Java

java -Xbootclasspath/p:c:\sqlitejdbc.jar -jar PowerBIXMLASync.jar -g MyProductionPowerBIXMLAConfig.xml -f MyProductionPowerBIXMLASync.sql

Ready to get started?

Learn more or sign up for a free trial:

CData Sync