Skip to content

Atribute Relationships

June 6, 2011

Defining an Attribute Relationships

Attributes within a dimension are always related either directly or indirectly to the key attribute.

For each table included in a dimension, there is an attribute relationship that relates the table’s key attribute to other attributes from that table. You create this relationship when you create the dimension.

An attribute relationship provides the following advantages:

  • Reduces the amount of memory needed for dimension processing. This speeds up dimension, partition, and query processing.
  • Increases query performance because storage access is faster and execution plans are better optimized.
  • Results in the selection of more effective aggregates by the aggregation design algorithms, provided that user-defined hierarchies have been defined along the relationship paths.

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

For navigating data in the cube, you can also create a user-defined hierarchy that does not represent a natural hierarchy in the data (which is called an ad hoc or reporting hierarchy). Although the natural hierarchy benefits from aggregating and indexing structures — hidden from the user — that account for the natural relationships in the source data

Attribute Relationship Considerations

When the underlying data supports it, you should also define unique attribute relationships between attributes. To define unique attribute relationships, use the Attribute Relationships tab of Dimension Designer.

Any attribute that has an outgoing relationship must have a unique key relative to its related attribute. In other words, a member in a source attribute must identify one and only one member in a related attribute. For example, consider the relationship, City -> State. In this relationship, the source attribute is City and the related attribute is State. The source attribute is the “many” side and the related side is the “one” side of the many-to-one relationship. The key for the source attribute would be City + State.

Configuring Attribute Relationship Properties

The following table lists and describes the properties of an attribute relationship.

The following table lists and describes the properties of an attribute relationship.

Property Description
Attribute Contains the name of the attribute.
Cardinality Indicates the cardinality of the relationship. Values are Many, for a many to one relationship, or One, for a one to one relationship. Default value is Many. In Microsoft SQL Server Analysis Services, the cardinality property has no effect – its use is reserved for a future implementation.
Name Contains the friendly name of the attribute.
RelationshipType Indicates whether member relationships change over time. Values are Rigid, which means that relationships between members do not change over time, or Flexible, which means that relationships between members change over time. Default is Flexible. If you define a relationship as flexible, aggregations are dropped and recomputed as part of an incremental update (they will not be dropped if only new members are added). If you define a relationship as rigid, Analysis Services retains aggregations when the dimension is incrementally updated. If a relationship that is defined as rigid actually changes, Analysis Services generates an error during incremental processing. Specifying the appropriate relationships and relationship properties increases query and processing performance.
Visible Determines the visibility of the attribute relationship. Values are True or False. Default is True.

Create, Modify, or Delete an Attribute Relationship

You can create, modify, or delete an attribute relationship between attributes in a dimension by using the Attribute Relationships tab of Dimension Designer in Business Intelligence Development Studio.

To create an Attribute Relationship

  1. In Dimension Designer, open the dimension that contains the attributes that you want to create an attribute relationship between.
  2. On the Attribute Relationships tab, right-click an attribute in the diagram or in the Attributes pane, and then select New Attribute Relationship.
  3. In the Create Attribute Relationship dialog box, select a source attribute and a related attribute.
  4. In the Relationship type list, select a relationship type, and then click OK.

To modify an Attribute Relationship

  1. In Dimension Designer, open the dimension that contains the attribute relationship that you want to modify.
  2. Click the Attribute Relationships tab.
  3. Right-click the attribute relationship in the diagram or in the Attribute Relationships pane, and select Edit Attribute Relationship.
  4. In the Edit Attribute Relationship dialog box, select a source attribute and a related attribute.
  5. In the Relationship type list, select a relationship type, and then click OK.

To delete an Attribute Relationship

  1. In Dimension Designer, open the dimension that contains the attribute relationship that you want to delete.
  2. On the Attribute Relationships tab, right-click the attribute relationship in the diagram or in the Attribute Relationships pane, and then select Delete.
  3. In the Delete Objects dialog box, click OK.
Advertisements
Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: