Microsoft Dynamics data expertise

Assess the Landscape

Start planning the data effort by assessing what you have today and where you are going tomorrow. While frequently glossed over, spending time assessing the landscape provides three prime benefits to the program. The first benefit is that it gets the business thinking about the possibilities of what can be achieved within their data, by outlining what data they have today and what data they would like to have in the future. Secondly, this exercise gets the organization engaged on the importance of data and instills an ongoing master data management and data governance mindset. The third benefit is that it enables the team to start to seamlessly address the major issues that programs encounter when assessment is glossed over.

• Create legacy and future D365 data diagrams that capture data sources, business owners, functions, and high-level data lineages.
• Outline datasets that don’t exist today that would be beneficial to have in D365.
• Ensure the future D365 landscape aligns with data governance vision and roadmap.
• Reconcile legacy and future state data landscapes to ensure there are no major unexpected gaps.
• Perform initial evaluation of what can be left behind/archived by data area and legacy source.
• Perform initial evaluation of what needs to be brought into the new D365 based landscape and reason for its inclusion.
• Profile the legacy data landscape to identify values, gaps, duplicates, high level data quality issues.
• Interview business and technical owners to uncover additional issues and unauthorized data sources.

Create the Communication Process

Communication is a critical component of every implementation. Breaking down the silos enables every team member to understand what the issues are, the impact of specification changes, and data readiness. (Read about the impact of siloed communication.)

• Create migration readiness scorecard/status reporting templates that outline both overall and module specific data readiness.
• Create data quality strategy that captures each data issue, criticality, owner, number of issues, clean up rate, and mechanism for cleanup.
• Establish data quality meeting cadence.
• Define detailed specification approval, change approval process, and management procedures.
• Collect the data mapping templates that will be used to document the conversion rules from legacy to target values.
• Define high-level data requirements of what should be loaded for each instance. We recommend getting as much data loaded as early as possible, even if bare bones at first.
• Define status communication and issue escalation processes.
• Define process for managing RAID log.
• Define data/business continuity process to handle vacation, sick time, competing priorities, etc.
• Host meeting that outlines the process, team roles, expectations, and schedule.

Capture the Detailed Requirements

Everyone realizes the importance of up-to-date and comprehensive documentation, but many hate maintaining it. Documentation can make or break a project. It heads off unnecessary rehash meetings and brings clarity to what should occur versus what is occurring.

• Confirm the legal entity structure and the planned usage of data sharing functionality.  This will have greatest impact on how the data needs to be prepared.
• Collect the full list of data entities that need to be populated for each legal entity in D365.  Ensure the correct version of each entity is carefully considered.
• Customers V2 and Customers V3 are not the same.
• Document detailed legacy to D365 data mapping specifications for each data entity and incorporate additional cleansing and enrichment areas into data quality strategy.
• Incorporate when Microsoft quarterly releases will occur in each instance into the project schedule to ensure any updates to data entities are identified and are reflected in the mapping specs, conversion programs, etc.  At least two releases must be accepted every year.
• Be cautious when scheduling test cycles or go-live around accepted quarterly releases.
• Document system retirement/legacy data archival plan and historical data reporting requirements.

Build the Quality, Transformation and Validation Processes

With the initial version of the requirements in hand, it's time for the team to build the components that that will perform the transformation, automate cleansing, create quality analysis reporting, and validate and reconcile converted data. To reduce risk on these components, it's helpful to have a centralized team and dedicated data repository that all data and features can access.

• Create data analysis reporting process that assists with the resolution of data quality issues.
• Build data conversion programs that will put the data into the D365 data entity format.
• If necessary, review D365 documentation for any unexpected findings when working with data management delivered imports.
• Incorporate validation of the conversion against D365 configuration from within the transformation programs.
• Enable pre-validation reporting process that captures and tracks issues outside of the data management imports.
• Define data reconciliation and data validation requirements and corresponding reports.
• Create exports in data management to extract migrated data out of D365 to streamline validation and reconciliation process.
• Verify data quality issues are incorporated into the data quality strategy.
• Confirm any delta or catch-up data requirements are accommodated within the transformation programs.

Execute the Transformation and Quality Strategy

While often treated separately, data quality and the transformation execution really go hand-in-hand. The transformation can't occur if the data quality is bad and additional quality issues are identified during the transformation.

