Alteryx Success Stories
Learn how Alteryx customers transform their organizations using data and analytics.STORIES WANTED
Showcase your achievements in the Maveryx Community by submitting a Success Story now!
SUBMISSION INSTRUCTIONSWorkday Data Migration : How we saved over 2000 hours of manual effort
The workforce of Salvos is a very diverse group falling into one or more of the following worker categories:
|
Worker Type Category |
Definition |
Approx. Numbers |
|
Officer |
Salvation Army officers are ordained ministers of religion who receive allowances, occupy leadership positions and/or occupy service delivery positions within The Salvation Army. Officers may be in active positions or retired. |
1,700 |
|
Worker |
Someone who has a specific and defined relationship with Salvos and is paid via payroll. These include permanent, fixed term and casuals. |
10,000 |
|
Independent Contractor |
Individuals who provide services not ordinarily available within the organisation and receive remuneration for those services. |
<500 |
|
Volunteer |
Someone who belongs to Salvos as an adherent or a soldier, and/or someone who works for the organisation without payment. Volunteers generally fit into two distinct categories: 1. On-going (those who donate 4+ hours per week) 2. Event (one-off volunteers). |
30,000 60,000 |
Salvos contains various distinct organisational structures that support the diverse and huge workforce, some of which have their own legal entity and governance structure.
The main structures are:
- 3 Headquarters (THQ)
- Six Divisions (based on geographical areas) comprising corps, social centres and services
- Employment Plus (employment services)
- Salvos Stores (retail and logistics)
- Salvation Army Housing (housing and tenancy services)
- Aged Care Plus (aged care services)
- Salvos Funerals
- Salvos Legal
Salvos has been operating as 2 territories (Australian Southern and Eastern). The state of systems and data flow within Salvos was complex with no single source of truth of the national workforce. The data was duplicated in different systems, Microsoft Excel spreadsheets and hard copy files. Creating organisation reports such as the mandatory WGEA, national level workforce reporting, tracking integrity checks and training compliance data was extremely difficult and time consuming. At the start of 2019, in order to replace the numerous existing HR and Payroll systems TSA embarked on a national transformation program to implement Workday HCM + iChris Payroll systems with further roll-out of Kronos Time and Attendance across Salvos.
After exploring how to approach the massive data involved in such an undertaking, I was keen on exploring an automated platform that supports a repeatable data transformation and migration process. My search on the internet led to the paper Alteryx Workday data migration by Cynthia Eckles (an employee of Alteryx) who had used Alteryx to support Workday data migration in 2018. I subsequently reached out to her on LinkedIn and after a video chat with her, I was very inspired to use Alteryx as the data migration and transformation tool for this daunting endeavor. What followed was an absolute memorable year as we took on the biggest data challenge and broke it down in Alteryx.
Historically Salvos has been operating with 2 regional payroll systems which was the source of truth for worker and payroll data for over 10,000 staff members across Australia. Over time the usage and controls for data maintenance and practices have been different across both systems which had given rise to data integrity issues, duplication of information, challenges with workforce planning and a time taking process to come up with national level reporting of staff. The key challenges we faced are included below:
- Siloed Legacy systems: Data was managed in two separate Payroll systems with different data maturity levels based on business needs.
- Unharmonized Data: As a merging of two different operating organizations, there were different set of business rules and mandatory fields for the payroll databases. Hence the data quality on merging the data across the two systems required a huge clean-up effort.
- Duplicate & Redundant data: Some workers were duplicated between the organizations for reporting structure and visibility reasons, which created siloed duplicates between systems.
- National Level reporting: The entire source data had to be manually pulled and reconciled between the two systems and reporting unique worker list as a single organization was completely manual.
- Unlabelled Data: IPS system data had to be matched with our payroll data to determine the workers they relate to.
- Historical Data volume: The IPS data involved a lot of history of personnel, including their education, work history, experience, which increased the data volume for migration.
The data scope for the transformation project was the migration of over 230,000 rows of data from multiple legacy systems to Workday.
Through the Salvos transformation project, we had a very good chance to review existing practices and harmonize the worker data from both systems prior to bringing them into Workday. The path to achieving harmonization was a challenging one though as we faced lots of hurdles with the current state of data for eg staff records were duplicated across the systems, governance around mandatory fields across both systems were different, customization at a field level to name a few.
Given the huge volume of data for our project, the key challenge we faced at the start of the project was to explore how we could automate the data migration process to make it repeatable through the different data migration cycles. Over the past 12 months, we have found Alteryx to be an indispensable tool for assisting with the Workday and payroll data migration process. Without the power of Alteryx, we would not have been able to achieve this success. Though both of us in the team were exposed to data science, we were not trained in Alteryx before and were able to tackle the data challenge with ease because of the flexibility and ease-of-use provided by Alteryx. After starting to use Alteryx for data transformation, we soon started using Alteryx for other use cases such as Workday integration, configuration, data validation, post go live audit.
Some of the more common tools included : Preparation (e.g. Data cleansing , Filter , Formula , Generate rows , Multi-row formula, Multi-field formula, Sample , Record ID , Reg Ex , Sort , Unique) , Join (Append Fields , Find Replace , Fuzzy Match , Join , Join Multiple , Make group , Union) , Parse (Date time , Regex , Text to columns , XML parse) , Transform (Arrange , Count records , Cross tab , Running total , Summarize , Transpose) , Download , List Runner .
In general, the following systems formed the bulk of the raw data deployed in this project.
- Australian Eastern Region legacy payroll system – This system captured the payroll, demographic and personal data for over 5000+ workers in TSA eastern region
- Australian Southern Region legacy payroll system - This system captured the payroll, demographic and personal data for over 5000+ workers in TSA eastern region
- International Personnel System – This system captures the entire career history of TSA’s 1700+ officers. The data consists of appointments, service history, training, education data which we needed to migrate to Workday
The key steps we undertook to automate and develop a repeatable data migration process in our project have been outlined below:
- Review and understand the different Workday workbook templates that were required to be filled for Workday data migration
- Define mapping files containing transformation values (1-1, 1-many, many-1 etc) for fields between legacy and workday systems
- Define a query for each type of data in the source systems (Employee basic information, compensation, address, dependents, integrity checks, emergency contact information etc).
- Used the source system queries, transformation mapping files to build Workday data transformation logic within Alteryx
- Read the Workday book templates directly within Alteryx to convert raw data into transformed data in Workday workbook templates
- To cater to different types of Workday workbooks, we created 25 different types of Alteryx workflow for Employee data (Employee data, Emergency contacts, bank data, education and certifications, compensation, allowances, expense data, officer historical data, leave data etc)
We made extensive use of Alteryx in our Workday and payroll implementation project to perform data migration, data validation, configuration audit, post go live data audits and even use Alteryx as an integration system. In our paper we outline 5 areas where we used Alteryx to automate a significant amount of manual work:
- Generating Workday workbooks from legacy data using Alteryx
- Workday configuration and business process validations
- Integration of Workday with 3rd party system using Alteryx
- Workday data load validation
- Post go live data audit of Workday and Payroll system
I am pleased to present our exciting adventures of Alteryx in these areas below.
- Employee Data
Workday employee data generation involved the transformation of employee demographic, personal, compensation and service history data for Salvos workers from all the source systems. During the multiple rounds of data loading and cleansing, Alteryx was used to transform raw data into Workday workbooks, validate and confirm that the data in the current system matches what is in Workday. Exhibit 1 and 2 are applications of Alteryx in the Employee data workbook generation.
Exhibit 1: Employee Data workflow
Creating the employee data workflow was hugely challenging as we have catered to over 26 input files and created 16 output files in a single workflow. In this workflow the legacy systems data from the 2 payroll systems were sourced, explored, scrubbed and revised/corrected and cleansed to be transformed across more than 140 columns in the Workday workbook hire template. The workflow generates error files that report missing data for mandatory fields required in Workday.
In this workflow we have used several Alteryx tools for data preparation, parsing, transformation, joins, date field conversions. The keys steps in the workflow involve the following :
- Merge the two source systems as one data source
- Clean up and align the data between the systems using mapping files and common business rules.
- Remove duplicates and merge redundant records
- Merge the template to which we need to transform the output
- Run the transformation logic and prepare the workbook.
Exhibit 2: Officer IPS system – Pattern matching
International Personnel System (IPS) is the system that captures the career and service history data for the 1700+ officers in TSA. Our project scope involved the full data migration from IPS to Workday system. Before we could start to migrate the data, the immediate challenge for us was to match the existing officers in IPS with a unique identifier field. For data migration, since we were using the legacy payroll system ID as the unique identifier for all worker data migration into Workday, we had to match the IPS officers with the legacy payroll system staff IDs to determine matches. The big challenge we faced in the IPS data set was that it was unlabelled. In order to migrate the data to Workday, we had to first ascertain the unique payroll id numbers. A manual review for 1700+ officers between IPS and payroll system Chris21 would have taken us weeks to complete.
Using Alteryx, we automated the pattern matching using First name, last name, gender, date of birth, middle name and hire date. Using the join functionality, we defined 6 level of matching of different combination of fields to generate matching of officers across both systems. Using this workflow, we were able to automate the officer ID matching for over 95% of our officer workforce.
- Workday System configuration and validation
Workday is a system that is driven by business processes which are developed and delivered by Workday and cover the employee life cycle (hire to retire). At Salvos, we optimized the business processes to determine which role can initiate, approve, review and complete a business process within Workday. When we got a request from the business team for a role matrix on which roles have what access to a business process within Workday , we struggled to find a way to easily represent this data , given the Workday standard report contained several columns and had a lot of technical configuration related information which would not have been useful for the business stakeholders.
Exhibit 3: Workday Roles matrix
Using Alteryx tools such as Cross tab, text to rows we generated a user-friendly version of the Workday business role matrix as defined in Exhibit 3. The input for this workflow is a Workday standard report called Extract Business process definition. The output of this workflow has 2 files which show the Workday role matrix by roles and by action / business process step. These reports were a big help to the change management team to identify and understand impacts to roles in future state and for business subject matter experts and stakeholders.
Workday output file – Roles by action
Workday output file – Actions by Role
Exhibit 4: Workday Business process reconciliation between implementation tenants
As part of the workday system implementation, the configurations in Workday were migrated from a prototype build environment to a test environment and then to a production environment. Due to the complex nature of our requirements, we had included a full configuration review of Workday business processes as part of our Test strategy. Instead of reviewing the configuration set up manually, we decided to automate the configuration review in Alteryx.
In the below workflow, standard workday reports containing over 600 rows of configuration data across 2 environments have been matched using multidimensional join tool to generate the configuration that did not match. The results were shared with our implementation partner to resolve. Automating this reconciliation made the process repeatable across 3 times during the project. On an average this reconciliation would have taken us days to complete for each iteration.
Unmatched Workday configuration between development and test environments
- Integration
As part of Exhibit 5, we will elaborate how Alteryx was used to generate a live feed of data from Workday, transform and clean the data and transfer the data to a 3rd party system IPS using the Push API method.
Exhibit 5: New Hire integration with International Personnel System
Post go live there was a requirement that all new officers in Salvos who are hired in Workday are updated in the International Officer system called IPS. In order to automate the integration, we created an Alteryx workflow as outlined below.
The automation was achieved through 3 key components in the Alteryx workflow:
- Extract data from Workday: We first created a reported in Workday containing the hire fields that were required to be transferred. This report was set up as a webservice. The json webservice path link was then copied and used as an input to the Download tool within Alteryx. This ensure real time access to Workday data
- Transform the new hires data: The json parse tool was used to parse the data in Workday. Using text to columns, cross tab, formula, data cleansing and arrange tools the data was further rearranged into the specific template required by IPS
- Transfer data to IPS server: The data is now appended with the destination URL and fed through a JSON build tool to generate the data in json format. The final step is to use the download tool to push the data into IPS server by using the PUT action
- Workday Data Load Validation
The following exhibits showcase our work in Alteryx to automate data validations for Workday data migration.
Exhibit 6: Workday data load validation workflow
The workday data migration scope for our implementation was huge as we had to migrate over 250,000 rows of data. We used Alteryx to automate the data validation for over 100 unique fields of data for employee data such as contact info, worker address, position data, personal data, demographic data, training and certifications data etc.
The data validation workflow captured in Exhibit 6 has 3 key components:
- Extract data from Workday: Using the Alteryx download tool, we download over 20 workday standard reports in real time. The workday reports extraction workflow was converted into a macro which is displayed through the Workday icon on the left-hand side of the image. The downloaded reports were written as csv files into a directory which was used by the Employee data validation component
- Employee data validation: The source files that were provided for upload and the workday downloaded reports were compared across key fields using the Join tool to identify exact matches. The left side of the Join signifies the data that is only in Workday but not in our source files. The data from the left side of the join were written to results file in the Not Matched Output section
- Not matched Output: The mismatched data from each of the employee data validation tool containers were written to individual results file in this component.
- Post Go live Audit
Soon after we went live with Workday and our payroll system iChris, we developed Alteryx workflow to conduct real time data audit of Workday and iChris to make sure we are constantly reviewing and cleaning data issues that may impact upcoming payroll runs. We run this workflow periodically and distribute the data report to our HR stakeholders to enable them to follow up on missing data.
Exhibit 7: Workday data audit workflow
The workday data audit has been automated within Alteryx. Using connectivity tools explained in prior exhibits, we download a master report from Workday in real time and transform it to generate different types of audit reports for eg workers with missing date of birth, missing or incorrect cost centers, missing bank data, missing or incorrect work schedule data.
Exhibit 8: Workday and payroll systems data reconciliation Workflow
Using Alteryx workflow, we ran periodic reconciliation of data between Workday and payroll system to generate mismatches of payroll relevant data such as bank data, cost centers, pay cycle data, employment status, worker class related information. This report has been critical for ensuring we are in front of pay related issues prior to the pay processing.
Using workflows outlined in Exhibit 7 and 8, here is a sample Data audit dashboard that we generated using Alteryx. Please note the data in the dashboard is made up due to privacy.
In this paper we have covered only 25% of the Alteryx workflows developed as part of our Workday and Payroll implementation. As outlined below, quantifying the Alteryx workflows for each of the 8 exhibits produced a cumulative saving of over 600 hours of manual effort. If we were to quantify savings from all the 25 Alteryx workflows that were developed, we would easily have generated savings of over 2000 hours of manual effort in the project.
Further to the tangible benefits outlined above, the ability to be able to prototype data migration with a live dataset through the various data cycles and payroll parallel runs enabled us to keep reducing the lead time. If we were to do it differently next time, it will be worth focusing on automating the data update process into Workday directly from Alteryx.
Lastly I and Ganesh were working on both data and testing streams at the same time. It was challenging for me to lead both stream of work given the scope and complexity. But I was very privileged to be working with Ganesh and Alteryx. Without Alteryx , managing a data migration of such a size and scope would have been impossible with a 1.5 FTE allocation.
That’s I and Ganesh (in the front) posing before our Data dashboard after submitting the final data files prior to Go live. Post Go live we also took the Alteryx core certification exams and successfully cleared it to be Alteryx core certified 😊
