Data transformation case studies

Definian partnered with the Salesforce team to create an enterprise tool for all Salesforce internal users that incorporates multiple data sources, styles, and cadences and allows users to browse and interact with essential objects such as data cataloging tools, data governance, and risk management guidelines, and data quality best practices, to name a few. The teams worked extensively to create a unified view to create a data marketplace, with all analytical solutions in one place. This makes it possible for existing data APIs to be discovered company-wide and replaces a previously fragmented experience with a Unified Smart Catalog. The metadata app, knowledge base, and corporate updates can now be accessed with a single click, and users are able to receive self-serve analytics based on usage patterns and trends.

A unified experience for the Salesforce team means an easy-to-use, one stop shop to explore KPIs, key metrics, and top data assets across the company. The data discovery process is easier than ever, adding traceability to monitor usage of data assets, and using modern practices for data orchestration, transformation, storage, warehousing, interactive query, and visualization. The intuitive design of the solution makes the framework scalable, which will serve the company’s ongoing and future projects as well.

The Teams from Definian and Salesforce worked together to plan, develop, and test the centralized user experience that now delivers great value through the following features:

• A single user interface (UI) for Data Catalog Capabilities including Data Discovery, Data Stewardship, and a Business Glossary
• A single view of compliance audits and risk assessments through various reports and dashboards
• A single view of data quality reports and dashboards by business unit
• An intuitive data health view with a quality score
• A newly-enabled knowledge base
• Custom dashboarding capabilities

This project signifies a great partnership with the Salesforce team to work effectively and efficiently with data from myriad sources.

Experiencing significant issues prior to User Acceptance Test, a multi-national company brought Definian in to assess the data migration for complex multi-application. The project was in danger. There was no confidence in the data migration process or the data itself.  The process took too long execute, the data quality was bad, real data had yet to make it completely through the process, and something needed to be done to get it back on track. Our goal was to figure out what that something was and to see what could be implemented prior to UAT.

The assessment lasted about 3 days, but during that time we were able to host 22 hours of interviews with 23 different team members, call out project strengths, and identify the major issues that were affecting the project.  

During the initial interviews with the team, it was clear that their greatest strength was their technical knowhow.  The team was savvy and whip smart on every individual technical aspect that needed to bring the system to live.  The major issues stemmed from organization’s culture and would not be an easy fix.  

The primary problem was culturally everything was someone else's problem. Individuals were confident their own slice was working perfectly, but data was always someone else's problem. Project leadership dished off leadership and management accountabilities to others, who pointed to others, who pointed back to the first group we talked with. When discussing a problem, process, or even general status, someone else was being set up to be the fall guy.  While this level of avoidance was atypically high, there were valid reasons for the team to skirt responsibility.  The organization was constantly on the lookout to cut costs and lay people off.  During the assessment, we learned that the company had laid off a chunk of the workforce including some subject matter experts to pay for the implementation.  This justifiably fed into the team’s fearfulness and poisoned many aspects of the organization.

Branching off the "it's not my problem culture", communication between each component and disparate application of the implementation was siloed.  Data requirements required by the third hop weren’t communicated down to the data source.  The group on the 3rd hop were raising flags, but without clear coordination across teams, those flags were not being seen. In conjunction with the siloed work streams, several disparate independently operated project plans had been created.  This siloed approach prevented the different aspects of the project from coming together at the appropriate times and prevented the ability to track the critical path across the program.  If one track had an issue, spec change, or just an upcoming vacation, the program didn’t have a way to calculate the impact.

The third major issue, which is also a biproduct of culture, was that the data governance council was dissolved prior to the start of the implementation.  This fed into the “not my problem” culture. Without clear data accountabilities, everyone ran away from data, since everyone knew it would be one of the most difficult aspects of the project to get right. Without the governance rules of the road and oversight, there wasn’t a global data vision, unified standards, comprehensive policies, lineage tracking, quality metrics, etc. During the assessment, the data governance council was in the process of being restarted, but culturally it would be difficult to make the council effective due a fear of accountability.

While the client was looking for an easy button solution that could get them back on track in a matter of a couple weeks, it wasn’t going to be possible in that time frame. Before the team could uncover any remaining technical gaps, the cultural, project management, and data governance issues needed to be addressed.   I wish there was a successful turnaround story for this project, but shortly after the assessment, the organization went through another round of significant layoffs and the project was cancelled.

Please take these lessons from another company and apply to your organization.  If these issues can happen at a globally recognizable company, they can happen anywhere. Technically, this group was incredibly talented and had all the technical pieces to be successful.  It was the fearful culture, siloed management, and lack of governance that drove their issues. In many failures, it is the non-technical pieces that are the most difficult to get right.

If you have questions about data management, data migration, data governance, building data cultures, or you just love data, let’s get together.  My email is steve_novak@premierintl.com.

Making a first data conversion test cycle a success requires good planning and proactive work. Here, we take you behind the scenes of 7 key steps that we do at Definian to help make your test cycle a success.

1. Identify similarities

