14 - Physical design for decision support, warehousing, and OLAP

Abstract

This chapter focusses on two decision support technologies: data warehousing and Online Analytical Processing (OLAP). It reviews the physical design issues that arise relative to these decision support technologies and some of the solutions are illustrated with examples.. The chapter discusses the use of materialized views for faster query response in the data warehouse environment. The different general categories of OLAP storage are described, including relational (ROLAP), multidimensional (MOLAP), and hybrid (HOLAP) based on data density. The dimensional design approach is covered briefly, with examples illustrating star and snowflake schemas. The usefulness of the data warehouse bus is demonstrated with an example, showing the relationship of conformed dimensions across multiple business processes. The data warehouse bus leads to a data warehouse constellation schema with the possibility of developing a data mart for each business process. Approaches toward efficient processing are discussed, including some hardware approaches, the appropriate use of bitmap indexes, various materialized view update strategies, and the partitioning of data.