Automate SAP Business Warehouse Integration Tasks from PowerShell

The CData ADO.NET Provider for SAP Business Warehouse 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 SAP Business Warehouse.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for SAP Business Warehouse; this tutorial shows how to use the Provider to retrieve SAP Business Warehouse data.

Once you have acquired the necessary connection properties, accessing SAP Business Warehouse data in PowerShell can be enabled in three steps.

To connect to SAP Business Warehouse, set the URL property to a valid SAP Business Warehouse server base URL. The driver must connect to SAP Business Warehouse instances hosted over HTTP with XMLA access.

The driver supports the following authentication schemes via the AuthScheme property:

• None: Anonymous authentication, if available on the server.
• Basic: Set User and Password and set AuthScheme to Basic.
• Kerberos: See the Using Kerberos section of the help documentation for the required Kerberos properties.

By default, the driver attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats.

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for SAP Business Warehouse\lib\System.Data.CData.SAPBusinessWarehouse.dll")
       

2. Connect to SAP Business Warehouse:

    
   $conn= New-Object System.Data.CData.SAPBusinessWarehouse.SAPBusinessWarehouseConnection("URL=https://mysapserver:8000;AuthScheme=Basic;User=username;Password=password;")
   $conn.Open()
   

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

   $sql="SELECT CustomerCount, City from Sales"
   
   $da= New-Object System.Data.CData.SAPBusinessWarehouse.SAPBusinessWarehouseDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.customercount $_.city
   }