Article
10/01/2010

Defining a Fact Relationship

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 dimensions. 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 Analysis Services, 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 Solution Explorer, right-click Dimensions, and then click New Dimension.
On the Welcome to the Dimension Wizard page, click Next.
On the Select Creation Method page, verify that the Use an existing table option is selected, and then click Next.
On the Specify Source Information page, verify that the Adventure Works DW data source view is selected.
In the Main table list, select InternetSales.
In the Key columns list, verify that SalesOrderNumber and SalesOrderLineNumber are listed.
In the Name column list, select SalesOrderLineNumber.
Click Next.
On the Select Related Tables page, clear the check boxes beside all of the tables, and then click Next.
On the Select Dimension Attributes page, click the check box in the header twice to clear all of the check boxes. The Sales Order Number attribute will remain selected because it is the key attribute.
Select the Customer PO Number attribute, and then click Next.
On the Completing the Wizard page, change the name to Internet Sales Order Details and then click Finish to complete the wizard.
On the File menu, click Save All.
In the Attributes pane of the Dimension Designer for the Internet Sales Order Details dimension, select Sales Order Number, and then change the Name property in the Properties window to Item Description.
In the NameColumn property cell, click the browse button (…). In the Name Column dialog box, select Product from the Source table list, select EnglishProductName for the Source column, 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 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.
To add the newly created dimension to the Analysis Services Tutorial cube as a cube dimension, switch to Cube Designer. On the Cube Structure tab, right-click in the Dimensions pane and select Add Cube Dimension.
In the Add Cube Dimension.dialog box, select Internet Sales Order Details and then click OK.