viernes, 22 de junio de 2012 13:55
What is the advantage of creating hierarchy in SSAS dimension? How different it is from other dimension attribute in the case of performance?
Todas las respuestas
viernes, 22 de junio de 2012 14:16
Hierarchies are useful for end users and certain calculations. I'm not sure about performance but I would imagine certain queries might be quicker in some cases (also depends on version of SSAS).
The only problem with hierarchies I came across was that it was not so easy to use specific level of hierarchy with Excel pivot table but there was option to do that which I don't remember, although most users might struggle to find it.
sábado, 23 de junio de 2012 19:20
in my opinion there should always be hierarchies because otherwise your users have to select many filters and adjust these filters to the proper selection. This can be accomplished with more ease and grace if your users are able to use hierarchies.
There is no direct relationship between hierarchies and performance but I've seen a lot of models without hierarchies and without attribute relationships.
So if you do not need to comfort the souls of your users with a calendar hierarchy you should always define the proper attribute relationships e.g.:
day --> month --> quarter --> year
domingo, 24 de junio de 2012 4:21
Hierarchies in themselves dont help much in performance until we have the Attribute relationships set between them, i.e. something called as natural hierarchies.
If we have some USER HIERARCHIES defined and attrinute relationships also setup on them, then it helps the ANalysis server in aggregation, lets take the example YEAR--> Qtr--> MOnth-->Day, this is the hierarchy defined and we have defined attribute one to many relationship as well so while building aggregations Analysis server just uses the aggregations build for the Day level for instance.
ELse in normal scenarios if the attribute relations are not defined then the aggregations are done on the primary key of the dimension and whenever values are required the primary key aggregations are used to retrieve values