Part 9 Review Questions and Exercises.docx

1、Part 9 Review Questions and ExercisesSOLUTIONS TO REVIEW QUESTIONSAND EXERCISESFOR PART 9 BUSINESS INTELLIGENCE (CHAPTERS 32 35)Solutions to Review Questions and ExercisesChapter 32 Data Warehousing Concepts 3Chapter 33 Data Warehousing - Design 6Chapter 34 OLAP 8Chapter 35 Data Mining 13 Chapter 32

2、 Data Warehousing ConceptsReview Questions32.1 Describe what is meant by the following terms, when describing the characteristics of the data in a data warehouse: (a) subject-oriented; (b) integrated; (c) time-variant; (d) non-volatile. See Section 32.1.2.32.2 Discuss how Online Transaction Processi

3、ng (OLTP) systems differ from data warehousing systems.See Section 32.1.432.3 Discuss the main benefits and problems associated with data warehousing. For the main benefits of data warehousing see Section 32.1.3 and for the main problems associated with data warehousing see Section 32.1.5.32.4 Prese

4、nt a diagrammatic representation of the typical architecture and main components of a data warehouse. For a diagram of the typical architecture of a data warehouse see Figure 32.1. 32.5 Describe the characteristics and main functions of the following components of a data warehouse. (a) load manager

5、See Section 32.2.3 (b) warehouse manager See Section 32.2.4 (c) query manager See Section 32.2.5 (d) metadata See Section 32.2.9 (e) end-user access tools. See Section 32.2.1032.6 Describe the processes associated with data extraction, cleansing, and transformation tools. The extraction step targets

6、 one or more data sources for the EDW; these sources typically include OLTP databases but can also include sources such as personal databases and spreadsheets, enterprise resource planning (ERP) files, and web usage log files. The data sources are normally internal but can also include external sour

7、ces, such as the systems used by suppliers and/or customers.The transformation step applies a series of rules or functions to the extracted data, which determines how the data will be used for analysis and can involve transformations such as data summations, data encoding, data merging, data splitti

8、ng, data calculations, and creation of surrogate keys. The output from the transformations is data that is clean and consistent with the data already held in the warehouse, and furthermore, is in a form that is ready for analysis by users of the warehouse.The loading of the data into the warehouse c

9、an occur after all transformations have taken place or as part of the transformation processing. As the data loads into the warehouse, additional constraints defined in the database schema as well as in triggers activated upon data loading will be applied (such as uniqueness, referential integrity,

10、and mandatory fields), which also contribute to the overall data quality performance of the ETL process.32.7 Describe the specialized requirements of a relational database management system (RDBMS) suitable for use in a data warehouse environment.See Section 32.4.232.8 Discuss how parallel technolog

11、ies can support the requirements of the data warehouse. See last topic discussed in Section 32.4.2 under the heading Parallel database technologies. 32.9 Discuss the importance of managing meta-data and how this relates to the integration of the data warehouse. See Section 32.4.3.32.10 Discuss the m

12、ain tasks associated with the administration and management of a data warehouse.The data warehouse administration and management tools must be capable of supporting the following tasks: monitoring data loading from multiple sources; data quality and integrity checks; managing and updating meta-data;

13、 monitoring database performance to ensure efficient query response times and resource utilization; auditing data warehouse usage to provide user chargeback information; replicating, subsetting, and distributing data; maintaining efficient data storage management; purging data; archiving and backing

14、-up data; implementing recovery following failure; security management.See Section 32.4.4.32.11 Discuss how data marts differ from data warehouses and discuss the main reasons for implementing a data mart. For a discussion on how data marts differ from data warehouses see introductory paragraphs of

15、Section 321.5 and for reasons for implementing a data mart see Section 32.5.1.32.12 Describe the features of Oracle that support the core requirements of data warehousing.See Section 32.6.Exercises32.13 You are asked by the Managing Director of DreamHome to investigate and report on the applicabilit

