Service Merchandise Company, Inc., a leading retailer of fine jewelry and home lifestyle products, is cycling through a major business overhaul and is using a new data warehouse and decision support technology to achieve results. The Fortune 500 company, which operates 358 stores in 35 states, is transitioning from a catalog showroom to a more traditional retail merchandise focus. As such, its business units began to bombard its IT department with requests for ad hoc reports containing market and store data. Service Merchandise was also in need of a system to analyze the success or failure of its various marketing programs to increase promotional planning effectiveness.

The Solution

The key to its solution was Intrepid's DecisionMaster, a powerful decision support solution based on MicroStrategy's relational on-line analytical processing (ROLAP) technology. The system bundles retail-oriented reports in a friendly graphical interface.

A total of 35 business users are currently using the system to access its 500GB data warehouse, including planning, merchandising, replenishment, marketing, logistics, stores and finance departments. The company plans to have up to 100 end users on the system, with the ability to license many more if needed.

"The product has been fantastic," said Chris Hey, assistant vice president of merchandising and planning at Service Merchandise. "DecisionMaster delivers critical benefits to nearly every facet of our business. Based on the insights we glean using DecisionMaster, we have been able to make decisions that are directly lifting our sales, fine tune our marketing and improve customer satisfaction."

Service Merchandise determined that relational OLAP could outperform its current multidimensional approach in terms of scalability and detailed analysis.

Using the DecisionMaster merchandise workbench as a foundation, Service Merchandise created custom reports to focus on sales and inventory. Sales information is organized by item, store and day in 300 million rows. Inventory is organized by item, store and week in 885 million rows. Users query this data, using exception-based reports that highlight problems and opportunities in their business. Empowered by DecisionMaster's drill-down capabilities, users can dynamically investigate data points and business trends and make informed decisions.

DecisionMaster is tightly integrated with MicroStrategy's leading relational on-line analytical processing (ROLAP) technology and tools. The system serves Service Merchandise in a three-tier platform environment. The data warehouse runs on a Sun Solaris operating system with an Informix RDBMS. The Intrepid software runs on an NT server and on Windows-based PCs.

Service Merchandise's hardware platforms include an Amdahl mainframe that feeds a Sun-Series 2000E with 12-way processor data warehouse. They utilized an Ethernet network running TCP/IP. A Sun Sparc Array storage system was used as the disk subsystem to house the data warehouse. One of the benefits received from the hardware architecture was realized via a fiber-optic channel that links the CPU and the disk storage, which speeds up the data throughput. Both COBOL and Object Star software were utilized to extract and transform the data prior to loading it into the data warehouse.

With the objective of winning new customers, Service Merchandise targeted about 15 of its markets with weekly newspaper ad insertions. DecisionMaster was able to analyze the program's effects. "DecisionMaster allowed me to go in and isolate those markets, bringing up sales information a day after the insert broke," Hey explained. "Instead of making an intuitive decision, as we would have in the past, it allowed us to make a very important sales decision. DecisionMaster helped us identify the markets that were not performing well for us, while incorporating more promising ones."

Another success involves the pull-tag program. Going to Service Merchandise in the past meant shopping with clipboard and pencil in hand to record the SKU numbers of items of interest. Wanting to overhaul its customers' shopping experience, Service Merchandise incorporated pull-tags, containing scannable SKU information, in place of the clipboard system. Based on the information being returned from the data warehousing system, Service Merchandise decided to roll the project out from a test group of stores to its entire organization. In addition to these successes, Service Merchandise is using DecisionMaster to identify areas of its supply chain that need improvement, as well as ensure that all items listed in upcoming flyers are in stock.

Prior to incorporating its decision support system, Service Merchandise used an in-house multidimensional database. Recognizing that it needed a technology that could handle the demands of its large retail data warehouse, the company concluded that relational OLAP could outperform its current multidimensional approach in terms of scalability and detailed analysis.

Instead of the limited value of 13 months of chain-level data provided by its old system, Service Merchandise can now make this-year/last-year type comparisons with its new system. The new system houses roughly three years of store-level data.

Beyond scalability concerns, Service Merchandise was looking to take the burden of report generation off the shoulders of its IT department, freeing them to pursue other corporate initiatives. Also, going through IT meant waiting anywhere from one week to several weeks to receive the desired results. Service Merchandise needed a system that eliminated or reduced this lead time to produce the needed information.

Practical Advice

It goes without saying that users should be part of the team that designs, develops and implements the data warehouse. This brings crossover knowledge to both IT and the user community. This knowledge will be invaluable in assisting the users to produce and utilize their own queries in as short a time as possible. This also produces "experts" in the user community who can provide insight and assistance to new users of the data warehouse.

The meta data, or the data defining the data, should be readily available to the user, preferably in both published and on-line forms. This meta data should include terms that the user is accustomed to using on a regular basis. This prevents misunderstanding and reduces startup times for new users in becoming familiar with and proficient in using the data warehouse.

A data warehouse, or OLAP system, is a very different entity than an on-line transaction processing (OLTP) system. The tuning, data layout, response times, etc., are different and need to be addressed accordingly. In an OLTP system, response time is expected to be sub-second. However, response times for an OLAP system could be measured in minutes or even hours. Correctly setting the user's expectation goes a long way in ensuring the success of a data warehouse.

Your data warehouse will not become a usable entity overnight. It will require planning, resources in both hardware and software, contributors from the user groups and IT, the extracting and scrubbing of data, loading the extracted data, tuning of the database and operating system software, tools to access the data warehouse, IT and user training in those tools, and a backup and recovery solution.

Our data warehouse has helped in areas that were not thought of in the original design and implementation stages. This should be true in most cases, as heretofore historical data may have only been available in a piecemeal fashion. Trends and exceptions are only a small part of what can be readily discerned with the proper query.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access