Serving as a strategic partner for clients with multi-year, multi-division ERP rollouts often means we encounter similar requirements across different parts of the business.  To minimize cost and accelerate development, we review existing documents and conversion code and leverage as much prior art as possible when building something new for the client.  It helps that our main competitive differentiator– Applaud® - is a rapid application development environment that enables our team to centralize development and quickly identify and reuse relevant code.

Business Challenge:  Definian converted Customer Master data for multiple sites of a single client over several years. Each site’s Customer Master came from the same legacy system and most of the conversion requirements and programming was reusable from site to site. But a new set of sites came into scope that used a Customer Master totally different from the earlier sites. Drivers related to the load application were updated at the same time that these complex new sites came into scope. It turned out that these updates broke the previous process of loading data to the target database.  This failure required a new load application to be used.  The client was faced with the challenge to maintain their aggressive go-live schedule while addressing the complexities arising from the newly introduced legacy system and technical changes to the load process.  Defnian's ability to test load processes ahead of time prevented unexpected issues from emerging during the first test cycle for the new sites.

2. Generate “pre-load” conversion error reports

Another competitive differentiator for Definian is our repository of “critical-to-quality”(CTQ) checks that identify and report data issues.  Our CTQ tool validates that data is structurally valid and that it fits configuration.  For a basic example, this validates that customer site level information appropriately ties to the customer header and that the pay term for the customer is configured.  These CTQ checks identify data issues before we even push data to the target system.  These checks are carried forward from project to project to decrease development time and improve the results for our client. By doing this, we can quickly identify numerous issues that need to be addressed. To contrast, standard load programs typically only identify one issue at a time and must be rerun repeatedly after solving each successive issue.

3. Spearhead data cleanup activities

To maximize the success of your test cycle you need to first improve the quality of the data.  The legacy system is rife with issues, and this is often one of the reasons to implement a new ERP solution.  Defnian’s best practices approach to data migration integrates data profiling, analysis, and cleansing with the data conversion itself.  Many data cleanup activities can be fully automated and are carried out in the conversion code and appear in the target system.  Automated cleansing could include translating atypical code values to the desired target value or merging duplicate customer or supplier records. Other cleanup activities can be reported before and during the test cycle for manual cleanup in the legacy system – often driving business value.  Examples of these manual cleanup activity include closing old order or invoices that have been left in open status or filling in required fields.

4. Mockup records for test loads before development is complete

While conversions are still being developed and legacy data is being cleaned up another key activity is to mockup sample data to test the load process. This step to reveals if there are additional dependencies or configurations required that have not been previously identified. Dependencies are critical to an implementation because any delay to a master data (or any object that has downstream dependencies) will lead to delays in later conversions. For instance, Sales Orders cannot be loaded until Customers are successfully loaded.

Business Challenge:  Load requirements are not always clear. In one example, a client used alphanumeric identifiers for their Accounts Receivable (AR) transactions. The alpha characters were an important feature of their legacy identifier because it classified the transactions and drove business processes. The division considered the letter a required attribute. The target transaction identifier was also defined as a character field, so it seemed that there would be no issue with retaining the original alphanumeric values. However, Defnian’s preemptive load tests discovered that the load program pushed the transaction identifier into a second field, a document number, which was defined as numeric. This meant that the document “numbers” with letters would fail to load. Testing the load program before going into the test cycle gave us the opportunity to address this issue and figure out a solution for the client. Had the load program not been tested until the integration test cycle, AR would not have been ready for the integration test.

5. Report and escalate issues and new questions arising from test loads

Documenting findings, noting configuration gaps, and identifying necessary scope changes are all part of the progressive elaboration of a project.  This part of the process will vary based on what tools are available, but no matter the method, it is important to track issues and decisions to help with successive test runs.    Inevitably, questions will arise later in the project as to why something is being done the way it is and a test management tool (e.g. Jira, HPQC, DevOps, etc.) will have the answer.

6.  Facilitate client review of data before it is loaded

When it comes to full test cycles it is critical for the business team to review the data to make sure that what is being loaded matches expectations.  Inadequate testing is one of the primary sources of project failure, which is why we serve as our client’s partner throughout their testing and review.  Definian speeds this process by providing a series of reports that help connect the dots between the legacy and the target system. This usually includes an extract of the data being loaded in excel format with key legacy fields and descriptions for key coded fields, a record count breakdown and error reporting. Ultimately, this approach demonstrates that converted data fits the requirements and can reconcile back to the legacy data source and satisfy auditors.

7. Optimize runtime performance

Even in the earliest phases of the project, our team is already thinking about cutover and go-live.  To accommodate aggressive schedules and minimize business disruption, we optimize the conversion process to run as quickly as seamlessly as possible. Often, this requires that we parallelize high-volume portions of the data extraction from the legacy system and the data load to the target system.  Sometimes, system configuration impacts load throughput or fine tuning of batch sizes can save precious hours from the cutover schedule.  Whatever it takes, We understand the importance of a timely go-live and strive to keep business shutdown as brief as we can safely accommodate.

Enter your test cycle with confidence and focus

