Loading and reading Prism Analytics tables from a Workday orchestration

How an orchestration loads and reads Prism tables

An orchestration works with Prism through the Prism Analytics REST API. Workday's Data Cloud page says integration apps load and refresh tables through that API or the Send Prism Request component, and Extend apps read a published table or dataset with WQL or the same API. For loading, the pattern Workday recommends in v3 for externally built files is: create a file container, upload gzip-compressed delimited files to it, then run an existing data change task against the container and check the resulting activity.

Which Prism API version and loading method to use

Workday publishes three versions of the Prism Analytics REST API and recommends version 3. Versions 1 and 2 work with wBuckets, and Workday does not recommend them. Version 3 offers three ways to change a table's data:

For an orchestration that has built the rows itself, from a WQL query, an external API or a file, the file container route fits: the orchestration produces a file, and an admin-maintained data change task decides how it lands (Insert, Update, Upsert, Delete, or Truncate and Insert). The base URL is https://{hostname}/api/prismAnalytics/v3/{tenantname}. Inside an orchestration you call it with a relative path such as prismAnalytics/v3/fileContainers, as Workday's Prism and Extend Design Patterns sample does.

What has to exist in the tenant first

The REST API runs a data change task. It does not create one. Workday states that both the table and the data change task must already exist in the Data Catalog, and that integration system users cannot create a table. Build these by hand once, in the target tenant:

1. The Prism table, with the fields your file will carry.
2. A data change task whose source type is file upload, whose target is that table, and whose operation is the one you want (the Workday sample uses Upsert keyed on transactionId; Upsert needs a table field configured as the external ID).
3. A Data Change Task API Name you can look up from the orchestration. The sample looks it up with GET prismAnalytics/v3/dataChanges?name=prismCommissionUpsert&type=summary and fails with a clear message if nothing comes back.

The account the orchestration runs as needs the Prism: Manage File Containers domain to upload files. It also needs one of: the Prism: Tables Owner Manage or Prism: Tables Manage domain, or a table-level permission such as Table Editor, Can Insert Table Data or Can Update Table Data, matching the operation the task performs.

The load, step by step in Orchestration Builder

1. Build the file. Accumulate rows into CSV, for example in a Loop's Aggregate, and compress it with the gzip() data member function. Workday requires gzip-compressed, RFC 4180-compliant delimited files with each record ended by CR, LF or CRLF.
2. Find the data change task. Use GET prismAnalytics/v3/dataChanges with the name query parameter and keep $.data[0].id.
3. Create a file container. POST prismAnalytics/v3/fileContainers returns the container's id. Containers have no name, only that ID.
4. Upload the file. POST prismAnalytics/v3/fileContainers/{id}/files with a multipart body. In the Workday sample, the single part is named file, its data is data.Loop.csv.gzip(), its content type is application/gzip and its filename disposition parameter ends in .csv.gz. Each request uploads one file, and filenames must be unique within the container.
5. Run the task. POST prismAnalytics/v3/dataChanges/{dataChangeId}/activities with this body:

{
  "fileContainerWid": "{file container id}"
}

Workday returns the ID of the data change activity it started.

1. Wait for the activity. Read GET prismAnalytics/v3/dataChanges/{dataChangeId}/activities/{activityId} until the activity's state.descriptor is Success or Failed. Workday's sample uses a separate check-status orchestration that does exactly this read.

You can make these calls with Send Workday API Request, as the sample does; its loading orchestration is synchronous, and Send Prism Request is not offered in that template. Send Prism Request is the component Workday describes for long-running Prism requests, during which Orchestrate pauses processing while it waits for the result. It is available in the Asynchronous, Business Process and Workday Integration System templates. Workday's reference does not list which Prism endpoints count as long-running, so read the activity state either way. A run that returned from the POST has started the load; it has not proved the load.

Rules that break orchestrations that loop

Several documented file container rules matter as soon as an orchestration runs more than once or loads more than one file:

• One container per activity. A file container can be used only once. Create a new container for every data change activity.
• One running activity per table. You can create several data change tasks for a table, but only one data change activity for a target table can run at a time. The Administrator Guide's data change task page allows one exception: tasks using the Insert operation can run concurrently into the same table when their source is not a dataset or table. For anything else, an orchestration that fires activities in parallel, or a schedule that overlaps itself, will collide. Workday's Prism and Extend Design Patterns sample describes itself as a single-threaded pattern for triggering data change tasks.
• Order is run order. Workday loads data in the order you run activities, not the order you created the containers.
• Same schema per container. Every file in one container must have the same schema.
• Containers expire. Workday deletes file containers older than 8 days, so do not create one in one run and fill it in a later run.
• Throttling. At most 10 concurrent uploads, by any method. Workday says to retry an upload that returns 429 Too Many Requests.

Limits on the file itself

From Workday's external data limits and source file requirements:

The format rules are stricter than the size limits suggest. Workday does not trim spaces around delimiters, so Tom Smith, Marge Smith stores a leading space in the second value. Numeric values may contain only digits and a period as the decimal point; a thousands separator or currency sign means the value is not read as a number. Dates must match a supported date format to be read as dates. When the orchestration writes numbers or dates into the CSV, format them explicitly so the values match what the table's fields expect.

Reading Prism data back into an orchestration

To read Prism data, publish the table or dataset as a Prism data source and query it like any other data source with WQL through Send Workday API Request to wql/v1/data. The sample app reads its commission table this way, with the Prism data source's API name in the FROM clause, for example SELECT agentId, type FROM cds_sales_Commission_TBL GROUP BY agentId, type. Prism data sources have their own security: security group access decides who can see them. An orchestration's account needs that access in addition to WQL access. The ISU side of this is covered in running WQL in Orchestrate as an ISU.

Two documented properties of Prism data sources affect what you read back. A Prism data source can become inactive, and an inactive one exists but is empty and cannot be queried. Instance and multi-instance fields in a Prism data source hold only the WID, not the display name. If a reading orchestration suddenly returns nothing, check whether the data source is still active before you debug the query. For when to keep data in Prism and when to query live, see Prism Analytics vs WQL.

How to prove a load worked

Check three things, in this order. First, the file upload response shows the file state as Success; Workday's other file states are Failed, Uploading and Timed Out. Second, the data change activity's state reaches Success. Third, the table holds the rows: open the Activities tab of the View Table report, the Data Change Activities tab of the Data Catalog, or the Prism Management Console, and compare the row counts with the number of rows the orchestration wrote. If the activity failed, the v3 API also lets you download the error file for the data change activity, so read that file before changing the orchestration.

Write the activity ID and its final state onto whatever record started the run. Then a person looking at that record can tell a load that finished from one that never started.