none
What is the advantages of creating hierarchy in SSAS?

    Pregunta

  • HI,

    What is the advantage of creating hierarchy in SSAS dimension? How different it is from other dimension attribute in the case of performance? 

    viernes, 22 de junio de 2012 13:55

Respuestas

  • 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


    Abhinav
    http://bishtabhinav.wordpress.com/

    domingo, 24 de junio de 2012 4:21

Todas las respuestas

  • Hi

    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.

    Regards

    Emil

    viernes, 22 de junio de 2012 14:16
  • Hi,

    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

    Tom

    sábado, 23 de junio de 2012 19:20
  • 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


    Abhinav
    http://bishtabhinav.wordpress.com/

    domingo, 24 de junio de 2012 4:21