SSAS Aggregations - Why so many hits in the same aggregation?

• badoxa

  Grasshopper

  Points: 13

  More actions

  January 13, 2016 at 7:39 am

  #150426

  Hello,

  I have multiple aggregations designed using the Wizard in my Measure Group, and I have at least 2 aggregations that include the "Month" Level in my Date Hierarchy, and when I query something simple like:

  select

  {[Measures].[Measure]} on 0,

  non empty [Date].[Month].&[2015]&[201501]&[201501]&[201503] on 1

  from [CUBE]

  I was hoping that in a cold query scenario, this query would hit the aggregation and return the value almost instantly, since the volume of this Partition is low (3 months data, with 50Million rows).

  However, when I look at the Profiler logs, I see that there are multiple hits on the aggregation, most specifically 11. I was hoping to see only one hit, since I am requesting a specific Month, which I thought would be already calculated in the Storage Engine as an aggregated row.

  The query takes around 900ms to return data, which is ok, and if I make the same query, and ask for a sibling such as Februray2015, it takes around 200ms, same thing for March2015 and it makes the same number of Aggregation hits (11).

  I would like to have some insight on why this behaves like this? Shouldn't the query hit in the aggregation only once?

  I have had some other queries that hit much more times, such as requesting the lower level Week.

  Thanks in advance for your inputs.

  Here's a sample output from from Profiler.