Managing Data

Welcome to the data mart

Data Warehouses have long been used to provide a clean, easy access database for analysis of business performance. They combine information that is gathered together from various operational data systems to give a high level view. Campbell McCracken explains.

This high level approach has several benefits compared to analysing the raw data. "Suppose a company wants to be able to give people access to the operational information so they can analyse it," said BRIO Technology's UK marketing director Andy Bellinger. "Typically they can't do that through the operational systems because either it will impact the performance of the operational systems, or they'll be unable to put the relevant security layer in place."

However, as new technology made it easier to gather larger amounts of raw data, the performance of the data warehouses began to suffer. They became unwieldy and were time consuming to populate and interrogate. The solution was to build smaller, subject specific warehouses which didn't contain all the information, but only that which was needed for, say, the marketing department. These are known as Data Marts.

But these are not without their own problems. They tend to be designed and built in isolation from each other, and unless everyone is using the same definitions for the dimensions of the data cube, data can get out of sync across the marts. "I provided a data mart to the marketing people and one to the finance people, the definition of 'Product' or 'Time' changed every time I created another data mart," said Peter Kokinakos, marketing director for Cognos UK.

"There was no way of guaranteeing the consistency, or 'Conforming Dimensions'. That's an important part of building a series of data marts because one of the things that we see at the end of each month is people sitting around the boardroom table, and the discussion is 'Whose figures are right?' as opposed to 'How is the business doing?'"

Blurring at the edges
Although it sounds as if operational systems, data warehouses and data marts are distinct and separate entities, there is a blurring at the edges. For example, business intelligence software exists that will allow you to create a 'virtual' data warehouse by viewing data across several data marts. Obviously having conforming dimensions is therefore important

Also, because of the time it takes to refresh a data warehouse, some business intelligence software allows the warehouse to be augmented from operational systems. "We can allow a certain amount of information to be dragged out of the online operational systems," said Brio's Bellinger. "So the data warehouse now provides the fundamental feeds of information that's required to make the business decisions, and if somebody wants the view of what's happening right now you can go off into the operational system and get that information."

Big Brother is watching you
With greater competition and deregulation of some industries, consumers have less allegiance to any one provider. Customers in a particular geographic region are no longer tied to a particular electricity, gas or communications company. They will go with the company that gives them the service that best matches their needs. To give them the edge, companies analyse the transaction information to the full. "The retail organisations have made massive in-roads into being able to identify valuable customers," said Brio's Bellinger.

Data warehouses support two main types of analysis. The first is the traditional, manual, top-down approach where a manager asks one question and asks his next question depending on the response he gets from the first. The analysis is used to support business decisions or to confirm business suspicions. "It's all down to asking business questions and getting really quick answers for them," said Cognos' Kokinakos. (See Case Studies 'Cognos at KLM' and 'Brio at MSAS'.)

The second type of analysis, sometimes known as data mining, is an automated approach where specialist software is let loose on the data to look for statistical relationships. "In the older technology you had to have a white coat and a pocket full of pens to understand this stuff," said Kokinakos. "But now a lot of the products put the drivers into a black box and give them a really easy to use interface." This includes Chi-squared analysis, linear regression and neural networking.

CASE STUDY ­ BRIO TECHNOLOGY AT MSAS Andy Bellinger - BRIO Technology MSAS Global Logistics is one of the world's leading supply chain and logistics companies. With the increasing volumes of documentation and complex routes it was experiencing, MSAS required access to a number of key performance indicators to help identify the profitability of routes. "One of the most frustrating things for us was not knowing exactly which routes were bringing in the most revenue," said John Mercer, UK IS development manager at MSAS. "We needed to know this so that we could maximise their use and take remedial action on the loss-making routes." MSAS' existing data warehouse was not meeting the current business requirements. Key reports had to be produced externally by an agency at a cost of £40,000 per year, and only three people in the IS department had the time to learn how to prepare the customised performance information needed. So, after a some months of trial and evaluation of different business intelligence solutions, Mercer and his team chose Brio's web-based client / server software. As well as making it easy for them to produce the reports they wanted, the new solution included remote access to the warehouse, which is a benefit to sales and strategy teams. In addition the improved analysis tools let MSAS produce valuable information that can be used to deliver efficiencies to its customers. "The next step is for us to deliver information that is critical to our customers' businesses direct to their desktops," said Mercer.

