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

4
performance of actions that would be unacceptable in a highly available environment, for example,
locking tables for long periods. Typically, these systems have very stringent requirements on the size
of load windows compared to the size of query windows.
Summary tables
Summary tables are typically a result of aggregation on top of join between the fact table and one or
more dimensions. For example, consider a sales report summary:
Summary tables can be designed so that they are insensitive to changes in some dimension table,
which means that changes to that table do not affect the summary. In our example, if a part is
transferred to a new category in Q2, the user will probably not want this to affect the sales report in
Q1, even though this does not preserve the exact join semantics. In this case, the aggregate ignores
changes to the dimension table.
On the other hand, if the user wants to keep the summary in sync with the base tables, changing a
dimension table implies a change of the summary table to reflect the new category of the part, as if it
has always been that way. In data warehouse terminology this is restating history. In general,
changing the dimensions happens infrequently compared to changing the fact table, and occurs at
well-controlled points.
Mixed OLTP/DSS environments are powered in a non-stop mode, in a sense that data is continuously
streamed to a number of critical fact tables. Some ODS systems adopt the star schema like data
warehouses. For example, in telecommunication applications the fact tables are subject to continuous
inserts as call data arrives from switches. These tables are usually insert-only (no updates or deletes).
Locking them for long periods is unacceptable. Typically, data is inserted to the database in
chronological order and thus creates a new range (i.e., phone calls during the last hour). Summaries
should typically be updated in shorter intervals than in the data warehouse environment.
In both environments, different summaries may implement different levels of aggregation. Aggregates
with a coarse granularity are used for maintaining aggregates with finer granularity. This reuse is
commonly referred to as rollup. Data in different tables and summaries might have different life spans.
For example, data can be kept for three months in the fact table, for six months using aggregates
with fine granularity (e.g., calls per user), and for five years using aggregates with coarse granularity
(e.g., calls per region). Deleting or purging data at one level does not necessarily trigger updates to
other levels.
SELECT part.category, time.day, SUM(price) tot_price
FROM sales, part, time
WHERE sales.timekey = time.timekey
AND sales.partkey = part.partkey
GROUP BY part.category, time.day;