Trust, but verify: 5 Considerations to Reconcile Your Data Nightly

As Ronald Reagan once said, many users follow the principle of “trust, but verify.”  When working with the multitude of technology solutions used to do their daily jobs, it becomes increasingly important to ensure that the users are painting an accurate picture to their leadership team.  Let me explain.

User logs into, let’s say Workday Financials, in this example.  This financial user runs an operating report for a specific account, fiscal month, and fiscal year.  This user then logs into the associated BI solution.  They run a longitudinal report on this same account across two fiscal years for comparison purposes.  At first blush, the user will compare what is seen in Workday to the same fiscal month/year in BI to ensure that the figures match.  They will then review the historical months/years.

What happens when the numbers “don’t match”?  In this example, the user will lose confidence in the BI solution quickly – and will question the figures seen in the longitudinal analysis.  Likely, this user will communicate with others on his or her team – and user adoption will decrease until the problem is acknowledged and corrected.  You’re probably thinking, “well – right…fix it!”.

The larger question arises as to how to corroborate the data and reconcile on a nightly basis so that this does not occur.  And, in the off chance that it does occur, you can be proactive in your communication to end-users to provide transparency around the issue(s).  Below are a few suggestions based upon our experience:

1. Row Counts – this one is simple.  We perform row count analysis on the data extraction from Workday – and ensure this exact number of rows is being written to the data warehouse. We broke this down further to identify the # of rows in the extract, # of rows inserted, and # of rows updated.
   • Note – we’ve seen some fall out due to inaccurate budget dates.  This error was quickly identified by this method.  The root cause of this budget date error was caused by an erroneous configuration in a nightly business integration.  This is value-add for the BI solution as it helped to fix an error that was causing data integrity issues.
2. Overall Debit/Credit Match – This is also a fairly simple check.  In our Informatica Workflow, we’ve setup a step to simply check if the overall debits and credits match.  This is a quick and easy way to check if you have something out of alignment.
3. Audit by Ledger Type and Period – In Workday, this was a bit more complex.  We wanted to bump the BI data against the Workday Trial Balance report.  The out-of-box Workday Trial Balance report cannot be scheduled via CSV or XML to an SFTP.  This is our transfer mechanism.  We recreated a custom report which mimics the Trial Balance functionality and then attached it to a corresponding EIB.  This allows for us to generate the Trial Balance report nightly, load the BI data, and then reconcile against the Workday Trial Balance report.  This is done by ledger type (actuals, obligations, and commitments) by fiscal year, period, and debit/credit.
4. Audit by Ledger Type, Ledger Account, and Period– This reconciliation is the same as step #3, but we added one more layer of detail.  In this audit, we also added the ledger account.  This bumps the Workday Trial Balance report against the loaded BI data by fiscal year, period, ledger type, ledger account, and debit/credit.
5. Variance File – Finally, to make entirely sure that everything is in alignment (or we didn’t overlook anything from steps #1-4) – we created a variance file.  If anything is out of alignment, the variances are highlighted in this exception file.  This is useful if something is out of alignment, then we don’t have to scroll through long lists (most of which are in alignment).

Hopefully, these have been helpful tidbits.  As always, I’m open to sharing more.  Please contact me if you’d like to learn more.  Or, please feel free to leave comments about what you are doing.  It’s always great to learn from one another.

Business Intelligence with Workday

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:

1. Utilize Workday Studio and leverage the web services-based Workday API
2. Join required Workday objects in a custom report and expose them as a RaaS (report-as-a-service)
3. 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:

1. 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.
2. 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.
3. 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.
4. 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!