Attribute relationship from Hierarchy in SSAS - Stack Overflow
As is well known, creating attribute relationships is a best practice in not the relationship of a dimension to a measure group in a cube. written permission of Scalability Experts Inc. If, however, your only means of . While designing attribute relationships, consider if the relationship is going to be . SQL Server Analysis Services (SSAS) is the Microsoft cube-building engine that With attribute relationships, SSAS can simply add three-month totals to reach.
Visual Studio will automatically create for each attribute a relationship between the attribute and the GameSK attribute, since every attribute must relate, directly or indirectly, to the dimension key attribute.
Your work should look like the following — note particularly the relationship pane highlighted, where all attributes relate directly to the dimension key attribute GameSK: Before we do model it, take a look at the SQL queries Analysis Services generates to populate this dimension. We will then have the following. Some books and documentation refer to such a hierarchy — one with no relationships defined — as unnatural. This exercise shows that explicit attribute relationships are not required between the levels of a hierarchy in order to model and browse it — they are only a best practice for browsing performance.
If you examine the SQL generated now, you will find it is the same in each case as before. In the Attribute Relationships pane, edit the relationships such that you now have: Some Analysis Services books and documentation refer to such a hierarchy — one with supporting relationships defined — as natural.
Fully process the dimension. We now get the duplicate attribute key error, specifically the following: Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: The error message provides clues but lacks clarity about the true cause of the problem. You can see that with attribute relationships, the query for a given non-dimension-key attribute is now not itself alone, but also includes its parent attribute as dictated by the attribute relationship — in this case Game Category.
Reasonably or not, Analysis Services calls this a duplicate key error, even though the cause is quite different from those explained in Part 1. In effect this means that to gain the query performance benefits of attribute relationships, the data model must obey stricter constraints than without them.
This is non-obvious and certainly not explained clearly by the resulting error, official documentation, or any book I have seen. Relationships are typically explained as if they are purely metadata embellishments that Analysis Services can leverage for performance, without mentioning that using them imposes constraints on your data as well. If your data is correct, then the fix is to restructure the key of the child attribute that gets the duplicate key error in such a way that the SELECT DISTINCT for its key and the key of its parent no longer finds the same child key under more than one parent — or more generally, ancestor — key.
The usual way to do this is to change the key structure of the child attribute to include both itself and the keys of each ancestor level within the hierarchy, as described by the attribute relationships. With Analysis Services MD there are at least three options for accomplishing this: Add a calculated column to the dimension table in the DSV exposing an appropriate key through an expression — perhaps a concatenation of other columns.SSAS Interview Questions: Types of Attribute Relationships in SSAS
In this approach the attribute key would then be based on this single column. If the dimension table in the DSV is actually a view as it should be per another best practicethe equivalent of 2 could be done in the view.
The first two options have the advantage of not requiring any change in the data source, which may not be feasible or allowed.
SQL Server Analysis Services Attribute Relationships
Between these two, 1 is to be preferred because it makes explicit what is going on when you view the attribute properties, rather than burying the detail in the DSV. It pushes Analysis Services details into the database not a good thing in generalbut if a view layer specifically for Analysis Services has been created as it should have beenthen it is reasonable to encapsulate such details there.
In the end it is a judgement call.
- Tallan Blog
- Attribute Relationships
In most cases, using any of these methods will also require explicitly defining some appropriate column in the source table as the source for the NameColumn property, since the new key value will in most cases not be suitable for display as the member value as it was before with an important exception noted later.
Open the properties pane of the Game Sub Category attribute and scroll down to the KeyColumns property highlighted below: In our example, the Date key is already related to each of the four attributes in the hierarchy. As such, our first level of the relationship, which is Date Key to Week, has already been established.
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.
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.
SQL Server Analysis Services Attribute Relationships
The corresponding Analysis Services dimension has seven attributes: Customer based on CustomerKey, with CustomerName supplying member names Age, Gender, Email, City, Region, Country Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.
For Analysis Services, this specifies a natural relationship and potential aggregation. In the Customer dimension, a natural hierarchy exists for the Country, Region, City, and Customer attributes.
The Country attribute as an attribute relationship to the Region attribute. The Region attribute as an attribute relationship to the City attribute.
The City attribute as an attribute relationship to the Customer attribute.