none
How to dynamically determine hierarchy of a dimension? RRS feed

  • Question

  • Hi,

    I have a dimension with attribute hierarchies and several other hierarchies with levels defined. I would like to know within the calculation, what is the attribute hierarchy being queried and alter the calculation based on the hierarchy-level or attribute hierarchy defined.

    The levels within the hierarchy, are also individual attribute hierarhies themselves. This is required, as the SSAS database is queried via report models in reporting services.

    Here's a sample calculation I attempted...
    WITH 
    
      MEMBER [Measures].[Offset] AS 
    
        [Time Zone].[Time Zone].CurrentMember.MemberValue * 4 
    
      MEMBER [Measures].[ACD Dur In Queue Sec custom] AS 
    
        CASE 
    
          WHEN 
    
            [Date Time].CURRENTMEMBER.Hierarchy IS Week 
    
          THEN 
    
            Sum
    
            (
    
              {
    
                  OpeningPeriod
    
                  (
    
                    [Date Time].[Calendar Year Week].[Date Time]
    
                   ,[Date Time].[Calendar Year Week].[CurrentMember
    
                  ).Lag(
    
                  [Measures].[Offset])
    
                : 
    
                  ClosingPeriod
    
                  (
    
                    [Date Time].[Calendar Year Week].[Date Time]
    
                   ,[Date Time].[Calendar Year Week].[CurrentMember
    
                  ).Lag(
    
                  [Measures].[Offset])
    
              }
    
             ,[MEASURES].[ACD Dur In Queue Sec]
    
            )
    
          WHEN 
    
            [Date Time].CURRENTMEMBER.Hierarchy IS Trimester 
    
          THEN 
    
            Sum
    
            (
    
              {
    
                  OpeningPeriod
    
                  (
    
                    [Date Time].[Calendar Trimester].[Date Time]
    
                   ,[Date Time].[Calendar Trimester].CurrentMember
    
                  ).Lag(
    
                  [Measures].[Offset])
    
                : 
    
                  ClosingPeriod
    
                  (
    
                    [Date Time].[Calendar Trimester].[Date Time]
    
                   ,[Date Time].[Calendar Trimester].CurrentMember
    
                  ).Lag(
    
                  [Measures].[Offset])
    
              }
    
             ,[MEASURES].[ACD Dur In Queue Sec]
    
            )
    
          ELSE 
    
            Sum
    
            (
    
              {
    
                  OpeningPeriod
    
                  (
    
                    [Date Time].[Calendar Quarter].[Date Time]
    
                   ,[Date Time].[Calendar Quarter].CurrentMember
    
                  ).Lag(
    
                  [Measures].[Offset])
    
                : 
    
                  ClosingPeriod
    
                  (
    
                    [Date Time].[Calendar Quarter].[Date Time]
    
                   ,[Date Time].[Calendar Quarter].CurrentMember
    
                  ).Lag(
    
                  [Measures].[Offset])
    
              }
    
             ,[MEASURES].[ACD Dur In Queue Sec]
    
            )
    
        END 
    
    SELECT 
    
      {
    
        [Measures].[ACD Dur In Queue Sec custom]
    
       ,[Measures].[ACD Dur In Queue Sec]
    
      } ON COLUMNS
    
     ,NON EMPTY 
    
        [Date Time].[Calendar Quarter].[Month].MEMBERS ON ROWS
    
    FROM [CosmoHazeCube]
    
    WHERE 
    
      [Time Zone].[Time Zone].[Time Zone].[GMT];
    
    
    
    

    The Time dimension at the lowest granularity is at a 15min interval. 

    The above calculation doesn't work,...if I drop all the CASE statements and simply create a calculation that uses any one of those variations with [Calendar Trimester], [Calendar Quarter] or [Calendar Year Week], then it all works, but in my SELECT statement I have to mention the hierarchy used in the calculation (as you can see) or I can use an attribute that is one of the levels from that hierarchy.

    I would like to know what is wrong with the above calculation, how to correct it... and if there is a more efficient way to do this?

    Hasanain

    • Edited by HasanainK Wednesday, August 5, 2009 8:00 PM Clarification
    Wednesday, August 5, 2009 7:55 PM

Answers

  • This will not work as the the .CurrentMember function needs to be called from a specific hierarchy. But what you can do is to exploit the fact that if a dimension is not explicitly used it will return the default member.

    So instead of:

      WHEN [Date Time].CURRENTMEMBER.Hierarchy IS Trimester

    I would try:

      WHEN NOT [Date Time].[Trimester].CURRENTMEMBER IS [Date Time].[Trimester].DefaultMember

    If your default members for your attributes is the [All] member you may get a performance increase by explicitly referencing the [All] member rather than making the engine resolve the .DefaultMember function.

    The only thing you have to be careful of is to test the more detailed attributes first (ie. check weeks before trimesters) as if there are any attribute relationships between the attributes then they get their current member set through the relationship. For example, if you picked [August 2009] in your month attribute, [2009] would become the currentmember in the year attribute assuming that you had a relationship between year and month.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by HasanainK Wednesday, August 5, 2009 9:45 PM
    Wednesday, August 5, 2009 9:15 PM
    Moderator

All replies

  • This will not work as the the .CurrentMember function needs to be called from a specific hierarchy. But what you can do is to exploit the fact that if a dimension is not explicitly used it will return the default member.

    So instead of:

      WHEN [Date Time].CURRENTMEMBER.Hierarchy IS Trimester

    I would try:

      WHEN NOT [Date Time].[Trimester].CURRENTMEMBER IS [Date Time].[Trimester].DefaultMember

    If your default members for your attributes is the [All] member you may get a performance increase by explicitly referencing the [All] member rather than making the engine resolve the .DefaultMember function.

    The only thing you have to be careful of is to test the more detailed attributes first (ie. check weeks before trimesters) as if there are any attribute relationships between the attributes then they get their current member set through the relationship. For example, if you picked [August 2009] in your month attribute, [2009] would become the currentmember in the year attribute assuming that you had a relationship between year and month.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by HasanainK Wednesday, August 5, 2009 9:45 PM
    Wednesday, August 5, 2009 9:15 PM
    Moderator
  • Darren,

    Thanks, that worked!

    Will try the [All] member as well...

    Hasanain
    Thursday, August 6, 2009 1:38 PM