By carrying out the preparatory steps listed above, Definian minimizes data risk from your transformation project.  This allows the business to focus on learning and optimizing the new ERP system, instead of chasing data issues.  Less distractions for the wider project teams means that more time can be spent on functionality, configuration, training, and the future or your enterprise.

Client: A Leading Data Intelligence Platform Technology Company

In a rapidly evolving tech landscape, one leading data technology firm faced a pressing issue: the need to increase its flagship software product's compatibility with diverse data sources. While they had put together a connector framework, it was immature, and help was needed to get integrations in place while they focused on the base framework.  

As Definian engaged, our task revolved around several key activities aimed at streamlining connectivity solutions: 

Connector Development: Leveraging our MetaMesh technology, we focused on crafting connectors for high-priority customer requests. Our goal was to address demands swiftly and effectively. 

Continuous Improvement: Alongside connector development, we identified gaps and areas for enhancement within the framework, helping the Client’s product development team create a clear path for ongoing refinement and optimization. 

Support Model Establishment: To increase operational efficiency, we established and managed a support system that ensures seamless functionality and user satisfaction. 

The enhanced compatibility this client’s data intelligence platform now has against diverse data sources has vastly increased the number of end clients they can serve. Since this platform can now work with more varied and complex data landscapes than before, it has increased the number of opportunities available to the client to drive revenue growth.

As a result of this initiative, we not only solidified our position as a strategic partner but we have also fostered a robust culture knowledge exchange between organizations. Together, we synchronized our efforts to improve integrations with forthcoming Artificial Intelligence and analytics engines and are actively discussing additional ways to facilitate data exchange and future-proof our client's platform.

The City of Dallas embarked on its Workday implementation in early 2018. Before Definian International was contacted, the city had already struggled through 3 test cycles on their own, averaging 10 weeks on each and completing only two. Delay after delay caused frustration within the city. The project to modernize Dallas’s Payroll and Human Resources was floundering. The situation became dire when first responders felt the impact of the city’s antiquated systems.

Data Issues Make Headline News

Data issues and inconsistencies within Dallas’ legacy Lawson system were the main cause of the delays. However, project delays were a minor issue compared to the litany of problems brought to public attention by the president of the fire fighters association in February of 2019. The president reported to local news outlets that Dallas firefighters were not being paid the proper amounts, merit raises were not being paid on time, and that health insurance was not being administered correctly.

Gathering Political Will

City administrators knew that project direction had to change quickly, as the livelihood of thousands of fire department personnel depended on solving this systemic issue. The help they needed came when the Workday system integrator referred the city to Definian International. Citing years of successful collaboration, the system integrator knew that Definian could address Dallas’ data issues and steer the project back on course. Next came days and nights of planning, after-hours text messages between the Dallas CIO and Definian International leadership, and accelerated negotiations to figure out the best path forward. City administrators cut through red tape with true Texan determination and successfully added contracting with Definian International to the city council agenda in March 2019.

The Cleanup Begins

Definian spearheaded the clean-up of Dallas’ legacy benefits systems and took responsibility for generating the workbooks needed for the Workday data migration. The SQL queries and COBOL programs previously used by the city’s IT (Information Technology) team were converted to Definian’s standard mapping specifications and rebuilt in the Applaud® software within the first month. Definian leveraged its repository of Critical-to-Quality (CTQ) checks specific to Workday, amassed from prior successful Workday HCM (Human Capital Management) Go-Lives, to find data inconsistencies and shortcomings in the data. These pre-validation checks ensured data integrity moving into Workday and to enabled the city to accelerate data cleanup.

Analyzing Employee Payroll and Benefits

Applaud made it possible to gather detailed statistics on the data set and zero in on root problems that made headline news the prior month. By comparing employee benefit plans, types, and number of dependents, Definian ensured that providers had the right information to serve city employees. This effort was critical to restoring the payroll and benefits that were promised to firefighters, police officers, and thousands of other city workers. Prior to the Workday go-live, Definian used their proprietary Gross-To-Net (G2N) tool to compare payroll runs in Workday against Dallas’ current payroll system over several pay periods. This robust and flexible reconciliation gave Dallas the confidence they needed in their new payroll system. However, additional challenges awaited the project team on their path to Workday.

Legacy System Gaps

When Dallas began populating Workday withholding orders, they discovered that data dumps out of their legacy Lawson system could never meet Workday technical requirements. The Workday tenant needed more data than what was captured in Lawson. In response, the Dallas payroll team manually entered withholding data into the Workday load workbook. Data entry for employee withholding orders was difficult since an employee could have multiple orders and these orders can be updated over time. These complications, along with the fact that you had multiple people doing data entry, made the effort rife with errors and Dallas needed a systematic way to audit employee withholdings.

Audit Employee Withholdings

To audit withholding data, Definian partnered with the Dallas payroll team to automate comparisons between Lawson and the manually created files. This effort revealed gaps in the manual process, programmatically derived critical information, and resulted in withholding data that could be confidently loaded to Workday.

The Future of City Administration

