Concept: Data Export REST API

Administrator Guide

Overview

The Data Export API in the Prism REST service provides the ability to export data from table-backed Prism data sources at large scale.

Key features

Create a data export job to export data from a table-backed Prism data source.

Cancel a specific data export job. The data export job status must be Scheduled or Running.

Users in unconstrained security groups can view and cancel all data export jobs.

Users in a self-service security group can view and cancel only the data export jobs that they created.

Check the status of the data export job.

Scheduled: Workday has scheduled to run the data export job.

Processing: Workday is currently running the data export job.

Success: Workday has completed the data export job and created 1 or more output files containing the exported data.

Canceled: Workday stopped running the data export job at the request of a user.

Failed: Workday encountered an error when trying to run the data export job.

Download the output files containing the exported data.

You can only download the output files permitted by the security profile of the current user.

You can download files either sequentially or in parallel. You can shorten the time required to download all output files by downloading them in parallel.

Download performance depends on:

The number of files.

The number of parallel downloads.

The network bandwidth between the API client and the Workday server. Example: If the client is in a different geographical region than the server, the time to download the files will increase.

Use Cases

Use Case	Description
Disclosures and statutory reporting.	On a schedule that can range from daily to yearly, you need to extract high volumes of detailed financial data for specific periods from Workday. After exporting, you can submit the data to an enterprise data lake or regulatory reporting tool. The tool makes it easier for you to format and submit financial disclosures in order to comply with stringent regulations.
Advanced analytics, data science, and miscellaneous reporting.	You need to extract high volumes of detailed operational and financial data for specific periods from Workday. After exporting, you can submit the data to an enterprise lake or a data science workbench, where you can create predictive models for these subjects among others: Customers and users. Employees. Marketing research. Performance. Products or services.
Regulatory holds and archivals.	You must meet regulatory and compliance standards by archiving 5 to 7 years of financial data. You must make this data available to regulatory authorities and auditors immediately upon request, in accordance with the applicable regulations and industry.
Audit requests.	To conduct a thorough audit, you must request all transactions, activity, and metadata for certain balances over a specified time period. This data is required on a monthly, quarterly, and annual basis, as well as for previous years. You must export a large number of data into your audit database.

URL Base Path

Tenant Base Path

https://{hostname}/api/prismAnalytics/{version}/{tenantname}/dataExport

Example to create a data export job:

https://yourTenantHostName.com/api/prismAnalytics/v3/<TENANT_NAME>/dataExport

Workday Extend API Gateway Base Path

For Workday Extend apps, use the regional API Gateway base URL for your company. See Reference: Workday Extend API Gateways and Authorization Base URLs on the Developer Site.

The API Gateway base URL doesn’t include the tenant name.

Security Considerations

These domains in the Prism functional area:

Prism Data Export: Execute
: Controls who can create data export jobs.

Prism Data Export: Manage
: Controls who can view and cancel data export jobs.

Creating Data Export Job

The

POST /dataExport

endpoint facilitates the creation of a data export job.

Security Considerations:

Prism Data Export: Execute
domain in the Prism Analytics functional area.

Any of these security requirements for table you export from:

Prism: Tables Manage
domain in the Prism Analytics functional area.

Prism: Tables Owner Manage
domain in the Prism Analytics functional area.

Table Viewer
permission on the table.

Table Editor
permission on the table.

Table Owner
permission on the table.

Use this method to create a data export job for a specified Prism data source.

When you create a data export job, Workday generates 1 or more files containing data from the Prism data source that you can download to your local computer.

In the request body, supply a value for these parameters:

Body Parameter	Type	Description
input	Object	Include a WQL query that specifies every field to export from a Prism data source. Use this format: "input": { "query": " WQL_Query ", "type": "SQL" } When writing the WQL query: Use the WQL Alias of the Prism data source and each field. List every field you want to include. Optionally, you can rename a field using the AS operator. (Optional) You can filter the records using a WHERE clause. You can filter a Date field by comparing it to a different Date field. You can't filter a Date field by comparing it to a literal date value. You can export any field type except Multi-Instance fields. For details on how to specify a valid query in the input parameter, see Reference: WQL Query Usage and Guidelines for Data Export.
output	Object	Use this format: "output": { "type": "CSV_GZIP", “headers”: true }

Sample Request:

POST /dataExport

Sample Request body:

