I test ROLAP dimensions - track history (means all levels have memerkey
unique=false, membernames.unique=false, storagetype=ROLAP, changing=True
without the bottom level which is invisible and memberkey.unique=true)

the questions for me are:
how generates as2k the queries for the elements if I query only for parts of
the dimension. My tests have shown the following results - if I query for
the whole level in the axis AS2K uses on statement for the whole level (
results are an query for each level )but if I query only for parts of the
dimensions as2k uses an recursiv algoriythm to generate sql queries for each
member from the toplevel down to the requested members. My problem is the
response time - if the results contains 200000 or more members (all with
attributes) --> if one query needs 1 ms to return the result only for the
dim needs minutes - with an level based query it takes only 500 ms for all
of the 200 000 members.
Is there a way to change this behavior - means AS2K uses range queries with
an DISTINCT instead of recusive loop - or queries allways the whole level in
one sql statement or ? - I don´t know?
I can tune the SQL Database as much, but if as2k queries the db in this way
its useles for me because there queries (300 000) cannot be answered as fast
as one query for all members?

Thanks for Help

Hannes Mayer
MCDBA SQL Server 2000

Bob,

you must use memberkey
unique=true to optimize access data in memory

----- Hannes Mayer wrote: -----

I test ROLAP dimensions - track history (means all levels have memerkey
unique=false, membernames.unique=false, storagetype=ROLAP, changing=True
without the bottom level which is invisible and memberkey.unique=true)

the questions for me are:
how generates as2k the queries for the elements if I query only for parts of
the dimension. My tests have shown the following results - if I query for
the whole level in the axis AS2K uses on statement for the whole level (
results are an query for each level )but if I query only for parts of the
dimensions as2k uses an recursiv algoriythm to generate sql queries for each
member from the toplevel down to the requested members. My problem is the
response time - if the results contains 200000 or more members (all with
attributes) --> if one query needs 1 ms to return the result only for the
dim needs minutes - with an level based query it takes only 500 ms for all
of the 200 000 members.
Is there a way to change this behavior - means AS2K uses range queries with
an DISTINCT instead of recusive loop - or queries allways the whole level in
one sql statement or ? - I don´t know?
I can tune the SQL Database as much, but if as2k queries the db in this way
its useles for me because there queries (300 000) cannot be answered as fast
as one query for all members?

Thanks for Help

Hannes Mayer
MCDBA SQL Server 2000

If I track history this is not possible! (only for the sorrogate Key in the
invisible base level)

I need an way to say the server should query for all members in one
statement per level and not o statement per member in all levels to the path

Hannes

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