The City of Dallas realized that the technical challenges they faced were outside their ability. Through careful self-reflection, resourceful problem solving, and contracting with Definian International, the city worked towards a solution that would correct mistakes in their payroll and benefits administration. Today, the City of Dallas is running on Workday with greater efficiency, more security, and better administration than ever before. Dallas’ strategic investment will serve them, their employees, and the entire city for many years to come.

A Growing Company at Risk

Prior to this global transportation/logistics company’s Workday implementation, revenues increased from $500 million to $800 million in a ten-year window. Amid their expansion, a key part of the business fell by the wayside. Significant gaps in data governance and master data management posed the greatest risk to their long-term IT (Information Technology) transformation project.

Discovering Data Governance Gaps

The first priority of their IT project timeline was to decommission their CODA (Unit4) system, the organization’s decades-old financial data storage platform. They elected the Workday Launch approach for this migration, which greatly condenses the project length compared to the traditional Workday framework. Their legacy CODA system did not scale with the rest of the organization; missing functionality and limited data validation in the legacy software led to workarounds and band-aid fixes.  The IT department consisted of only 2 employees – virtually the same staffing since the company’s inception – and there were no roles in the organization to ensure data governance. Further complicating matters, a corporate culture that promoted independence and creative problem solving led to unique and undocumented workarounds throughout the data landscape. The organization needed quick and accurate guidance in order to adhere to the tight Workday Launch timeline.

Instilling a Data Mindset

Recognizing their inability to provide clean extracts for their Workday test loads, the organization contacted Definian International for help on their in-flight implementation. Company leadership necessitated that Definian quickly generate data in the right format for Workday. CODA’s largely free-form column table structure led to several challenges: establishing how the legacy data was used in addition to determining the mappings necessary to move their existing data into Workday. Definian encouraged the organization to first understand their existing data landscape, which would then allow them to make more informed strategic decisions about how Workday will be configured and ultimately used. After much collaboration through extensive mapping sessions, the organization understood their own data better than ever before. Now the hard work could begin.

Undoing Years of Lax Data Governance

The supplier master conversion posed the greatest risk to the project, as the CODA legacy system did not have the functionality to set up separate remit-to supplier sites for a single top-level supplier. For years and years, the organization re-entered the same supplier master records whenever a new address was encountered (oftentimes the same address with a minor adjustment). It was even possible to have conflicting information like contact information, payment terms, and freight terms across these duplicate supplier records. Workday would not be able to support this structure; thus, consolidation of these supplier records was required. Definian built data conversion programs in Applaud® to dynamically merge legacy supplier records. Definian leveraged its repository of Workday Critical-to-Quality (CTQ) checks, amassed from prior successful Workday Financials implementations, to identify data inconsistencies and shortcomings that would inhibit records from loading to Workday. These pre-validation checks were instrumental in ensuring data integrity in Workday while also allowing the client time for cleanup.

Correcting Problem Data

Supplier Contracts were also a source of great risk to the project timeline. These contracts were managed in a separate SaaS (Software as a Service) system as well as in several manual spreadsheets. The different sources of Supplier Contracts were largely unchecked and not in sync. Definian’s data profiling and analysis reports revealed where start and end dates mismatched the terms of the lease contract, which greatly plagued their initial load to Workday.  The client was able to clean up their legacy data with the help of Definian’s reports; subsequent loads had substantially fewer errors, which positioned them for success leading up to the Gold Build (Production).

Going Live

In just 7 months’ time, Definian spearheaded data conversion for 3 test cycles which amounted in a successful Workday Financials go-live.  The aggressive timeline and quick turnaround were possible because of Definian’s lengthy experience managing data risk and our ability to accelerate projects with our proprietary Applaud® software. Shortly after the go-live onto Workday Financials, the organization acquired another logistics company. Because of careful data migration, the IT infrastructure of this organization is positioned for their strategic goals and continued growth.  

One of the world’s largest medical centers aimed to consolidate their ERP systems across hospital branches onto a single, integrated data platform. The purpose was to improve patient care and to be equipped to handle higher patient volume by achieving the following: modernization and optimization of business operations, standardization of new acquisition onboarding, and the enhancement of analytical insight capability. They selected Oracle ERP Cloud as their new technological platform and engaged Definian to support the data conversion efforts. As one of the first healthcare systems to adopt the full Oracle ERP Cloud solution, the client faced many unique challenges; however, Definian’s methodology and Applaud® technology minimized the risk associated with data migration and paved the way for a successful implementation.

Freeing the Hospital to Focus on Validation

The client was initially responsible for loading data into Oracle Cloud, but did not have knowledge of how to execute Oracle loads nor how to perform analysis of the accompanying load logs. Recognizing Definian’s experience in loading data to Oracle Cloud for other large organizations, the client asked Definian to perform data loads in addition to the other data conversion responsibilities. This lifted significant cost from the client, allowing them to focus on data validation and testing in the Oracle Cloud environment.

High Data Volume

