Lesson 5-2 - Defining a Fact Relationship
Applies to: 
SQL Server Analysis Services 
Azure Analysis Services
Fabric/Power BI Premium
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 SQL Server 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 SQL Server 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. The storage mode for each dimension can be configured independently.
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 Define 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
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 2019 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 SQL Server 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.
Defining a Fact Relationship for the Fact Dimension
In the Cube Designer for the SQL Server Analysis Services Tutorial cube, 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.
Click the browse button (...) in the Item Description cell, at the intersection of the Internet Sales measure group and the Internet Sales Order Details dimension, 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.
Click Cancel.
Browsing the Cube by Using the Fact Dimension
On the Build menu, click Deploy Analysis Services Tutorial to deploy the changes to the instance of SQL Server Analysis Services and process the database.
After deployment has successfully completed, click the Browser tab in Cube Designer for the SQL Server Analysis Services Tutorial cube, and then click the Reconnect button.
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 Filter.
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.
The following image shows the result of the previous steps.
Next Task in Lesson
Defining a Many-to-Many Relationship
See Also
Dimension Relationships
Define a Fact Relationship and Fact Relationship Properties