Automate Power BI XMLA Integration Tasks from PowerShell

The CData ADO.NET Provider for Power BI XMLA is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time access to Power BI XMLA.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Power BI XMLA; this tutorial shows how to use the Provider to retrieve Power BI XMLA data.

Once you have acquired the necessary connection properties, accessing Power BI XMLA data in PowerShell can be enabled in three steps.

By default, use Entra ID (formerly Azure AD) to connect to Microsoft Power BI XMLA. Entra ID (formerly 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 EntraID (formerly AzureAD).

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

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Power BI XMLA\lib\System.Data.CData.PowerBIXMLA.dll")
    

2. Connect to Power BI XMLA:

    
   $conn= New-Object System.Data.CData.PowerBIXMLA.PowerBIXMLAConnection("AuthScheme=EntraID;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

3. Instantiate the PowerBIXMLADataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Country, Education from Customer"
   
   $da= New-Object System.Data.CData.PowerBIXMLA.PowerBIXMLADataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.country $_.education
   }