Home

	Expand Search

WarehouseArchitect - User's Guide

Part 3 WarehouseArchitect Model

Chapter 14: Defining Multidimensional Objects

Defining fact tables and dimension tables

Defining fact tables and dimension tables

What is a fact table?A fact table stores variable numerical values related to aspects of a business. For example, sales, revenue, budget. These are usually the values you want to obtain when you carry out a decision support investigation. A fact table is at the intersection of dimension tables in a star schema.

ExampleThis fact table stores values related to the sales aspect of a business. It has columns for total sales and total revenue. It also has columns for the primary keys of the linked dimension tables.

A WAM can have a number of central fact tables linked to any number of dimension tables. The fact tables are usually the first tables you create when you create a WAM. Any other fact tables in the WAM are usually generated automatically as a result of aggregating or partitioning the central fact table. These fact tables form a hierarchy of facts.

A fact table is attached to a fact. When you create a fact table, WarehouseArchitect automatically creates a fact with the same name and attaches it to the table.

What is a dimension table?A dimension table stores data related to the axis of investigation of a fact. For example, geography, time, product. A WAM can have any number of dimension tables. Dimension tables are connected to a central fact table. The primary key in the dimension table migrates as a foreign key in the fact table. Dimension tables can also be connected to other dimension tables to form a hierarchy of dimensions.

ExampleThis dimension table stores data related to the time aspect of a business. It has columns for different levels of detail of dimension data.

A dimension table is attached to a dimension. When you create a dimension table, WarehouseArchitect automatically creates a dimension with the same name and attaches it to the table.

Organizing tables into a schema

The way in which you organize fact and dimension tables depends on the type of data warehouse schema you create.

Star schemaIn a star schema, a central fact table connects a number of individual dimension tables.

Snowflake schemaIn a snowflake schema, a central fact table connects a number of dimension tables, which in turn connect other dimension tables. A chain of connected dimension tables is called a dimension hierarchy.

Matrix schemaIn a matrix schema, a single fact table has columns corresponding to dimensions.

Creating a fact table or a dimension table

• To create a fact table or a dimension table:
  1. Click the Table tool in the tool palette.

  2. Click anywhere in the model window.

     A table symbol appears at the click position.

     

     At creation, a table is named Tab_n, where n is the number of the table in the order of creation of objects in the model.

  3. Click the Pointer tool.

  4. Double-click the table symbol in the model.

     The table property sheet appears.

     

  5. Type a table name and a table code.

     or

     Type a name and click the Equals button in the Code column.

     or

     Type a code and click the Equals button in the Name column.

  6. Select Fact from the Type dropdown listbox.

     or

     Select Dimension from the Type dropdown listbox

  7. Click OK.

Modifying the type of a PDM table

When you use a PDM as the basis for a WAM, you can define the existing tables as fact or dimension tables.

• To modify the type of a PDM table:
  1. Double-click a table symbol.

     The table property sheet appears. By default, the table type is None.

  2. Select Fact from the Type dropdown listbox.

     or

     Select Dimension from the Type dropdown listbox.

  3. Click OK.

     A symbol corresponding to the selected table type appears in the top left corner of the table symbol.

Adding a fact or a dimension to a table

A fact table can have more than one attached fact. A dimension table can have more than one attached dimension.

• To add a fact or a dimension to a table:
  1. Double-click a fact table symbol.

     or

     Double-click a dimension table symbol.

     The object property sheet appears.

     

  2. Click the Ellipses button after the Type box.

     If you selected a fact table in step 1, the List of Facts appears. It displays the facts attached to the table. By default, it contains a fact with the same name as the table.

     If you selected a dimension table in step 1, the List of Dimensions appears. It displays the dimensions attached to the table. By default, it contains a dimension with the same name as the table.

     

  3. Click the Add button.

     If you selected a fact table in step 1, a list of available facts appears.

     If you selected a dimension table in step 1, a list of available dimensions appears.

     

  4. Select one or more facts.

     or

     Select one or more dimensions.

  5. Click OK in each dialog box.

Modifying the type of a fact table or a dimension table

Fact tables have associated facts, and dimension tables have associated dimensions.

When you modify the type of a fact or dimension table, you must indicate whether to simply detach the associated facts or dimensions from the table, or to delete them from the dictionary.

Deletion of associated facts or dimensionsThe following table describes the confirmation choices possible for fact or dimension deletion.

Confirmation choice	Result
Delete the existing objects	Object deleted from dictionary
Detach only	Object remains in dictionary but is no longer attached to the table

• To modify the type of a fact or dimension table:
  1. Double-click a fact table symbol.

     or

     Double-click a dimension table symbol.

     The object property sheet appears.

  2. Select a table type from the Type dropdown listbox.

  3. Click OK.

     A confirmation message asks you how you want to delete the facts or dimensions associated with the table.

     

  4. Select a radio button.

  5. Click OK.