How A Dedicated DataMart Can Enable & Enhance Supply Chain Management Infrastructure

// data management

How A Dedicated DataMart Can Enable & Enhance Supply Chain Management Infrastructure

Executive summary

Manufacturing transactional systems (i.e. ERP, MES, Quality) can be large, complex, and contain disparate data. The Logistics Data Mart is an ODS (Operational Data Store) designed to harmonize data from multiple sources for alternate operations on the data, such as reporting, real-time analytics, decision support operations and more. It supports rapid, adaptable responses to changes in a business environment.

Client:

CPG

Category:

Data management

// data management

Integrated Supply
Chain Management

I ntegrated Supply Chain Management (SCM) involves seamless linkage of various business functions and processes across and within companies. Success of an integrated supply chain depends on how good the data management of the organization is. Master data management and data administration gets more streamlined with the help of a data management system. A successful system ensures data accessed is cleansed, presentable, traceable, accountable and fast. It becomes easy to see how operations in one segment of the organization affects other segments of the organization. This case study talks about the learnings of a CPG company through this process and shows how they used a Logistics DataMart to deploy SCM solutions faster and with a higher reliability:

SCM systems cross reference ERP data with the need of planning & scheduling applications. As a result, sometimes changes need to be made to the ERP system to reflect reality, e.g BOM, Equipment changes etc.
ERP systems are not flexible enough to create decision making reporting on demand
Native SCM system databases are typically limited to only supporting the needs of the supply chain applications. The Logistics DataMart is configurable and can house data to support decision making in supply chain and additional areas in a matter of hours
Layer of abstraction between MES and ERP system ensures an additional layer of redundancy to reduce downtime due to ERP failures
Smart reporting for departmental views instead of enterprise views (subset of data) – speed, reliability & better decision making
Get data quickly, report issues quickly & make decisions quickly around optimizing available resources

Purpose

T his case study examines the establishment of an operational data store, by a manufacturing organization, as a means of enabling/enhancing existing supply chain management applications. The purpose of this proposed effort is to get the right data to supply chain management applications.

Background

T he organization referenced in this whitepaper is a Fortune 500 CPG company with annual revenues of more than 25 Billion USD, and just over 8,000 employees. Through multiple corporate acquisitions and mergers, this organization has found itself operating with multiple ERP, Scheduling, and MES systems along with numerous disparate standalone legacy databases for design & production data. Stakeholders engaged in this initiative came from the following departments, truly representing the entire supply chain: Demand planning & forecasting, Brand/Marketing/Product Commercialization, Production planning, Production scheduling, Materials/Warehouse management, Procurement/Supplier relationship management, Manufacturing production, Shipping & Distribution. Production Planning & Scheduling collaborated with Manufacturing Production to sponsor this initiative.

Challenges

Ashlaketech worked very closely with the client to identify the following business challenges that were being faced on a regular basis:

High direct material and finished good inventory costs
High rate of changeovers
Common master data, stored differently, depending on the source system (i.e. multiple ERP systems)
Business units spending large amounts of time to compile/aggregate/consolidate data between various systems
Each business unit was maintaining similar data via separate infrastructure (i.e. spreadsheets)
Inability to get real-time data from disparate systems
Inability to measure against KPIs for KWYA (Know Where You Are) Reporting

Key Challenges were categorized, elaborated into the following:

Data related challenges:

1. Data Accessibility – MES and ERP data were in two different, disparate systems and needed complex procedures to link the information together
2. Data Integration – Multiple ERP systems housed information with different material IDs to refer to the same material and it was difficult to link this together automatically
3. Data Integrity – Lab Information Management Systems (LIMS) test direct materials but is not linked to ERP systems which is the master data source for direct materials

Supply Chain challenges:

1. Data Accessibility – MES and ERP data were in two different, disparate systems and needed complex procedures to link the information together
2. Data Integration – Multiple ERP systems housed information with different material IDs to refer to the same material and it was difficult to link this together automatically
3. Data Integrity – Lab Information Management Systems (LIMS) test direct materials but is not linked to ERP systems which is the master data source for direct materials

// data management

Ashlaketech’s Approach

S upply Chain Systems are predicated on clean data input. It became evident that the solution was around Master data management issues and if those could be resolved, then the tools would get the right data that would yield good outputs with actionable information. After assessing the challenges, a proposal was submitted to create the Logistics DataMart (LDM). The intent was to provide a SQL Server DataMart to function as a clearing house for data going into and out of Supply Chain systems. The original LDM solution took 9 months to implement with a project team of 12 people (including business SMEs). Not only was the initial implementation very successful, but the organization has continued to invest in this infrastructure for nearly 20 years. Included below are some of the best practices and lessons learned:

