Home

	Expand Search

WarehouseArchitect - User's Guide

Part 3 WarehouseArchitect Model

Chapter 16: Aggregation and Partitioning

Chapter 16
Aggregation and Partitioning

About this chapterThis chapter explains how to aggregate and partition facts in a WAM.

Aggregating a fact

What is aggregation?Aggregation is a technique that optimizes data retrieval by summarizing rows of a fact table according to a specific dimension.

Why you do itOperational systems typically load data into a data warehouse at an atomic level of detail. However, most decision support systems require consolidated, or aggregated information, not atomic detail.

What happensWhen you aggregate data, you summarize it along frequently-accessed lines of query. The summarized, or aggregated, data is stored in a single aggregated fact table which can be easily accessed by the decision support system.

Creating an aggregated fact table

When you aggregate a fact, you create a new fact table. The aggregated fact table and the original fact table form a hierarchy of facts.

Each time you create an aggregated fact table, you add another table to the hierarchy.

• To create an aggregated fact table:
  1. Select WAM-->Aggregation Wizard.

     The Wizard for Fact Table Aggregation dialog box appears.

     

  2. Click the Select button.

     The List of Fact Tables appears. It lists only the facts in the Multidimensional Hierarchy that are attached to fact tables.

     

  3. Select the fact table to aggregate.

     The name and code of the selected fact table appears in the Name and Code boxes.

  4. Click Next.

     The dialog box displays the multidimensional hierarchy for the fact corresponding to the selected fact table.

     

  5. Expand dimension nodes, and clear checkboxes to define the level of granularity of the aggregation.

     By default all checkboxes are selected.

     When you clear a checkbox for a dimension in a dimension hierarchy, or for an attribute in an attribute hierarchy, all checkboxes for lower level dimensions or attributes are cleared.

  6. Click Next.

     

  7. Type a name and a code.

     or

     Type a name and click the Equals button after the Code box.

     or

     Type a code and click the Equals button after the Name box.

  8. Select the Show Aggregated Tables checkbox to display the aggregated table in the WAM.

     
     

     Displaying an aggregated table If you do not select the Show Aggregated Tables checkbox, you can display the table by selecting Dictionary-->List of Tables and selecting the corresponding Display checkbox.

     
     

  9. Click Finish to create the aggregated table.

     A symbol for the aggregated table appears in the WAM. The aggregated fact table contains foreign key columns that correspond to the primary key columns in the dimension tables.

     

     
     

     Columns in fact table not displayed in aggregated fact table Columns in the fact table other than primary or foreign key columns, only appear in the aggregated fact table when they are attached to a metric or an attribute. To attach a column to a metric or an attribute, use the object property sheet in the Multidimensional Hierarchy.

     
     

Example of aggregation in a star schema

Structure of a star schemaA star schema typically has a central fact table linked to a number of dimension tables. Each dimension table has an associated hierarchy of attributes corresponding to the levels of detail of the dimension data. Each attribute in the attribute hierarchy corresponds to a column in the dimension table.

What aggregation involvesAggregation of data in a star schema involves selecting a level of dimension detail by removing attributes. This creates a new dimension table that only has columns for the selected attributes.

In this example, a central fact table (Sales) is linked to the dimension tables Customer, Product, Store and Time.

Graphical structureThe graphical structure of the WAM looks like this:

Multidimensional structureThe multidimensional structure of the WAM looks like this:

The Time dimension has a hierarchy of attributes corresponding to Year, Quarter, Month, and so on.

To aggregate data so that you can quickly access data on sales per quarter, you clear the Month checkbox. Because the attributes are organized in a hierarchy, checkboxes for all levels below Month are automatically cleared.

ResultThe result of aggregation is an aggregated fact table (Sales per Quarter), and a new dimension table (Time2). The Time2 dimension table only has columns for Year and Quarter.

Example of aggregation in a snowflake schema

Structure of a snowflake schemaA snowflake schema typically has a central fact table linked to a number of dimension tables. Each dimension table is linked to other dimension tables to form a dimension hierarchy. The number of tables in the dimension hierarchy correspond to the level of detail of the dimension data.

What aggregation involvesAggregation of data in a snowflake schema involves selecting a level of dimension detail. The result of aggregation is a new fact table that is linked directly to the dimension table corresponding to the required level of dimension detail.

