RETAIL ACTIVE DATA WAREHOUSE

ORGANIZATION

The client is a leading privately-held grocery supermarket chain operating more than 300 stores in three states across the south-eastern United States. The retail grocery chain operation is spread over multiple categories including grocery, drugs, fresh produce, and baked goods. Our Client wanted to obtain near real-time insight by implementing continuous integration in a real-time mode between various POS systems across the 300 stores into the Enterprise Data Warehouse.

CHALLENGE

For years, the client adopted a batch process for uploading the Point of Sale (POS) data from its 300 stores into the corporate data warehouse on a nightly basis. The staggeringly high data volumes—a result of transacting more than 5,000 items at 300 stores and a large number of customers daily —amounted to about 40 to 50 million transactions a day. The high transaction volumes lead to long data migration cycles resulting in huge delays in the downstream reporting, analysis, and decision making process. This delay oftentimes bled into the next day and transactions for the subsequent days were held up from being interfaced and loaded rendering the issue with additional complexity. In some cases, sales data was being made available to the business users and to other operational systems with a lag of 24 hours or more. Data loads often failed and users were faced with yet another issue: incomplete runs, which were rendering the downstream reports and analysis highly unreliable.

The client enjoyed a formidable level of customer loyalty, and the business thrived on its loyal customer base. The success of the business model was further augmented by efficient operational and sales analysis abilities of its merchandising and store management personnel. However, the reporting and analysis delays were jeopardizing the ability of the client to respond to changing market conditions in a timely and effective manner. The Active Data Warehouse initiative was conceived and sponsored to eliminate these business issues and alleviate the impact of the challenges faced by key users. The primary goals of this initiative were to make the data available to business users in a near real-time and reliable manner while also attempting to obtain a more accurate insight into the planning and operations based on near real-time data. CURRENT TECHNICAL LANDSCAPE The technical situation was characterized by the following:

• Batch upload of sales data was untimely, incomplete and often unreliable.
• There was no backup, recovery, or failover mechanism for failed uploads.
• The network connectivity from 300 stores were not 100 percent reliable.
• The standard operating procedure was a complete run of the entire batch file in case of failure.
• All the sales data existed in raw-form batch files form versus individual sales transaction and the various target systems parsed individual transactions, which comprised a market basket of customers.
• Reusability was absent as a result of each system parsing the same data.
• Serious performance issues in data upload.
• Higher maintenance eforts to manage the data upload transactions.

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 provided three different mechanisms for failure at different levels to efficiently deliver accurate data to the corporate data warehouse. If the failure occurred at the DW connectivity level, alternate mechanisms would trigger the delivery of the data file to the DW system. The need to reprocess entire batch files was eliminated if a failure occurred as the solution provided the capability to reprocess the data, after recovery, from the point of failure. Additionally the solution laid the foundation for active intelligence in the client’s BI portfolio. The solution was designed to handle large volumes of transactions with an SLA of 10–20 minutes in the end-to-end performance per transaction metric, and from an availability perspective, it provided a 99.9 percent uptime. The solution unpacked the raw POS data, parsed each transaction, and published the individual transaction on the TIBCO ESB for all relevant systems to subscribe from. This served to reduce the processing load associated with the parsing on each subscribing system. With orchestrated activities to handle 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 the business and IT teams:

• Providing Business users with sales data as recent as 60 minutes ago.
• Enabling components for Active Intelligence that leveraged real-time data.
• Real-time inventory refinement at stores or warehouse levels.
• Real-time inventory and PO refinement by corporate buyers and visibility to 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 promotions activities.
• Ability for store managers to view real-time sales data pushed by corporate to make decisions.
• Ability for store managers to create flex events based on real-time data.
• Reduced the efort on IT teams by automating the feeds in lieu of manual upload of batch files.
• Provided flexibility to add or remove interested systems for the sales data.
• Eliminated a custom data mart to propagate the sales data to other operational systems.
• Ensured high availability resulting from the multiple failovers engineering of the solution.

KEY COMPONENTS

TECHNOLOGIES USED TIBCO™ – BusinessWorks; EMS; Hawk TIBCO™ – Teradata Adapter; File Adapter TIBCO™ SDK TIBCO™ Administrator Teradata® Download the Case Study PDF here