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:
- 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.
- 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.
- 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.
- 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.
- 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.
Robert Goodman 