# Hierarchy Design Problem • ### Question

• I'm using SSAS 2008 I have a dimension with hierarchy looks like this, for ex.

A - B

-- a

-- b

- C

-- c

-- d

-- e

Until now, only  the leaf nodes (a,b,c,d,e) had attribute data,

so I had a relational table for this dimension like below.

SeqNo | level1 | level2 | level3

1 | A | B | a

2 | A | B | b

3 | A | C | c

4 | A | C | d

5 | A | C | e

But the design in OLTP DB will be changes in the way non-leaf node B, and C can have data,

and if no data exists for any of the children, it implicitly inherits parent's attributes.

C - 100

d - 50

=> c, e have 100 from C's 100, but d has 50

My questions are,

1. How should I change the dimension in a relational model to work in a cube?

2. How can I show parent's attribute when leaf level is chosen in a cube dimension design?

Wednesday, May 25, 2011 8:49 AM

• Hi,

I'll make an odd idea here that I cannot test at the moment, but see if it makes sense to you:

Create another fact table that will have higher granularity related to the parent level (Level 2: {B, C,...}) - I'll name this level Level_2, while Level_3 holds (a, b, c).

Let's call this fact table MG_Level_2, and the original fact table MG_Level_3.

Now the fact records would be split between those two measure groups and you would use scope to "combine" the values eg:

```SCOPE([Measures].[MG_Level_3_Amount]);

SCOPE(Dim.[Hierarchy].Level_2.MEMBERS);
this = [Measures].[MG_Level_3_Amount] + [Measures].[MG_Level_2_Amount];
FREEZE(THIS);
END SCOPE;

SCOPE(Dim.[Hierarchy].Level_3.MEMBERS);
this = CoalesceEmpty([Measures].[MG_Level_3_Amount], [Measures].[MG_Level_2_Amount]);
END SCOPE;

END SCOPE;
```

Now, as I said I haven't tested this so it may not work as written, but the idea here is:

• First we assign the value to Level_2 members as a sum of children (implicit  as base measure represents sum of children) and direct value from other measure group (MG_Level_2_Amount)
• Then we must freeze this assignment so that when we assign the values to empty children the values don't add up to level_2 members
• Then we assign the value to Level_3 empty members (the value comes from MG_Level_2 and should be equal to parents value because we are below granularity)

Now that I think of it ... I wonder if a calculated measure (m1 + m2) would do the trick also...hm, I think it could but there would have to be a scope also to not add up the MG_Level_3_Amount for level_3 members if MG_Level_2_Amount is not empty. ... it really needs to be checked with a real cube.

Lucky guess:

```CREATE MEMBER CurrentCube.[Measures].[Combined Amount] AS NULL;

SCOPE(Dim.[Hierarchy].MEMBERS, [Measures].[Combined Amount]);
this = [Measures].[MG_Level_3_Amount] + [Measures].[MG_Level_2_Amount];
END SCOPE;

SCOPE(Dim.[Hierarchy].Level_3.MEMBERS, [Measures].[Combined Amount]);
this = CoalesceEmpty([Measures].[MG_Level_3_Amount], [Measures].[MG_Level_2_Amount]);
END SCOPE;

```

EDIT: the above should be written as:

```CREATE MEMBER CurrentCube.[Measures].[Combined Amount] AS
[Measures].[MG_Level_3_Amount] + [Measures].[MG_Level_2_Amount];

SCOPE(Dim.[Hierarchy].Level_3.MEMBERS, [Measures].[Combined Amount]);
this = CoalesceEmpty([Measures].[MG_Level_3_Amount], [Measures].[MG_Level_2_Amount]);
END SCOPE;

```