Conversion requirements stated that a large volume of historical data needed to be migrated for auditing purposes. Definian worked with the client to outline strategies to best address the large data volumes. From a conversion standpoint, we grouped and aggregated historical data by the month or the year using our Applaud® software, thereby decreasing data volume and easing validation efforts. For production loads, Definian worked with the client to create a schedule that ensured most of their data would be loaded prior to Cutover, allowing more time for urgent tasks closer to go-live. Definian also optimized the load processing times by tweaking Oracle’s standard settings, which improved load efficiency by up to 500%.

Master Data Management

Supplier and customer addresses from the client’s legacy systems were inconsistently formatted and contained user-input errors. Definian’s address standardization process normalized and validated addressed while providing audit trails, ensuring transparency every step of the way.

All master data (Customers, Suppliers, Items, and more) was loaded into Production at least three weeks prior to their corresponding transactional data (Sales Orders, Purchase Orders, Invoices, and more). During this transition period, all changes in the legacy systems also needed to be made in Oracle ERP Cloud. Using Applaud®, Definian developed conversion programs that accounted for both newly created and modified master data to ensure all transactional data would be aligned with existing data in the Oracle Cloud Production environment.

Project Risks and Complexities

Due to the COVID-19 pandemic, we conducted the Cutover data conversions virtually. Definian consultants continued to maintain regular communication with the client and we addressed all data-related issues as they arose.

The client determined that they needed to convert Purchase Order (PO) Receipts, despite recommendations from vendors to avoid this conversion due to validation complexity. Definian took on the challenge and minimized load errors by using Definian’s Oracle Cloud Critical to Quality (CTQ) pre-load validation checks, reporting validation issues before loads took place. Definian also optimized the PO Receipts conversion process by utilizing inheritance riles to pull values from the corresponding Purchase Order, which minimized the risk for mismatching POs/receipts and significantly decreased the volume of load errors.

As a research hospital, the client uses the Oracle Cloud Grants module to manage the finances of research grants and awards. The client’s awards data requirements necessitated significant manual updates/intervention, so they would not be handled with programmatic conversion like other data objects. We instead leveraged our technical support team to develop webservices that efficiently updated award records and ensured that the awards data met the client’s initially specified requirements.

Key Activities

• Identified and documented requirements for converting data to Oracle ERP Cloud.
• Identified, prevented, and resolved problems before they became project issues.
• Facilitated discussions between the business stakeholders, the functional team, and the load team to coordinate requirements and drive data issues to completion before each test cycle and Go Live.
• Quickly adapted to changing data conversion requirements and proactively identified and addressed downstream implications, keeping the project on schedule.
• Harmonized, cleansed, and restructured Supplier data through Definian’s deduplication process; only clean, unique records were converted to Oracle. Standardized and cleansed Customer and Supplier Addresses; additionally provided clear reporting to business stakeholders.
• Definian’s Oracle Cloud Critical to Quality (CTQ) pre-load validation checks identified load errors prior to executing Oracle data loads. This early-stage resolution enabled efficient data loads to fit into the tight Cutover window.
• Utilized robust data profiling and error reporting to assist business stakeholders with data cleansing, data enhancement and data enrichment prior to Go-Live.
• Fully automated all data transformation activities, removing error-prone manual steps from all processes; ensured consistent and predictable output.
• Tracked records throughout the data transformation process to assist with post-project audits.
• Loaded over 18 million data records into Oracle Cloud while meeting cutover deadlines and maintaining high data quality.
• Provided project management services to ensure data migration was completed on-time and within budget.
• Generated reports through Applaud® to aid in validation of finances and to ensure accounting tied out.
• Provided detailed tasks in project plan to ensure every step in cutover was accounted for.
• Developed webservices to efficiently update large volume of awards and projects that otherwise would have required manual updates by the client.

A Proudly Home-Grown Solution

Our steel manufacturer client proudly designed their own Supply Chain software solution decades ago. Everything involving requisitioning, manufacturing, and sales was handled in a custom solution they built on mainframe servers. Occasionally, I would stumble upon decades-old comments between lines of COBOL code and be reminded that this is a home-grown, one-of-a-kind system.  For example, a happy anniversary message to my colleague's mom and dad 20 years ago was among the many lines of code I reviewed. These happy notes aside, this legacy database posed serious challenges to the transformation project because of how different it was from the target system, Microsoft Dynamics 365.

The Challenge of Missing Data

We suffered from a total absence of certain master data in the legacy system. Missing data in the system posed challenges to the project and called into question the feasibility of data migration.  To explain how this business could operate without master data, they intentionally built their software solution around transactions. This design allowed steel product to be defined dynamically by customer orders.  Additional specifications for finished products were derived from steel industry standards (ASTM) and physical, chemical, and mechanical properties.  The programmers of this custom mainframe manufacturing software recognized that they could carry out business without maintaining a Product Master!  Creatively, the legacy solution used attributes on work orders and sales orders to define the finished product.

Likewise, there was no master data for Formula, Routes, and Operations – these functional areas document the raw material and the processing steps needed to manufacture finished product for customer orders. The cold drawing, stress relieving, turning, grinding, and other cold finishing steps were driven by the deeply knowledgeable foremen on the plant floor and scheduled accordingly.  Effectively, there was no 'single source of truth' for several data objects that were needed in Dynamics 365.

