Conceptual problem with multi-level hierarchies

#1

I think I am getting myself very confused and would like some clarification.

Question
The crux of my question is, I think:
When defining a user hierarchy, do each level need to be globally unique?

Example - data

I created a data cube, pulling from SQL a week/hour hierarchy. There are thus 168 (7*24) rows, each of the form:

Monday, 0
Monday, 1
…
Monday, 23
Tuesday, 0
…
Sunday 23

I then made a 2-level hierarchy out of it: day/hour.
So far so good.

Example - usage

Now I have another cube, with data related to this hierarchy. Output rows could be for instance:

day, hour, count
Monday, 12, 42
Wednesday, 20, 1
…

Day and hour are dimensions. Now if I want to promote them to my new fancy user-defined hierarchy, I just cannot in my current setup right? I can promote either hour, either day but they could not possibly cover 2 fields.

Solution

The only solution I came up with is basically to add an extra column to my initial hierarchy, which would then read:

Monday, 0, Monday-0
…
Wednesday, 12, Wednesday-12
…
Sunday, 23, Sunday-23

Basically creating a globally unique column over the whole hierarchy. I can then use this unique column in my user hierarchy, and add multiple levels where the caption would be ‘day’ for the 1st and ‘hour’ for the second.

Then, in my real ‘data’ data cube I do the same, basically replacing my 2 columns day/hour with one concatenated column. This one column can then be promoted to the user hierarchy.

Is this the right and only way to go?

#2

Generally, when creating a table or a data source of any kind for the purpose of defining a hierarchy, the child level should always be unique. The parent level may or may not be unique, depending on how you group things up, but the child level will always be.

To use your example, say we have

Monday, 0
Monday, 1
…
Monday, 23
Tuesday, 0
…
Sunday 23

You will have two ID’s - 1 for the parent and 1 for the child. Now, the parent ID should be straightforward

1 - Monday
2 - Tuesday
…
7 - Sunday

However, the child ID should be each individual possible grouping of parent and child, not just each individual child. So in your case, child ID would be something like

Pname - Cname - PID - CID
Monday - 0 - 1 - 1
Monday - 1 - 1 - 2
…
Monday - 23 - 1 - 24
Tuesday - 0 - 2 - 25
…
Sunday - 23 - 7 - 144

With P and C standing for parent and child respectively. The above quotebox is what you will want your dimension table to ultimately look like, to simplify the process of defining the hierarchy. After that, for your fact table, you will want to just have the child ID there to identify each row. This field is what you will want to promote to your previously defined hierarchy in process result.

https://www.dundas.com/support/learning/documentation/cleanse-consolidate-modify-data/transforms/output/process-result

This may be a lot to unpack, but I hope it does give you a better understanding on how to achieve your use case.

#3

Thanks Cristian. This makes prefect sense (although it’s not what I hoped to hear :slight_smile: ) and I do understand much better now. I need to have a hard look at a few of my cubes now.