Unbalanced Hierarchy in DMR modelling

[blueblue]

Hi there,

I am trying to model an unbalanced hierarchy in FM and run this in analysis studio but i'm having problems.

Structure in db:

ORGUNITKEY ORGUNITID ORGUNITNAME ORGUNITLEVEL ORGUNITPARENTID ORGUNITPARENTNAME

1 1 Org A 1
2 2 Org B 2 1
3 3 Org C 2 1
4 4 Org D 3 2
6 1 Org A 1 1 Org A
7 2 Org B 2 1 Org A
8 3 Org C 2 1 Org A
9 4 Org D 3 1 Org A
11 2 Org B 2 2 Org B
12 4 Org D 3 2 Org B
14 3 Org C 2 3 Org C
15 4 Org D 3 4 Org D



So, basically Org A is the root of the hierarchy, and has no parent. Next level contains Org B & Org C. Org D is the child of Org B.

first four rows have no parent in the table.
I have tried modelling this in FM as DMR data but the hierarchy won't work in Analysis studio.

Anyone have anything similar to this and got it to work?

Thanks
blueblue

 

[Vivarto]

This structure is not going to work in any tool.

1) You've got items being their own parent e.g. orgunitkey 6 has orgunit name of ORG A and a parent of ORG A.

2) Each Org unit has multiple parents and each parent has multiple children i.e. a many-to-many relationship - not a hierarchy, work on the basis of one-parent-many-children, one-child-one-parent (don't want to complicate this by talking about alternate hierarchies)

3) Each org unit is flagged as being in multiple levels

4) You're trying to model a three level hierarchy in two columns, which can be OK but not the way Cognos normaly expects to see hierarchies.


Work on the basis of a column for each level

level_1_id, level_1_desc,
level_2_id, level_2_desc,
level_3_id, level_3_desc

where L1 is the parent of L2 and L2 is the parent of L3

you only need two rows:

L1 L2 L3
- - -
A, B, D
A, C

You'll often see DB tables set up with the lowest levels first in the column list rather than as shown here, no fundamental difference.

Once you've got your DB set up you'll still have to wrestle with Analysis Studio for which I'd start with the help system / Cognos KB