Aggregates

A fact table can grow to have millions of rows. Indexes could be used to search for required data but they would be inefficient. A better method would be the use of aggregates.

Aggregate fact tables can be constructed by attempting to predict the type of question that has to be answered. There would then be several fact tables built into the star schema (snowflake). Examples:

• Sales to each member of each wine summed for each quarter.
• Sales to each wine summed by each area for each month.
• Sales to each wine summed for each area for each quarter.

The degree of aggregation is somtimes referred to as the granularity of the data:

• All detail - No aggregation - lots of small grains
• Monthly - lightly aggregated - a lesser number of larger grains
• Quarterly - highly aggregated - fewer, larger grains

The techniques of drilling down and drilling up between the aggregate levels enable powerful reporting facilities.

Aggregate tables should be monitored. If it is found that aggregate tables are not being used they should be dropped.

Aggregate Navigation

An aggregate navigator is a level of software that enables a user to access aggregate tables. Metadata is held by the aggregate navigator in the data warehouse or separate database. The metadata is used to map the queries to the data warehouse (including aggregate tables).

Any SQL query can be checked by the aggregate navigator to establish the columns required and the level of aggregation needed.

The aggregate navigator can also record which aggregate tables are being accessed. In addition it can record response times, who is using the aggregate tables and provide statistics to enable new aggregate tables to be constructed.

Presentation of Information

The final component of a data warehouse is the presentation component. The usual configuration is that of client/server where the user is using a PC (client) and the data warehouse resides on its own server. The system operates over a network and the client software can usually present the information in graphical form in addition to textual reports.

Comments, suggestions, ideas to
Stuart Banner