Defining a Referenced Relationship

Up to this point in the tutorial, each cube dimension that you defined was based on a table that was directly linked to the fact table for a measure group by a primary key to foreign key relationship. In Lesson 3 and Lesson 4, you included dimension attributes from a table that was not directly linked to the fact table, called a snowflaked table, within a dimension that was directly linked to the fact table by its key column. For example, in Lesson 3 you added attributes from the DimGeography table to the Customer dimension. The attributes from the DimGeography table were linked to the fact table by using the GeographyKey, an attribute from the Customer table and the primary key in the DimGeography table. Through this linkage, you were able to define the Customer Geography hierarchy and let users dimension customer sales by geography. For more information, see Defining a Referenced Relationship and Referenced Relationship Properties

In the tasks in this topic, you link the Geography dimension to the fact table for reseller sales through the Reseller dimension, which is called a reference dimension. This lets users dimension reseller sales by geography.

Dimensioning Reseller Sales by Geography

To dimension Reseller Sales by Geography

  1. In Solution Explorer, right-click Analysis Services Tutorial in the Cubes folder, and then click Browse.

  2. Remove all hierarchies from the Data pane, and then verify that the Reseller Sales-Sales Amount measure appears in the data area of the Data pane. Add it to the Data pane if it is not already there.

  3. From the Geography dimension in the Metadata pane, drag the Geography user-defined hierarchy to the Drop Row Fields Here area of the Data pane.

    Notice that the Reseller Sales-Sales Amount measure is not correctly dimensioned by the Country-Region attribute members in the Regions hierarchy, as shown in the following image.

    Dimensioned Reseller Sales-Sales Amount measure

  4. Open Data Source View Designer for the Adventure Works DW data source view.

  5. In the Diagram Organizer pane, select Reseller Sales, and then review the relationship between the DimGeography table and the FactResellerSales table.

    Notice that there is no direct link between these tables. However, there is an indirect link between these tables through either the DimReseller table or the DimSalesTerritory table.

  6. Double-click the arrow that represents the foreign key–primary key relationship between the DimGeography table and the DimReseller table.

    In the Edit Relationship dialog box, notice that the GeographyKey column is the primary key in the DimGeography table and the foreign key in the DimReseller table, as shown in the following image.

    Edit Relationship dialog box

  7. Click Cancel, switch to Cube Designer for the Analysis Services Tutorial cube, and then click the Dimension Usage tab.

    Notice that the Geography cube dimension does not currently have a relationship with either the Internet Sales measure group or the Reseller Sales measure group, as shown in the following image.

    Current relationships of Geography cube dimension

  8. Click the ellipsis button () in the Full Name cell at the intersection of the Customer dimension and the Internet Sales measure group.

    In the Define Relationship dialog box, notice that a Regular relationship is defined between the DimCustomer dimension table and the FactInternetSales measure group table based on the CustomerKey column in each of these tables. All the relationships that you have defined within this tutorial up to this point have been regular relationships.

    The following image shows the Define Relationship dialog box with a regular relationship between the DimCustomer dimension table and the FactInternetSales measure group table.

    Define Relationship dialog box

  9. Click Cancel.

  10. Click the ellipsis button () in the unnamed cell at the intersection of the Geography dimension and the Reseller Sales measure group.

    In the Define Relationship dialog box, notice that no relationship is currently defined between the Geography cube dimension and the Reseller Sales measure group. You cannot define a regular relationship because there is no direct relationship between the dimension table for the Geography dimension and the fact table for the Reseller Sales measure group.

  11. In the Select relationship type list, select Referenced.

    You define a referenced relationship by specifying a dimension that is directly connected to the measure group table, called an intermediate dimension, that Analysis Services can use to link the reference dimension to the fact table. You then specify the attribute that links the reference dimension to the intermediate dimension.

  12. In the Intermediate dimension list, select Reseller.

    The underlying table for the Geography dimension is linked to the fact table through the underlying table for the Reseller dimension.

  13. In the Reference dimension attribute list, select Geography Key, and then try to select Geography Key in the Intermediate dimension attribute list.

    Notice that Geography Key does not appear in the Intermediate dimension attribute list. This is because the GeographyKey column is not defined as an attribute in the Reseller dimension.

    The following image shows that Geography Key is not available as an intermediate dimension attribute in the Define Relationship dialog box for the Reseller intermediate dimension.

    Define Relationship dialog box

  14. Click Cancel.