• Ensure that communication plans are being carried out.
• Capture all data related activities to create your conversion runbook/cutover plan while processes are being built/executed.
• Create and obtain approval on each D365 load file.
• Run converted data through the D365 import process.

Validate and Reconcile the Data

In addition to validating D365 functionality and workflows, the business needs to spend a portion of time validating and reconciling the converted data to make sure that it is both technically correct and fit for purpose. The extra attention validating the data and confirming solution functionality could mean difference between successful go-live, implementation failure, or costly operational issues down the road.

• Execute data validation and reconciliation process.
• Execute specification change approval process per validation/testing results.
• Obtain sign-off on each converted data set.

Retire the Legacy Data Sources

Depending on the industry/regulatory requirements system retirement could be of vital importance. Because it is last on this checklist, doesn't mean system retirement should be an afterthought or should be addressed at the end. Building on the high-level requirements captured during the assessment. The retirement plan should be fleshed out and implemented during throughout the course of the project.

• Create the necessary system retirement processes and reports.
• Execute the system retirement plan.

Following this checklist can minimize your chance of failure or rescue your at-risk D365 implementation. While this list seems daunting, rest assured that what you get out of your D365 implementation will mirror what you put into it. Time, effort, resources, and – most of all – quality data will enable your strategic investment in D365 to live up to its promises.

What is Asset Management Data and Why is it Critical?

Enterprise Asset Management (EAM) involves the maintenance of an organization’s assets.  EAM data includes information on physical assets, their locations, maintenance activities, and items needed to support maintenance, repair, and operations (also known as MRO).

Accurate EAM data is critical is to make sure assets for the business are recorded correctly. Among other things, high quality data ensures that the business can purchase the right goods to maintain and repair equipment and operations throughout production. It is important to manage the master level data and the data that falls under it so that the accessibility of a specific asset can be easy to search and to proceed with the necessary precautions for their business to thrive.

Choosing Dynamics 365 for Asset Management

This steel manufacturer chose Dynamics 365 as their Asset Management solution so that they could better support and operate their business in a tech-savvy and flexible environment. Innovations in process automation available in Dynamics 365 will accelerate business activities and increase efficiency of maintenance activities. Dashboards makes it possible for the buisness to discover patterns and trends relating to asset management and drives insights that ultimately help the business minimize costs, maximize profits, and grow the business.

Reverse Engineering the EAM Solution

Due to complexities in the legacy data and stringent requirements in Dynamics 365, the System Integrator for the project suggested a manual approach for entering EAM data.  The manual approach would require multiple rounds of time-consuming data entry for the business.  Worse still, the data entry activity would land on the critical path of the project and detract from other critical activities. Fortunately, this steel company has Definian as their data migration partner, and we knew there was a better way.

Initial EAM data loads into Dynamics 365 was a challenge. The business and the system integrator needed Definian’s supports to connect the dots between legacy data structures and the target formats in Dynamics.  After running into load errors and discovering missing configuration, the Definian team decided to reverse engineer data structures in Dynamics 365 and build the conversions based on this analysis.  This approach included manual entry of test data into Dynamics 365, exporting back out the test data, and trial and error adjustments throughout the process. The Definian team first went into a test load environment and exported over 120 data entities related to Asset Management. After this exhaustive process, we analyzed and reviewed every exported data file to uncover connections to in-scope fields. This enabled us to find missing data entities and data configurations that needed to be loaded to minimize load errors and issues. We identified 15 entities and configurations that helped to avoid the load issues we initially encountered. We shared our findings with the system integrator and closed remaining gaps through default values approved by the business.

Leveraging Unconventional Data Sources

The business was able to provide us spreadsheets outlining an Asset Hierarchy for each specific site. Data included main locations, main assets, child assets, instruction list for specific activities to be conducted, and more. We built logic into the import application to analyze the asset hierarchy and greatly reduce the amount of time the client needed to spend on the spreadsheet.  This exercise helped set the foundation of for how this Asset Hierarchy would be displayed. The initial design would have been a challenge, very time consuming, and ultimately would have delayed tests and validations.  Our agile handling of this new data source minimized rework for the client.  Because of the time we saved, we strategically expanded scope to include MRO Product Items and Asset BOM (Bill of Materials) data entities to better serve growing client needs.

Surprise Challenges