Establish Data Governance board and enterprise data guidelines
Define a dynamic data model to allow product and process agnostic data storage across varied source systems
Configure a data gateway to manage ETL for source system input
Setup configuration layer to manage MDM and business rules engine
Configure system alerts for notifications of data errors
Facilitate the integration of Sales, ERP, Production Planning & Scheduling, MES, Warehouse Management & Distribution systems
Define approach for (& frequency of) layers of abstraction between transactional and reporting systems
Define access layer data strategies to partition exposition of data across multiple business units, with appropriate security
Define access layer data storage to ensure efficient reporting and analytical capabilities

Key Challenges were categorized, elaborated into the following:

Data related challenges:

1. Data Accessibility – Enable consolidation of data from multiple sources in one spot and role-based access capabilities
2. Data Integration – Integrate data to/from multiple disparate systems to support better communication between systems and business departments
3. Data Integrity – Establish single system of reference across multiple systems of records to ensure accuracy and consistency of data used throughout the enterprise

Supply Chain challenges:

1. Data Cleansing – While advanced supply chain tools were in place, clients struggled to get value from their tools due to poor data input. Master data was stored differently in the various ERP systems and needed to be cleansed before going into supply chain tools. Formal rules for master data management to ensure appropriate data cleansing policies prior to routing data to downstream applications/systems
2. Data Analytics/Visualization – Easy reporting due to harmonized and flattened data
3. Data Traceability – Auditable data records to show lineage and changes of data from multiple sources

Here is a consolidated view of the interactions between the different supply chain, ERP, MES & other systems that LDM interacts with:

Implementation

T he infrastructure was setup in a 3-environment configuration (Dev, QA & Production) to support parallel development, testing and support efforts as a part of the overall program. Additional disaster recovery, failure rollover & security was built in to the infrastructure to ensure that the information was appropriately access controlled and secure. Software Setup: Each environment had the following components:

SQL Server

.NET Framework

Email client

File transfer client

Additional, the dev environment had Visual Studio for development purposes.

The following diagram shows the high-level data flow that was used in the design of the DataMart. Each section is further explained below the diagram:

Source Systems

Multiple systems of record were referenced to extract information via flat files dropped into a drop point folder. Interfaces were written (governed by formal data contracts) to load the data into the metadata & landing sections

Metadata Area

This section contains master data or metadata required to support the loading and processing of data into the DataMart. E.g. BOM structure

Landing Area

This section is the initial entry point for the data loaded via SSIS packages from the source file. Data is retained in this section for a pre-defined period of time in case there are issues in the data load process

Staging Area

This section contains the transformation & harmonization of the individual data components prior to loading the data into the DataMart

DataMart

This is the final resting place of the harmonized data that will be eventually used to build views to support reporting

Data Access

These views are built to consolidate information from multiple sections of the DataMart to facilitate ease of reporting

Reports

This section contains ad-hoc and custom reports built to support reporting needs of the client

Others

Required to support the architecture and infrastructure requirements of the client

L ogistics DataMart is built on an enterprise architecture that is regularly backed up and follows enterprise data recovery & business continuity standards. Data is gathered from multiple data sources and harmonized so Supply Chain Management applications can focus on supply chain optimization with cleansed & structured data. Alerts and notifications are built into the DataMart to notify users of data anomalies & load failures. These notifications are very important from a support perspective to ensure that the supply chain models have the latest data they need on a consistent basis. LDM was built from the ground up as a customizable, configurable product that could be deployed easily at multiple organizations. This flexibility has allowed LDM to scale easily as the organization grows & expands into new businesses. The APIs to interface with external systems allow seamless integration across the board.

Client Benefits

V arious Supply chain decision making and optimization tools are used by the organization to improve their Supply chain processes. For these tools to perform at their best it is very important to have a good set of data provided as input to these systems. The availability of cleansed data combined with these tools transform data into usable information, empowers end users to make quick, informed decisions that can make the difference between success and failure in the global economy. The following benefits were realized by the client:

Data redundancy is reduced
Increases data consistency and Integrity while reducing data errors
Improved data access to users through use of host and query languages
Improved data security
Reduced data entry, storage, and retrieval costs
Analyze data in variety of ways
Increased end-user productivity

Conclusion

Many businesses do not have the time or resources available to gather and process large quantities of information. This may lead to lack of information about:

It’s not always obvious what information is potentially valuable, so there is a need to gather as much data as possible as it relates to supply chain processes. Using a sophisticated database management system allows to store data that builds up over time and can become increasingly useful and valuable. For example:

In short, a disciplined approach to gather, enter and process enormous amounts of data that is coordinated, cleansed, harmonized and verified. This can help ensure that supply chain solutions are deployed faster, run more efficiently with high reliability and ensure that the business runs in a regulated and properly managed manner.