Aggregation on different level of dimension hierarchy

Question

I have two dimensions, lets say Date Hierarchy and Product and a measure which has MAX (Measures.[Max]) aggregation.

The requirement would be to have SUM of Measures.[Max] on DAY or HOUR level of Date Hierarchy and be summarized in Month level.

I have the following query:

With 
    Member Measures.SumOfMax as SUM([Date].[Hierarchy].[Hour].AllMembers, Measures.[Max])

Select
    NON Empty
    {
        Measures.SumOfMax
    } ON COLUMNS,
    NON EMPTY
    {
        [Date].[Hierarchy].[Month].AllMembers *
        [Product].[Product Name].[Product Name].Allmembers      
    } Having Measures.[Max] > 0 
    ON ROWS
FROM [Cube]

Above query runs very slow. Are there any ways to optimized this?

Do you need to have this set in your Sum calculation [Date].[Hierarchy].[Hour].AllMembers ? — whytheq
Yes, because I need to specify what level in Date hierarchy I have to aggregate, either on HOUR, or Day. — ggarcia
but that set is everything so I do not see it's purpose - do you get different results if it is omitted and your measure is expressed as SUM(Measures.[Max]) ? — whytheq
Yes. It will still be aggregated as MAX(Measures.[Max]) per hour. It should be SUM(Measures.[Max]) — ggarcia

SouravA SouravA · Accepted Answer · 2016-01-07T19:30:15

The problem with this query is that the calculated measure Measures.SumOfMax is evaluated for every cell on the axis although it's yielding the same value each time. SSAS engine is not intelligent enough to understand that, but since you know about this behavior, you can take advantage of FE caching so that it gets evaluated only once and gets cached in FE cache. Read more on it here

With 
    Member Measures.[_SumOfMax] as SUM([Date].[Hierarchy].[Hour].AllMembers, Measures.[Max])
    Member Measures.[SumOfMax] as ([Date].[Hierarchy].[Hour].[All], Measures.[_SumOfMax])
Select
    NON Empty
    {
        Measures.SumOfMax
    } ON COLUMNS,
    NON EMPTY
    {
        [Date].[Hierarchy].[Month].AllMembers *
        [Product].[Product Name].[Product Name].Allmembers      
    } Having Measures.[Max] > 0 
    ON ROWS
FROM [Cube]

Hope this helps.

Aggregation on different level of dimension hierarchy

1 Answers