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 offers a wide range of footwear and fashion accessories products by focusing on innovation and dynamic response. The client supports its operations with several warehouse facilities and a supply chain managed through a Warehouse Management System (Manhattan WMS).

CHALLENGE

Before implementing a BI solution, the client users relied on operations reports generated from the implemented WMS system. However, the lack of analytical depth in these operational reports, coupled with the increased demand for real-time insights to address critical performance indicators, led to an inadequate scenario for the client. The need for real-time insights from transactional data and the ability to facilitate effective and timely business decisions through the use of enterprise data within the WMS led to the initiation of a separate data warehouse project. This warehouse would be populated with data from various WMS sources. The business faced the following challenges:

• Reports could only be generated using data from the previous 21 days. Automated analysis of monthly and yearly data was not feasible, and manual implementation was highly challenging.
• Historical data was not available online for analysis purposes.
• Data stored in the WMS and WM (MS-Access) database could not be easily correlated with data from other client systems.
• The business was limited to running analysis and generating reports within a specific time slot each day to avoid impacting DC transactions and system performance.

TECHNICAL LANDSCAPE

The technical landscape was characterized by the following:

• The data management process, along with its associated controls and governance, was inadequate.
• Data was extracted using COBOL subroutines and transferred to an MS Excel spreadsheet for analysis at the end of each business day.
• Reports were generated using Crystal Reports based on this data and distributed via email to business users.
• Production Summary Reports were created in MS Access and exported to MS Excel for review.
  Executing reports significantly impacted the performance of the WMS system, particularly in terms of transaction processing.
• The lack of historical data for analysis and reporting within WMS led to substantial operational gaps.
• Issues were further exacerbated by data joins, as the two different distribution centers operated on separate instances of the WMS application.

TECHNICAL SOLUTION

XTIVIA delivered a near real-time solution utilizing the TIBCO suite of enterprise data management products. Developed collaboratively for the two WMS systems implemented in different distribution centers, the data was integrated into a centrally developed and deployed enterprise data warehouse. This solution was leveraged across multiple projects within the client’s applications enterprise-wide. The XTIVIA team analyzed existing applications and, based on a thorough review of business requirements, provided a comprehensive integrated data warehousing and reporting solution.

The solution was engineered to meet another critical business goal: reusability of existing services and adherence to retail industry standards. XTIVIA developed a wrapper TIBCO BW service to invoke various TIBCO DX tasks. These TIBCO DX tasks stored data from each WMS into the data warehouse after transforming based on the VEST (Validation, Enrichment, Standardization & Transformation) approach.

Additionally, XTIVIA fully developed common services such as Notification, Exception Handling, Transaction Logging, and Auditing following pure SOA principles. These services were leveraged across multiple projects in the client’s applications portfolio, for use enterprise-wide. The TIBCO BW wrapper services monitored various TIBCO DX tasks, enabling continuous monitoring and proactive notifications of any failures during the entire ETL job schedule. This scalable and robust solution integrated the enterprise data warehouse with:

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

BUSINESS RESULTS

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

• Elimination of the MS Access database for reporting and ad-hoc databases for DC data analysis.
• Enabled analytical reporting on historical data, ranging from a few days to up to five years.
• Provided clear and meaningful analysis of correlated data between the two distribution centers.
• Broadened access to data for analysis to a wider group of business users.
• Enhanced data consolidation and improved system scalability.
• Reduced operational data storage requirements on the WMS system.
• Improved the operational performance of the WMS.
• Avoided using the WMS system for analysis, thereby reducing overall system resource usage.

KEYWORDS
TIBCO EMS, TIBCO DataExchange, TIBCO ObjectStar, Data Warehouse Integration

SOFTWARE
TIBCO™ BusinessWorks, TIBCO DataExchange, TIBCO Rendezvous, TIBCO EMS, TIBCO ObjectStar, Adapter for Files (z/OS), Adapter for Files (UNIX)

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.