TM1 Forum

Hello All,

I have requirement where we want to store level by level information of a dimension in a DB table,
for Example: For Hierarchy:

Country--> America --> New York
--> Los Angeles
--> San Francisco
--> Canada --> Toronto
--> Vancouver
--> Mexico --> Mexico City
Table structure i want to set as:

Level1 Level2 Level3
Country America New york
Country America Los Angeles
Country America San Francisco
Country Canada Toronto
Country Canada Vancouver
Country Mexico Mexico City

Please help if i can achieve it using the TI process.

Thanks,

Yep, look up odbcconnect, odbcwrite etc in the help file. Searching for "ODBC*" should bring all of it up.

Martin

hi manoj,

just incase if you are asking how to put levels of data into table, i can provide you with some idea how this can be done before using ODBC connect.

On your TI code you can use 3 variable, Level0, Level1, Level2

Then you can use ELLEV to check what level is the element in the structure.

if ELLEV ( element) = 2)
Levek2 = element
if ELLEV ( element) = 1)
Levek1 = element
if ELLEV ( element) = 0)
Levek0 = element
insert data into table ( table name, Level2, Level1, Level0)

manoj928 wrote:Level1 Level2 Level3
Country America New york

Actually, in TM1 the levels are reversed so if city is the bottom level it would be level 0 and Country would be level 2. If this is a balanced hierarchy then what you want to do is extremely simple. Create a dynamic subset on the dimension that filters on level 0 elements (City) and use that as a source for your TI. Then in your data tab have the following code: