Hi All

Correct me - please - if I'm wrong here: When constructing a normal physical
cube with some calculated members the results of these calculations are
stored as aggregations on some level in the cube when processing - and this
level can be changed using Usage Based Optimisation - when processing a
virtual cube there is no aggregations despite the fact that there is a lot
of calculated members and the UBO is not an option when I right click on the
Cube.

Could this be true ?

\Michael V.

"Michael V" <123 (AT) test (DOT) com> wrote

No -- the results of calculated members are always calculated on-the-fly and
never pre-aggregated in Analysis Services.

Thanks to both of you

That made a little bit wiser - however i'm still a bit confused - i have a
physical cube that tells me it's making a number of aggregations when
processed - this physical cube has lots of calculated members and it is
joined with another physical cube into a virtual cube which has even more
calculated members.

How come

1) the virtual cube doesn't say anything about aggregations when processed ?
2) i cannot do usage based optimisation on the virtual cube ?

\Michael V.

"Michael V" <123 (AT) test (DOT) com> wrote

Aha

But are you then saying that the Usage Based Opti doesn't work on calculated
members ? Will they never be stored in the cube ?

If not i'm a little disappointed ... how come this option isn't provided to
the user.

\Michael V.

"Nigel Pendse" <nigelp.nospam (AT) compuserve (DOT) com> wrote

"Michael V" <123 (AT) test (DOT) com> wrote

Yes, that's exactly what the previous three responses said. Calculated
cells and members are *always* performed on-the-fly. They may get some
benefit from caching, but the results are never stored on disk. Usage based
optimization is all about tuning which aggregates to store. It has nothing
to do with calculated members or cells.

Yes, that is a weakness of Analysis Services 2000. It makes cubes very
small on disk and fast to process, but can make some queries too slow.

Nigel Pendse
OLAP Solutions
http://www.olapreport.com

Thanks Nigel

Sorry about my ongoing questioning - having some performance problems with a
cost allocation model programmed solely in mdx - we having got that much
data and the problems only occur in the virtual cube (but this cube is the
one with most calculated members - about 80 - where lots of them depends on
others)

Had hoped it wasn't necessary to redo / rethink the mdx - structure - but
based on your responses I think it is....

\Michael V.

"Nigel Pendse" <nigelp.nospam (AT) compuserve (DOT) com> wrote

"Michael V" <123 (AT) test (DOT) com> wrote

Yes, allocations often have performance problems in Analysis Services, so
you need to think hard about how you design the model. And try using
calculated cells.

It's not necessarily true that AS doesn't use aggregates when computing
calculated members. Calculated members are computed at run-time, but
ultimately they must rely on a base member of some sort (more than likely a
measure). If you have aggregates defined for these "base" measures, AS can
use them when computing the calculated member. A simple example might be
Average Store Sales where you have one measure based on the SUM and another
based on the COUNT. AS can aggregate these underlying measures at the
appropriate dimension levels that you specify and use those aggregates in
determing Average Sales. Defining those aggregates correctly can definitely
improve the performance of the dependent calculated members.

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.

"Nigel Pendse" <nigelp.nospam (AT) compuserve (DOT) com> wrote

Absolutely, but I didn't say that calculated members wouldn't *use*
aggregates or cached results, just that the results for calculated members
aren't stored, and therefore have to be re-evaluated each time they are
used. Allocations are a particular problem in any OLAP because they are
cross-dimensional calculations that often use the results of other
calculated members; in some procedural OLAPs they are hard to define, and
even in non-procedural OLAPs they can be slow (as with AS2k).

I know that Microsoft now understand this issue well, but don't think it did
when Analysis Services was designed, almost five years ago.

Nigel Pendse
OLAP Solutions
http://www.olapreport.com

"Sean Boon [MS]" <seanboon (AT) online (DOT) microsoft.com> wrote