In the next task, you will solve this problem by defining an attribute that is based on the GeographyKey column in the Reseller dimension.

Defining the Intermediate Dimension Attribute and the Referenced Dimension Relationship

To define the intermediate dimension attribute and the referenced dimension relationship

  1. Open Dimension Designer for the Reseller dimension, and then review the columns in the Reseller table in the Data Source View pane, and review the defined attributes in the Reseller dimension in the Attributes pane.

    Notice that although GeographyKey is defined as a column in the Reseller table, no dimension attribute is defined in the Reseller dimension based on this column. Geography is defined as a dimension attribute in the Geography dimension because it is the key column that links the underlying table for that dimension to the fact table.

  2. Add a new attribute called Geography Key to the Reseller dimension based on the GeographyKey column, and then define this new attribute as a hidden, non-optimized, and non-ordered attribute.

    The Geography Key attribute in the Reseller dimension will only be used to link the Geography dimension to the Reseller Sales fact table. Because it will not be used for browsing, there is no value in defining this attribute hierarchy as visible. Additionally, ordering and optimizing the attribute hierarchy will only negatively affect processing performance. However, the attribute must be enabled to serve as the link between the two dimensions.

  3. Switch to Cube Designer for the Analysis Services Tutorial cube, click the Dimension Usage tab, and then click the ellipsis button () at the intersection of the Reseller Sales measure group and the Geography cube dimension.

  4. In the Select relationship type list, select Referenced.

  5. In the Intermediate dimension list, select Reseller.

  6. In the Reference dimension attribute list, select Geography Key, and then select Geography Key in the Intermediate dimension attribute list.

    Notice that the Materialize check box is selected. This is the default setting for MOLAP dimensions. Materializing the dimension attribute link causes the value of the link between the fact table and the reference dimension for each row to be materialized, or stored, in the dimension's MOLAP structure during processing. This will have a minor effect on processing performance and storage requirements, but will increase query performance (sometimes significantly).

  7. Click OK.

    Notice that the Geography cube dimension is now linked to the Reseller Sales measure group. The icon indicates that the relationship is a referenced dimension relationship.

  8. In the Dimensions list on the Dimension Usage tab, right-click Geography, and then click Rename.

  9. Change the name of this cube dimension to Reseller Geography, and then press ENTER to make this name change take effect.

    Because this cube dimension is now linked to the Reseller Sales measure group, users will benefit from explicitly defining its use in the cube, to avoid possible user confusion.

    The following image shows the Dimension Usage tab of Cube Designer for the Analysis Services Tutorial cube after a relationship has been defined between the Geography cube dimension and the Reseller Sales measure group.

    Dimension Usage tab of Cube Designer

Successfully Dimensioning Reseller Sales by Geography

To dimension Reseller Sales by Geography

  1. On the Build menu, click Deploy Analysis Services Tutorial.

  2. When deployment has successfully completed, click the Browser tab in Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect.

  3. In the Metadata pane, expand Reseller Geography, right-click Geographies, and then click Add to Row Area.

    Notice that the Reseller Sales-Sales Amount measure is now correctly dimensioned by the Country-Region attribute of the Geographies user-defined hierarchy, as shown in the following image.

    Dimensioned Reseller Sales-Sales Amount measure

Next Task in Lesson

Defining a Fact Relationship

See Also

Other Resources

Attribute Relationships
Defining a Referenced Relationship and Referenced Relationship Properties

Help and Information

Getting SQL Server 2005 Assistance