Data Mining & Analysis, LLC

CASE STUDY

Scalable POS Data Warehouse at Southland Corp.
Commentary by David Carty,  IT Senior DSS Application Developer

With increasing competition from gas companies and their co-branding with fast food restaurants,  Southland 7-11 convenient stores have had to reconsider the best way to secure and maintain a competitive advantage.

Southland has provided its product marketing analysts with a decision support solution originally from IRI which became Oracle Express Sales Analyzer.  For the past three years SLC analysts have had summary access to order information only.  "If it was ordered, we had to assume it was sold.   What we didn't envision up front, was the degree to which the thirst for detailed information would grow, and at what rate" said Carty.  In conjunction SLC spent the next 1.5 years implementing a Point Of Sale (POS) system in our convenient stores.  Prior to the POS rollout it became obvious that the demand and type of sales detailed information people wanted would require a more powerful DSS solution.  With the POS data and Order data integrated we could now analyze vendor performance.  i.e.  Ordered 10 items, they delivered 7, 3 were back ordered, and then we sold 5 and wrote off 2 for expiration or theft.

Q. What specific business goals did you set for the data warehouse?

Carty:  We wanted to create a flexible reporting environment that allowed both executives to see key sales performance indicators refreshed daily and allow product mix analysts to manage shelf movements, promotions, and make product and pricing recommendations.

Q. What platform and tools did you choose?

Carty:  We chose a HP9000 SMP with 4 CPUs and HP-UX Operation System with Oracle as the RDBMS, and Information Advantage as the analysts access tool.

Q. What was the biggest challenge?

Carty:  One.  Educating  the users on the iterative query, then interpret and re-query versus the old green bar printed reports mentality.  Two.   Refreshing the warehouse nightly with POS transactions in our six hour window phased by time zone.

Reflections:  We succeeded in giving our executives daily sales performance indicators and allowing our analysts to mange product movement and increase profitability.   However, technically, we built a star schema design with detailed transaction, daily, weekly and monthly aggregation fact tables.  Mainly because our access tool is great at drilling but we do not have a true ad-hoc query tool.  Nor do we have a enterprise atomic level layer allowing for data mining.