Connect to Workday Data in DigitalSuite Studio through RunMyProcess DSEC

The CData JDBC Driver for Workday implements JDBC Standards and enables a applications ranging from BI to IDE to connect with Workday. In this article, we describe how to connect to Workday data from Arkobi Digital RunMyProcess's DSEC and connect to Workday in RunMyProcess.

Getting Started

---

Setting up EnterpriseConnect Agent

Configure the EnterpriseConnect Agent following the EnterpriseConnect page in the RunMyProcess documentation.

Setting up JDBC Adapter

The JDBC Adapter section describes the steps to connect to RDBMS through JDBC. Follow the steps and open the JDBC.config file.

1. Create a JSON entry for the CData JDBC Driver for Workday, e.g.

   Workday = {
    "sqlDriver" : "...",
    "sqlSource" : "...",
    "sqlDriverPath" : "..."
   }
   

2. Set the "sqlDriver" field to the Class name for the CData JDBC Driver, e.g.
   cdata.jdbc.workday.WorkdayDriver
3. Set the "sqlSource" field to a JDBC URL for connecting to Workday, e.g.
   jdbc:workday:User=myuser;Password=mypassword;Tenant=mycompany_gm1;BaseURL=https://wd3-impl-services1.workday.com;ConnectionType=WQL;InitiateOAuth=GETANDREFRESH;

   Built-in Connection String Designer

   For assistance in constructing the JDBC URL, use the connection string designer built into the Workday JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

   java -jar cdata.jdbc.workday.jar
   

   Fill in the connection properties and copy the connection string to the clipboard.

   To connect to Workday, users need to find the Tenant and BaseURL and then select their API type.

   Obtaining the BaseURL and Tenant

   To obtain the BaseURL and Tenant properties, log into Workday and search for "View API Clients." On this screen, you'll find the Workday REST API Endpoint, a URL that includes both the BaseURL and Tenant.

   The format of the REST API Endpoint is: https://domain.com/subdirectories/mycompany, where:

   • https://domain.com/subdirectories/ is the BaseURL.
   • mycompany (the portion of the url after the very last slash) is the Tenant.
   For example, in the REST API endpoint https://wd3-impl-services1.workday.com/ccx/api/v1/mycompany, the BaseURL is https://wd3-impl-services1.workday.com and the Tenant is mycompany.

   Using ConnectionType to Select the API

   The value you use for the ConnectionType property determines which Workday API you use. See our Community Article for more information on Workday connectivity options and best practices.

   API	ConnectionType Value
   WQL	WQL
   Reports as a Service	Reports
   REST	REST
   SOAP	SOAP

   
   

   Authentication

   Your method of authentication depends on which API you are using.

   • WQL, Reports as a Service, REST: Use OAuth authentication.
   • SOAP: Use Basic or OAuth authentication.

   See the Help documentation for more information on configuring OAuth with Workday.

   👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)
   
4. Set the "sqlDriverPath" field to the name of the CData JDBC Driver JAR file, e.g.
   cdata.jdbc.workday.jar

Sample JDBC.config File

#DBAgent Configuration
Workday = {"sqlDriver" : "cdata.jdbc.workday.WorkdayDriver", "sqlSource" = "jdbc:workday:User=myuser;Password=mypassword;Tenant=mycompany_gm1;BaseURL=https://wd3-impl-services1.workday.com;ConnectionType=WQL;InitiateOAuth=GETANDREFRESH;","sqlDriverPath" = "cdata.jdbc.workday.jar" }

Put the JDBC driver JAR file (cdata.jdbc.workday.jar) into the same directory as unified-adapter-[version].jar.

👁 runmyprocess

Note: Make sure to put the CData license file (cdata.jdbc.workday.lic) into the same directory. Since the license is generated based on the unique identifier of the machine where the product in installed, you will need an offline activation if you want to put the file on another machine.

Starting DigitalSuite EnterpriseConnect Agent

In Windows, start RunMyProcess DigitalSuite EnterpriseConnect Agent in Windows services. To start the application through command line, see Starting the EnterpriseConnect Agent in the RunMyProcess documents.

Starting the JDBC Adapter

Start the JDBC Adapter from runAdapter.bat. Once the Adapter is running, you can access the application through the agent address (e.g. 127.0.0.1:8080). Below is an example executing the command in Windows.

... > java -Djava.util.logging.config.file=./log.properties -cp lib/* org.runmyprocess.sec2.AdapterHandler :
2021-06-09 14:37:58|INFO|correlationId=|Searching for config file...
2021-06-09 14:37:58|INFO|correlationId=|Adapter Handler started with [JDBC] configuration
2021-06-09 14:37:59|INFO|correlationId=|agent address: 127.0.0.1:8080
2021-06-09 14:38:00.251:INFO::ConnectionThread: Logging initialized @1820ms to org.eclipse.jetty.util.log.StdErrLog
2021-06-09 14:38:00|INFO|correlationId=|onConnect() websocket connection between Agent and Adapter established

Once the DigitalSuite EnterpriseConnect Agent and JDBC Adapter are running, access http://localhost:(specified-port-number)/ through your browser to open the page shown below.

👁 runmyprocess

Check the availability of the JDBC Adapter using tools such as Postman or cURL. Here, we use Postman to send the HTTP POST request.

Configure the RequestHeader as follows:

Content-Type application/json

Configure the RequestBody as follows:

{
	"protocol":"JDBC",
	"data":{
		"DBType":"Workday",
		"sqlUsername":"",
		"sqlPassword":"",
		"sqlStatement":"SELECT * FROM Workers"
	}
}

If the JDBC.config file contains credential information, sqlUsername and sqlPassword can be left empty. If you are not sure of the table name, you can retrieve the list of tables using the request SELECT * FROM sys_tables

The request is successful if the Status is 200 and the Body contains Workday data in JSON format.

👁 runmyprocess

Connect to Workday through DSEC Agent in DigitalSuite Studio

Create a DigitalSuite Studio project and then create a Provider in the project.

• URL: The URL for accessing JDBC Agent (e.g. http:localhost:8080/)
• Authentication Scheme: Login/password
• Login: The value from agent.user in the application.properties file
• Password: agent.password in the application.properties file
• Secured: Checked
• Use DigitalSuite EnterpriseConnect: Checked
• With domain: The value from agent.domain in application.properties file

👁 runmyprocess

Next, create a Connector in the Provider.

• Connector URL: Leave this empty
• Architecture: REST/XML-RPC
• Method: POST
• Result format: JSON
• Accept media type: application/json
• Character set: Automatic
• Content: Same as the Request body used in the JDBC Adapter
• Content type: application/json

👁 runmyprocess

The JSON data we used as the Request body in JDBC Adapter:

{
	"protocol":"JDBC",
	"data":{
		"DBType":"Workday",
		"sqlUsername":"",
		"sqlPassword":"",
		"sqlStatement":"SELECT * FROM Workers"
	}
}

Open Launch Test to perform the test. The test is successful if Workday data is shown in Result on the right pane.

👁 runmyprocess

Now you can use Workday data in RunMyProcess DigitalSuite Studio through DSEC.

For the detailed information on supported SQL commands, refer to the SQL Compliance section in our help documentation. For information on tables, refer to the Data Model section.