Page 198 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Data Warehousing and Data Mining

notes Materialised views

Aggregate data is calculated on the basis of the hierarchical relationships defined in the dimension
tables. These aggregates are stored in separate tables, called summary tables or materialised
views. Oracle provides extensive support for materialised views, including automatic refresh
and query rewrite.

Queries can be written either against a fact table or against a materialised view. If a query is
written against the fact table that requires aggregate data for its result set, the query is either
redirected by query rewrite to an existing materialised view, or the data is aggregated on the
fly.
Each materialised view is specific to a particular combination of levels; in Figure 10.5, only two
materialised views are shown of a possible 27 (3 dimensions with 3 levels have 3**3 possible level
combinations).

Example: Let, an organisation sells products throughtout the world. The main four major
dimensions are product, location, time and organisation.

figure 10.5: example of star schema

In the example Figure 10.5, sales fact table is connected to dimensions location, product, time
and organisation. It shows that data can be sliced across all dimensions and again it is possible
for the data to be aggregated across multiple dimensions. “Sales Dollar” in sales fact table can
be calculated across all dimensions independently or in a combined manner, which is explained
below:

1. Sales Dollar value for a particular product
2. Sales Dollar value for a product in a location
3. Sales Dollar value for a product in a year within a location
4. Sales Dollar value for a product in a year within a location sold or serviced by an
employee
10.3.2 Snowflake Schema

The snowflake schema is a variant of the star schema model, where some dimension tables are
normalised, thereby further splitting the data into additional tables. The resulting schema graph
forms a shape similar to a snowflake.

192 LoveLy professionaL university