Chapter 16

 1.  Why are operational databases not particularly suited for decision support applications?
 Ans: Operational databases must be cleaned and integrated for decision support. Decision support needs a broad view that integrates business processes. Because of the different requirements, operational databases are usually separate from databases for decision support. Using a common database for both kinds of processing can significantly degrade database performance and make it difficult to summarize activity across business processes.

2.   How is a data warehouse different from a data mart?
Ans: A data warehouse is a central repository for summarized and integrated data from operational databases and external resources. A data mart is a subset of a data warehouse typically at a department or functional level.

 3.  When is the three-tier data warehouse architecture more appropriate than the two-tier data warehouse architecture?
Ans: The three-tier data warehouse architecture is more appropriate when the two-tier data warehouse architecture has performance problems for large data warehouse with data-intensive applications for decision support.

 4.  What are the components of an enterprise data model?
Ans: An enterprise data model contains the structure of a data warehouse and the metadata to access operational databases and external data sources. The EDM may also contain details about cleaning and integrating data sources.

 5.  What are some causes for failures in data warehouse projects?
Ans: Failures in data warehouse projects have been caused by poor planning or too much emphasis being placed on first creating an enterprise data model. The difficulty of cleaning and integrating data sources has also been the downfall of some projects.

 6.  Does a bottom-up data warehouse architecture use an enterprise data model?
Ans: No, a bottom-up data warehouse architecture does not use an enterprise data model. However, a data mart contains a smaller-scale data model that contains the metadata to access operational databases and external data sources.

 7.  What is an oper mart?
Ans: An oper mart is a just-in-time data mart, usually built from one operational database in anticipation or in response to major events such as disasters and new product introductions. An oper mart supports peak demand for reporting and business analysis that accompanies a major event. After the decision support demand subsides, the oper mart may be dismantled or it may be merged into an existing data warehouse.

8.   What is the purpose of the data warehouse maturity model?
Ans: The maturity model is a framework that provides guidance about investment decisions in data warehouse technology. The model consists of 6 stages (prenatal, infant, child, teenager, adult, and sage) in which business value increases as organizations progress to higher stages.

9.   What is an important insight of the data warehouse maturity model?
Ans: An important insight of the maturity model is the difficulty of moving between certain stages. For small but growing organizations, moving from the infant to the child stages can be difficult because a significant investment in data warehouse technology is necessary. For large organizations, the struggle is to move from the teenager to the adult stage. To make the transition, upper management must perceive the data warehouse as a vital enterprise resource, not just a tool provided by the information technology department.

10. What are the advantages of multidimensional representation over relational representation for business analysts?
Ans: A multidimensional representation is conceptually easier to understand and visualize if the underlying data model has more than two dimensions.

11. Explain why a dimension may have multiple hierarchies.
Ans: A dimension may have multiple hierarchies to provide alternative organizations among dimension elements. For example, a location dimension can have one hierarchy for country, state, and city and a second hierarchy for country, state, and zip code. In a dimension with multiple hierarchies, usually at least one level is shared.

12. What are the advantages of using time-series data in a cell instead of time as a dimension?
Ans: Using time-series data allows users to store all historic data in one cell, instead of specifying a separate time dimension. A major advantage is that a number of statistical functions can operate directly on time-series data.

13. How is slicing a data cube different from dicing?
Ans: Slicing data focuses on a subset of the dimensions to gain insights. Dicing data focuses on a subset of members to gain insights.

14. What are the differences between drilling down and rolling up a data cube dimension?
Ans: Drilling down a data cube allows users to navigate to more detailed levels of hierarchical dimensions. Rolling up is the opposite operation allowing a user to navigate to more general levels of hierarchical dimensions.

15. How is a pivot operation useful for multidimensional databases?
Ans: The pivot operation supports rearrangement of the dimensions in a data cube.

16. Explain the significance of sparsity in a data cube.
Ans: Sparsity indicates the extent of empty cells in a data cube. Sparsity can be a problem if two or more dimensions are related.  For example, if certain products are sold only in selected states, cells may be empty. If a large number of cells are empty, the data cube can waste space and be slow to process. Special compression techniques can be used to reduce the size of sparse data cubes.

17. What is a star schema?
Ans: A star schema consists of a fact table surrounded by dimension tables. There is a 1-M relationship from each dimension table to the fact table.

18. What are the differences between fact tables and dimension tables?
Ans: Fact tables store numeric data (facts), while dimension tables store descriptive data corresponding to individual dimensions of the data cube.

19. How does a snowflake schema differ from a star schema?
Ans: A snowflake schema has multiple levels of dimension tables related to one or more fact tables. In contrast, a star schema only has one level of dimension tables. You should consider the snowflake schema instead of the star schema for small dimension tables that are not in 3NF.

20. What is a constellation schema?
Ans: A constellation schema contains multiple fact tables in the center related to dimension tables. Typically the fact tables share some dimension tables.

