ETL technology helps Shoppers Stop leverage sales data

Shoppers Stop, the leading retail player, deployed the Oracle Data Integrator ETL technology that helped it manage its sales data and maintain growth tempo.

Shoppers Stop is one of the oldest and popular retail players in India. Its popularity can be explained by marketing statistics which show that almost 70 per cent of its customer base belongs to the loyalty- customer category. Not only that, the growth graph of the store has been showing a steady increase since its inception.

Coupled with its own burgeoning popularity, the dynamic movement in the Indian retail market was fuelling the company’s growth. Riding high on the growth wave, the company wanted to reduce the time taken by Business Intelligence (BI) applications for reflecting searches with the help of ETL (Extract, Transform and Load) technology. The company was sure that this reduction in time would help it in manipulating sales data rapidly and rolling out loyalty programmes and schemes, which in turn would maintain the growth tempo. For this purpose, it began by zeroing in on the ETL technology that suited its needs.

Prior to initiating this change with the help of ETL technology, Shoppers Stop had in place a successful loyalty program and used to have an SQL (Structured Query Language) server-based data mart for BI reporting and a Microsoft Data Transformation Service (MS DTS) for populating it. This technology sufficed for the department level, but for taking it to the next level and also to consolidate data at the enterprise level it wanted ETL technology.

With the decision for going in for ETL technology in place, the management deployed Oracle Data Integrator Enterprise Edition (formerly Sunopsis) as part of its Enterprise Data Warehouse (EDW). It further chose Netezza, which is an appliance that works as a cheaper version of Teradata and has root-versioning facilities, with regard to the EDW.

That apart with regard to the ETL technology, the company also invested in Claraview, the retail logical data model for smooth integration, which mainly catered to the U.S. markets. The Shoppers Stop team took this model and customized it to suit their needs while going in for ETL technology.

The Oracle Data Integrator (ODI) was chosen as it works on ETL architecture. It seemed a perfect combination with Netezza as far as ETL technology was concerned. Further, it helped the team load data in increments from multiple disparate systems into the EDW overnight, cleanse the data, and make it available to associates the next morning.

With this ETL technology in place, the company can also take full advantage of the parallel processing power of Netezza. The ODI knowledge modules used to populate the EDW are pure SQL-based and could be easily customized. This gave tremendous flexibility to the developers to build custom knowledge modules that can take full advantage of the source and target systems and also helping in the deployment of new ETL technology.

Before the deployment of the ETL technology was enabled, the IT team of the company conducted an elaborate evaluation study. The study focused on five-year business plans along with the reporting and data-consolidation requirement and centralized data storage needs. The company scanned the environment for available solutions and found that for the technology stack chosen; this was the most appropriate solution. Apart from Netezza, products from Sybase, IBM and Teradata were also considered with regard to going in for ETL technology.

As an added benefit to the earlier-mentioned features of ETL technology, the company had ODI’s change data capture (CDC) feature at its disposal. It helped to integrate legacy transaction systems with the new ETL technology without a time stamp easily.

The ETL technology project started in January 2008 and ended by the third quarter of the year. The major milestones with regard to theETL technology deployment were adoption of the Claraview retail model to suit requirements, implement ODI ETL modules and populate the EDW. There were no major roadblocks faced with regard to the ETL technology deployment, owing to the stringent evaluation process that was followed to choose the right partners and do the system integration.

Help was taken from Claraview to customize the retail data model with regard to the ETL technology deployment. From source to target system mappings, these were done by the internal team and ODI ETL modules were implemented by Crisma, Italy. There were no integration issues with regard to the ETL technology implementation. Today it is managed with the sustenance of the EDW that is done by the internal team.

Deployment of the ETL technology and the EDW as such has helped in providing business with a single source for all reporting requirements. The consistency and speed of data availability has improved.  The turnaround time for new reporting requirements has reduced. The EDW has become the strong foundation to all the analytical projects done internally. The time taken by the ODI ETL technology to refresh data from its data warehouse has reduced from half a day to one hour.

Even though no RoI analysis has been conducted, there are expected benefits from the ETL technology implementation. Since the past two years, initiatives are made in the direction of product migration (upgrading customers to higher-end products). These initiatives are built around the concept of customer benefits and loyalty programs. Thus, for better targeting specific groups, micro-segmenting, BI and business analytics the consolidated database will be vital so also the newly deployed ETL technology.

Read more on Data warehousing