Data Warehousing:

A Mine
of Business
Information

by Pierre Gaboury

Information technology (it) has revolutionized the way in which corporations conduct business. IT has become a critical component of modern business, and is a vital decision-making tool for today's managers.

In today's competitive business environment, accurate information is one of a firm's greatest assets. The ability to convert trillions of bytes of data into meaningful information, and to quickly access that information in a convenient format, has redefined organizations and revolutionized the work of managers.

To stay competitive, many corporations now look at their organizational structure as networks of intra- and inter-organizational relationships. Corporate managers, therefore, must redefine their roles. Smart managers view their work not just as creating and marketing products, but also as managing the corporation's information.

Underutilized assets
In your own business, historical records stored as minute transactions may be your most under-used corporate asset. Having greater access to more useful information could give you the ability to build better business solutions - solutions that contribute to the bottom line. This is why data warehousing can be the key to success, as a business and information technology strategy in which the goal is to bring useful information into the hands of business analysts and decision-makers.

Consider the following case example: A multinational organization with retail outlets around the world uses its massive base of information to measure its service and profit relationships. The corporation's management tracks profits daily, by unit, market manager, sales zone, and country. The company also integrates this information with the results of exit interviews that they conduct with nearly one million customers annually. By correlating the profit information with the service information, the company is able to directly relate profitability to customer satisfaction.

Suppose, for example, the company discovers that the outlets with the highest customer satisfaction scores outperform other outlets on important business measures, such as sales, cost containment, and profitability. Corporate managers can then develop strategies to build customer satisfaction (and thus profitability) in all stores.

The beauty of data warehousing is that sophisticated analysis doesn't have to stop there. By further examining its human resource records for employee turnover in individual stores, our example company might discover that the 20% of stores with the lowest turnover rates enjoy double the sales, and 50% higher profits, than the 20% of stores with the highest employee turnover rates. Again, this suggests certain steps that could contribute to the bottom line.

The key point of this example is that data warehousing strategies allow the company to take complicated transaction data out of the hands of a few systems people, and give a wider audience of business decision-makers access to valuable corporate information.

Assessing the potential
To understand the role data warehousing (DW) can play in your own organization, it is useful to look at a data warehouse development model from a senior management perspective. The model, shown in the figure on page 17, has four components: business need, business function, data warehouse strategy, and data warehousing implementation. Each of these components must be aligned with the others in order to maximize the success of your DW initiative.

Business Need - Unlike traditional transaction processing systems that are heavily focused on optimizing business functions, a data warehouse directly addresses a strategic business need. The results at the close of the day give only a glimpse of a business, and business needs cannot be addressed by looking only at the most recent financials. As in our example, in order to project the future of a business, senior executives need to look at such measurements as profitability, market share, product quality, and customer satisfaction, and they must look at these measurements over time, across business functions, across product lines, and across regions.

In our example case, the management team works with a multidimensional model of time, product lines, regions, and customer profiles, with each point in the multidimensional array representing a measurement of the business. The managers can fulfill their business needs because they are empowered with critical information. They can thus make informed decisions and thereby realize a return on their information assets.

Business Function - The first critical task in designing a data warehouse is to target decision improvement within a selected, specific business function. Focusing on a single function has several benefits, including minimizing data requirements and cross-functional issues, as well as defining and clarifying goals. This "one step at a time" approach makes implementing a complex system manageable.

Obviously, this first task cannot be undertaken by the IT staff in isolation. Senior management and leaders of the selected business function must be active team members. Together, the team chooses a specific goal with quantifiable results. The goal should be realistic; that is, it must be technically feasible, and the data must be available. This environment allows the team to track progress towards the goal.

Each business function will have its own view of the overall business. Take, for example, the marketing and accounting functions in our sample retail store case. The marketing managers need to track their advertising and promotion expenditures, and the subsequent responses to the campaign by market, in order to judge effectiveness. But the marketing managers' definition of "expenditure" will differ from the accounting manager's definition of "payable," even though the two are inextricably linked. (For marketing, an expenditure is tied to a campaign period. For accounting, the same expenditure is linked to payment schedules and bank transactions.)

Data Warehouse Strategy - In the data warehouse strategy stage, collecting data is an important task. The critical success factor associated with this task is getting the right data into the warehouse. The six factors of data gathering are selection, granularity, extraction, transformation, quality, and meta-data management. (See the "Six considerations for gathering data" sidebar on page 18.)

