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,
Dimension Hierarchy extraction to an Oracle table
- Martin Ryan
- Site Admin
- Posts: 1988
- Joined: Sat May 10, 2008 9:08 am
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2010
- Location: Wellington, New Zealand
- Contact:
Re: Dimension Hierarchy extraction to an Oracle table
Yep, look up odbcconnect, odbcwrite etc in the help file. Searching for "ODBC*" should bring all of it up.
Martin
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
Jodi Ryan Family Lawyer
-
- Regular Participant
- Posts: 180
- Joined: Thu Jul 01, 2010 3:06 am
- OLAP Product: Cognos Express
- Version: 9.5
- Excel Version: 2007
- Location: Melbourne, Australia
Re: Dimension Hierarchy extraction to an Oracle table
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)
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)
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Dimension Hierarchy extraction to an Oracle table
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:manoj928 wrote:Level1 Level2 Level3
Country America New york
Code: Select all
Level_1 = ELPAR('DimName',Element,1);
Level_2 = ELPAR('DimName',Level_1,1);
ASCIIOutPut('FileName',Level_2,Level_1,Element);