21. How is time represented for a fact table?
Ans: Most fact tables have time represented as a foreign key to a time table with augmentation for time of day if required. Using a time dimension table instead of a timestamp data type supports convenient representation of organization specific calendar features such as holidays, fiscal years, and week numbers that are not represented in timestamp data types.

22. What is an accumulating fact table?
Ans: a fact table that records the status of multiple events rather than one event. Each event column can be represented by a foreign key to the time table along with a time of day column if needed.

23. What is the difference between Type II and Type III representations for historical dimension integrity?
Ans: A Type II representation uses a version number to augment the primary key of a dimension table allowing unlimited history storage. In contrast, a Type III representation uses use additional columns to maintain a fixed-length history.

24. What is the purpose of the Oracle CREATE DIMENSION statement?
Ans: The star schema and its variations do not provide explicit representation of hierarchical dimensions. A dimension table does not define the hierarchical relationships among the levels of a dimension. Because dimension definition is important to support data cube operations as well as optimization techniques for relational database queries, many relational DBMS vendors have created proprietary SQL extensions for dimensions such as the CREATE DIMENSION statement.

25. What is the purpose of the SQL CUBE operator?
Ans: The CUBE operator augments the normal GROUP BY result with all combinations of subtotals. The CUBE operator is appropriate to summarize columns from independent dimensions rather than columns representing different levels of a single dimension.

26. What is the purpose of the SQL ROLLUP operator?
Ans: The CUBE operator augments the normal GROUP BY result a partial set of subtotals. The ROLLUP operator is appropriate to summarize columns from a single dimension.

27. What is the purpose of the SQL GROUPING SETS operator?
Ans: The GROUPING SETS operator requires explicit specification of column combinations. The GROUPING SETS operator is appropriate when precise control over grouping and subtotals is required.

28. Briefly list some of the variations of the CUBE, ROLLUP, and GROUPING SETS operators.
Ans: A partial CUBE can be done to produce subtotals for a subset of independent dimensions. A partial ROLLUP can be done to produce subtotals for a subset of columns from the same dimension. Composite columns can be used with the CUBE or ROLLUP operators to skip some subtotals. CUBE and ROLLUP operations can be included in a GROUPING SETS operation.

29. Why are materialized views important for data warehouses but not important for operational databases?
Ans: Materialized views are attractive in data warehouses because the source data is stable except for periodic refreshments that can usually be performed during non peak times. In contrast, non-materialized views dominate operational database processing because the refresh cost can be high.

30. What materialization properties does Oracle 10g provide for materialized views?
Ans: The Oracle materialization properties include the method of refresh (incremental or complete), the timing of refresh (on demand or on commit), and the timing of build (immediate or deferred). Oracle has a number of restrictions on the types of materialized views that can be incrementally refreshed.

31. Compare and contrast query rewriting for materialized views to query modification for traditional (non materialized) views.
Ans: The query rewriting process for materialized views reverses the query modification process for traditional views. The query modification process substitutes base tables for views in queries that reference views so that materialization of the views is not needed. In contrast, the query rewriting process substitutes materialized views for base tables to avoid accessing large fact tables. In both processes, the DBMS performs the substitution process, not the user. In the query rewrite process, the query optimizer must evaluate whether the substitution will improve performance over the original query. Overall, query rewriting is more complex than query modification because query rewriting involves a more complex substitution process and query rewriting requires the optimizer to evaluate costs.

32. Briefly explain the matching processes to enable query rewriting.
Ans: Query rewriting involves matching between available materialized views and a data warehouse query referencing fact and dimension tables. Matching involves row conditions, grouping for level of detail, grouping for functional dependencies, and aggregate functions.

33. Explain the significance of indexing fact and dimension tables in a data warehouse.
Ans: Because data warehouses are used mostly for retrieval, many indexes can be used to speed retrieval speed. When refreshing a data warehouse, indexes can be dropped to speed the loading process and then recreated after loading completes.

34. What are the pros and cons of a MOLAP storage engine?
Ans: The storage engines of MOLAP systems are optimized for the unique characteristics of multidimensional data such as sparsity and complex aggregation across thousands of cells.  Because data cubes are precomputed, MOLAP query performance is generally better than competing approaches that use relational database storage.  Even with techniques to deal with sparsity, MOLAP engines can be overwhelmed by the size of data cubes.  A fully calculated data cube may expand many times as compared to the raw input data.  This data explosion problem limits the size of data cubes that MOLAP engines can manipulate.

35. What are the pros and cons of a ROLAP storage engine?
Ans: Despite the intensive research and development on ROLAP storage and optimization techniques, MOLAP engines still provide faster query response time. However, MOLAP storage suffers from limitations in data cube size so that ROLAP storage is preferred for fine-grained data warehouses. In addition, the difference in response time has narrowed so that ROLAP storage may involve only a slight performance penalty if ROLAP storage and optimization techniques are properly utilized.

