Type 1 and 2 Relationships and Dimension Design in SSAS | Jen Stirrup
Natural Hierarchy Relationships; Defining an Attribute Relationship; Attribute In Microsoft SQL Server Analysis Services, attributes within a dimension are For navigating data in the cube, you can also create a user-defined. Using a Modified Version of the Analysis Services Tutorial Project Specifying Attribute Relationships Between Attributes in a User-Defined. Creating a Date Dimension in an Analysis Services (SSAS) Cube In the textbox bellow 'Select or enter a database name', type Some configurations, like the attribute relationships, can optimize the dimension processing.
Enhancements in dimension, partition, and query processing, based upon a reduction in the total amount of memory required for dimension processing. Enhanced query performance due to more rapid storage access, as well as more optimized execution plans within Analysis Services. Enhanced performance because of the selection of more effective aggregates via the Analysis Server aggregation design algorithms assuming that user-defined hierarchies have been defined along relationship paths.
One of the most important objectives in building a high performance Analysis Services solution is an efficient and effective dimension design. The identification of attribute relationships, and the effective use of hierarchies which we examine in other articles of this subseries represent the two most important considerations underlying the optimization of dimension design.
Specifying Attribute Relationships Between Attributes in a User-Defined Hierarchy
Overview An Analysis Services dimension typically has, as an underlying source, a relational data warehouse dimension table. And within relational data warehouses, each dimension usually contains a primary key, attributes, and, in some instances, foreign key relationships to other tables.
The attributes provide descriptive context to the primary key and, from a relational perspective, each attribute has either a many-to-one relationship with the primary key this is, by far, most frequently the case or a one-to-one relationship with the primary key.
The attribute relationships existing within the Product dimension of the Adventure Works DW sample database available to anyone installing Analysis Services appear as depicted in Illustration 1. Product Dimension Primary Key Some attributes also have relationships with other attributes. For example, in the Product dimension we have cited, the Subcategory attribute has a many-to-one relationship with the Category attribute, as shown in Illustration 2.
This is the purpose of attribute relationships: To optimize our dimension designs, we must first understand how our attributes are related to each other, and then we must take steps to let Analysis Services know, via attribute relationships, what those relationships are.
With the default scenario in place, anytime a query to the data source includes an attribute within the dimension, Analysis Services will summarize data from the primary key and then group it by the attribute under consideration.
As you can see in the below illustration, we now have established the Date Key to Calendar Week to Calendar Month relationships.
We could continue to use the drag and drop method to complete our relationship setup or we can use the New Attribute Relationship button. As displayed below, once you click the New Attribute Relationship button, the Create Attribute Relationship window opens.
On the left side you select the "lower" attribute level to be added to the higher rollup value which appears on the right side. This screen also shows the Relationship Type option. For attribute values that are changing, this option should be Flexible. However for values that do not change, setting this option to Rigid will tell SSAS during processing that these values will not change with future reprocesses of this dimension. A date dimension is often a great candidate for a Rigid relationship type.
Completing the Calendar Quarter to Calendar Year relationship results in the below relationship setup.Analysis Services - 07 Dimension Attribute Relationships
Notice that any attributes that do not fall within the relationship appear under the main key, for example Calendar Week. Furthering our example, we could actually create a second hierarchy, and then establish an additional relationship for our new hierarchy as displayed next. Notice how multiple attribute relationship paths can exist. Next Steps Hierarchies are a very useful tool to utilize within SSAS dimensions in order to improve performance of queries related to a dimension.
Attribute Relationships: Settings and Properties - Page 2 — az-links.info
In order to get the most performance boost out of dimensions, we need to establish attribute relationships between each of the different attributes that exist within a dimension. The performance boost occurs, since SSAS can navigate the relationship in order find and aggregate the measure without having to go all the way back down to the dimension key, if an appropriate relationship exists.
- Attribute Relationships
- Lesson 4: Defining Advanced Attribute and Dimension Properties
- Lesson 5: Defining Relationships Between Dimensions and Measure Groups
With a natural hierarchy, if you define attribute relationships between the attributes that make up the levels, Analysis Services can use an aggregation from one attribute to obtain the results from a related attribute. If there are no defined relationships between attributes, Analysis Services will aggregate all non-key attributes from the key attribute.
Therefore, if the underlying data supports it, you should define attribute relationships between attributes. Defining attribute relationships improves dimension, partition, and query processing performance. When you define attribute relationships, you can specify that the relationship is either flexible or rigid.
If you define a relationship as rigid, Analysis Services retains aggregations when the dimension is updated. If a relationship that is defined as rigid actually changes, Analysis Services generates an error during processing unless the dimension is fully processed.
Specifying the appropriate relationships and relationship properties increases query and processing performance. In the tasks in this topic, you define attribute relationships for the attributes in the natural user hierarchies in the Analysis Services Tutorial project.
These user hierarchies are all natural hierarchies. In the Hierarchies pane, notice the levels in the Customer Geography user-defined hierarchy. This hierarchy is currently just a drill-down path for users, as no relationship between levels or attributes have been defined.
Lesson 5: Defining Relationships Between Dimensions and Measure Groups | Microsoft Docs
Click the Attribute Relationships tab. Notice the four attribute relationships that link the non-key attributes from the Geography table to the key attribute from the Geography table. The Geography attribute is related to the Full Name attribute.
The Postal Code attribute is indirectly linked to the Full Name attribute through the Geography attribute, because the Postal Code is linked to the Geography attribute and the Geography attribute is linked to the Full Name attribute.