Home

	Expand Search

WarehouseArchitect - User's Guide

Part 3 WarehouseArchitect Model

Chapter 14: Defining Multidimensional Objects

Chapter 14
Defining Multidimensional Objects

About this chapterThis chapter explains how to define multidimensional objects and link them to objects in the physical model.

Defining facts and dimensions

What is a fact?A fact corresponds to the focus of a decision support investigation, for example, Sales, Revenue, and Budget are facts.

You attach facts to fact tables. When you create a fact table, a fact with the same name as the table is automatically created.

What is a dimension?A dimension defines the axis of the investigation of a fact. For example, Product, Region, and Time are the axes of investigation of the Sales fact.

You attach dimensions to dimension tables. When you create a dimension table, a dimension with the same name as the table is automatically created.

Creating a fact or a dimension

• To create a fact or a dimension:
  1. Select WAM-->List of Facts to create a fact.

     or

     Select WAM-->List of Dimensions to create a dimension.

     A list of objects of the selected type appears.

     

  2. Click a blank line in the list.

     or

     Click the New button.

     An arrow appears at the beginning of the line.

  3. Type a name and a 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.

  4. Click the Table column and attach the fact or dimension to a table.

  5. Click OK in each dialog box.

Attaching a fact or a dimension to a table

You attach facts to fact tables, and dimensions to dimension tables.

You can only attach one table to each fact or dimension.

When you attach a fact or dimension to a table with no table type, the table becomes a fact table or a dimension table.

• To attach a fact or a dimension to a table:
  1. Select WAM-->List of Facts.

     or

     Select WAM-->List of Dimensions.

     A list of objects of the selected type appears.

     

  2. Select a fact.

     or

     Select a dimension.

     An arrow appears at the beginning of the line.

  3. Click the Table column and click the Ellipses button.

     The List of Tables appears.

     If you selected the List of Facts in step 1, the List of Tables displays fact tables, and tables with no table type.

     If you selected the List of Dimensions in step 1, the List of Tables displays dimension tables, and tables with no table type.

     

  4. Select a table.

  5. Click OK.

     The name of the selected table appears in the Table column.

  6. Click OK.

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.

Defining metrics and attributes

What is a metric?A metric is a variable or measure that corresponds to the focus of an investigation. Metrics are typically numeric values. For example, Total and Price are metrics. A metric can be the result of an operation or calculation involving several columns of the fact table.

Metrics are attached to columns in a fact table. For example, the Sales Total metric is attached to the Sales Total column in the Sales fact table.

What is an attribute?An attribute is an object that qualifies a dimension. For example, Year is an attribute of the Date dimension.

Attributes are usually attached to columns in a dimension table. For example, the Month attribute is attached to the Month column in the Time dimension table.

Attributes can also be attached to facts, for example in a matrix schema.

Creating a metric or an attribute

You create a metric or an attribute for a selected fact or dimension.

When the fact or dimension is attached to a fact table or dimension table, the metric or attribute is automatically attached to the same table.

• To create a metric or an attribute:
  1. Select WAM-->List of Dimensions.

     or

     Select WAM-->List of Facts.

     A list of objects of the selected type appears.

     

  2. Select a dimension.

     or

     Select a fact.

  3. Click the Attributes button.

     or

     Click the Metrics button.

     A list of metrics or attributes attached to the selected dimension or fact appears.

     

  4. Click a blank line in the list.

     or

     Click the New button.

     An arrow appears at the beginning of the line, and the name of the table to which the selected fact or dimension is attached appears in the Table column.

     

  5. Type a name and a 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. Click the Column column to attach the attribute or metric to a column.

  7. Click OK in each dialog box.

Creating a metric or an attribute from a column

You can create metrics and attributes from columns in the WAM.

• To create a metric or an attribute from a column:
  1. Select WAM-->List of Dimensions.

     or

     Select WAM-->List of Facts.

     A list of objects of the selected type appears.

     

  2. Select a dimension.

     or

     Select a fact.

  3. Click the Metrics button.

     or

     Click the Attributes button.

     A list of metrics or attributes attached to the selected dimension or fact appears.

     

  4. Click the Add button.

     The List of Columns appears. It displays a list of available columns for the table to which the dimension or fact is attached.

     

  5. Select one or more columns.

  6. Click OK.

     The selected columns appear in the list of attributes or the list of metrics attached to the dimension or the fact.

     

  7. Click OK in each dialog box.

Attaching a metric or an attribute to a column

You attach metrics to columns in a fact table. For example, you would attach the Total metric to the Total column in the Sales fact table to give the total sales.

You generally attach an attribute to a column in a dimension table. For example, you would attach the Year attribute to the Year column in the Date dimension table.

You can attach an attribute to a column in a fact table when the fact table is not linked to a dimension table. For example, you might attach the Product code attribute to the Product code column in the Sales fact table.

• To attach a metric or an attribute to a column:
  1. Select WAM-->List of Dimensions.

     or

     Select WAM-->List of Facts.

     A list of objects of the selected type appears.

     

  2. Select a dimension.

     or

     Select a fact.

  3. Click the Attributes button.

     or

     Click the Metrics button.

     A list of attributes or metrics attached to the selected dimension or fact appears.

     

  4. Select an attribute.

     or

     Select a metric.

  5. Click the Column column of the attribute or metric, and click the Ellipses button.

     The List of Columns appears. It displays a list of available columns for the table to which the fact or dimension is attached.

     

  6. Select a column.

  7. Click OK.

     The name of the selected column appears in the Column column.

  8. Click OK in each dialog box.

