Hi

I am in the process of manually design aggregations based on a few on the the most common queries in a cube. I am trying to be as economical as possible so as not to take up too much space due to these aggregations. Consequently, I'd like to know that if I design an aggregation on a lower level of a dimension, and I execute MDX that requires the next highest level of that dimension (but otherwise dimensionality is identical to the agg), will Analysis Services use this lower level aggregation and the aggregate this? Or will it go to the raw data because it cannot find an exact match in the aggregations

EG: Dimensions and levels as follows

Perio
- Moving Annual Tota
- Semeste
- Quarte
- Mont

Produc
- Tota
- Manufacture
- Produc
- Pac

Aggregation is designed on
Semester ; Manufacture

I execute a query on
Moving Annual Total ; Manufacture

Will analysis services use this aggregation or go to the raw data

Thanks
Steve.

Theoretically the lower aggs would be used in that circumstance.

public @ the domain below
www.tomchester.net

"Steve Jenkins" <sjenkins (AT) au (DOT) imshealth.com> wrote

possible so as not to take up too much space due to these aggregations.
Consequently, I'd like to know that if I design an aggregation on a lower
level of a dimension, and I execute MDX that requires the next highest level
of that dimension (but otherwise dimensionality is identical to the agg),
will Analysis Services use this lower level aggregation and the aggregate
this? Or will it go to the raw data because it cannot find an exact match in
the aggregations?

And you can actually monitor this in perfmon. There are a couple of
counters you can use, (DSN requested/ DSN Used). The values for those
counters represent the levels of the dimensions that you referenced in your
query. So for example, if each dimension in your cube has 3 levels and you
get the following values back:

DSN Requested: 121
DSN Used: 222

Then you know that you are using non fact table data to resolve that query
(it would be "333" if it required a MOLAP fact table scan).


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.







"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

Thanks for the advice. I've never actually used perfmon for Analysis Services before. I've used the MS Access query log, but this gives a whole heap more options.

One problem I've found with the DSN Requested and DSN Used counters though - it seems that if there are more than 19 dimensions (I have 7 star dimensions and 14 virtual dimensions built over the top of some member properties), then the counter variable overflows and gives incorrect results. I'm assuming this is because we're dealing with a bigint variable.

Do you know of any way around this (other than dropping 3 dimensions from my cube).

Thanks,
Steve.