Export dimensional hierarchy to relational source

[HurricaneDitka]

Hi,

Is there anyway to export a dimension with hierarchy in tact to a database table? I have a scenario where we load a flat chart of accounts into TM1 and set up our hierarchy structure within the dimension (i.e. specify that the Advertising Account rolls into Marketing Expenses > Total Expenses > Income Before Taxes). I need to reference that hierarchy from a relational source.

I'm aware of the ability to export a dimension to a cma and have worked with the ODBCOutput function as well... but these do not produce desired results as parents are all in one column. I'm looking for a solution that produces columns representing each level in the hierarchy. For example

Column: Acct_Level1 / Acct_Level2 / Acct_Level3 / Acct_Detail
Data: Income Before Taxes / Total Expenses / Marketing Expenses / Advertising

Thanks in advance for any advice!

[David Usherwood]

You will probably need to do some work with TI to get the structures flowing the way you want eg (in pseudocode)
Read all items
if level0 then
output elem, par, gp, ggp, gggp
else skip

But this does depend on you having a nice simple level based structure. It's pretty common for a TM1 hierarchy not to be like that.

We have built a routine which transfers dimensions between servers (and between TM1 versions!) using a relational store. You only need a couple of tables to move all dimensions in a server. But you'd have to work on it to get it usable in a dimensionally modelled relational world.

[HurricaneDitka]

I'm not sure how to identify the parent of an element. Would I have to set up the dimension with attributes describing the parents? (Not desirable as this is an added layer of maintenance as account hierarchy changes). Or is there functionality available to get the parent?

As for our hierarchy, it's ragged... but is level based.

Thanks again!

[Michel Zijlema]

Hi,

You can determine the parent of an element using the function ElPar(dimname, element, index).
The tricky thing is the index parameter. If you have multiple alternative hierarchies in the dimension an element can have multiple parents.
If each element can only have one parent setting the index parameter to 1 will do. Otherwise you need to loop (using While) through all parents the elements has. The function ElParN(dimname, element) will return the number of parents for the element.

Michel

[dnicol]

Hello,
For anybody else who finds this thread and wants some example syntax - I have a similar requirement that I just coded up in a TI to export a dimension hierarchy to SQL Server. I'm pretty new to TM1 so sometimes even simple things like this can take a while (no thanks to TM1's ancient IDE and debugging features grrr).

Anyway, below shows the data tab logic/while loop required to accommodate the circumstance where a child may have multiple parent elements (as discussed earlier in the thread). Once I get the data in SQL Server I can easily recreate the parent child relationship using SQL.

Code: Select all

sSQL = '';

CountofParent = ElParN('Account', Account);
ElementLevel = ElLev('Account', Account);
ParentCounter = 0;

WHILE (ParentCounter <= CountofParent);

ParentElement = ElPar('Account', Account ,ParentCounter);

sSQL = Expand('INSERT INTO dbo.AccountTable (Account, parentelement, countofparent, elementlevel) VALUES (''%Account%'',''%ParentEl
ement%'',''%CountofParent%'', ''%ElementLevel%'')');

ODBCOutPut('SQLDEV', sSQL );

ParentCounter = ParentCounter+1;
END;