Client
A leading global asset management company.
Challenge
Our client wanted to automatically and dynamically export Workday data to a business analytics database. However, the nature of the reports exported from Workday was subject to frequent change. The idea of being able to keep a database dynamically populated with Workday reports of unknown content and structure is a tricky one. The main challenge of this project was creating a methodology to sequentially upsert reports into a database regardless of the data modified and the number of columns it contained.
Solution
The approach we took in developing this solution was the first of its kind at Dispatch. We chose Workato as the data extraction and transformation platform and Azure MS SQL as the destination database to build this solution.
We leveraged similar XSD schemas and report patterns across Workday reports to dynamically append reports to a database without prior knowledge of the report’s columns and contents. Report column names and data types are populated accordingly, with the Workday report being processed during the integration run. We scheduled the integration to run quarterly, adding or removing new reports during each run. The client can change this schedule based on business needs.
Value Added
This solution has enabled a reliable and automated way to extract complex data from Workday for further analysis and processing. By using Workato, we were able to develop a solution that was both intelligent and flexible. While our client chose Azure SQL for the database destination, this approach could be adapted to other target databases, such as Snowflake.
This solution:
- Automatically maintains tables in a managed database delivered from aggregated and dynamic Workday reports.
- Keeps records of historical integration job runs for audit purposes.
- Provides efficient data availability to the HR Analytics team in a form that suits their analytics tools.
- Can be managed and modified relatively easily, given the no-code nature of Workato.
Outcome
The client is delighted with the solution and is optimistic about utilizing the data contained within the database for HR trend visualization, analysis, and extrapolation.
Ruma Husain