Blog article and copyright by Lukas Hillesheim hillesheim@n-dimensions.
Attribute relationships are internal objects of the Analysis Services MultiDimensional Engine. They are not visible for the MDX client, can only be used indirectly and can only be configured in the development environment. However, they affect MDX objects and implicit behaviors. Understanding relationships is therefore essential for understanding MDX.
Attributes are the building blocks within Analysis Services MultiDimensional. Attributes are used to derive attribute hierarchies, hierarchies with multiple levels and element properties. They also control implicit behaviors such as MDX [<hierarchy>
].CurrentMember and AutoExist.
The examples of this document use the following data model:
Figure 1 - Data Source View
Extraction of SSAS attributes from SQL table columns. SSAS MultiDimensional makes the contents of table columns available though attributes. Mapping of attributes on table columns is configured in SSDT:
Figure 2 - Attribute-Column Mapping
In the example, there is a key attribute [Customer] that get it´s data from the table column [dimCustomer].[Id]. The attribute - which originally has the same name as the column - was renamed to [Customer]. In addition, there are various non-key attributes [City], [Country], [Region] and [Phone No]. By default, all attributes have a direct relationship to the key attribute:
Figure 3 - Initial Attribute Relationships
Please note that there are two types of relationships: 1. relationships in SSAS between attributes and 2. relationships between tables in the relational model. Only attribute relationships are relevant for understanding MDX.
Please also note, that i terms of relationships, there is a fundamental difference between SSAS MultiDimensional and SSAS Tabular. In a tabular environment, relationships between tables are presented to the DAX client and must be considered as part of the DAX language.
2. Attribute Hierarchies
The MultiDimensional exports the following objects:
- - Dimension
- - Hierarchy
- - Member
- - Property
- - Measures
The difference between "attribute hierarchy" and "multi-level hierarchy" is considered in this article and it is import for the general understanding, but the distinction between both types is not relevant from a syntactic point of view.
Without further intervention, each attribute is exported as a hierarchy. List of attributes:
Figure 4 - List of Attributes
List of hierarchies of the [dCustomer] dimension:
Figure 5 - List of Hierarchies
This default behavior is set through the attribute's "AttributeHierarchyEnabled" property. [City] attribute properties:
Figure 6 - Property 'AttributeHierarchyEnabled' true
If "AttributeHierarchyEnabled" is set to "true", aggregation is applied to each member. E.g. for the members [Berlin], [Madrid] etc. [Sum] and [Count] are computed and added to the cube data. In addition, the artificial member [ALL] is created and also added to the hierarchy. The value of [ALL] derives from the "aggregate of the aggregates".
Figure 7 - Hierarchy Members including [ALL]
Some data is not interesting in the sense of an aggregation, but in the sense of a description. This applies, for example, to the [Phone No] attribute. It does not make sense to form aggregates according to telephone numbers, but it is useful to display the telephone number along with a customer.
Disabling "AttributeHierarchyEnabled" makes the attribute disappear from the list of hierarchies, but the data of the attribute is still part of the cube and can be used in other ways (see below):
Figure 8 - Property 'AttributeHierarchyEnabled' false
[Phone No] is still part of the attribute list; but is assigned a different icon:
Figure 9 - [Phone No] Attribute as Member Property
[Phone No] is no longer part of the hierarchy list:
Figure 10 - [Phone No] missing
3. Member Properties
Attribute relationships turn an attribute on the right side of a relationship into a property of the attribute on the left.
Switching "AttributeHierarchyEnabled" off has no affected on the relationships mentioned above. [Phone No] is still on the right side and [Customer] on the left side of an attribute relationship. In other words, [Phone No] is still a member property of [Customer]. This fact is expressed by the following MDX code:
Figure 11 - Use [Phone No] as Member Property
Since [City], [Region], and [Country] are also related to [Customer] - each residing on the right side of an attribute relationship - they all are properties of [Customer]:
Figure 12 - Use of all Hierarchies as [Customer] Member Property
Another effect of an attribute relationship is that the respective CurrentMember on the right side is set via the relationship in the background and vice versa. For example, while [Customer].[John] is selected on the left side, [City].CurrentMember on the right side is automatically set to [City].[Madrid]. If - on the other hand - [City].[Madrid] was selected on the right side, [Customer].CurrentMember was implicitly set to [Customer].[All]. Please additionally note, that accessing [Phone No].CurrentMember causes an error because the CurrentMember function can only be applied to hierarchies: [<hierarchy>
Example with all relationships:
Figure 13 - Current Members within same Hierarchy
The following example shows how the attribute relationship works from the right to the left side. Members from the [City] hierarchy are iterated over using the expression "[dCustomer].[City].[City].Members". While the iteration takes place, the Measure-Expression "[dCustomer].[Customer].CurrentMember" is evaluated. Because [Customer].CurrentMember is implicitly driven by the right side, it is set to [Customer].[All] thereby overriding the Slicer Axis! The Slicer axis initially sets a context for the iteration with a restriction to [Ann] and [John]. Due to the restriction - placed on the left side (!) - only those cities where [Ann] and [John] live - are selected for the iteration. This explains, why only two cities are visible. During the iteration, the Initial Context from the Slicer Axis including [Ann] and [John] is overridden with a new Runtime Context which sets [Customer].CurrentMember constantly to [Customer].[ALL]. This example demonstrates the effect of attribute relationships twice!
There are no relationship between attributes of the [dCustomer] dimension and attributes of the [dProduct] dimension. Attribute relationships end at the boundary of a dimension. In the absence of a relationship between [Customer] and [Category], [Category].CurrentMember cannot be modified and remains constantly [ALL]:
Figure 14 - Current Member in a non-related Dimension
5. MultiLevel Hierarchies
The data stored in the attributes of a dimension can express hierarchical facts. For example, customers are located in a specific city, which in turn is located in a specific region. Viewed from above, the result is a tree – with [ALL] at the top level, a 2nd level with regions, a 3rd level with cities and a 4th level with customers.
In the Dimension Editor, a developer can create hierarchies with multiple levels from hierarchy-enabled attributes. The example shows how the hierarchy [hCustomer] is derived from [Region], [City], and [Customer]:
Figure 15 - Custom Hierarchy with multiple Levels
For the hierarchy to "work", there are two conditions to match:
1. Attribute relationships should be defined to express the parent-child relationship between the respective entities. This means that the lower level should have an N:1 relationship to the higher level. Without this relationship, there are either schema errors or potential performance issues.
2. The data must satisfy the relationships. For example, a relationship defined as N:1 requires [City].[Madrid] to have only a single combination with [Region]; that´s [Region].[South]. If another combination of [City].[Madrid] with another region such as "North" was found when processing the dimension, the cube would abort the process with errors.
Relationships for the example:
Figure 16 - List of Attributes
Transitive Relationships. The change in the relationship model results in additional behaviors that you should be aware of: Attribute relationships are transitive / cascading. For example, with [Customer].[John] on the left side of the relationship "Customer -> City", not only [City].CurrentMember is set to [City].[Madrid], but the cascading nature of the relationships additionally sets [Region].CurrentMember to [Region].[South] and [Country].CurrentMember to [County].[Spain].
Members as pointers. The multi-level hierarchy technically represents a bunch of pointers to the underlying attributes. The member [John] from the [hCustomer] hierarchy is essentially a pointer to the member [John] from the hierarchy [Customer]. In the background, the relationships provide a kind of "pointer synchronization".
The following example shows an iteration over the members of [hCustomer]. As an affect of the underlying attribute relationships, each CurrentMember of each involved hierarchy is modified. The iteration on [hCustomer] regards all members on each level including the top level. For members at the "Customer" level, the grids shows a complete "information" according to [City], [Region] and [Country]. The members of this level such as [Karen] are located on the left side of the "leftmost" relationship. Transitivity reaches all hierarchies and their right-hand CurrentMembers. For members of the "City" level such as [Prague] there is an incomplete "information"; [Customer].CurrentMember is set to [ALL]. This is because [City] is on the right side of the relationship and [Customer] is on the left. However, modifying a CurrentMember in order to retrieve a specific member only works from left to right.
Figure 17 - Synchronizing Member Pointers
From a user´s point of view, the question of whether a particular tuple "exists" can be defined in two ways:
1. Tuples exist when there is a value. For example, due to the business processes customers in [East] joined the company starting from . Therefore a tuple ([East], ) result in an [Amount] of NULL. According to this rule, the tuple does not exist. Technically, existence and non-existence rely on existing or non-existent facts rows.
2. Tuples exist if the dimension schema allows a combination of the corresponding members. For example, the combination of [Madrid] with [South] might be allowed because the scheme establishes an N:1 relationship between [City] and [Region] and the combination is found within the data. The tuple ([Madrid], [South]) would be valid in this sense even if the corresponding [Amount] is NULL.
"AutoExist" is a behavior of the Vertipaq engine. The engine automatically eliminates tuples that do not "exist" in the sense of the 2nd rule. Tuples whose existence is not "allowed" due to the scheme are automatically excluded. The AutoExist behaviour can be seen in a wide variety of constellations. The most obvious effect is the limitation of CrossJoin.
Here are two examples. The first example crossjoins [Region] and [Year]. Because both hierarchies are in different dimensions, AutoExist cannot impose a restriction.
In the second example, a CrossJoin is performed between [Region] and [City]. In this case, AutoExist has a restrictive effect.
Example 1: [Region] CrossJoin [Year]. The complete Cartesian Product is formed.
Figure 18 - AutoExist - No Restriction
Example 2. [Region] CrossJoin [City]. Not a complete Cartesian product. Each region appears only once in the result:
Figure 19 - Restriction by AutoExist