RETAIL ACTIVE DATA WAREHOUSE

ORGANIZATION

The client is a leading privately-held grocery supermarket chain operating over 300 stores across three states in the southeastern United States. The retail grocery chain spans multiple categories, including grocery, drugs, fresh produce, and baked goods. Our client aimed to obtain near real-time insights by implementing continuous integration in a real-time mode between various POS systems across the 300 stores and the Enterprise Data Warehouse.

CHALLENGE

For years, the client used a batch process to upload Point of Sale (POS) data from its 300 stores into the corporate data warehouse nightly. The immense data volumes—resulting from over 5,000 items transacted at 300 stores and a significant number of daily customers—amounted to approximately 40 to 50 million transactions per day. This high volume led to lengthy data migration cycles, causing significant delays in downstream reporting, analysis, and decision-making. These delays often extended into the next day, holding up transactions and increasing complexity. In some cases, sales data was made available to business users and other operational systems with a lag of 24 hours or more. Additionally, data loads frequently failed, leaving users with incomplete runs and unreliable downstream reports and analyses.

The client enjoyed strong customer loyalty, with a business model reliant on a loyal customer base. The success was further enhanced by efficient operational and sales analysis from merchandising and store management. However, delays in reporting and analysis were hindering the client’s ability to respond to changing market conditions effectively and in a timely manner. The Active Data Warehouse initiative was launched to address these issues, aiming to provide near-real-time and reliable access to data while offering more accurate insights into planning and operations.

CURRENT TECHNICAL LANDSCAPE

The technical situation was characterized by the following:

• Batch uploads of sales data were untimely, incomplete, and often unreliable.
• There was a lack of backup, recovery, or failover mechanisms for failed uploads.
• Network connectivity across 300 stores was not consistently reliable.
• Standard operating procedure required a complete batch file run in case of failure.
• Sales data existed in raw batch files, with individual transactions parsed by various target systems, leading to inefficiency and duplication.
• Reusability was minimal, as each system handled the same data separately.
• Significant performance issues were encountered during data uploads.
• The process required higher maintenance efforts to manage data upload transactions effectively.

SOLUTION

XTIVIA formulated and implemented a reliable, fault-tolerant, and load-balanced Active Data Warehouse solution that provided business users with near real-time data.

The solution included three distinct mechanisms for handling failures at different levels to efficiently deliver accurate data to the corporate data warehouse. In the event of DW connectivity failure, alternate mechanisms would trigger the delivery of the data file to the DW system. This eliminated the need to reprocess entire batch files, as the solution allowed for reprocessing data from the point of failure after recovery. Additionally, the solution laid the foundation for active intelligence within the client’s BI portfolio.

The solution was designed to handle large volumes of transactions, with an SLA of 10–20 minutes for end-to-end performance per transaction. From an availability perspective, it ensured 99.9% uptime. The solution unpacked raw POS data, parsed each transaction, and published individual transactions to the TIBCO ESB, enabling all relevant systems to subscribe to the data. This reduced the processing load associated with parsing on each subscribing system.

With orchestrated activities to manage specific failure scenarios, the deployed solution also included auditing, monitoring, and management for failures at various levels.

RESULTS

The Active Data Warehouse solution provided the following benefits to both business and IT teams:

• Providing business users with sales data as recent as 60 minutes ago.
• Enabling components for Active Intelligence that leverage real-time data.
• Real-time inventory refinement at both store and warehouse levels.
• Real-time inventory and purchase order (PO) refinement by corporate buyers with visibility into direct suppliers.
• Refinement of promotions execution based on real-time sales data.
• Insights into market basket changes due to events, seasonality, and other actions to refine assortment, inventory, and promotional activities.
• Allowing store managers to view real-time sales data pushed by corporate for decision-making.
• Enabling store managers to create flex events based on real-time data.
• Reducing the effort required by IT teams through automated feeds instead of manual batch file uploads.
• Providing flexibility to add or remove interested systems for sales data.
• Eliminating the need for a custom data mart to propagate sales data to other operational systems.
• Ensuring high availability through multiple failover mechanisms engineered into the solution.

KEYWORDS
Active Data Warehouse (ADW), Point of Sale (POS) systems, Enterprise Data Warehouse, real-time insights, batch process, ETL, DataStage, TIBCO ESB, SLA, load balancing, fault tolerance, automated feeds, manual batch file uploads, reprocessing data, high availability, failover mechanisms, Active Intelligence, market basket analysis, real-time inventory refinement, automated auditing, monitoring, and management, transaction volumes, system reliability

SOFTWARE
Java, SQL

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.