Surprise challenges emerged while loading EAM into Dynamics 365.  While loading Functional Locations and Assets, there was an issue where the unique assets were duplicating in the Asset View page. Although the correct asset appeared with its unique Maintenance Asset ID, duplicate child data appeared with no information besides the Asset Name. Thanks to the speed at which we could load and validate data, this was quickly identified and resolved.

To minimize further unwanted surprises, validation steps were incorporated into the conversion process to ensure that spreadsheets inputs would not cause errors during the creation of the data extracts. Some of these validation checks included making sure a child asset level was not skipped, and that the asset hierarchy was logically consistent. Other validations checked against Dynamics 365 configuration, uniqueness constraints, and associations with related modules.

Success Today and for Years to Come

Definian's data migration approach made it possible to go live with high quality data. Since go live, the client is now able to use the full suite of Microsoft Dynamics's features, including many that will reduce maintenance costs and preemptively solve production problems.  In addition, the facility now has a reliable source of truth for Enterprise Asset Management data, compared to the problematic data contained in their outdated previous system.  The new cloud solution will serve the business for many years to come.

A dramatic redesign of the Item Master proposed

Global supply chains have changed dramatically over the last few decades and constant innovation is required to keep the American steel industry competitive.  Strategic investments in Enterprise Resource Planning (ERP) software is one way that our client – a Fortune 500 steel manufacturer – is extracting more value from the same raw materials.  As part of a global rollout of Dynamics 365, the Item Master for a particular cold finishing mill required a dramatic redesign.

One of the main objectives of this initiative was to build a true item master across the entire line of business to improve their reporting.  This was defined as a collection of 21 individual attributes including shape, size, chemical composition, length, and others.  Any distinct combination of values for these 21 attributes represented a unique item.

One piece of the puzzle

This cold finishing mill represents only one of the dozens of divisions implementing Dynamics 365.  This steel company is vertically integrated – steel cast at one mill may ultimately find its way into this cold finishing mill where it can be turned, ground, rolled, chamfered, or annealed.  A goal for this global implementation is to integrate the Enterprise Resource Planning (ERP) systems across the divisions to streamline steel production and finishing.  That said, implementing Dynamics 365 for this cold finishing mill required meeting the requirements and matching the conventions set by divisions already live in Dynamics 365.  This mill was just one piece of the puzzle.

The sites that had already successfully migrated to D365 were all made to stock and migrated off a common legacy system.  This made the process efficient and repeatable; however, this division practiced a ‘made to order’ business strategy, meaning all material was made based on the specifications defined by the customer.  This meant the functional business requirements were going to differ vastly from the already live divisions and need to be fluid enough to accommodate a wider range of customer demands.

Overcoming documentation gaps

Decades ago, the mill purchased mainframe servers and leveraged in-house knowledge to build their supply chain management system from scratch using COBOL.  The truly one-of-a-kind solution that ran this mill helped them maintain operational excellence and profitability through turbulent years for the American steel industry.  Today, however, the hardware is at risk of failing, the software cannot scale, and the talented resources that built the legacy system are nearing retirement.  Worse still, the documentation for this complex system is sometimes buried between lines of COBOL code, relegated to tribal knowledge, or is missing entirely.

The robust Dynamics operations solution requires more detailed data on the production of the finished products, namely formulas and routes.  However, this data did not exist in a structured fashion in the legacy system.  This required a much deeper dive by Definian and the technical team to understand how this data could be extracted and migrated to the new system.  

Reverse engineering:  How we used transactional data to define items

The unique operating model of this steel mill meant that there was no true item master in the legacy system.  Deriving an item master and having it integrate seamlessly with the other divisions became a colossal task in data analysis, cleansing, and conversion.

The process began with a review of all required transactional data.  Analysis of the data behind nearly one hundred thousand sales order lines allowed Definian to build the finished goods, hundreds of thousands of lines of production scheduling to create WIP parts, and years of purchase history to derive the raw materials.

However, the redesign did not end with the Item Master. With the item master was now complete, the formula and route data was also needed.  The team faced similar challenges here true master data did not exist in the legacy system for formulas and routes. Read more about how we solved these challenges here.  Adding to this complexity, were customer specifications for highly regulated industries and critical applications that we discuss further here.

Success Today and for Years to Come

