PowerShell Scripting to Replicate SAP Ariba Procurement Data to MySQL

The CData Cmdlets for SAP Ariba Procurement offer live access to SAP Ariba Procurement data from within PowerShell. Using PowerShell scripts, you can easily automate regular tasks like data replication. This article will walk through using the CData Cmdlets for SAP Ariba Procurement and the CData Cmdlets for MySQL in PowerShell to replicate SAP Ariba Procurement data to a MySQL database.

After obtaining the needed connection properties, accessing SAP Ariba Procurement data in PowerShell and preparing for replication consists of four basic steps.

In order to connect with SAP Ariba Procurement, set the following:

• ANID: Your Ariba Network ID.
• ANID: Specify which API you would like the provider to retrieve SAP Ariba data from. Select the Buyer or Supplier API based on your business role (possible values are PurchaseOrdersBuyerAPIV1 or PurchaseOrdersSupplierAPIV1).
• Environment: Indicate whether you are connecting to a test or production environment (possible values are TEST or PRODUCTION).

Authenticating with OAuth

After setting connection properties, you need to configure OAuth connectivity to authenticate.

• Set AuthScheme to OAuthClient.
• Register an application with the service to obtain the APIKey, OAuthClientId and OAuthClientSecret.

  For more information on creating an OAuth application, refer to the Help documentation.

Automatic OAuth

After setting the following, you are ready to connect:

APIKey: The Application key in your app settings. OAuthClientId: The OAuth Client Id in your app settings. OAuthClientSecret: The OAuth Secret in your app settings.

When you connect, the provider automatically completes the OAuth process:

1. The provider obtains an access token from SAP Ariba and uses it to request data.
2. The provider refreshes the access token automatically when it expires.
3. The OAuth values are saved in memory relative to the location specified in OAuthSettingsLocation.

Collecting SAP Ariba Procurement Data

1. Install the module:

   Install-Module SAPAribaProcurementCmdlets

2. Connect to SAP Ariba Procurement:

   $saparibaprocurement = Connect-SAPAribaProcurement -ANID $ANID -API $API -APIKey $APIKey -AuthScheme $AuthScheme

3. Retrieve the data from a specific resource:

   $data = Select-SAPAribaProcurement -Connection $saparibaprocurement -Table "Orders"

   You can also use the Invoke-SAPAribaProcurement cmdlet to execute pure SQL-92 statements:

   $data = Invoke-SAPAribaProcurement -Connection $saparibaprocurement -Query 'SELECT * FROM Orders WHERE OrderStatus = @OrderStatus' -Params @{'@OrderStatus'='CHANGED'}

4. Save a list of the column names from the returned data.

   $columns = ($data | Get-Member -MemberType NoteProperty | Select-Object -Property Name).Name

Inserting SAP Ariba Procurement Data into the MySQL Database

With the data and column names collected, you are ready to replicate the data into a MySQL database.

1. Install the module:

   Install-Module MySQLCmdlets

2. Connect to MySQL, using the server address and port of the MySQL server, valid user credentials, and a specific database with the table in which the data will be replicated:

   $mysql = Connect-MySQL -User $User -Password $Password -Database $Database -Server $Server -Port $Port

3. Loop through the SAP Ariba Procurement data, store the values, and use the Add-MySQL cmdlet to insert the data into the MySQL database, one row at a time. In this example, the table will need to have the same name as the SAP Ariba Procurement resource (Orders) and to exist in the database.

   $data | % { $row = $_ $values = @() $columns | % { $col = $_ $values += $row.$($col) } Add-MySQL -Connection $mysql -Table "Orders" -Columns $columns -Values $values }

You have now replicated your SAP Ariba Procurement data to a MySQL database. This gives you freedom to work with SAP Ariba Procurement data in the same way that you work with other MySQL tables, whether that is performing analytics, building reports, or other business functions.

Notes

• Once you have connected to SAP Ariba Procurement and MySQL in PowerShell, you can pipe command results to perform the replication in a single line:

  Select-SAPAribaProcurement -Connection $saparibaprocurement -Table "Orders" | % { $row = $_ $values = @() $columns | % { $col = $_ $values += $row.$($col) } Add-MySQL -Connection $mysql -Table "Orders" -Columns $columns -Values $values }

• If you wish to replicate the SAP Ariba Procurement data to another database using another PowerShell module, you will want to exclude the Columns, Connection, and Table columns from the data returned by the Select-SAPAribaProcurement cmdlet since those columns are used to help pipe data from one CData cmdlet to another:

  $columns = ($data | Get-Member -MemberType NoteProperty | Select-Object -Property Name).Name | ? {$_ -NotIn @('Columns','Connection','Table')}