* This blog was last tested on Taleo Build: 2020.PRD.4.0.80 Version: 20D.0.7 + Oracle Analytics Cloud Data Sync (2.6.1) + ADW 19c by Jay Pearson (Oracle A-Team) *
This article will outline a method for extracting data from Taleo Cloud Service, and automatically loading that data into Autonomous Data Warehouse (ADW). Two tools will be used: (a) Taleo Connect Client, and (b) Oracle Analytics Cloud Data Sync. The Taleo Connect Client will be configured to extract data in CSV format from Taleo, and save that in a local directory. Data Sync will monitor that local directory, and once the file is available, it will load the data into ADW using an incremental load strategy. This process can be scheduled to run, or run on-demand.
This article will be broken into 3 sections:
1. Set-up and configuration of the Taleo Connect Client.
2. Set-up and configuration of the Data Sync Tool.
3. The scheduling and configuration required so that the process can be run automatically and seamlessly.
The Taleo Connect Tool communicates with the Taleo backend via web services and provides an easy to use interface for creating data exports and loads.
See TCC: Downloading the Taleo Connect Client Installation Files (TCC and PIP) (Doc ID 1517668.1) for more details.
* To get the most recent TCC client application, choose 19D DLP: Oracle Taleo Platform Cloud Service - Connect 19D ( Oracle Taleo Platform Cloud Service - Connect )
* To get the most recent PIP for use with TCC, choose 19A DLP: Oracle Taleo Platform Cloud Service - Connect 19.A ( Oracle Taleo Platform Cloud Service - Connect )
Taleo Connect tool can be downloaded from Oracle Software Delivery Cloud.
a. Search for 'Oracle Taleo Platform Cloud Service - Connect', and then select the Platform. The tool is available for Microsoft Windows and Linux.
b. View the cart -> click continue -> select OS -> download.
c. Double click on downloaded .exe file -> to run Oracle Download Manager.
d. Extract / Unzup the downloaded Zip file, then first install 19A exe, which contains the Data Model Product Integration Pack (PIP)
Then install 19D 'Taleo Connect Client Application Installer' exe.
e. If specific Encryption is required, enter that in the Encryption Key Server Configuration screen, or select 'Next' to use default encryption.
f. When prompted for the Product Packs directory, select the 'Taleo Connect Client Application Data Model' folder that was downloaded and unzipped in the previous step, and then select the path for the application to be installed into.
a. Run the Taleo Connect Client. By default in windows, it is installed in the "C:\Taleo Connect Client" directory. The first time the tool is run, a connection needs to be defined. Subsequent times this connection will be used by default.
b. Enter details of the Taleo environment and credentials. Important - the user must have the 'Integration Role' to be able to use the Connect Client.
c. Select the Product and correct version for the Taleo environment. In this example 'Recruiting 14A'.
Note: When this was re-tested in May 2021 - Recruiting 17.4 was specified.
d. Select 'Ping' to confirm the connection details are correct.
Exporting data with Taleo Connect tool requires an export definition as well as an export configuration. These are saved as XML files, and can then be run from a command line to execute the extract.
This article will walk through very specific instructions for this use case. More details on the Connect Client can be found in this article.
1. Create The Export Definition
a. Under the 'File' menu, select 'New Export Wizard'
b. Select the Product and Model, and then the object that you wish to export. In this case 'Department' is selected.
c. To select the fields to be included in the extract, chose the 'Projections' workspace tab, as shown below, and then drag the fields from the Entity Structure into that space. In this example the whole 'Department' tree is dragged into the Projections section, which brings all the fields in automatically.
d. There are options to Filter and Sort the data, as well as Advanced Options, which include using sub-queries, grouping, joining, and more advanced filtering. For more information on these, see the Taleo Product Documentation. In the case of a large transaction table, it may be worth considering building a filter that only extracts the last X period of data, using the LastModifiedDate field, to limit the size of the file created and processed each time. In this example, the Dataset is small, so a full extract will be run each time.
e. Check the 'CSV header present' option. This adds the column names as the first row of the file, which makes it easier to set up the source in the Data Sync tool.
f. Once complete, save the Export Definition with the disk icon, or under the 'File' menu.
2. Create The Export Configuration
a. Create the Export Configuration, by selecting 'File' and the 'New Configuration Wizard'.
b. Base the export specification on the Export Definition created in the last step.
c. Select the Default Endpoint, and then 'Finish'.
d. By default the name of the Response, or output file, is generated using an identifier, with the Identity name - in this case Department - and a timestamp. While the Data Sync tool can handle this type of file name with a wildcard, in this example the 'Pre-defined value' is selected so that the export creates the same file each time - called 'Department.csv'.
e. Save the Export Configuration. This needs to be done before the schedule and command line syntax can be generated.
f. To generate the operating system dependent syntax to run the extract from a command line, check the 'Enable Schedule Monitoring' on the General tab, then 'Click here to configure schedule'.
g. Select the operating system, and interval, and then 'Build Command Line'.
h. The resulting code can be Copied to the clipboard. Save this. It will be used in the final section of the article to configure the command line used by the scheduler to run the Taleo extract process.
i. Manually execute the job by selecting the 'gear' icon
j. Follow the status in the monitoring window to the right hand side of the screen.
In this example, the Department.csv file was created in 26 seconds. This will be used in the next step with the Data Sync tool.
The Data Sync Tool can be downloaded from OTN through this link.
For more information on installing and configuring Data Sync, see this post written by Richard Williams in 2015. Use this to configure the Data Sync tool, and to set up the TARGET connection to ADW where the Taleo data will be loaded.
a. Under "Project" -> "New" -> "Add new source object" -> "File source" -> "Choose a file" -> Browse for 'Department.csv' file created by the Taleo Connect tool.
b. Under 'Import Options', manually enter the following string for the Timestamp format.
This is the format that the Taleo Extract uses, and this needs to be defined within the Data Sync tool so that the CSV file can be parsed correctly.
c. Enter the name of the Target table in ADW. In this example, a new table called 'TALEO_DEPARTMENT' will be created.
d. The Data Sync tool samples the data and makes a determination of the correct file format for each column. Confirm these are correct and change if necessary.
e. If a new table is being created in ADW as part of this process, it is often a better idea to let the Data Sync tool create that table so it has the permissions it requires to load data and create any necessary indexes. Under 'Project' / 'Target Tables' right click on the Target table name, and select 'Drop/Create/Alter Tables'
f. In the resulting screen, select 'Create New' and hit OK. The Data Sync tool will connect to the ADW Target environment and execute the SQL required to create the TALEO_DEPARTMENT target table
g. If an incremental load strategy is required, select the 'Update table' option as shown below
h. Select the unique key on the table - in this case 'Number'
i. Select the 'LastModifiedDate' for the 'Filters' section. Data Sync will use this to identify which records have changed since the last load.
In this example, the Data Sync tool suggests a new Index on the target table in ADW. Click 'OK' to let it generate that on the Target ADW database.
Under 'Jobs', select 'New' and name the job. Make a note of the Job name, as this will be used later in the scheduling and automation of this process
a. Execute the newly created Job by selecting the 'Run Job' button
b. Monitor the progress under the 'Current Jobs' tab.
c. Once the job completes, go the the 'History' tab, select the job, and then in the bottom section of the screen select the 'Tasks' tab to confirm everything ran successfully. In this case the 'Status Description' confirms the job 'Successfully completed' and that 1164 rows were loading into ADW, with 0 Failed Rows. Investigate any errors and make changes before continuing.
As an overview of the process, a '.bat' file will be created and scheduled to run. This 'bat' file will execute the extract from Taleo, with that CSV file being saved to the local file system. The second step in the '.bat' file will create a 'touch file'. The Data Sync Tool will monitor for the 'touch file', and once found, will start the load process. As part of this, the 'touch file' will automatically be deleted by the Data Sync tool, so that the process is not started again until a new CSV file from Taleo is generated.
a. In a text editor, create a '.bat' file. In this case the file is called 'Taleo_Department.bat'.
b. Use the syntax generated in step '2 h' in the section where the 'Taleo Export Configuration' was created.
c. Use the 'call' command before this command. Failure to do this will result in the extract being completed, but the next command in the '.bat' file not being run.
d. Create the 'touch file' using an 'echo' command. In this example a file called 'DS_Department_Trigger.txt' file will be created.
e. Save the 'bat' file.
f. Configure the Data Sync tool to look for the Touch File created in step d, by editing the 'on_demand_job.xml', which can be found in the 'conf-shared' directory within the Data Sync main directory structure.
g. At the bottom of the file in the 'OnDemandMonitors' section, change the 'pollingIntervalInMinutes' to be an appropriate value. In this case Data Sync will be set to check for the Touch file every minute.
h. Add a line within the <OnDemandMonitors> section to define the Data Sync job that will be Executed once the Touch file is found, and the name and path of the Touch file to be monitored.
In this example, the syntax looks like this
<TriggerFile job="Taleo_Load" file="C:\Users\oracle\Documents\DS_Department_Trigger.txt"/>
The Data Sync tool can be configured to monitor for multiple touch files, each that would trigger a different job. A separate line item would be required for each.
h. The final step is to Schedule the '.bat' file to run at a suitable interval. Within Windows, the 'Task Scheduler' can be found beneath the 'Accessories' / 'System Tools' section under the 'All Programs' menu. In linux, use the 'crontab' command.
This article walked through the steps for configuring the Taleo Connect Client to download data from Taleo and save to a location to be automatically consumed by the Data Sync tool, and loaded to ADW.