Developing a Product Master from Transactional Data

Before we could go live in Dynamics 365, we first needed to create a Product Master.  Our conversion program considered 2 years’ worth of sales orders, work orders, and purchase orders. Dozens of fields across these tables were used to derive physical and chemical attributes of the product. After extensive research and data profiling, we grouped transactions on matching attribute fields and could confidently define a Product Master for each distinct combination or attributes.  95,000 sales orders, work orders, and purchase orders boiled down to 5,000 distinct products successfully converted to Dynamics 365.

Developing Formulas and Routes from Transactional Data

To standardize manufacturing processes, we were also tasked with creating master Formula and Route data for Dynamics 365.  Surprisingly, most of the metalworking instructions we needed for conversion were non-existent in the system.  We found that only a few weeks' worth of scheduling data was retained at any given time.  To overcome this gap, the business restored 2 years' worth of scheduling data from tape backups, and we used these historical backups to extrapolate the Formula, Route, and Operations.  In addition to historical scheduling data, we worked with manufacturing experts to understand undocumented complexities in their manufacturing process. For example, certain steps needed to be structured inline (like when steel passed directly from a turning machine to a grinding machine) and others needed to be discretely broken out for capacity planning.  Also, certain operations were structured as alternates to give the buisness greater flexibility in the manufacturing process.  As a result, the business now has a source of truth for the plant floor that does not rely on tribal knowledge.

A Brilliant Legacy System Unraveled

Reflecting on the experience, I recognize that the legacy database and software was the result of over 20 years of ongoing development by loyal, lifelong employees.   The clever and unconventional software this group created contributed to the cold finishing for millions of pounds of steel.  Very likely, I have driven in a car, or been in a skyscraper, or flew in an airplane made with steel that at some time was a data point in this AS/400 mainframe server. I am humbled to have been called to help this business maintain their leadership position in the global supply chain.  Together we unraveled this steel mill’s  one-of-a-kind solution and migrated it onto a system that will carry the business forward for the next 20 years

Client:  $17B Privately Held Diversified Automotive Company

After implementing Workday HR solution, our client encountered difficulties in effectively tracking the usage of Workday reports and managing inbound/outbound HR-related datasets. Consequently, there was little insight into user adoption, poor visibility into potential data misuse, and a continued lack of data oversight. To address these issues, our team embarked on a mission to streamline their data management processes and enhance data governance.

Assess and Improve Data Governance Operations:  Definian's Data Governance-in-a-Box approach assessed current data governance processes, operational model, and requirements.  The assessment created a roadmap to quickly meet the immediate needs and set the groundwork to more easily incorporate future requirements.

Integrated Workday, Collibra, and Purview Metadata: Leveraging Definian's MetaMesh technology, we designed and integrated the metadata across Workday, Collibra, and Purview. This enabled the unified management of Tableau reports, Workday reports, diverse datasets, and other data products across the enterprise.  Driving clarity and trust within our Client's data

Workflow Design and Implementation: Our team designed and implemented robust workflows aimed at governing the report certification process. These workflows were pivotal in ensuring that data shared both internally and externally adhered to stringent data-sharing agreements.

One of the largest technical hurdles that we worked to overcome was how Workday limited the extraction of metadata necessary to meet our Client’s requirements.  To overcome those constraints, we strategically extended the functionality of native Workday reporting. This extension enabled MetaMesh  to extract the essential metadata required for effective tracking and governance of their reports and datasets.

Through integrating Collibra, Workday, and Purview, designing governance workflows, and innovatively extending the capabilities of Workday reporting, we were able to build clarity and trust around the HR reporting functions for our Client. Our solution not only facilitated efficient tracking but also bolstered their data governance framework, ensuring adherence to regulations and optimizing data utilization.

This initiative signifies our commitment to crafting tailored solutions that overcome system limitations and propel organizations towards effective data governance and management. At every step, we remain dedicated to empowering businesses to harness the full potential of their data assets.

Client: State Government with Antiquated Legacy Data Landscape

Background

The state is running across nine disjointed, antiquated, and disparate ERP applications for HCM and finance across 70+ agencies.  Faced with a series of evolving business requirements, increased maintenance costs, loss of technical mainframe knowledge, and difficulty hiring/keeping staff that is willing to use these old systems, the State cannot successfully run its business.  In order to improve their operations and address these issues, the State embarked on a transformation initiative that will consolidate their business processes and data into a single instance of Workday.

Project Challenges

• Mainframe with missing and inaccurate COBOL copybooks
• Lack of technical resources that understand mainframe data structures
• Data issues from lack of data validations and field re-purposing over time
• Data integrity issues across legacy Benefits and HR applications
• Compressed timelines caused by budget constraints

Key Solutions Summary

• Data migration software that automatically accesses raw EBCDIC data without programming
• Automated processes that identify copybook and EBCDIC data mismatches
• Automated profiling provides data statistics about field usage
• Data migration experts guide business resources through tight timelines, maintaining focus to meet the tight schedule.
• CTQ (Critical-to-Quality) validations ensure the data is Workday ready prior to each tenant build

