Hi,
I found this thread some time ago but I cannot find any follow-up.
Is there one?

SKS

From: Carol (cpfcarol (AT) hotmail (DOT) com)
Subject: Multiple Hierarchy created huge aggregations?! (re-post)
This is the only article in this thread
View: Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2001-07-20 00:14:08 PST


I learnt from MS paper that multiple hierachy in a single
dimension can reduce unnecessary aggregations because of
its common source from the dimension table. However, I
tried to design my cube using multiple hierarchy and found
that it created huge aggregations that I didn't expect.

I've tested in AS2000SE (SQL7SE) with the following
scenario with all cube designs incorporating 3 shared
dimensions (DimTime, DimCompany, DimLayer) plus:

In Design A -
DimSchema1, DimSchema2, DimSchema3

SName
GName
LName

that each shared dimension built from a dimension table
and totally 3 dimension tables have the same product set
(lowest level) but 3 different tree maps.

In Design B -
MH0.DimSchema1, MH0.DimSchema2, MH0.DimSchema3

SName
GName
LName

that each shared dimension built from a dimension table
and totally 3 dimension tables have the same product set
(lowest level) but 3 different tree maps. But I tried to
relate them all by setting multiple hierarchy with "MH0."

In Design C -
I understand that a true multiple hierarchy should be
applied from a single dimension table. I put 3 schemas
into one single table as below:
SName GName LName PName
----- ----- ----- -----
SCHM1 GROUP1 LINE1 PROD1
SCHM1 GROUP1 LINE2 PROD2
SCHM1 GROUP2 LINE3 PROD3
:
SCHM2 GROUP3 LINE4 PROD1
SCHM2 GROUP4 LINE5 PROD2
SCHM2 GROUP4 LINE6 PROD3
:
SCHM3 GROUP5 LINE7 PROD1
SCHM3 GROUP6 LINE8 PROD2
SCHM3 GROUP6 LINE9 PROD3
:

I was able to build 3 dimensions MH1.DimSchema1,
MH1.DimSchema2, MH1.DimSchema3 by filtering SName
in "Source Table Filter" from the single table. But I hit
an error when building a cube incorporating them.
The error was about
-SCHM2 & SCHM3 not found in MH1.DimSchema1
-SCHM1 & SCHM3 not found in MH1.DimSchema2
-SCHM1 & SCHM2 not found in MH1.DimSchema3
It really let me confuse if "Source Table Filter" does not
work inside the cube (but work well in a dimension!)

In Design D -
From others' experience in newsgroup, they suggested to
have UNIQUE common leaf nodes of the single dimension for
multiple hierarchy.
Then I changed the table design as
PName S1Name G1Name L1Name S2Name G2Name L2Name S3Name
G3Name L3Name
----- ------ ------ ------ ------ ------ ------ ------ ----
-- ------
PROD1 SCHM1 GROUP1 LINE1 SCHM2 GROUP3 LINE4 SCHM3
GROUP5 LINE7
PROD2 SCHM1 GROUP1 LINE2 SCHM2 GROUP4 LINE5 SCHM3
GROUP6 LINE8
PROD3 SCHM1 GROUP2 LINE3 SCHM2 GROUP4 LINE6 SCHM3
GROUP6 LINE9
:

to have
MH3.DimSchema1
S1Name
G1Name
L1Name
MH3.DimSchema2
S2Name
G2Name
L2Name
MH3.DimSchema3
S3Name
G3Name
L3Name
Both dimensions & cube were doing fine except it created
huge no. of aggregations and much longer time spent than
other designs!

TEST RESULTS (T=Time Spent(Hr); A=No. of Aggregations;
S=Cube Size(MB)):
Design A - T=1; A=185; S=161
Design B - T=6; A=921, S=440
Design C - <failed>
Design D - T>24; A=1301; S=650

I'm surprising that the 'best' result is Design A with 1
hierarchy/dimension!!

Please tell me if I've misunderstood the true meaning and
method about multiple hierarchy. Thanks a lot!

Carol