Dimension Hierarchy extraction to an Oracle table

Post Reply
manoj928
Posts: 60
Joined: Thu Mar 17, 2011 2:13 pm
OLAP Product: IBM Cognos TM1
Version: 10.2.1
Excel Version: 2010

Dimension Hierarchy extraction to an Oracle table

Post by manoj928 »

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,
User avatar
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

Post by Martin Ryan »

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

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
winsonlee
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

Post by winsonlee »

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)
tomok
MVP
Posts: 2831
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

Post by tomok »

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:

Code: Select all

Level_1 = ELPAR('DimName',Element,1);
Level_2 = ELPAR('DimName',Level_1,1);
ASCIIOutPut('FileName',Level_2,Level_1,Element);
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Post Reply