Retail Sector
Background: This client is the largest retailer in Ireland with a turnover in excess of 3 billion euro. The main processing system is a in-house software system built on Ingress. This system lacks all the functionality required by today's retailing systems, such as statistical reports, orderable quantities, sales assortments and special concentration of stock loads, and placing the best products in each store based on its segment.
The client has purchased the GOLD software application from Aldata to update their information to be in line with industry standards. The GOLD application is a unix based oracle back-end with completely new data structures and referential data requirements which literally turns their current data inside out and back to front again. It is a vast project and has a two year life cycle. PEC Consulting are technical lead on the data migration from ingress to GOLD.
The project has been divided into seven distinct phases with processing performed in SQL server, with DTS being the main driver of all migration tasks.
Phase 1 (P1) Extraction: All data from source is loaded by group (supplier, product) into P1 tables. This is a "dumb" load and all data is loaded without any processing or transformation into tables that contain no restrictive data type settings.
Phase 2 (P2) Transformation: Tables are moved from P1 to P2 with a data type validation routine controlling what records make it to P2. Any failing records will be flagged with an indicator as to which field(s) caused the validation failure. Any records that fail to be moved to P2 are sent to cleanse tables and must be cleaned by data analysts and made available for reload (this is a development bonus in that data cleanse is identified at an earlier stage and data will be far more BRILLIANT for go-live, when all failures will result in a load stoppage)
The records on P2 are then subject to the transformation rules for that group, with the routines making use of SQL stored procedures and reusable function calls for all processing. Various log calls record the success of the transformation tasks. When P2 has completed the data is moved to P3.
P3 Holding area: The holding area's purpose is to act as a bridge between P2 and P4 tables. The P4 tables are specific to the target system and the P3 tables essentially hold this data ready for load. This would appear to be an overhead to the system but is a requirment of workflow. P3 moves the data to P4 and completes.
P4 Load to Target: The data is now sitting at P4 and is ready for the load to the target system. The target is oracle-based and the load is very basic, with any errors being handled once the data is loaded. The logs are then updated with the status and processing completes.
The structure of the system is designed to handle a massive audit and reconciliation between the source and target systems. The go-live date depends on this audit of the data running smoothly and any necessary changes being made.