Fetching data from Salesforce with TimeXtender Orchestration and Data Quality

Fetching data from a Salesforce report in TimeXtender Orchestration and Data Qualityis best handled by using a PowerShell Data Provider. In this article, we will query Salesforce and to use either in a Query or to transfer data to a SQL Server.

Creating the query

1. Create a Query and select the PowerShell Data Provider.
2. Paste in the following PowerShell script

   $reportPath = 'https://CUSTOMER.my.salesforce.com/services/data/v48.0/analytics/reports/XXXXXXXXXX?includeDetails=true'
   
   # Log in to get a token
   $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
   $headers.Add("Content-Type", "application/x-www-form-urlencoded")
   $headers.Add("Cookie", "BrowserId=XXXXXXXXXXXXXXXXXXXX")
   
   $body = "grant_type=password&client_id=CLIENTID&client_secret=CLIENTSECRET&username=USERNAME&password=PASSWORD"
   
   [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
   $token_response = Invoke-RestMethod 'https://login.salesforce.com/services/oauth2/token' -Method 'POST' -Headers $headers -Body $body
   
   # This is our token
   #$token_response.access_token
   
   $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
   $headers.Add("Authorization", "Bearer "+$token_response.access_token)
   $headers.Add("Cookie", "BrowserId=XXXXXXXXXXXXXXXXXXXXX")
   
   # Invoke our report
   $response = Invoke-RestMethod $reportPath -Method 'GET' -Headers $headers
   
   # Populate our result table
   $timeXtenderResult= New-Object system.Data.DataTable
   # Create the table structure foreach ($col in $response.reportMetadata.detailColumns) {
   
   $column_name = $col -replace "\.", "_"
   
   $col1 = New-Object system.Data.DataColumn $column_name,([string])
   $timeXtenderResult.columns.add($col1)
   
   }
   
   # Loop through each row and add to the results
   foreach ($row in $response.factMap."T!T".rows) {
   
   $dtrow = $timeXtenderResult.NewRow();
   $i=0
   foreach ($col in $response.reportMetadata.detailColumns) {
   $column_name = $col -replace "\.", "_"
   $dtrow[$column_name] = $row.dataCells[$i].label
   
   $i++
   
   }
   
   $timeXtenderResult.Rows.Add($dtrow)
   
   }
   
   $timeXtenderResult

3. Replace CUSTOMER, CLIENTSECRET, CLIENTID, USERNAME and PASSWORD with values from your installation.
4. Replace $reportPath with a full path to your report.
5. Set up the Query as normally and you should be able to fetch data from the report.

   

Tips and tricks

• For reusability, it can be helpful to move everything except the first line to a Query Snippet. Then the only difference between separate Queries is the report path.
• For safety, you can move the PASSWORD and CLIENTSECRET to an encrypted Global Parameter. Then they are not visible during development or in execution logs.
• Querying services can be slow when you need to run multiple queries against the same data set. In this case, it can be helpful to create a Data Transfer which moves the data to the TimeXtender Orchestration and Data Quality database, and then create queries that work on that data.