Hi all,
parent child dimensions with low level of hierarchy and a
vast number of members are having a deep impact on
overall performance in our system.

Has anyone come up with a way of converting a parent
child dimension (with a ragged hierarchy) into a standard
dimension? Actually I mean converting a parent child
table into a regular table. The dimension is non-changing.

Everything works well as long as users do not touch the
parent child dimensions (response time via internet is 3
sec). With p-c-dim the system exits after 20 min+.

TIA
Lutz

I had to do this all the time before parent-child dims
came along.

There's no easy way to do it. Essentially you need to
write a bit of code that does the conversion to a regular
table. There are obvious problems (e.g. You don't know how
deep the hierarchy will be). Its not pretty but there's no
other way to do it, that I know of anyway.

Regards
Jamie

Thanks Jamie,
so I need to write a stored procedure to transform a p-c-
dim into a regular one?
I know oracle has something like that built in.

Are there any code samples or snippets around?

And does turning a p-c-dim into a regular on really have
a large impact on query response time? We are using MOLAP
and want to get rid of the dynamic parent-child-dim.

It would be nice, if someone could give a hand. I assume
that this is a normal problem with Analysis Services. I
wonder why there is no information on this from Microsoft
itself. It was not mentioned in the Performance guide at
all... and it is the biggest performance killer here.

TIA Lutz

"Lutz Morrien" <nospam.lutz.morrien (AT) ocb (DOT) com> wrote

I seem to recall that Comshare's MPC product includes exactly this
capability, for the obvious reason. But I don't know exactly how they do it
or if the code they use is available outside MPC.

Don't have a solution to you're problem - just a stupid question - i'm
examing Analysis for the momemt but when you
say you need to convert a parent child to a standard does that mean

1) that the dimensions's hierachy is removed ? Or do you still have the
hierachy but it is
a different type of dimension in Analysis ?
2) Any changes to the structure in the Relational database (Staging /
Subject) as a result of
this ?

\Michael V.

"Lutz Morrien" <nospam.lutz.morrien (AT) ocb (DOT) com> wrote

Michael,
the problem I have is the following:
I have a customer dimension in an SQL server database fed
by Siebel. The base table is a parent child one.
It is used to describe customer hierarchies / business
structures.
A number of customers have these hierarchies, others do
not.
Our first approach was to use the parent-child table as
dimension table. Problem is, analysis services cannot
create preaggregations for these kind of tables. With a
large dimension this means agregations (and hierarchies)
have to be created at query time...

This results in extremely long query response times.
Intolerable. My hope is that by creating a normalized
table we will be able to cut down on these times.

1. Dimension hierarchy is not removed (hopefully). Tests
have confirmed this

2. Changes are to happen in the staging database. Not in
the source database.

Parent child tables do not appear in traditional data
warehouse schemes since aggregations can not be created
beforehand. Therefore we'll try to get rid of all parent
child tables, I guess. It is much less dynamic than p-c-
dims... but it improves query time, if a large dimension
is concerned. For all the resundancy created, it will in
turn consume much more storage space.

Funny enough I found little clues about the disadvantage
of parent child dimensions in all the documentation I
read.

Have a nice weekend
Lutz

I have seen that thread before.
I guess it really narrows down to:
If you want to use it, use it, otherwise don't.

I realize there are a lot of advantages in parent child
dimensions. Unlimited number of hierarchy levels, dynamic
change in hierarchy without reprocessing a cube...

There are probably many more. Parent child tables are a
database designer's favourite because they represnt
ultimate flexibility.
Query speed is much slower with large p-c-dims, though.

It is definitely an advantage of analysis services that
one can use parent child as an extra option...

I just wished I realized the disadvantages much sooner.
They should be pointed out clearly, so designers know
beforehand when they make a choice.

I guess it is take the pros and the cons and then make a
decision. Parent child is not a "must do", it is a "can
do"... which is good.
I just won't do it any more...

Lutz