USE OF A DATA WAREHOUSE TO ANALYZE DATA (STUDY OBJECTIVE 6)
In many instances, the data that managers need are much broader than that set of data currently used in day-to-day operations. Management often needs data from several fiscal periods from across the whole organization. A data warehouse can serve as this source of broader information for management. A data warehouse is an integrated collection of enterprise-wide data that includes five to ten years of nonvolatile data used to support management in decision making and planning. The data warehouse can be better understood if we compare it with the operational database. The operational database is the data that are continually updated as transactions are processed. Usually, the operational database includes data for the current fiscal year and supports day-to-day operations and record keeping for the transaction processing systems. Each time a new transaction is completed, parts of the operational data must be updated. For example, recording a sale means that sales, inventory, and receivables balances must be updated. This type of update does not occur in a data warehouse. Exhibit 13-9 shows the data warehouse and operational data layout.
The data are enterprise-wide because the data are pulled from each of the operational databases, and these data are maintained in the data warehouse for many fiscal periods. Ideally, the data warehouse should contain five to ten years of data. The data in the data warehouse are pulled from sales order processing, inventory systems, receivables, and many other transaction processing systems with the organization. The data in a data warehouse are called nonvolatile because they do not change rapidly in the same way that operational data change. Periodically, new data are uploaded to the data warehouse from the operational data, but other than through this updating process, the data in the data warehouse do not change.
Exhibit 13-9 The Data Warehouse and Operational Databases
BUILD THE DATA WAREHOUSE
To ensure the usefulness of a data warehouse, it must be built correctly. The data in the data warehouse must support users' needs and must be standardized across the enterprise. Rather than collect and incorporate all of the available data into the data warehouse, it is important to include only data that meet user needs. Management, accounting, finance, production, and distribution functions will be using this data warehouse to budget, plan, forecast, and analyze profitability.
IDENTIFY THE DATA
The data in the data warehouse must provide the right kind of information to these user groups. To determine data that should be in a data warehouse, it is important to examine user needs and high-impact processes (HIPs). HIPs are the critically important processes that must be executed correctly if the organization is to survive and thrive. The identification of HIPs must take into account the long-term strategic objectives of the organization.
THE REAL WORLD
Anheuser-Busch Companies, Inc., must make sure that it keeps convenience stores and liquor stores stocked with the right amount and type of beer. If too little is stocked, Anheuser-Busch will lose sales; if too much is stocked, excessive stocking costs may be incurred and freshness concerns may arise. Therefore, the company's distribution system is a high-impact process. Warranty repairs are not important at all to Anheuser-Busch. However, warranty repair processes are likely to be critical to a company such as Hewlett-Packard Co. HP sells computer products that typically carry warranties of 90 days to one year. Without customer responsive warranty repair systems, HP would very likely lose sales.
An organization must set up cross-functional teams to identify the HIPs and the data that flow in and out of those processes. Each team must consider which critical processes will help the company achieve its long-term strategic objectives. The cross-functional teams also must ask users what kind of data they need and the kind of business problems they face. By identifying and examining both HIPs and user data needs, the set of data needed in the data warehouse can be determined.
STANDARDIZE THE DATA
The data in the data warehouse will come from many different processes and subunits across the enterprise. Different applications within the enterprise might use the same information, but in a different manner. For example, both sales order processing and marketing functions may use a field called “customer number.” The marketing system may have a customer account number that is five digits with three leading zeros. So, a customer account number might be 00053425. Sales order processing systems may use a customer field with five digits and two leading zeros. Therefore, the same customer in the sales order application would be 0053425. To incorporate all information about this customer from both the marketing and sales order systems, the account number must be standardized within the data warehouse. One could fairly ask, Why not change the underlying marketing or sales order systems so that they agree? Most companies do not feel that they can afford the time or effort to rewrite source code in the older, legacy systems. Rather than change existing systems, it is easier to standardize data in the data warehouse.
CLEANSE, OR SCRUB, THE DATA
Since the data in a data warehouse are likely to come from many different sources within the enterprise, there will probably be errors and inconsistencies in the data. To the extent possible, the data should be cleansed, or “scrubbed,” to remove or fix errors and problems in the data.
UPLOAD THE DATA
Data from each of the HIP systems must be uploaded to the data warehouse. Also, on a regular basis, new data should be uploaded to the data warehouse. Between the dates when data are uploaded, the data warehouse is static—it does not change. As an example, if data are uploaded at the end of every month, there are no changes in the data warehouse up until the last day of the month.