As part of my current project at Georgetown University, we have a requirement to extract large volumes of data for business intelligence purposes from Workday. We’ve been running Workday HCM for about 2 years and plan to go-live with Workday Financials this July. Historically, the university has been largely an advocate of on-premise solutions – and Workday represents one of the first large steps we’ve taken to move ERP data “into the cloud”.
What does this mean for business intelligence? Let’s dive a bit deeper.
Provided that Workday is a SaaS (software-as-a-service) vendor, a direct on-premise database connection isn’t an option. That being said, we had to explore options to get data of out of the Workday platform – and to do it quickly and efficiently.
Below are a few data extraction methods which we considered:
- Utilize Workday Studio and leverage the web services-based Workday API
- Join required Workday objects in a custom report and expose them as a RaaS (report-as-a-service)
- Utilize Informatica Cloud
We quickly ruled out option #3. We enjoy using Informatica and thought this might be a quick win. Unfortunately, when aligning the data options available in Informatica Cloud with our requirements – we had several gaps (i.e. missing data, data was not defined down to the appropriate grain, etc). Informatica offered to partner with us to expand capabilities (much appreciated), but unfortunately our timelines did not permit this to occur for our current project. We continue to use Informatica PowerCenter as our best-in-class data integration tool.
So – which option was better #1 (API) or #2 (RaaS)? This question was kicked around for quite some time. After much deliberation, we decided on option #2 (RaaS). You’re probably already asking why. Isn’t the API faster? Below were the results of our analysis:
- The Workday API was hitting a governor limit for our tenant. This may have been a specific configuration setting for our tenant but it was prohibitive to pull large amounts of data out of the API. The API was timing out for certain data pulls at the 2 hour mark. It also required an API developer to manage. Workday has done a nice job of documenting their API. We found this to be extremely helpful along the way. I’m sure we’ll have a future use for the API.
- Custom Reports allowed us the flexibility to join multiple objects that met our requirements. The API calls were limited to what is publicly available in those calls. We have a bit more flexibility to combine objects which support our requirements in Custom Reports.
- Custom Reports are built using the Workday GUI. This is an added benefit at it did not require a developer to maintain the API calls. We can have a functional resource maintain the custom reports which are used for data extraction.
- Custom Reports (so far) have been able to support the large data extraction that we require – without timing out. An example is that we have been able to extract about 200K bi-weekly payroll records in about 12 minutes. We will use this Custom Report-delivered-by-EIB mechanism to support incremental loads to our Finance and HCM data warehouses.
Using Workday’s Enterprise Interface Builder (EIB), we have been able to schedule the Custom Reports and have them output to a text file format which is conducive to load into our business intelligence platform. The Workday Enterprise Interface Builder (EIB) tool provides an easy-to-use graphical and guided interface to define inbound and outbound integrations without requiring any programming. Used by the majority of Workday customers, the EIB can be used by both business and IT users to address a variety of integration needs.
Our project is still underway. I’ll write another post with more details as our project moves further toward completion. If you have any thoughts or suggestions regarding Workday data extraction / integration, please feel free to comment. Suggestions and comments are welcome!
Hi Robert. Do you also have to integrate Workday with any of your other applications? And do you use the same method of data extraction? We also have been using Workday’s built in data extraction capabilities but have been wondering what’s the best path forward.
I just connected with you on LinkedIn so that we can discuss further. We do integrate Workday with a number of our institutional applications. We’ve setup integrations that feed data from these applications into our Workday tenant. Most of those integrations are inbound and are handled via CSV. We also do more sophisticated bi-directional integration with SaleForce. We still push/pull our WD data primarily via CSV files. We established a custom report or RaaS – and then attach it to an EIB which has a CSV transformation. We started down the path of utilizing XML, but ran into issues when we encountered multiple line fields that were not handled well in the accompanying XSD from WD. This issue caused the multiline field to be parsed into multiple columns by Informatica. That being said, some cases were are using APIs and web services to move data back and forth. I prefer the latter method when possible, but there are limitations to the API, too. Depending on your requirements, Informatica Cloud may be worth taking a look at also. Hope this helps. Let me know if I can further assist and feel free to reach out on LinkedIn. -Robert