16、y of data warehousing for the organization. The report should compare data warehouse technology with OLTP systems and should identify the advantages and disadvantages, and any problem areas associated with implementing a data warehouse. The report should reach a fully justified set of conclusions on

17、 the applicability of a data warehouse for DreamHome. The format and the appropriate content to be covered in answering this question is described in the question set. Chapter 33 Data Warehousing - DesignReview Questions33.1 Discuss the activities associated with initiating an enterprise data wareho

18、use (EDW) project.To begin a data warehouse project, we need answers for questions such as: which user requirements are most important and which data should be considered first? Also, should the project be scaled down into something more manageable, yet at the same time provide an infrastructure cap

19、able of ultimately delivering a full-scale enterprise-wide data warehouse? The requirements collection and analysis stage of an EDW project involves interviewing appropriate members of staff such as marketing users, finance users, sales users, operational users, and management to enable the identifi

20、cation of a prioritized set of requirements for the enterprise that the data warehouse must meet. At the same time, interviews are conducted with members of staff responsible for OLTP systems to identify, which data sources can provide clean, valid, and consistent data that will remain supported ove

21、r the next few years.The interviews provide the necessary information for the top-down view (user requirements) and the bottom-up view (which data sources are available) of the EDW.33.2 Compare and contrast the approaches taken in the development of an EDW by Inmons Corporate Information Factory (CI

22、F) and Kimballs Business Dimensional Lifecycle.Inmons approach is to start by creating a data model of all the enterprises data; once complete, it is used to implement an EDW. The EDW is then used to feed departmental databases (data marts), which exist to meet the particular information requirement

23、s of each department. The EDW can also provide data to other specialized decision support applications such as Customer Relationship Management (CRM). Inmons methodology uses traditional database methods and techniques to develop the EDW. For example, entityrelationship (ER) modeling (Chapter 12) is

24、 used to describe the EDW database, which holds tables that are in third normal form (Chapter 14). Inmon believes that a fully normalized EDW is required to provide the necessary flexibility to support the various overlapping and distinct information requirements of all parts of the enterprise.Kimba

25、lls approach uses new methods and techniques in the development of an EDW. Kimball starts by identifying the information requirements (referred to as analytical themes) and associated business processes of the enterprise. This activity results in the creation of a critical document called a Data War

26、ehouse Bus Matrix. The matrix lists all of the key business processes of an enterprise together with an indication of how these processes are to be analyzed. The matrix is used to facilitate the selection and development of the first database (data mart) to meet the information requirements of a par

27、ticular group of users of the enterprise. This first data mart is critical in setting the scene for the later integration of other data marts as they come online. The integration of data marts ultimately leads to the development of an EDW. Kimball uses a new technique called dimensionality modeling

28、to establish the data model (referred to as a dimensional model (DM) for each data mart. Dimensionality modeling results in the creation of a dimensional model (commonly called a star schema) for each data mart that is highly denormalized. Kimball believes that the use of star schemas is a more intu

29、itive way to model decision support data and furthermore can enhance performance for complex analytical queries.33.3 Discuss the main principles and stages associated with Kimballs Business Dimensional Lifecycle.The main stages are summarized in Figure 33.1.33.4 Discuss the concepts associated with

30、dimensionality modeling.Every dimensional model (DM) is composed of one table with a composite primary key, called the fact table, and a set of smaller tables, called dimension tables. Each dimension table has a simple (noncomposite) primary key that corresponds exactly to one of the components of t

31、he composite key in the fact table. In other words, the primary key of the fact table is made up of two or more foreign keys. This characteristic “star-like” structure is called a star schema or star join. Another important feature of a DM is that all natural keys are replaced with surrogate keys. T

32、his means that every join between fact and dimension tables is based on surrogate keys, not natural keys. Each surrogate key should have a generalized structure based on simple integers. The use of surrogate keys allows the data in the warehouse to have some independence from the data used and produced by the OLTP systems.33.5 Describe how star, snowflake, and starflake schemas differ.Star schema is a logical structure that has a fact table containing factual data in the center, surrounded by dim