{
 "input": {
  "query": "SELECT agentCity, GET_DISPLAY_ID(billingCompany) AS
 billing_company, GET_DISPLAY_ID(billingCostCenter) AS billing_CostCenter
 FROM cds_insuranceClaimData WHERE claimAmount > 1000",
  "type": "SQL"
 },
 "output": {
  "type": "CSV_GZIP",
  "headers": true
 }
}

Sample Response

{
 "createdMoment": "2017-03-17T00:00:00.000Z",
 "status": "Scheduled",
 "input": {
  "query": "SELECT agentCity, GET_DISPLAY_ID(billingCompany) AS
 billing_company, GET_DISPLAY_ID(billingCostCenter) AS billing_CostCenter
 FROM cds_insuranceClaimData WHERE claimAmount > 1000",
  "type": "SQL"
 },
 "id": "b1bd0e1ac5d410001193bf9340050000"
}

Getting Data Export Job Status

The

GET /dataExport

endpoint facilitates the retrieval of all data export jobs.

The

GET /dataExport/{id}

facilitates the retrieval of 1 export job.

Security Considerations:

Prism Data Export: Manage
domain in the Prism Analytics functional area.

This endpoint returns the data export jobs that the current user has permission on. When retrieving a collection, use these optional query parameters:

Query Parameter	Description	Default	Max
type	The value for type determines which response fields to include. full: Returns all data export information. Summary: Returns a summarized response by excluding the output results list.	summary
limit	The limit of object data entries included in a single response.	20	1000
offset	The offset to the first object in a collection to include in the response.	0

Sample Request:

GET /dataExport

Sample Response:

The response is a collection of data export jobs, in JSON format.

This sample response displays only 1 data export job.

{
 "total": 7,
 "data": [
  {
   "createdMoment": "2023-08-03T22:47:10.929Z",
   "createdBy": {
    "id": "274555853a4446cf8809325243534f34",
    "descriptor": "BLiu / Betty Liu (manager 4300, CostCtrMgr
 30.3, 41200, PayIntPartner; PayPartner, PayAdmin)",
    "fullName": "BLiu / Betty Liu (manager 4300, CostCtrMgr
 30.3, 41200, PayIntPartner; PayPartner, PayAdmin)"
   },
   "status": "Success",
   "input": {
    "query": "SELECT insuranceOfficeState, sourceFileTag,
 sort1, sort2, agentCity, agentCountry, agentNote,
 GET_DISPLAY_ID(billingCompany) AS billing_company,
 GET_DISPLAY_ID(billingCostCenter) AS billing_CostCenter FROM
 cds_insuranceClaimData",
    "type": "SQL"
   },
   "output": {
    "noOfFiles": 4,
    "totalSizeInBytes": 5610214,
    "totalRows": 110408
   },
   "id": "b1bd0e1ac5d4100013ad1f50c6910000"
  },
  ...
 ]
}

Sample request to retrieve information about the data export job with the ID = b1bd0e1ac5d410001193bf9340050000:

GET /dataExport/b1bd0e1ac5d410001193bf9340050000

Sample Response:

{
 "createdMoment": "2023-08-03T22:08:42.928Z",
 "createdBy": {
  "id": "274555853a4446cf8809325243534f34",
  "descriptor": "BLiu / Betty Liu (manager 4300, CostCtrMgr 30.3,
 41200, PayIntPartner; PayPartner, PayAdmin)",
  "fullName": "BLiu / Betty Liu (manager 4300, CostCtrMgr 30.3,
 41200, PayIntPartner; PayPartner, PayAdmin)"
 },
 "status": "Success",
 "input": {
  "query": "SELECT agentCity, GET_DISPLAY_ID(billingCompany) AS
 billing_company, GET_DISPLAY_ID(billingCostCenter) AS billing_CostCenter
 FROM cds_insuranceClaimData WHERE claimAmount > 1000",
  "type": "SQL"
 },
 "output": {
  "createdTime": "2023-08-03T22:08:53.725Z",
  "expirationTime": "2023-08-10T22:08:53.725Z",
  "noOfFiles": 2,
  "totalSizeInBytes": 359680,
  "totalRows": 41301,
  "results": [
   {
    "name":
 "part-00000-70e49bea-487e-4a3e-b43e-be3935e951c1-c000.csv.gz",
    "length": 298913
   },
   {
    "name":
 "part-00001-70e49bea-487e-4a3e-b43e-be3935e951c1-c000.csv.gz",
    "length": 60767
   }
  ]
 },
 "id": "b1bd0e1ac5d410001193bf9340050000"
}

