Chris,
you are definitely 'barking up the right tree' as far as
I am concerned.

In my experience, Analysis Services is doing a pretty
good job with parent child based tables as long as they
are relatively small in member size or have a large
number of levels.

There definitely is a performance problem when you have
dimensions with 60,000+ members and few levels. The
reason for this is that p-c- dimensions do not
preaggregate.

The solutions we have found are:

- transfer the parent child table into a standard (star
scheme) table. This will work well as long as the
dimension does not contain any properties.
- reduce the member count by eliminating members
of 'little interest'. (i.e. customers which have only
made a single transaction over all times, products which
have sold just once etc.) These can be summarized on an
artificial member 'others'.

Going Rolap is not really an option, I guess ,since Molap
is fastest at cost of disk speed. - unless you want to do
real time olap you should consider molap.

Recursion is a big issue though. I am looking forward to
hear more opinions on this.

HTH Lutz Morrien

Hi Lutz,

Thanks for your words of reassurance.

When you say "transfer the parent child table into a standard (star
scheme) table" - this was what I was hoping that Analysis Services would do
for me (or at least it would optimize the snowflake without me needing to do
anything) - is this a little naive! I guess I'm looking for a solution with
as little thought and work required!!

I am currently looking at the Sql Server Accelerator for Business
Intelligence in a search for this "lazy" solution - do you have any
experience with this?

Regards,

Chris

"Lutz Morrien" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Hello,

I have a little problem with a Parent-child dimension.

The memory of the OLAP client reaches 2GB when the dimension is crossed with
a 100.000 member Dimension. The queries return the TOP and BOTTOM clients.

Currently I am in a process of migrating the parent-child dimension to a
standard one.

I will have to convice the end user that he will loose a little bit of
flexibility managing the dimension. The number of levels must be fixed.

Best regards



"Chris Lewis" <chrislewis (AT) etnospamsolutions (DOT) com> wrote