How to implement multiple hierarchies in single dimension?

[abcuser]

Hi,
using TM1 v9.5.2 on Windows 2008 RC2 I would like to implement multiple hierarchies in single dimension.
To demonstrate my problem I have created simple sample (see picture bellow).

I have "Product type" dimension with top most member name "Product type". Children of "All products" are all of the products available in company. Each product from 'All products' list can belong to zero or multiple sub-groups (Grows on trees, Drupe etc), for example Ananas is only located in "All products" group. Banana belongs to "All products" and "Grows on trees" group. Cherry belongs to "All products", "Grows on trees" and "Drupe".

How to implement this kind of multi-hierarchy in TM1? I used to work with Oracle Hyperion Essbase cube server where children of "Grows on trees" and "Drupe" were marked as "Shared member". Is there something like that in TM1 or some other solution?
Regards

[qml]

The simple answer is - you just build your hierarchy like that and it will work fine. There is nothing special you need to do in TM1 to make it work. All N elements in a dimension can belong to multiple hierarchies, can have as many parents as you want (within reason) and also, can have different weights in each of the consolidations they belong to.
Also, "ananas" in English is nothing else than a good old pineapple.

[abcuser]

Hi,
it looks like it is VERY important how the input data are organized! If data are in the 'level like' format:

Code: Select all

Level 2      | Level 1      | Level 0
--------------------------------------
Product type | All products | Banana
Product type | All products | Ananas
Product type | All products | Apple
Product type | All products | Cherry
Product type | Grows on trees | Banana
Product type | Grows on trees | Apple
Product type | Grows on trees | Cherry
Product type | Drupe | Apple
Product type | Drupe | Cherry

then all data are loaded into cube without any problem. But if the data are loaded using recursive SQL, so in the 'parent child' format:

Code: Select all

Parent       |  Child
-----------------------------
Product type | All products
All products | Banana
All products | Ananas
All products | Apple
All products | Cherry
Product type | Grows on trees
Grows on trees | Banana
Grows on trees | Apple
Grows on trees | Cherry
Product type | Drupe
Drupe | Apple
Drupe | Cherry

then members are moved to the hierarchy that is last loaded.

I have rewritten and SQL in turbointegrator from second input data to first input data and now data are correctly loaded.
Just a question, is there any way to set that second format of data should not be "moved" to new hierarchy location, but instead add new leaf member when turbointegrator process is executed?
Regards

[lotsaram]

There should be no issue with the second simple 2 column format of parent | child. In fact this is the format I usually prefer and creatin multiple hierarchies from such input is no problem at all.

If this isn't working for you then there is something wrong with your code and I would suggest that you post your code.

[Michel Zijlema]

it looks like it is VERY important how the input data are organized! If data are in the 'level like' format:
...
then all data are loaded into cube without any problem. But if the data are loaded using recursive SQL, so in the 'parent child' format:
...
then members are moved to the hierarchy that is last loaded.

Hi,

Not sure whether this is causing what you see, but... Please note that clicking 'All' in the subset editor will display all elements once, because of which not all consolidations are expanded when elements are part of multiple consolidations. To open up all consolidations you need to click the 'Expand all ' button (which is not the same as the 'All' button).

With both source sets described above you should be able to reach what you're after.

Michel

[abcuser]

Hi,
I have written a recursive SQL to demonstrate the problem, but have found out that everything is working correctly. Strange, I can't reproduce the problem. I don't know how exactly my SQL was written in the first place, maybe I done some mistake when writing recursive SQL. It looks problem is solved. If in some other project I will get into this problem again I will post the sample (yes I know, I will look deeply into sample to make it sure it is really a problem).
Regards