EDIT 2: Forgot the explanation - as this is a calculated measure we can safely change the values in Level_3 as it will not propagate to parent levels (because it's a calculated measure)

Let me know how it goes,

Hrvoje Piasevoli

• Edited by Thursday, May 26, 2011 5:46 PM see EDIT 2
• Marked as answer by Thursday, June 2, 2011 11:06 AM
Thursday, May 26, 2011 5:41 PM

### All replies

• Hi,

Just to clear things out before I make any suggestions:

What if

Scenario 1:

A = 100 (data member)

B = 0 (no data linked directly to B)

a = 50

b = 50

C = 0 (no data linked directly to B)

d = 50

c = ?

e = ?

The totals are

B = 100 (B_children_rollup)

C= 50 (C_children_rollup)

A = 100 (A_datamember) + (100 (B_children_rollup) + 50 (C_children_rollup)) (A_children_rollup) = 250

But what should c and e show?

Regards,

Hrvoje Piasevoli

Wednesday, May 25, 2011 10:22 AM
• The problem is ..

Current Attribute Data entries defining Relationship with product P1 only for leaf nodes.

Table 1

P1 - a - 100

P1 - b -20

P1 - c - 40

P1 - d - 40

P1 - e - 100

But the OLTP table will be changed to Table2, because a bunch of leaf nodes will be added frequently, and Table 1 is growing very fast.

Table 2.

P1 - B - 100

P1 - b -20

P1 - C - 40

P1 - e - 100

Table 2 has the same information as Table1, with the leaf nodes.. ( a little more, that is which leaf nodes explicitly not inheriting values from its parent.)

I can make out the leaf node values in ETL to create Table 1, but as the number of leaf nodes getting large, table will grow very fast.

For the hierarchy aggregation, I need sum up from the leaf nodes.

From my example above,

B - 120 (a (100) + b(20))

C - 180 (c(40) + d (40) + e(100))

The easiest way to cope with OLTP table change is to recreate Table 1, I guess..

Thursday, May 26, 2011 1:52 AM
• Hi,

I'll make an odd idea here that I cannot test at the moment, but see if it makes sense to you:

Create another fact table that will have higher granularity related to the parent level (Level 2: {B, C,...}) - I'll name this level Level_2, while Level_3 holds (a, b, c).

Let's call this fact table MG_Level_2, and the original fact table MG_Level_3.

Now the fact records would be split between those two measure groups and you would use scope to "combine" the values eg:

```SCOPE([Measures].[MG_Level_3_Amount]);

SCOPE(Dim.[Hierarchy].Level_2.MEMBERS);
this = [Measures].[MG_Level_3_Amount] + [Measures].[MG_Level_2_Amount];
FREEZE(THIS);
END SCOPE;

SCOPE(Dim.[Hierarchy].Level_3.MEMBERS);
this = CoalesceEmpty([Measures].[MG_Level_3_Amount], [Measures].[MG_Level_2_Amount]);
END SCOPE;

END SCOPE;
```

Now, as I said I haven't tested this so it may not work as written, but the idea here is:

• First we assign the value to Level_2 members as a sum of children (implicit  as base measure represents sum of children) and direct value from other measure group (MG_Level_2_Amount)
• Then we must freeze this assignment so that when we assign the values to empty children the values don't add up to level_2 members
• Then we assign the value to Level_3 empty members (the value comes from MG_Level_2 and should be equal to parents value because we are below granularity)

Now that I think of it ... I wonder if a calculated measure (m1 + m2) would do the trick also...hm, I think it could but there would have to be a scope also to not add up the MG_Level_3_Amount for level_3 members if MG_Level_2_Amount is not empty. ... it really needs to be checked with a real cube.

Lucky guess:

```CREATE MEMBER CurrentCube.[Measures].[Combined Amount] AS NULL;

SCOPE(Dim.[Hierarchy].MEMBERS, [Measures].[Combined Amount]);
this = [Measures].[MG_Level_3_Amount] + [Measures].[MG_Level_2_Amount];
END SCOPE;

SCOPE(Dim.[Hierarchy].Level_3.MEMBERS, [Measures].[Combined Amount]);
this = CoalesceEmpty([Measures].[MG_Level_3_Amount], [Measures].[MG_Level_2_Amount]);
END SCOPE;

```

EDIT: the above should be written as:

```CREATE MEMBER CurrentCube.[Measures].[Combined Amount] AS
[Measures].[MG_Level_3_Amount] + [Measures].[MG_Level_2_Amount];

SCOPE(Dim.[Hierarchy].Level_3.MEMBERS, [Measures].[Combined Amount]);
this = CoalesceEmpty([Measures].[MG_Level_3_Amount], [Measures].[MG_Level_2_Amount]);
END SCOPE;

```

EDIT 2: Forgot the explanation - as this is a calculated measure we can safely change the values in Level_3 as it will not propagate to parent levels (because it's a calculated measure)

Let me know how it goes,

Hrvoje Piasevoli

• Edited by Thursday, May 26, 2011 5:46 PM see EDIT 2
• Marked as answer by Thursday, June 2, 2011 11:06 AM
Thursday, May 26, 2011 5:41 PM