Since go live, the client is now able to identify the specific products they buy and sell, while still delivering the same experience customers have been accustomed to for years.  In addition, the facility now has a true accounting of their production processes, something that was previously confined to the minds of a few planners.  And finally, with such a drastic modernization of their technology, the client location is finally able to report on an enterprise level, rather than the silo they operated on for decades.  

If you have questions about data management, data quality, or data migration, click the chat bubble below. I'd like to learn about them and work with you to figure out solutions.

A Project in Jeopardy

User Acceptance Testing (UAT) is typically considered a dress rehearsal for Go-Live and should result in minimal change requests.  But this was not your typical UAT. During the final weeks of the project a major gap in critical-to-customer data was discovered that jeopardized this steel mill’s entire Microsoft Dynamics 365 implementation.

User Acceptance

As the name suggests, a successful UAT depends on the end users accepting the results of the conversion test cycle.  To guarantee a successful go-live, each subsequent test cycle includes more end users and experts than the one that preceded it.  For this final round of testing, the expert resource on steel certifications discovered that many Customer Specifications were missing and could not be entered manually.  This surprise finding altered the course of our Dynamics 365 implementation, and all resources resolved to find a solution.

Customer Specifications

In a nutshell, Customer Specifications (specs) include the chemical, physical, and mechanical properties that customers need displayed on the steel certification when they receive the product.  Customer specifications are applied to materials that will eventually find their way into products where failure would be disastrous.  For example, if a steel bar does not meet straightness requirements, then hydraulic cylinder applications (as with those on heavy construction equipment) would fail and endanger workers.  We have faith in the structural integrity of skyscrapers and the reliability of cars and heavy equipment because manufacturers strive to meet strict industry standards (like ASTM) and additional customer specifications for critical applications.

Commitment to Customers

By meeting customer specifications, the steel mill demonstrates their commitment to customer needs and delivers on a promise to provide the highest quality steel needed for each application.  When UAT began, we were already migrating 5,000 such specifications.  Weeks after the UAT loads, our client, one of the largest steel manufacturers in the US, informed us that there was something odd with the data.

Piecewise Uncovering of New Information

This had become a recurring theme – a gradual, piecewise uncovering of new information revealing how their heavily customized legacy AS/400 (mainframe) system operates. The client’s homegrown system afforded countless operational benefits over several decades of use but created challenges for today’s implementation.  The downside with non-standard systems like this is that data management is incredibly difficult.  Executing a successful conversion required Definian’s project management methodology and the technical capability, data profiling, and rapid application development available through Applaud® software.

Enhanced Conversion Program Delivered

The total count of converted Customer Specifications jumped from 5,000 to over 25,000 just weeks before go-live. Given the short timeframe and quick turnaround, our herculean efforts to expand conversion scope were met with praise from both our client and our partners. A combination of end user due-diligence, expert project management, technical consulting, and the Applaud software streamlined development of the Customer Specification conversion program enhancements.  We celebrated this victory and continued loads to Dynamics 365 for testing and validation. Little did we know that an even more daunting task awaited us.

Recreating Custom Backend Code

We were told, yet again, that certain specs were missing. This time, even after extensive data spelunking, no information about the specs could be retrieved in the presumed Customer Product Master table or any other master data tables in the mainframe database. We were at an impasse – how could customer specs exist while no information about them is present in the database? The client revealed that they implemented backend COBOL logic in the AS/400 mainframe that dynamically creates specs directly from sales orders.  To close this gap Definian was asked to mimic the backend logic and create item-level specs from transactional data.  Since this master data did not exist in the mainframe, we needed to dynamically create it through conversion code.

Rising to the Challenge

The challenge of dynamically creating master data from transactions did not discourage us.  Because of Definian’s commitment to client success, this challenge could not phase us. It is exactly moments like this where we rise to the occasion and leverage the power of Applaud to make the impossible a reality. Definian’s experience in complex situations like this empowered us to gather the necessary requirements and turn around a new Customer Specs load file in a single weeks' time. The final record count had skyrocketed to roughly 62,000 – an 1100% increase from the UAT record count.

Reflecting on Our Success

This is one of the many stories from this Microsoft Dynamics 365 implementation where Applaud enabled us to make complex conversion changes quickly and keep the project timeline on track.  In ambitious projects like this, requirements are expected to evolve over time. But when complex changes are requested late into an implementation, it introduces risk into the conversion process.  I credit our success on this implementation to Applaud and its unique ability to rise to even the most complex technical challenges and minimize data migration risk.

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