Data warehousing in Japan
It will come as no surprise that Japan lags behind the US in the development of data warehouses. Several factors account for this lag. Many offices, for example, still have low PC-per-person ratios, and no local area networks (LANs). Also, because a high proportion of senior executives do not actively use computers, they tend to have only a vague idea regarding the potential of recent technologies.

This is less the case with large, foreign multinationals. The Japanese subsidiary of a foreign company often has an advantage when considering a data warehousing initiative. In some cases, the overseas head office will give cues, and during visits to the head office, senior managers may be introduced to simple-to-use, powerful applications. In other cases, the head office may have corporate standards that will serve as a guideline for the business in Japan.

In both cases, though, the challenge for the Japanese subsidiary has been finding adequate Japanese software tools to support the data warehouse system. The number of data warehousing tools that support the Japanese language is quite limited, with only the major relational database management system (RDBMS) vendors - Oracle, Informix, Sybase, and IBM - having local versions of their leading RDBMS products.

Analysis and reporting tools tend to follow the major RDBMS products in terms of availability. Only one of the top-selling query builders, BusinessObjects, is available in a Japanese version. And Informix's Metacube, which allows a multidimensional view to be placed on top of data stored in a conventional RDBMS server, has only recently been released in Japan. Arbor Software's Essbase and Oracle's Express are special-purpose, multidimensional databases that have been available for some time in Japanese, but these databases rely on a custom engine rather than conventional RDBMS technology. The most time-consuming task in building a data warehouse is developing the critical components that pull data out of the legacy systems and place it into the data store. This part of the market has a large number of smaller players, most of whom have not yet addressed the Japanese market. It is here that customization is generally needed.

With the mixed bag of software tools available in Japanese, integration nightmares are common as data warehouse designers resort to custom applications and try to integrate English-language tools. For this task, the company's IT staff would be advised to augment its expertise by using outside consultants with data warehouse expertise. Outside consultants offer many advantages, including previous experience building data warehouses, knowledge about the performance of current technologies, and advanced information about which tools will be available in Japanese, and in what time-frame.

Third-party data
One factor to consider when developing an overall data warehousing architecture is the impact of third-party data. Consumer packaged goods (CPG) companies, for example, have alternatives to in-house data sources. Large third-party data vendors, including Nielsen Japan and MIC, can provide CPG companies with valuable POS (point-of-sales) data. The raw data collected from retailers who gather barcode data at check-out counters in supermarkets and department stores is sold to the data vendor, who then cleans, consolidates, and processes it before selling the data back to retailers or manufacturers.

Value-added networks, such as Planet, offer another avenue for collecting information. This data provides information about products as they move through the distribution chains. The data, however, does not cover the final stages in which the products come into the hands of retailers, unlike in the US.

Getting started
Building a data warehouse system is a complex undertaking. With a well-designed development plan and a commitment of organizational resources, however, it is a manageable undertaking for a company interested in increasing the return-on-investment of one of its greatest assets - information.

Key to the development effort is teamwork. Two teams are needed: the development team, and the maintenance team. The lifecycle for building a data warehouse can be as short as three months. As shown in the figure on page 18, business analysis and system design often can be completed in two weeks. The core of the development effort - the extraction and transformation of data - can be accomplished within two months. System testing and training require another two weeks.

Data warehousing has become an indispensable business tool in the US and Europe, and its use in Japan is growing as foreign multinational corporations continue to introduce advanced business analysis tools to their Japanese subsidiaries.

Japan does have some successful large data warehouses. One large retailer already has a data warehouse in the over-100-gigabyte range. Several other companies have smaller warehouses, but are eager to collect more information and hope to reach the 500 gigabyte range within a year. Such aggressive data warehousing initiatives tend to ride against the traditional Japanese business model. There is some question as to whether many local initiatives, particularly those resulting in limited or little success, fulfill real business needs or are simply attempts to follow trends.

As the business landscape changes in Japan, especially as a result of deregulation, global companies need to proactively participate in the redefinition of the way business is conducted in Japan. Data warehousing gives a corporation a different and better way to make business decisions. These business decisions will result in more competitive business solutions.

Dr. Pierre Gaboury is a principal with Fusion Systems Japan, Inc., Commercial Systems Group. FSJ is an innovative systems intergration company whose clients include major multinational firms throughout Japan.

Back to table of contents