Data Gathering Workbooks - Excel Add-In Solution
Problem with current Data Gathering Workbooks
One of the first challenges a company will face when migrating their existing decentralised HR systems to Workday is - how do we extract the data efficiently and accurately?
The typical route prescribed by both Workday and your Implementation Partner (IP) for migrating your existing HR system(s) into Workday is to use a series of Data Gathering Workbooks, typically one for each module you have purchased. These data gathering workbooks are then distributed to your local SMEs for completion. These data gathering workbooks are made up for numerous tabs containing in some cases over a hundred fields. If you have a number of SMEs completing these workbooks over a number of geographical sites, then quite often you will find that not all your SMEs will understand what to complete. This can cause costly delays to the project given that your data lead will not know the quality of the data gathering workbooks until they are returned for data integrity checks and validation. If you are migrating several sites, then it can become a huge administrative exercise tracking and managing the data validation issues for each workbook.
Solution
Here at ETLcloud we have developed an Excel Add-in that will sit on top of the data gathering workbooks and will test the data integrity of the entire workbook. Below is brief summary of those tests
Data requirements - does each worker have the minimum data required for migration into workday
Data validation - have the SMEs selected and used the correct reference IDs (Employee Type, Locations etc)
Data integrity - does each tab on the workbooks have corresponding data rows across the workbook
Technical Details
Why analyse the data integrity of your data gathering workbook?
Typically, your Workday Data Lead will not have a great deal of control of how and what is entered into a data gathering workbook, even if your data lead adds lookup values for your reference and set up data. Excel will not stop your data SME (Subject Matter Expert) from simply cutting and pasting over the top of any data set up. This can cause your data lead a huge amount of work transforming the data back into reference ids. If you have several workbooks and each workbook may have 20 – 30 reference id lookups, then quite quickly this becomes a huge logistical exercise to transform the data added by the SME.
In addition to the transformation issues, your data lead will also need to test the integrity of the information entered into the data gathering workbooks.
What is meant by workbook data integrity in this context?
First some background. In order to migrate your existing HR worker data into Workday requires a minimum set of data: Name, some form of contact (Email address, Address, Telephone number), position data (Hire Date, Employee Type, Supervisory Organisation, Job Code, Business Title, etc), organisation assignment (Cost Centre, Legal Entity). This minimum set of required data items are distributed across multiple tabs on the data gathering workbooks. Any missing data for any of the above required data items will cause the worker record to be rejected.
Having worked on a number of Workday data migration projects we at ETLcloud can attest from experience that logging and returning data integrity issues back to the SMEs for rework can be extremely time-consuming and can be especially difficult when working with SMEs on different time zones.
What is the solution?
Provide your SMEs with a set of tools that will provide instant feedback on the current state of the data gathering workbooks. By doing this, there is no need to return the workbook back to the data lead for analysis. Essentially you are asking your SMEs to Extract and Transform your current HR data at source. This will improve the accuracy of the data. It will also significantly reduce the amount work required by the Workday data team in transforming and validating the data.
Here are at ETLcloud we have developed a series of VBA Modules that sit on top the data gathering workbook, after a small amount configuration has been complete the VBA module is ready to analyse the data gathering workbook.
Product Details
Data Visualisation
Data Integrity Checks
Data Integrity Checks
The first thing we have is a visualisation tool, this will tell the SME instantly whether every tab has the same number of records on each tab; if there are differences then it will check which records are missing.
Data Integrity Checks
Data Integrity Checks
Data Integrity Checks
The above is a sample of some of the more common data integrity checks. Here at ETLcloud we continually review and add additional checks to keep pace with the ever changing HCM Workday ecosystem.
Orphaned Data Items
Data Integrity Checks
Orphaned Data Items
The above data integrity check looks for orphaned data items that may exist between the various workbook tabs. Quite often a row may have been added only to the Government ID tab and nowhere else on the Workbook.
Set up Data Checks
Data Integration Checks
Orphaned Data Items
Next check for reference id and data lookups. This check will tell the SME if they have added a value that is not currently configured in Workday, or they have overwritten a look up value.
Data Integration Checks
Data Integration Checks
Data Integration Checks
Most organisations will have some integration requirements, whether it is an Active Directory outbound feed, or a Benefit enrolment integration that has very specific data requirements. These specific data requirements can be custom added to our current architecture. Above is an example of a custom set of data validation requirements.
Transformation
Data Integration Checks
Data Integration Checks
ETLcloud WB Analysis module will extract the Reference ID and user-friendly Instance names for your HCM set up data and transform the workbook once completed by the SMEs.