Project Summary

A division of a multi-billion dollar global aerospace and defense company needed to consolidate their 14 different business data sources onto a single integrated platform. The purpose was to help modernize and optimize their business operations, streamline the on-boarding of new acquisitions, and improve analytical insights. They selected Oracle ERP Cloud as their solution and engaged Premier to support the data conversion requirements as data cleansing and harmonization were a critical concern.

“Your ‘can do‘ attitude really helped keep things moving and your ability to organize 35 simultaneous conversions was pretty amazing!” – PTM Lead

Project Risks and Mitigation Strategy

• The client was utilizing a homegrown ERP system to manage their manufacturing data. Without client technical resources, Premier worked with the business to compare the front-end with the back-end data structure and to determine the data mapping required for Oracle ERP Cloud
• A large portion of legacy data was maintained across numerous spreadsheets. The manual nature of the legacy data resulted in mistyped, incomplete and incorrect information. The invalid data scenarios were identified as part of Applaud’s comprehensive error reporting, providing the Client data confidence well before Go Live.
• Supplier and customer data was duplicated both within and across the 14 data sources. Additionally, the existing data did not meet Oracle’s data requirements or structure. Premier deduplicated and restructured both suppliers and customers as part of the automated conversion process, ensuring only unique suppliers and customers were brought forward into the new Oracle solution.
• Existing supplier and customer addresses consisted of unverified free text with no internal consistency. Premier’s address standardization process standardized and validated addresses while providing audit trails, ensuring transparency every step of the way.
• A major source of duplicated customer data was driven by complex and inconstant legacy contract pricing business processes. Premier’s de-duplication engine identified, consolidated and harmonized these inconsistencies to allow the client to gain insights to the real overall customer spend.
• The existing General ledger and Oracle ERP Cloud ledger store transactions at different precision levels. Premier created detailed reports to help the business quickly isolate and address the resulting imbalances within each accounting period.
• Business requirements for On Hand Inventory serial numbers varied greatly between legacy and Oracle solution. Premier developed the conversion such that all On Hand Inventory was split into individual quantities associated with each serial number to bridge the gap without losing sight of the assigned serial numbers.
• All master data (customers, suppliers, etc) was loaded to production four weeks prior to transactional data (sales orders, purchase orders, etc). During this transition period, all changes in the legacy systems needed to also be made in Oracle ERP Cloud. Premier’s delta processing reported both newly created and changed master data to ensure Oracle Cloud remained up to date in the interim.

‍

Key Activities

• Identified and documented requirements for converting data from up to fourteen legacy sources, including unsupported homegrown solutions, to Oracle ERP Cloud.
• Identified, prevented, and resolved problems before they became project issues.
• Facilitated discussions between the business, the functional team, and the load team to coordinate requirements and drive data issues to completion before each test cycle and Go Live.
• Quickly adapted to changing requirements and proactively identified and addressed downstream implications, keeping the project on schedule.
• Harmonized, cleansed, and restructured customer and supplier data through Premier’s flexible de-duplication process such that only clean, unique records were converted.
• Standardized and cleansed addresses while providing transparency to the business.
• Utilized robust data profiling and error reporting to assist the business with data cleansing, data enhancement and data enrichment prior to Go Live.
• Fully automated all data transformation activities, removing error prone manual steps from all processes and ensuring consistent and predictable results.
• Tracked records throughout the data transformation process to aid with post-project auditing. Every record was traceable each step of the way from legacy to target.
• Identified changes made in legacy during the cutover period, enabling the business to account for these changes prior to Go Live and eliminating dual maintenance concerns.

The Results

From unraveling a homegrown ERP system to cleansing duplicate data, Premier proactively identified and resolved all data issues well in advance or go live, supporting a smooth transition to the Cloud. Premier improved overall data quality with an early focus on data and through a combination of data harmonization, address standardization, and pre-validation error handling. Premier’s detailed record tracking provided the business with everything they needed to support post-project audits and ensured everyone knew exactly how every scenario was handled. Premier’s comparison reports eliminated the concerns of dual maintenance during the four-week cutover period, providing peace of mind that everything was captured, and nothing was missed.

By engaging Premier’s data migration services, the client was able to achieve a smooth and successful Go Live on time and on budget. With 100% of converted data successfully loaded and verified, the business could begin leveraging their new Oracle ERP Cloud solution from Day One.

‍

The Applaud® Advantage

To help overcome the expected data migration challenges, the organization engaged Premier International’s Applaud® data migration services.

Three key components of Premier International’s Applaud solution helped the client navigate their data migration:

1. Premier’s data migration consultants: Premier’s services group averages more than six years of experience working with Applaud, exclusively on data migration projects.
2. Premier’s methodology: Premier’s EPACTL approach to data migration projects is different than traditional ETL approaches and helps ensure the project stays on track. This methodology decreases overall implementation time and reduces the risk of the migration.
3. Premier’s data migration software, Applaud®: Applaud has been optimized to address the challenges that occur on data migration projects, allowing the team to accomplish all data needs using one integrated product.

