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.


Integrated Supply
Chain Management
- 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
Background
Challenges
Logexsoft 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
Logexsoft’s Approach
- 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
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

Client Benefits
- 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:
- how their business is performing
- how profitable their product lines are
- if customers are making repeat purchases
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:
- historical data can show the business trends
- sales records can identify valuable customers
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.
References
Client references available upon request