LearnNowOnline

Blog

SQL Server 2008: Refining Attribute Relationships

Excerpt by Ann Weber | April 03, 2014

Once you create a basic cube with dimensions, hierarchies, and measure groups, you will need to refine your cube design to optimize performance. One critical step in optimizing performance is to refine the attribute relationships, especially those in your natural hierarchies. When you build relationships between the attributes that form the levels of a hierarchy, SSAS can use an aggregation that was stored at one level to build aggregations for another level. For example, in a time dimension, a relationship exists between the semester and year level, and SSAS has stored the aggregation for the semester level. When you query the year level, SSAS can add the two semester totals to determine the result for the year, thus improving query speeds.

Once you create a hierarchy in the Dimension Designer in BIDS, you can change to the Attribute Relationships tab to manage the relationships between the attributes being used in the hierarchies. The Attribute Relationships tab has three sections, the design surface (which holds a relationship diagram), the Attributes pane, and the Attribute Relationships pane. In the design pane, you can drag and drop attributes to define the required attribute relationships. You should start with the key level and build from there. To create a relationship between the Employee key and Title attributes, you would drag the Employee key and drop it on the Title. An arrow will appear both on the design surface and in the Attribute Relationships section to represent the relationship pictured in Figure 1.

Figure 1. Attribute relationships are indicated by arrows.

In the Dimension Designer, as shown in Figure 2, you will notice that the arrow between Employee and Birth Date and the arrow between Employee and Start Date are solid black. This indicates a rigid relationship.

Figure 2. Rigid relationships are indicated by a solid black arrow.

Attributes where the relationship are not likely to change over time should be defined as rigid relationships. The examples in the previous paragraph of Birth Date and Start Date as they relate to each employee should not change over time. The employee's original start date should not change once they have started work. Rigid relationships allow SSAS to better optimize aggregations during incremental updates. Aggregations for rigid relationships are maintained during an incremental update, while aggregations for flexible relationships are dropped and must be reprocessed.

NOTE Aggregations and dimension processing are beyond the scope of this chapter. For more information about aggregations, see the SQL Server Books Online topic, Aggregations and Aggregation Designs and for more information on dimension processing see the SQL Server Books Online topic, Processing (Analysis Services - Multidimensional Data) and Processing Options and Settings

Flexible relationships may change over time, for example, an employee's title  may change when they are promoted. By default, all relationships are flexible. To change a relationship from flexible to rigid, right-click on the relationship arrow in either the diagram or Attribute Relationships areas, and then select Flexible or Rigid on the Relationship Type submenu. Additionally, when you right-click the relationship, you can select Edit Attribute Relationship to modify both the attributes and the relationship type.

This post is an excerpt from the online courseware for our SQL Server 2008 Analysis Services course written by expert Ann Weber.