Project Summary

After a 100 years of international growth, a privately held materials company was ready to take the next step forward by modernizing their business. This modernization effort consisted of streamlining their business processes and implementing a series of Best in Breed SaaS applications. Central to this transformation was the consolidation of their multi-national business units from JD Edwards (JDE) into Oracle’s HCM, Supply Chain Management, and Financials Cloud applications.

Recognizing both the importance and risk that data posed to the implementation, Definian was brought in to facilitate and execute the data migration process. To consider the implementation successful, Definian needed to ensure that as part of the migration, the data would be in a state that enabled the business to scale and operate within a complex application landscape.

‍

“ You made the data conversion track look easy and fluid despite all of the complexity and effort you put in place behind the scenes.”​

– Technical Team Lead

​

Project Risks

In order to ensure that the data would be delivered on time, scalable, and interoperable, there were a number of risk factor that needed to be overcome:

• The large number of integrations between non-Oracle systems meant that data quality and transformations needed to meet Oracle’s functionality requirements and the requirements of all downstream applications. The complex and new business application landscape drove frequent business requirements changes throughout the course of the implementation.

• The complexities of the integrations also impacted configuration requirements. As the configurations for integrations were overhauled with massive changes, the data conversions were forced to follow suit.

• The tight project timelines and budget did not have sufficient leeway for changes in scope. Many HCM related modules had fluctuating requirements and scope definitions. These scope changes, along with the unexpectedly high number of changes from integrations, needed to be addressed in extremely efficient manners

• Legacy JDE customer and vendor structures required a high degree of data transformation and enrichment to meet the Oracle Cloud and additional applications business requirements and structure.

• Complex survivorship and harmonization rules for duplicate vendors across the international business units needed to be established and traced.

• Data sources for HCM-related conversions had different combinations of identifying information—names (combined and split), SSNs, dates of birth, etc.—which complicated the connecting HCM employee, beneficiary, and dependent datasets.

• Client SME lead and functional expert turnover complicated business rule definition and decision-making process as established business requirements were questioned by changed resources.

Key Activities

Definian was able to mitigate these risks through a combination of our expert consultants, our EPACTL approach to data migration, and our proprietary data migration software, Applaud®.

• Through the business rule driven analysis and transformation features of Applaud®, Definian adapted to changing requirements and identified key downstream implications.

• Definian facilitated discussions between legacy and target application users to coordinate requirements, using a combination of our Oracle knowledge base, data expertise, and data profile reports.

• Applaud enabled connectors with Oracle Cloud identified configuration gaps as the pod was being configured, well before data conversion loads. These pre-emptive validations enabled the team to get ahead of configuration issues before they became problems.

• Definian quickly integrated approved changes into the data transformation processes. Specialized testing scenarios were developed to minimize the runtime of these repeatable processes, which ensured conversion enhancements worked as expected.

• Applaud’s® robust error handling and record tracking features accurately tracked and maintained reports for post-project auditing. These reports were especially critical for managing the complex customer and vendor data transformations.

• Applaud’s® de-duplication processes consolidated and re-structured vendors (with their subsequent datasets, such as sites, addresses, and banking information) across business units. Additionally, USPS address standardization was used to harmonize US addresses.

• Various complex data transformation rules reduced risk amongst HCM-related conversion objects and tied together employees, beneficiaries, and dependents across several disparate legacy sources. De-duplication rules prevented duplicate contacts between beneficiaries, dependents, and emergency contacts.

• Definian’s project management methodology ensured that despite the shifting resources on the project, all team members were quickly brought up-to-date on previous decisions regarding selection logic and data transformation. This helped ensure a smooth transition onto the project with limited time required to inform new resources about existing, complex conversion processes.

The Results

Despite the resource turnover and numerous requirement changes within a narrow project timeline, Definian was able to work in coordination with the implementation partner and end client to ensure a successful, uneventful, and on-time go-live.

​

The team was able to leverage Applaud’s® specialized capabilities to adapt to ongoing requirements changes (over 300 during the final two months of the project). Definian’s methodology and experience facilitated the proactive identification and resolution of data gaps caused by requirements changes, environment configurations, and data quality before problems developed.​

This resulted in a 100% of the expected records loading successfully into the Oracle Cloud applications, data that allows the business to grow, scale, and flow through their complex application ecosystem.​

The Applaud® Advantage

To help overcome the expected data migration challenges, the organization engaged Definian International’s Applaud® data migration services. Three key components of Definian International’s Applaud solution helped the client navigate their data migration:

1. Definian’s data migration consultants: Definian’s services group averages more than six years of experience working with Applaud, exclusively on data migration projects.
2. ‍Definian’s methodology: Definian’s EPACTL approach to data migration projects is different than traditional ETL approaches and helps ensure the project stays on track. This methodology decreases overall implementation time and reduces the risk of the migration.
3. ‍Definian’s data migration software, Applaud®: Applaud has been optimized to address the challenges that occur on data migration projects, allowing the team to accomplish all data needs using one integrated product.​