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 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.
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.
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.
Defining a Fact Relationship for the Fact Dimension
To define a fact relationship for the Fact dimension
Switch to Cube Designer for the Analysis Services Tutorial cube, and then click the Dimension Usage tab.
Notice that the Internet Sales Order Details cube dimension is automatically configured as having a fact relationship, as shown by the unique icon.
The following image shows the Internet Sales Order Details cube dimension with a fact relationship.
At the intersection of the Internet Sales measure group and the Internet Sales Order Details dimension, click the ellipsis button (…) in the Item Description cell to review the fact relationship properties.
The Define Relationship dialog box opens. Notice that you cannot configure any of the properties.
The following image shows the fact relationship properties in the Define Relationship dialog box.
Browsing the Cube by Using the Fact Dimension
To browse the cube by using the Fact Dimension
On the Build menu, click Deploy Analysis Services Tutorial to deploy the changes to the instance of Analysis Services and process the database.
After deployment has successfully completed, click the Browser tab in Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect.
Clear all measures and hierarchies from the Data pane, and then add the Internet Sales-Sales Amount measure to the data area of the Data pane.
In the Metadata pane, expand Customer, expand Location, expand Customer Geography, expand Members, expand All Customers, expand Australia, expand Queensland, expand Brisbane, expand 4000, right-click Adam Powell, and then click Add to Subcube Area.
Filtering to limit the sales orders returned to a single customer lets the user drill down to the underlying detail in a large fact table without suffering a significant loss in query performance.
Add the Internet Sales Orders user-defined hierarchy from the Internet Sales Order Details dimension to the row area of the Data pane.
Notice that the sales order numbers and the corresponding Internet sales amounts for Adam Powell appear in the Data pane.
Expand each sales order number in the row area to view the details of each line item in those orders.
The following image shows the dimensioning from the previous steps.