GLOBAL RETAILER DATA WAREHOUSE INTEGRATION

ORGANIZATION

The Client is a global shoe retailer operating over 4,800 stores across the USA, Canada, Central America, the Caribbean, and South America. The company ofers a wide range of footwear and fashion accessories products by focusing on innovation and dynamic response.

The client has several warehouse facilities and supply chain supported by a Warehouse Management System (Manhattan WMS).

CHALLENGE

Prior to implementing a BI solution, the client users relied on operations reports being generated from the implemented WMS system. The lack of analytical depth in the operational reports as well as increased need for real-time insight to meet the imperatives of critical performance indicators from an operational standpoint led to this being considered a less than optimal scenario for the client. The need to gain real-time insight from transactional data and to facilitate efective and timely business decisions by leveraging the enterprise data in the WMS led to the chartering of an initiative for creating a separate data warehouse that would be populated by the various WMS sources.

The business was facing the following challenges:

• Reports could only be generated based on the prior 21 days of data. Monthly and yearly data analysis was technically not possible (in an automated manner) and was very difficult to implement in a manual manner.
• Historical data was not available online for analysis purposes
• The data in the WMS and WM (MS-Access) database could not be easily correlated with data in other client systems
• Business could only run the analysis and reports in a limited time slot during each day so as to not impact the DC transactions (system performance issues)

TECHNICAL LANDSCAPE

The technical landscape was characterized by the following:

• The data management process and the associated controls and governance were lacking
• Data was extracted using COBOL subroutines and transferred to an MS-Excel spreadsheet for analysis at end of each business day
• Reports were generated (on Crystal Reports) based on this data and sent via email to business users
• Production Summary Reports were created in MS Access and exported to MS-Excel for reviews
• The performance of the WMS system was pretty severely impacted from a transactions processing standpoint when reports were being executed
• Lack of historical data for analysis and reporting in WMS resulted in large operational gaps
• Issues were further compounded with data joins since the two different distribution centers resided on separate instances of the WMS application

SOLUTION

XTIVIA delivered a near real-time solution utilizing the TIBCO suite of enterprise data management products. Developed jointly for each of the two WMS implemented in different distribution centers, the data was integrated to a centrally developed and deployed enterprise data warehouse. Leveraged across multiple projects throughout the clients applications enterprise-wide.

The XTIVIA team analyzed the existing applications and based on the review of each of the business requirements, the team provided a comprehensive integrated data warehousing and reporting solution. The solution was engineered to attain another previously unstated critical business goal one of reusability of the existing services and adherence to the retail industry standards.

XTIVIA developed a wrapper TIBCO BW service to invoke various TIBCO DX tasks. The TIBCO DX tasks were designed to store the data from each WMS to the data warehouse after transformations based on the VEST (Validation, Enrichment and Standardization & Transformation) approach.

The approach also required XTIVIA to fully develop all the common services such as Notification, Exception Handling, Transaction Logging and Auditing in accordance with pure SOA principles. These common services were leveraged across multiple projects in the client’s applications portfolio, for use enterprise-wide.

The TIBCO BW wrapper services also monitored the various TIBCO DX tasks being run. This enabled continuous monitoring and proactive notifications of any failures during the entire ETL job schedule.

The scalable and robust solution involved integrating the enterprise data warehouse with:

• Production data from the two Distribution Centers (DC) for analytics and reporting
• Labor scheduling data for staff at the Distribution Centers for job-scheduling
• Production Summary Reports were created in MS Access and exported to MS-Excel for reviews
• Inbound data from the legacy systems:
  1. ASN (Advanced Shipping Notice) data, including container and trailer information
  2. WETA (Warehouse Estimated Time of Arrival) data
• Order data from the sourcing data store

RESULTS

Among the many benefits that the implementation of the solution entailed, the following are key:

• Elimination of MS Access database for reporting and ad-hoc databases for DC data analysis
• Enabled analytical reporting on historical data (ranging from just days up to 5 years)
• Yielded clear and meaningful analysis of correlating data between the two distribution centers
• Access to data for analysis to a broader group of business users
• Improved the data consolidation and system scalability
• Reduced operational data storage on WMS system
• Improved operational performance of the WMS
• Avoided using the WMS system for analysis, thereby reducing overall system resource usage

KEY COMPONENTS

TECHNOLOGIES USED

TIBCO™ BusinessWorks

TIBCO DataExchange

TIBCO Rendezvous

TIBCO EMS

TIBCO Object Star

Adapter for Files (z/OS)

Adapter for Files (UNIX)

Download the Case Study PDF here

XTIVIA needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime, read our Privacy Policy here.