36. What are the pros and cons of a HOLAP storage engine?
Ans:   HOLAP allows a data warehouse to be divided between relational storage of fact and dimension tables and multidimensional storage of summary data cubes. When an OLAP query is submitted, the HOLAP system can combine data from the ROLAP managed data and the MOLAP managed data. Despite the appeal of HOLAP, there are potential disadvantages that may limit its use. First, HOLAP can be more complex than either ROLAP or MOLAP especially if the DBMS vendor does not provide full HOLAP support. To fully support HOLAP, the DBMS vendor must provide both MOLAP and ROLAP engines as well as tools to combine both storage engines in the design and operation of a data warehouse. Second, there is considerable overlap in functionality between the storage and optimization techniques in ROLAP and MOLAP engines. It is not clear whether the ROLAP storage and optimization techniques should be discarded or used in addition to the MOLAP techniques. Third, because the difference in response time has narrowed between ROLAP and MOLAP, the combination of MOLAP and ROLAP may not provide significant performance improvement to justify the added complexity.

37. List some storage and optimization techniques used in ROLAP engines.
Ans:   Bitmap join indexes are particularly useful for columns in dimension tables with few values. Star join query optimization uses bitmap join indexes on dimension tables to reduce the number of rows in the fact table to retrieve. Query rewriting using materialized views can eliminate the need to access large fact and dimension tables. Summary storage advisors determine the best set of materialized views that should be created and maintained for a given query workload. Partitioning, striping, and parallel query execution provide opportunities to reduce the execution time of data warehouse queries.

38. What is cooperative change data?
Ans:   Cooperative data involves notification from the source system about changes. The notification typically occurs at transaction completion time using a trigger. Cooperative change data can be input immediately into the data warehouse or placed in queue for later batch input with other changes.

39. What is logged change data?
Ans:   Logged change data involves files that record changes or other user activity.  For example, a transaction log contains every change made by a transaction, and a web log contains page access histories (called clickstreams) by website visitors. Logged change data usually involves no changes to source systems as logs are readily available for most source systems.

40. What is queryable change data?
Ans:   Queryable change data comes directly from a data source via a query. Queryable change data requires timestamping in the data source.  Since few data sources completely support timestamps, queryable change data must be augmented with other kinds of change data. Queryable change data is most applicable for fact tables using fields such as order data, shipment data, and hire data that are stored in a data source.

41. What is snapshot change data?
Ans:   Snapshot change data involves periodic dumps of source data. To derive change data, a difference operation uses the two most recent snapshots. The result of a difference operation is called a delta. Snapshots are the most common form of change data because of applicability. Snapshots are the only form of change data without requirements on a source system.

42. Briefly describe the phases of data warehouse maintenance.
Ans:   The preparation phase manipulates change data from individual source systems. The preparation phase involves extraction, transportation, cleaning, and auditing. The integration phase involves merging the separate, cleaned sources into one source. Merging can involve removal of inconsistencies among the source data. Auditing involves recording results of the merging process, performing completeness and reasonableness checks, and handling exceptions. The update phase involves propagating the integrated change data to various parts of the data warehouse including fact and dimension tables, materialized views, stored data cubes, and data marts. After propagation, notification can be sent to user groups and administrators.

43. List common data quality problems that should be resolved by the preparation and integration phases.
Ans: Multiple identifiers, multiple names, different units, missing values, orphaned transactions, multipurpose fields, and conflicting data.

44. What is the benefit of using ETL tools in data warehouse maintenance?
Ans: ETL tools eliminate the need to write custom coding for many maintenance tasks. Most ETL tools use rule specifications rather than procedural coding to indicate logic and actions. Some ETL tools can generate code that can be customized for more flexibility.

45. What is valid time lag?
Ans: Valid time lag is the difference between the occurrence of an event in the real world (valid time) and the storage of the event in an operational database (transaction time).

46. What is load time lag?
Ans: Load time lag is the difference between transaction time (storage of an event in an operational database) and the storage of the event in the data warehouse (load time).

47. What is the primary objective in managing the refresh process for a data warehouse?
Ans: The primary objective in managing the refresh process is to determine the refresh frequency for each data source. The optimal refresh frequency maximizes the net refresh benefit defined as the value of data timeliness minus the cost of refresh. The value of data timeliness depends on the sensitivity of decision making to the currency of the data. The cost to refresh a data warehouse includes both computer and human resources. Computer resources are necessary for all tasks in the maintenance workflow.  Human resources may be used in the auditing tasks in the preparation and integration phases.

48. What types of constraints affect the refresh process?
Ans: Source access constraints can be due to legacy technology with restricted scalability for internal data sources or coordination problems for external data sources. Integration constraints often involve identification of common entities such as customers and transactions across data sources. Completeness/consistency constraints can involve maintenance of the same time period in change data or inclusion of change data from each data source for completeness. Data warehouse availability often involves conflicts between online availability and warehouse loading.