Displaying a list of metrics or a list of attributes

You can display a list of all the metrics or all the attributes in the WAM.

• To display a list of metrics or a list of attributes:
  1. Select WAM-->List of Metrics.

     or

     Select WAM-->List of Attributes.

     If you selected List of Metrics, the List of Metrics appears. It lists all the metrics in the WAM.

     

     If you selected List of Attributes, the List of Attributes appears. It lists all the attributes in the WAM.

     

  2. Click OK.

Defining fact hierarchies and dimension hierarchies

What is a fact hierarchy?A fact hierarchy is a fact that is split into other facts. You automatically create a fact hierarchy when you aggregate or partition a fact table. A WAM usually only has one fact hierarchy.

Example of a fact hierarchyFor example, the facts Sales per Quarter and Sales per Year are the result of aggregation of the Sales fact. Both these facts form part of the hierarchy of the Sales fact.

The tables belonging to this fact hierarchy are shown below.

What is a dimension hierarchy?A dimension hierarchy is a dimension that is split into other more detailed dimensions. Each descending level in a dimension hierarchy corresponds to a finer level of detail. The number of levels in a dimension hierarchy corresponds to the available levels of granularity in a query. A WAM can have any number of dimension hierarchies.

Example of a dimension hierarchyFor example, Year, Quarter, and Month are part of the Time dimension hierarchy. Quarter contains a greater level of detail than Year, and Month contains a greater level of detail than Quarter.

The tables belonging to this dimension hierarchy are shown below.

Creating a fact hierarchy or a dimension hierarchy

• To create a fact hierarchy or dimension hierarchy:
  1. Select WAM-->List of Hierarchies.

     The List of Hierarchies appears.

     

  2. Click a blank line in the list.

     or

     Click the New button.

     An arrow appears at the beginning of the line.

  3. Type a name and a 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.

     Dimension Hierarchy appears by default in the Type column.

  4. Click OK to create a dimension hierarchy.

     or

     Select the Fact Hierarchy radio button to create a fact hierarchy, then click OK.

Adding a SQL Cube expression to a fact or metric

You add a SQL Cube expression to a fact or metric in the fact or metric property sheet. These property sheets can only be opened from the Multidimensional Hierarchy dialog box.

The SQL Cube expression is based on tables and columns. You must have a table listed for the fact, or a column listed for the metric, in order to type a SQL Cube expression.

You can use the Ellipses button from the property sheet of a fact or a metric to select a table for the fact, or a column for the metric.

• To add a SQL Cube expression to a fact or metric:
  1. Right-click on the fact in the Multidimensional Hierarchy dialog box.

     or

     Right-click on the metric in the Multidimensional Hierarchy dialog box.

     The fact or metric context menu appears.

  2. Select Properties from the context menu.

     The property sheet appears.

     

  3. (Optional) Click Zoom.

     The SQL Query Editor dialog box appears.

     

  4. Type the SQL Cube expression.

     If you clicked the Zoom button, you can use the SQL Query Editor to select items to add to the SQL Cube expression.

  5. Click OK in each of the dialog boxes.

Adding a formula to a metric

You can add any number of formulas to a metric. A metric can have either a formula or a SQL Cube expression, but not both.

You add a formula to a metric in the metric property sheet. This property sheet can only be opened from the Multidimensional Hierarchy dialog box.

• To add a formula to a metric:
  1. Right-click a metric in the Multidimensional Hierarchy dialog box.

     The metric context menu appears.

  2. Select Properties from the context menu.

     The metric property sheet appears.

     

  3. Clear the Column box.

     The SQL Cube list box is grayed and the Formula box is ungrayed.

  4. Type the formula in the Formula box.

  5. Click OK.

Retrieving multidimensional objects

The retrieval automatically converts the tables you select into fact or dimension tables.

The tables are converted as follows:

Original table	Conversion to
Parent	A dimension table
Child	A fact table, if the original table is not also a parent table
Self-referenced	Not converted
Nonreferenced	A fact table

You have the following conversion options:

Option	When selected
Retrieve facts	Retrieves fact tables
Retrieve metrics	Retrieves metric tables
Update existing facts	Updates already existing fact tables. If you click the Ellipses button, you can use the List of Facts dialog box to select existing facts
Retrieve dimensions	Retrieves dimension tables
Retrieve attributes	Retrieves attributes. When not selected, disables Update existing dimensions option
Update existing dimensions	Updates already existing dimension tables. If you click the Ellipses button, you can use the List of Dimensions dialog box to select existing dimensions
Rebuild multidimensional hierarchy	Opens the Rebuild Multidimensional Hierarchy dialog box immediately after retrieving the multidimensional objects. For information on rebuilding the Multidimensional Hierarchy, see the chapter Creating a Multidimensional Model

• To retrieve multidimensional objects:
  1. Select WAM-->Retrieve Multidimensional Objects.

     The Retrieve Multidimensional Objects dialog box appears.

     

  2. Select the All Tables radio button to convert all tables.

     or

     Select the List radio button, click the Question Mark button, select tables, and click OK.

  3. Select retrieve and update options.

  4. Select Rebuild Multidimensional Hierarchy to open the Rebuild Multidimensional Hierarchy dialog box immediately after the retrieval.

     or

     Clear Rebuild Multidimensional Hierarchy.

  5. Click OK.

     The multidimensional objects are retrieved.