Best Practices for Using Materialized Views in HP Neoview Release 2.4 (R2.4, R2.4 SP1, R2.5)

3
Background
Materialized views are targeted to improve query performance in two kinds of environments: classical
data warehouses (pure DSS), and mixed OLTP/DSS environments (e.g., ODS). The field survey
performed during the project analysis stage showed that HP customers mimic the materialized views’
behavior by manual maintenance of summary tables that are actually aggregate MVs. Maintaining
summary tables at the application level complicates system management, is bug-prone, and provides
poorer performance than built-in materialized views.
Schema organization
Sales
(Fact)
PartKey
StoreKey
TimeKey
Price
Part
(Dimension)
PartKey
Name
Category
Store
(Dimension)
StoreKey
Name
Address
ZipCode
Time
(Dimension)
TimeKey
Day
Month
Year
Figure 1 Sample star schema organization
The standard model of data in DSS environments consists of a group of fact tables, surrounded by a
set of dimension tables. Fact tables record the transaction history of the organization, hold massive
amounts of data, and typically are updated frequently. Dimension tables describe the organization
entities, are usually smaller, and are not frequently updated.
For example, in Figure 1 the fact table sales is associated with the part, store, and time dimensions.
The fact table is related to the dimension tables by referential integrity (RI) constraints. For
performance reasons, constraint checking is typically not enforced; it is assumed to have been done
by the front-end system. Dimension attributes can form multilevel hierarchies, where each level is
implemented in a separate table that has an RI relationship to tables in the next level. This design is
often referred to as a snowflake schema. However, for performance reasons multilevel hierarchies are
often stored in a single “de-normalized dimension” table. This special case of a snowflake schema
that implies a fact table surrounded by a single level of dimension tables is referred to as a star
schema. INSERTs to the fact table are the most typical operation at the loading phase; however, other
operations can occur.
Typical data warehouse environments are characterized by disjoint refresh window and query
window phases. The first window (typically occurring once a day) is intended for loading new data to
the warehouse and updating the summary tables towards the query window. During this phase, the
database is non-operational for query purposes. The process of loading data is finely controlled, in
the sense that data arrives from known sources at known times. This design allows for the