Downloading Output Files

The

GET /dataExport/{id}/results/{fileName}

endpoint facilitates the downloading of output files from a data export job.

Specify:

The ID of the data export job.

The name of the output file from the data export job.

The

GET /dataExport/{id}

endpoint provides the names of the output files.

You can only download the output files permitted by the security profile of the current user. You can download files either sequentially or in parallel.

Security Considerations:

Prism Data Export: Manage
domain in the Prism Analytics functional area.

Sample request to download the file named part-00000-70e49bea-487e-4a3e-b43e-be3935e951c1-c000.csv.gz:

GET /dataExport/b1bd0e1ac5d410001193bf9340050000/results/part-00000-70e49bea-487e-4a3e-b43e-be3935e951c1-c 000.csv.gz

Cancelling a Data Export Job

The

POST /dataExport/{id}/cancel

endpoint facilitates the cancelling of a specific data export job that is either scheduled or running.

You can only cancel data export jobs permitted by the security profile of the current user.

Security Considerations:

One of these domains in the Prism Analytics functional area:

Prism Data Export: Execute

Prism Data Export: Manage

Any of these security requirements for table you export from:

Prism: Tables Manage
domain in the Prism Analytics functional area.

Prism: Tables Owner Manage
domain in the Prism Analytics functional area.

Table Viewer permission on the table.

Table Editor permission on the table.

Table Owner permission on the table.

Sample Request:

You must include an empty JSON string {} in the request body for this method.

Sample request to cancel a data export job with the ID of b1bd0e1ac5d4100018d18abc4ea00000:

POST /dataExport/b1bd0e1ac5d4100018d18abc4ea00000/cancel

Sample Response:

The response contains the data export job including its current status of Canceled in JSON format.

{
 "createdMoment": "2023-08-04T00:21:24.914Z",
 "createdBy": {
  "id": "274555853a4446cf8809325243534f34",
  "descriptor": "BLiu / Betty Liu (manager 4300, CostCtrMgr 30.3,
 41200, PayIntPartner; PayPartner, PayAdmin)",
  "fullName": "BLiu / Betty Liu (manager 4300, CostCtrMgr 30.3,
 41200, PayIntPartner; PayPartner, PayAdmin)"
 },
 "status": "Canceled",
 "input": {
  "query": "SELECT agentCity, GET_DISPLAY_ID(billingCompany) AS
 billing_company, GET_DISPLAY_ID(billingCostCenter) AS billing_CostCenter
 FROM cds_insuranceClaimData",
  "type": "SQL"
 },
 "id": "b1bd0e1ac5d4100018d18abc4ea00000"
}

Limitations

Export Jobs are low priority jobs and will have lower precedence than other jobs like publish.

You can’t download the generated files after 7 days as the files will be deleted.

These maximums are set as guardrails to optimize system performance and reliability:

1 billion rows per export job.

1000 columns per query.

Concurrent download requests:

If the system limit is reached, you’ll receive a 503 - HIT_SERVER_LIMIT response.

If a tenant exceeds its specific limit, you’ll receive a 429 - HIT_TENANT_LIMIT response.

Concurrent export jobs:

Only 1 export job can run at a time per user or tenant.

Any additional export jobs will be automatically queued until the current job completes.

Common Errors

Validation Errors:

Malformed input json.

Malformed sql, invalid fields/tablename, unsupported functions.

Guardrails: Number of fields > 10000.

Security constraints not met.

Execution Errors

System errors.

Gaudrails: Fails if extraction had more than 1B rows.

Download APIs

When downloading, it is always recommended for the HTTP client to have retries due to unforeseen network issues or system issues. There is rate limiting applied on the number of concurrent connections created to a tenant and a server. You might occasionally see HTTP status codes

429

503

due to these enforced limits. It is recommended for the client to wait for sometime and try the request again.

Performance Considerations

Data extraction performance:

Data extraction execution time varies depending on the type of data and the number of rows and columns in the data.

The execution time increases with the volume of data.

Download performance:

The total download time for all file sizes decreases linearly with the number of processes that download the results.

Download performance may also be influenced by network bandwidth and tenant server location.

Steps: Set Up Tenant for the Prism Data Export REST API

Steps: Export Data from a Prism Data Source

Reference: WQL Query Usage and Guidelines for Data Export