Two Types of Data Warehouse
A new type of warehouse has come about from the rise in numbers of dotcom companies. "One of the dreams that people had for the Internet was that it was going to allow them to have virtual storefronts where they would really just be representing vendors and they wouldn't actually have to have a warehouse," said ETI's Kay Hammer. "When they took an order for goods they in turn placed an order on their suppliers."

The problem was that their success depended on having the information to allow them to know how much stock they had, how much they could sell it for and how quickly it could be delivered. If they took more orders for a particular product than the supplier could deliver, their customers would be let down. If the new stock cost more than the old stock, their margins would be affected.

"Garden.com was one of the golden dotcom companies in Austin Texas but it is now out of business," said Hammer. "Too often they were taking orders they couldn't fulfil in a timely fashion. They eventually had to build a real warehouse to stock their most popular items. This cut into their profit margin, incurring a lot of the cost that the traditional bricks and mortar distribution networks had."

The same thing happened to ToysRUs.com. They were sued in a class action in the US in January 2000 because they took orders for Christmas presents from customers that they were unable to deliver. It also ended up building a giant warehouse to provide a stock buffer.

Case Study - COGNOS at KLM PETER KOKINAKOS ­ COGNOS UK KLM UK is a UK-based airline serving Amsterdam Schiphol Airport from 15 regional UK airports. It was finding that the increase in low-cost carriers and the mergers and alliances of major international airlines meant there was more pressure on its margins and it needed to react more quickly. Inevitably it needed more detailed business intelligence information for itself and its trade partners. "We have seen the thirst for market intelligence treble over the last few years and the responsibility of sourcing and distributing relevant data for analysis to our managers and alliance partners had become too great for us," said Eric Wynton, head of management information (MI) at KLM UK. KLM's managers needed to know answers to questions such as "What if we discounted fares by 20% on a particular route?" or "What if we reduced the number of services on a route but increased the size of aircraft?" The MI department built a data mart in 1998 which was fine as an off-line solution but not robust enough to handle its operations. The eventual strategy was to web-enable the data mart using Cognos' PowerPlay WEB, resulting in a data warehouse that would be accessible to all trade partners - including major travel agents Trailfinders and Carlson Wagonlit - through the KLM extranet and internal users through the intranet. This allowed KLM to tailor its marketing efforts and focus resources on its most valuable revenue earners. "In addition it gives our sales team access to information such as our share of a particular travel agent's total sales to enable it to analyse their success in selling our product." said Wynton.

Supply chain management
The biggest frustration these companies had was that they couldn't give customers accurate information. Customers want to know at the time of placing their order whether the goods they have ordered can be delivered in time. They don't want to have to wait hours or days to find out.

So some people thought of the idea of having another kind of warehouse - the offline inventory data warehouse. "What the dotcom companies are looking at is the ability to tap into their suppliers' supply chain management system," said Brio's Bellinger. This lets them look at the stock profiles of those particular products.

"The offline inventory warehouse is very different from the traditional decision support warehouse," explains ETI's Hammer. "In the latter you may have a couple of hundred users using complex queries requiring a lot of processing of data from maybe 15 different tables. In contrast, the offline inventory warehouse may have 200,000 different users. The queries are going to be much easier, but the number of feeds is going to be much bigger, perhaps 80 - 100 feeds, because of the variety of vendors.

"Another difference is that in a decision support warehouse you typically want to keep the data for a fairly long period of time, maybe three months to three years, so that you can see trends. The information in the offline inventory warehouse has a very short life span - you only care about now!"

"The offline inventory warehouse gives you a view that is not real-time, not 100 percent accurate, so the frequency that a website renews it depends on a risk analysis," said ETI's CTO David Marshall. "It's a hedge against the real inventory. If the inventory's very volatile they may want to renew the warehouse daily or twice a day. If it's less volatile, maybe there's hundreds of thousands of items and they're only selling a thousand or so, they have less risk."

Campbell McCracken