• Modern Database Management

Chapter 9 1 Chapter 9 Data Warehousing Chapter Overview The purpose of this chapter is to introduce students to the rationale and basic concepts of underlying data warehousing from the perspective of data and database management. We contrast operational and informational processing, and we discuss the reasons why so many organizations are seeking to exploit data warehouses for competitive advantage. We discuss alternative data warehouse architectures (especially the database architectures) and techniques for populating a warehouse. After the introduction of the new chapter on Big Data and Analytics (Chapter 11), this chapter focuses almost solely on the infrastructure of data warehousing. Chapter Objectives Specific student objectives are included in the beginning of the chapter. From an instructor’s point of view, the objectives of this chapter are to: 1. Establish the fact that many organizations today are experiencing an information gap.

That is, they are drowning in data but starving for information. Define data warehousing and describe four characteristics of a data warehouse.

Describe two major factors that drive the need for data warehousing as well as several advances in the field of information systems that have enabled data warehousing. Contrast operational systems and informational systems from the viewpoint of data management.

Describe the basic architectures that are most often used with data warehouses. Contrast transient and periodic data, and discuss how data warehouses are used to build a historical record of an organization.

Discuss the purposes of populating a data warehouse and the problems of data reconciliation. Contrast data warehouses and data marts. Describe and illustrate the dimensional data model (or star schema) that is often used in data warehouse design. Copyright © 2016 Pearson Education, Inc. Modern Database Management, Twelfth Edition 2 Key Terms Conformed dimension Data mart Data warehouse Dependent data mart Derived data Enterprise data warehouse (EDW) Grain Independent data mart Informational system Logical data mart Operational data store (ODS) Operational system Periodic data Real-time data warehouse Reconciled data Snowflake schema Star schema Transient data Classroom Ideas 1. Discuss the importance of data warehousing in organizations today. Over 90 percent of large (Fortune 1000) companies have completed data warehouses or have a warehousing project underway.

Ask your students to suggest reasons for this popularity. Discuss job opportunities in data warehousing, business intelligence, and data mining. Numerous Web sites have job listings as well as newspaper advertisements. Emphasize that a successful data warehousing project requires the integration of everything the students have learned throughout the database course (in fact, everything in the IS curriculum). Discuss the idea of heterogeneous data (use Figure 9-1). Ask your students for reasons why such data are so commonplace and what problems they present.

Modern Database Management

Compare operational and informational systems using Table 9-1. Ask your students for examples of each type of system. Compare the independent data mart (Figure 9-2), dependent data mart and operational data store (Figure 9-3), and logical data mart (Figure 9-4) architectures. Discuss the three-layer data architecture (Figure 9-5). Ask your students why it might be necessary to have both a reconciled data layer and a derived data layer. Compare transient data (Figure 9-7) with periodic data (Figure 9-8).

Explain how periodic data provide a historical record of events. Introduce components of a star schema (Figure 9-9) and discuss the example shown in Figures 9-10 and 9-11. Have your students help you diagram another example (university, football team, etc.). Discuss some variations of the star schema (Figure 9-13, 9-14). Discuss conformed dimensions and how these could be used (Figure 9-13).

Discuss normalizing dimension tables, highlighting multivalued dimension, hierarchies, and the use of helper tables (Figures 9-14, 9-15, 9-16, and 9-17). Discuss slowly changing dimensions and some ways to handle this. Use Figure 918 as an example of one possible solution.

Have your students register for the Teradata Student Network and show them how they can access extensive material on data warehousing and business intelligence. Copyright © 2016 Pearson Education, Inc. Chapter 9 3 Answers to Review Questions 1.

Define each of the following terms: a. Data warehouse. A subject-oriented, integrated, time-variant, non-volatile collection of data used in support of management decision-making processes (Inmon and Hackathorn, 1994) b. A data warehouse that is limited in scope, whose data is obtained by selecting and (where appropriate) summarizing data from the enterprise data warehouse c. Reconciled data. Detailed, historical data that are intended to be the single, authoritative source for all decision support applications and not generally intended to be accessed directly by end users d.

Derived data. Data that have been selected, formatted, and aggregated for end-user decision support applications e. Enterprise data warehouse.

A centralized, integrated data warehouse for the entire enterprise that provides data to all end users of decision support applications. Real-time data warehouse. A data warehouse that receives and analyzes (near) real-time data feeds from systems of record (instead of using batch ETL), making data available for very quick responses to business events as they take place. A simple database design in which dimensional data are separated from fact or event data. A dimensional model is another name for star schema h. Snowflake schema. An expanded version of a star schema in which all of the tables are fully normalized i.

The length of time (or other meaning) associated with each record in the table j. Conformed dimension. One or more dimension tables associated with two or more fact tables for which the dimension tables have the same business meaning and primary key with each fact table 2. Match the following terms and definitions: c d e f b g i a h periodic data data mart star schema grain reconciled data dependent data mart real-time data warehouse transient data snowflake schema Copyright © 2016 Pearson Education, Inc. Modern Database Management, Twelfth Edition 4 3. Contrast the following terms: a. Transient data; periodic data.

In transient data, changes to existing records are written over previous records, thus destroying the previous data content. In periodic data, the data is never physically altered or deleted once they have been added to the store.