PowerShell Scripting to Replicate SAP Ariba Source Data to MySQL

The CData Cmdlets for SAP Ariba Source offer live access to SAP Ariba Source 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 Source and the CData Cmdlets for MySQL in PowerShell to replicate SAP Ariba Source data to a MySQL database.

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

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

• API: Specify which API you would like the provider to retrieve SAP Ariba data from. Select the Supplier, Sourcing Project Management, or Contract API based on your business role (possible values are SupplierDataAPIWithPaginationV4, SourcingProjectManagementAPIV2, or ContractAPIV1).
• DataCenter: The data center where your account's data is hosted.
• Realm: The name of the site you want to access.
• Environment: Indicate whether you are connecting to a test or production environment (possible values are TEST or PRODUCTION).

If you are connecting to the Supplier Data API or the Contract API, additionally set the following:

• User: Id of the user on whose behalf API calls are invoked.
• PasswordAdapter: The password associated with the authenticating User.

If you're connecting to the Supplier API, set ProjectId to the Id of the sourcing project you want to retrieve data from.

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 Source Data

1. Install the module:

   Install-Module SAPAribaSourceCmdlets

2. Connect to SAP Ariba Source:

   $saparibasource = Connect-SAPAribaSource -API $API -APIKey $APIKey -Environment $Environment -Realm $Realm -AuthScheme $AuthScheme

3. Retrieve the data from a specific resource:

   $data = Select-SAPAribaSource -Connection $saparibasource -Table "Vendors"

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

   $data = Invoke-SAPAribaSource -Connection $saparibasource -Query 'SELECT * FROM Vendors WHERE Region = @Region' -Params @{'@Region'='USA'}

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 Source 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 Source 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 Source resource (Vendors) and to exist in the database.

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

You have now replicated your SAP Ariba Source data to a MySQL database. This gives you freedom to work with SAP Ariba Source 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 Source and MySQL in PowerShell, you can pipe command results to perform the replication in a single line:

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

• If you wish to replicate the SAP Ariba Source 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-SAPAribaSource 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')}