This example shows a typical snowflake schema. A central table (Sales) is linked to the dimension tables Product, Customer, and Store. It is also linked to a dimension hierarchy that contains the tables Year, Quarter, Month, Week, and Day.

Graphical structureThe graphical structure of the WAM looks like this:

Multidimensional structureThe multidimensional structure of the WAM looks like this:

To aggregate data so that you can quickly access data on sales per quarter, you clear the Month checkbox. Because the dimensions are organized in a hierarchy, checkboxes for all levels below Month are automatically cleared.

ResultThe result of aggregation is an aggregated fact table (Sales per Quarter) that is linked to the original fact table, and to the Quarter dimension table.

Partitioning a fact

What is partitioning?Partitioning is a technique that optimizes data retrieval by dividing rows of a fact table according to one or more dimensions, for particular values of a dimension column.

When you do itWhen a large quantity of atomic level data is stored in a single table, you can improve query response time by separating, or partitioning, the data into multiple atomic level tables.

What happensWhen you partition data, you group data from different dimensions, for different values of each dimension. The pre-selected, or grouped data is stored in separate fact tables which can be easily accessed by the decision support system.

Creating a partitioned fact table

A partitioned fact table contains atomic level data for specific values of one or more dimension attributes. For example, you could partition a sales fact table to obtain three separate fact tables containing data on the sales of red telephones for the years 1995, 1996 and 1997.

The number of partitioned tables you create depends on the number of values you specify for each dimension attribute. For example, specifying the sales of blue telephones as well would result in the creation of six separate fact tables.

You create partitioned tables by selecting dimension attributes. You can only partition a fact table when the columns in the dimension tables are attached to attributes.

• To partition a fact table:
  1. Select WAM-->Partitioning Wizard.

     The Wizard for Fact Table Partitioning dialog box appears.

     

  2. Click the Select button.

     The List of Fact Tables appears. It lists only the facts in the Multidimensional Hierarchy that are attached to fact tables.

     

  3. Select the fact to partition.

     The name and code of the selected fact appear in the Name and Code boxes.

  4. Click Next.

     The dialog box displays the multidimensional hierarchy for the selected fact.

     

  5. Expand dimension nodes, and select attribute checkboxes to define the partitioning criteria.

     By default, all checkboxes are cleared.

     

  6. Click Next.

     The dialog box lists the selected attributes, their code, and the fact or dimension to which they are attached.

     

  7. Select an attribute

     An arrow appears at the beginning of the line.

  8. Click the Values button.

     The List of Values dialog box appears.

     

  9. Type a value of the selected attribute.

     A partitioned table is created for each value of the attribute.

  10. Type a label in the Label column.

  11. Click OK.

  12. Repeat steps 6 to 10 for each selected attribute.

      You must type at least one value for each attribute.

  13. Select the Show Partitioned Tables checkbox to display the created table.

      
      

      Displaying a partitioned table If you do not select the Show Partitioned Tables checkbox, you can display the tables by selecting Dictionary-->List of Tables and selecting the corresponding Display checkboxes.

      
      

  14. Click Finish.

      Partitioned tables appear in the WAM.

      
      

      Columns in fact table not displayed in partitioned fact tables Columns in the fact table other than primary or foreign key columns, only appear in the partitioned fact tables when they are attached to metrics or attributes. To attach a column to a metric or an attribute, use the object property sheet in the Multidimensional Hierarchy.

      
      

Example of partitioning

The result of partitioning is the same, regardless of the type of schema.

In this example, a central fact table (Sales) is linked to the dimension tables Customer, Product, Store and Time.

Graphical structureThe graphical structure of the WAM looks like this:

Multidimensional structureThe multidimensional structure of the WAM looks like this:

To partition data so that you can quickly access data on sales for the years 1995 and 1996, you select the attribute checkbox for Year.

To access data on sales of a particular category of products for these years, you also select the Category checkbox.

To specify the years 1995 and 1996, you enter the values 1995 and 1996 in the List of Values for the Year attribute.

To specify data for deluxe and standard categories only, you enter the corresponding values in the List of Values for the Category attribute.

ResultThe result of partitioning is four partitioned fact tables. Each one is linked to each of the dimension tables.