Defining a Fact Relationship

Article
12/03/2008

Users sometimes want to be able to dimension measures by data items that are in the fact table or to query the fact table for specific additional related information, such as invoice numbers or purchase order numbers related to specific sales facts. When you define a dimension based on such a fact table item, the dimension is called a fact dimension. Fact dimensions are also known as degenerate dimension. Fact dimensions are useful for grouping together related fact table rows, such as all the rows that are related to a particular invoice number. Although you can put this information in a separate dimension table in the relational database, creating a separate dimension table for the information provides no benefit because the dimension table would grow at the same rate as the fact table, and would just create duplicate data and unnecessary complexity.

Within Microsoft SQL Server 2005 Analysis Services (SSAS), you can determine whether to duplicate the fact dimension data in a MOLAP dimension structure for increased query performance, or whether to define the fact dimension as a ROLAP dimension to save storage space at the expense of query performance. When you store a dimension with the MOLAP storage mode, all the dimension members are stored in the instance of Analysis Services in a highly compressed MOLAP structure, in addition to being stored in the measure group's partitions. When you store a dimension with the ROLAP storage mode, only the dimension definition is stored in the MOLAP structure—the dimension members themselves are queried from the underlying relational fact table at query time. You decide the appropriate storage mode based on how frequently the fact dimension is queried, the number of rows returned by a typical query, the performance of the query, and the processing cost. Defining a dimension as ROLAP does not require that all cubes that use the dimension also be stored with the ROLAP storage mode. This is different from SQL Server 2000 Analysis Services.

When you define a fact dimension, you can define the relationship between the fact dimension and the measure group as a fact relationship. The following constraints apply to fact relationships:

The granularity attribute must be the key column for the dimension, which creates a one-to-one relationship between the dimension and the facts in the fact table.
A dimension can have a fact relationship with only a single measure group.

Note

Fact dimensions must be incrementally updated after every update to the measure group that the fact relationship references.

For more information, see Dimension Relationships, and Defining a Fact Relationship and Fact Relationship Properties.

In the tasks in this topic, you add a new cube dimension based on the CustomerPONumber column in the FactInternetSales fact table. You then define the relationship between this new cube dimension and the Internet Sales measure group as a fact relationship.

Defining the Internet Sales Orders Fact Dimension

To define the Internet Sales Orders fact dimension

In Cube Designer for the Analysis Services Tutorial cube, click the Cube Structure tab.
Right-click anywhere in the Dimensions pane, and then click Add Cube Dimension.
In the Add Cube Dimension dialog box, click New dimension.

The Dimension Wizard opens.
On the Welcome to the Dimension Wizard page, click Next.
On the Select Data Source View page, click Next to select the data for the dimension from the Adventure Works DW data source view.
On the Select the Dimension Type page, click Next to specify that the dimension will be a standard dimension.
On the Select the Main Dimension Table page, select dbo.FactInternetSales in the Main table list.

Notice that a composite key is defined based on the SalesOrderNumber and SalesOrderLineNumber columns as the key columns, and that the SalesOrderLineNumber column is defined for the member name property. Notice also that you cannot define a column for the member name property from any table other than the FactInternetSales table.
Click Next.
On the Select Related Tables page, verify that no tables are selected, and then click Next.
On the Select Dimension Attributes page, clear all the selected dimension attributes, and then select the check box for the Customer PO Number dimension attribute.

Notice that you cannot select either of the composite keys as an attribute from within the Dimension Wizard. This is because the composite keys were defined as column keys. To add them as attributes in their own right, you will have to define them as attributes after the dimension is defined.
Click Finish, change the dimension name to Internet Sales Order Details, click Finish, and then click OK to add the newly created database dimension to the Analysis Services Tutorial cube as a cube dimension.
Open Dimension Designer for the Internet Sales Order Details dimension.
In the Attributes pane, select Internet Sales, and then change the Name property in the Properties window to Item Description.
In the NameColumn property cell, select (new), select Product for the source table, select EnglishProductName for the source column in the Object Binding dialog box, and then click OK.
Add the Sales Order Number attribute to the dimension by dragging the SalesOrderNumber column from the InternetSales table in the Data Source View pane to the Attributes pane.
Change the Name property of the new Sales Order Number attribute to Order Number, and change the OrderBy property to Key.
In the Hierarchies and Levels pane, create an Internet Sales Orders user hierarchy that contains the Order Number and Item Description levels, in that order.
In the Attributes pane, select Internet Sales Order Details, and then review the value for the StorageMode property in the Properties window.

Notice that, by default, this dimension is stored as a MOLAP dimension. Although changing the storage mode to ROLAP will save processing time and storage space, it occurs at the expense of query performance. For the purposes of this tutorial, you will use MOLAP as the storage mode.