Reading the data

What an extract writes

Every extract keeps raw tables and publishes views over them. The tables are the record of what Workday said; the views are what people query.

Tables. IN_<PREFIX>_<TABLE>, one row per record or line. The record's header is flattened onto one root row; each line table is named by the same suffix the submit uses, so IN_JOURNAL_JOURNAL_ENTRY_LINE_DATA and OUT_JOURNAL_JOURNAL_ENTRY_LINE_DATA describe the same lines. References arrive as three columns, WID, ID and descriptor. Repeating attributes arrive as one JSON column each. Every row carries the run that wrote it, and line rows carry their position.

Views. Created with the tables when a business object is switched on and refreshed with every spec change:

• V_<PREFIX>_<TABLE> for the record and for each line table: current rows only, each reference collapsed to <NAME>_ID (the business ID), <NAME>_WID and <NAME>_DESCRIPTOR, and every line carrying the record's own reference beside its line number, so a line joins to its record without a key column of its own.
• V_<PREFIX>_<TABLE>_<ATTRIBUTE> for each JSON column: one row per element. A list of references such as worktags gives ID_TYPE, ID, WID and DESCRIPTOR per element, so a line's worktags are rows you filter by type. A list of objects such as attachments gives one column per field.

The views follow Workday's element names throughout. The patterns, collapsed references, bridge views for repeating references and lines keyed to their record, are the ones analysts know from relational models of Workday, so a report written against one business object reads the same against the next.

-- journal lines with their cost center worktag, current rows only
SELECT l.JOURNAL_ENTRY_ID, l.LINE, l.LEDGER_ACCOUNT_ID, l.DEBIT_AMOUNT, l.CREDIT_AMOUNT, w.ID AS COST_CENTER_ID
FROM DATA.V_JOURNAL_JOURNAL_ENTRY_LINE_DATA l
LEFT JOIN DATA.V_JOURNAL_JOURNAL_ENTRY_LINE_DATA_WORKTAGS w
  ON w.ROW_ID = l.ROW_ID AND w.ID_TYPE = 'Cost_Center_Reference_ID';

Load types

Each extract has a load type, set when the business object is switched on and changed on the Operation page.

• Merge keeps the current version of every record by its key. A record that comes back changed replaces its earlier rows; one that stops coming back stays. This is the usual choice for master data and transactions alike.
• Replace empties the tables and writes the run's rows, in one transaction, so readers never see a half-written set.
• Append keeps every run's rows. For history tables and audits.

The key is the record's own reference, which the app recommends from the spec.

Windows and the highwater mark

An incremental extract asks Workday for records updated in a window. The end of the last successful window is the highwater mark, and the next scheduled run starts from there. Run the extract now from the Operation page to fetch an ad hoc window, such as a backfill or a re-read of one day; an ad hoc window leaves the highwater alone. A business object whose Workday operation has no update window is extracted whole each run.

Schedules

A schedule is a small piece of JSON on the extract.

{"rules": [{"at": ["01:00"]}]}

Rules can name times of day, weekdays and intervals, and an extract can hold several. The Operation page shows the next slot the heartbeat will take. An extract can also list submits in Run after: it then runs once a batch for any of them completes, so Reconcile has fresh data minutes after a submit rather than the next morning.

Pages, workers and tuning

Workday answers an extract in pages. The extract fetches pages in batches with several workers at once, and writes each batch through a staging table with explicit casts, so a page that fails after its retries is fetched once more at a lower level before it counts against the run.

Page size and max workers on the Operation page are ceilings. With tuning automatic, the default for every business object, a run steps down one level after a failure or when a level proved no faster than the one below, climbs back after three clean batches, and waits longer each time the same level gives way again. A failed page is fetched once more at the lower level; when workers are already at one, the page size gives way for the next run. Every decision is logged, and the Operation page lists the changes and the levels the next run starts at. Start over forgets what was learned.

Large tenants extract tens of thousands of records and multi-megabyte pages. The app is built for those volumes: pages are parsed as they arrive, the flatten is one pass, and the sink writes each batch in one statement.

Retention

Extracted rows are kept for the retention set per business object on the Operations page, or the default under Admin; 0 keeps everything. The heartbeat purges once a day and records a purge run in History. The current version of a merged record is never purged, so a merge table stays complete however short its retention; only superseded versions and appended history go.

Reference lookups

Extracts that are on also serve the submits. For every reference ID column a submit carries whose business object an extract that is on serves, such as a company, a supplier or a cost center worktag, the app generates a lookup rule: the value must exist in that extract's view, or the record is rejected before anything is sent. The rules are rewritten on every switch, so switching on Cost Centers tightens every submit that references one.