Home

	Expand Search

WarehouseArchitect - User's Guide

Part 3 WarehouseArchitect Model

Chapter 16: Aggregation and Partitioning

Partitioning a fact

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.