multi-level hierarchy turbointegrator process

[em_past]

Hi,
How can I get levels for data from TI process?
I've heard that there are some functions for it - ELLEV for example, but do You know how can i use it? When I try something like that:
var=ELLEV('dimName', 'ElName');
ASCIIOutput('c:\data.txt', var);

I've got an error - something is bad with structure of my code.
Do You know which function should i use?
I need to know for which level my data belongs to.

Thanks a lot!

[Duncan P]

It would help if you posted what exact error you got.

From your code I would guess it is complaining because you are passing var which is a number to ASCIIOutput which is expecting only string arguments. You should use NumberToString.

[em_past]

Hi,
I don't know wky, but now code is OK.
This is a piece of my code:

if(ELLEV('0COSTCENTER_0COSTCENTER','COSTCENTER') = 0);
ASCIIOutput('c:\data.txt', '0');
ELSEIF(ELLEV('0COSTCENTER_0COSTCENTER','COSTCENTER') = 1);
ASCIIOutput('c:\dane.txt', '1');
ELSE;
ASCIIOutput('c:\data.txt', 'inne');
ENDIF;

a=ELLEV('0COSTCENTER_0COSTCENTER','COSTCENTER') ;
ASCIIOutput('c:\data2.txt', COSTCENTER, NumberToString(a));

But as a result i see that all elements have the same level in a hierarchy. My datasource is SAP BW and i checked there my data, and saw that they have different levels, not only 1.
My hierarchy has 4 levels. May be i use wrong function to check it? (ELLEV)

[jstrygner]

I think COSTCENTER is a variable, so you need to remove apostrophes from this part:
ELLEV('0COSTCENTER_0COSTCENTER','COSTCENTER')

Seems like your dimension does have an element named COSTCENTER, but putting it in apostrophes makes your code always checking level of this particular element only.

[em_past]

I did like you said, but results are the same.
COSTCENTER is realy a name of element. My code is in process of export data to SQL. May be i should do it in process of import data from SAP BW to cube?
I don't know if this ELLEV function is correct for data level extraction.

[jstrygner]

I did like you said, but results are the same.

In such a case it is a very good moment for you to show exaclty what you did (please paste exact code with information of names on Variables tab of your TI) and what are your results.
It would be helpful if you could also re-write what in fact are you trying to achieve. The more I try to dig in what you already wrote, the more confused I get.

My datasource is SAP BW

My code is in process of export data to SQL.

From above I would conclude your source for TI is a SAP BW object (maybe a cube or dimension?) to which you are directly connecting.
And your target would be a table in RDB (not data.txt and dane.txt flat files).
So does this 0COSTCENTER_0COSTCENTER dimension even exist in your TM1 model?

May be i use wrong function to check it? (ELLEV)

Every element in every dimension in TM1 has a property of level. This property can be retrieved by the ElLev ( Dimension, Element ) function. If that is what you want to achieve, then that is the function to use.

With my above questions and quotations I am only trying to express how lost I am in finding exactly what your goal is and why what you get so far is not what you expect.
Please try to describe it more clear and I will be glad to help you here.

Regards

[rmackenzie]

So does this 0COSTCENTER_0COSTCENTER dimension even exist in your TM1 model?

It's an important question because if you pass a dimension name that does not exist to ELLEV it won't throw an error but just return 0. Consider these code examples:

Code: Select all

# this does not error even though the dimension does not exist
nElementLevel = ELLEV ( 'DIM_DOES_NOT_EXIST', 'FOO' );
AsciiOutput ( 'temp.txt', 'Element level: ' | NumberToString ( nElementLevel ) );

# the ascii output goes ahead because the IF condition is TRUE even though the dimension does not exist...
IF ( ELLEV ( 'DIM_DOES_NOT_EXIST', 'FOO' ) = 0 );
  AsciiOutput ( 'temp.txt', 'False positive?' );
ENDIF;

# the IF condition will never be met for a non-existent dimension
IF ( ELLEV ( 'DIM_DOES_NOT_EXIST', 'FOO' ) > 0 );
  AsciiOutput ( 'temp.txt', 'This output should never occur' );
ENDIF;

The official documentation refers to a 'valid dimension name'. Given that 0 is a valid output in the case of n-level elements or consolidations with no child elements, I think this is a bug in TI.

I don't know if this ELLEV function is correct for data level extraction.

Regarding the OP's question it is going to require further explanation. I suspect that (s)he incorrectly thinks that the ELLEV function may return information about the hierarchy level in the source (BW) cube/ dimension and has not yet actually created a TM1 dimension. The ELLEV bug I'm referring to is confusing the matter because an error isn't being thrown regarding the non-existence of the TM1 dimension. That's pure speculation on my part - we need more information from the OP.

[em_past]

Thanks for your replies!

May be it is not important that my datasource is SAP BW, but i wrote it because with SAP and TM1 are some problems , I heard.
This is my code from data tab in advanced View in my process:

Code: Select all

/*
	This is only for testing ELLEV function. I don't use this txt file.
*/

if(ELLEV('0COSTCENTER_0PROFIT_CTR', PROFIT_CTR) = 0);
ASCIIOutput('c:\dane.txt', '0');
ELSEIF(ELLEV('0COSTCENTER_0COSTCENTER',COSTCENTER) = 1);
ASCIIOutput('c:\dane.txt', '1');
ELSE;
ASCIIOutput('c:\dane.txt', 'inne');
ENDIF;

a=ELLEV('0COSTCENTER_0COSTCENTER',COSTCENTER) ;
ASCIIOutput('c:\dane2.txt', COSTCENTER, NumberToString(a));

/*
	Here i'm exporting data to table in SQL. You can see dimension names and elements names.
*/
ODBCOutput('DWSTG_CONN',
'INSERT INTO [COSTCENTER]
(
    [Comp_code]
   ,[Costcenter]
   ,[Resp_pers]
   ,[Resp_pers2]
   ,[Co_area]
   ,[Profit_ctr]
   ,[_Key]
   ,[Name]
   ,[MediumName]
   ,[LongName]
   ,[ProfitCenterKey]
   ,[ProfitCenterName]
   ,[ProfitCenterMediumName]
   ,[Levels]
)
VALUES(''' 
	 | COMP_CODE | ''','''
	 | COSTCENTER | ''','''
	 | ATTRS('0COSTCENTER_0COSTCENTER', COSTCENTER, 'resp_pers') | ''','''
	 | ATTRS('0COSTCENTER_0PROFIT_CTR', PROFIT_CTR, 'resp_pers2') | ''','''
	 | CO_AREA | ''','''
	 | PROFIT_CTR | ''','''
	 | ATTRS('0COSTCENTER_0COSTCENTER', COSTCENTER, 'key') | ''','''
	 | ATTRS('0COSTCENTER_0COSTCENTER', COSTCENTER, 'name') | ''','''
	 | ATTRS('0COSTCENTER_0COSTCENTER', COSTCENTER, 'mediumName') | ''','''
	 | ATTRS('0COSTCENTER_0COSTCENTER', COSTCENTER, 'longName') | ''','''
	 | ATTRS('0COSTCENTER_0COSTCENTER', COSTCENTER, 'profitCenterKey') | ''','''
	 | ATTRS('0COSTCENTER_0COSTCENTER', COSTCENTER, 'profitCenterName') | ''','''
	 | ATTRS('0COSTCENTER_0COSTCENTER', COSTCENTER, 'profitCenterMediumName') | ''','''
         | ATTRS('0COSTCENTER_0COSTCENTER', COSTCENTER, 'levels')  |
''')');

I've attached screen of my elements which i've created in process. Does my explanation is clear now?

Regards,
Michael

[lotsaram]

Not that the TI mapping tab isn't (a little) helpful but what you really need to include is the ACTUAL CODE and a screenshot of the dimension(s) particularly cost enter. It looks like you have defined each field in the source as a separate dimension rather than defining intra-dimension mapping; that is parent child relationships and therefore levels. If you haven't defined any mapping within the tm1 dimension then of course the dimension will be flat and contain no levels. The structure within TM1 is therefore NOT THE SAME as in the source system.

HTH. I'm sure you'll get there in the end but it will help you and anyone trying to assist you if you follow the request for assistance guidelines and include all relevant information.

[em_past]

I'm sory for stupi questions but:
what code do you need? I haven't a lot of my own code - do you need screenshot of tab where i create dimension?

I think that i haven't mapping dimensions. Where and how can i do it? Can I do it in some tab or i need use some function?


Regards,
Michael

[jstrygner]

Hi Michael,

Thanks for providing some more information, unfortunately for me it is still too much about guessing, so I want to ask you to provide following:
1. Please provide at least three full rows from your source (all columns with names and values in those columns, if your source also have some numeric data that carry some business value, of course here you can put a zero, I am interested in metadata part).
2. Please provide expected effect in SQL table generated based on those three (or more) rows.
3. Please provide screenshot of Variables (Zmienne) tab from your TI process, so it will be visible what types you have defined for particular columns.
4. Please let know if the 0COSTCENTER_0COSTCENTER dimension has any elements in your TM1 model (Bez zmian means No change, so even if you have COSTCENTER variable defined as Element and assigned to the 0COSTCENTER_0COSTCENTER dimension, no element inserting is performed). If it does have elements, are all of them leaves or maybe there are also consolidations?
5. You provided only code from Data tab. Does it mean there is no code on Prolog, Metadata and Epilog? Even the automatically #****XYZ: Generated Statements*** section is emtpy (not sure how this is translated in Polish TM1 version)?

Regards

[em_past]

Hi,

Thanks for your time for help me.

ad. 1
For now i can't give you full rows from datasource because i haven't directly access to SAP unfortunately.

ad. 2

COL_1 COL_2 COL_3
CodeName CodeName/48021 CodeName
CodeName CodeName/48024 CodeName
CodeName CodeName/48025 CodeName

COL_4 COL_5 COL_6
CodeName/512 2013-03-08 16:15:57.370 PersonalData
CodeName/512 2013-03-08 16:15:57.373 PersonalData
CodeName/512 2013-03-08 16:15:57.373 PersonalData


COL_7 COL_8 COL_9
CodeName/48021 Lokal_Użyt-nieruch Dzierż.Lokale Użytkowe-nieruchomości
CodeName/48024 Air Liquide Polska Air Liquide Polska Sp.z o. o.
CodeName/48025 SCHLUMBERGER SCHLUMBERGER


COL_10 COL_11 COL_12
CodeName/512 Nieruchomości do Wyn Nieruchomości do Wynajęcia
CodeName/512 Nieruchomości do Wyn Nieruchomości do Wynajęcia
CodeName/512 Nieruchomości do Wyn Nieruchomości do Wynajęcia


COL_13 COL_14 COL_15
Level_0_name(or number) Level_1_name(or number) Level_2_name(or number)
Level_0_name(or number) Level_1_name(or number) Level_2_name(or number)
Level_0_name(or number) Level_1_name(or number) Level_2_name(or number)


I hope that it is not too complicated. I've got only String values or date. Of course i haven't 3 last columns, this is wahat i want: number or name of level of my element/attribute.

ad. 3
i've attached Variables sreenshot.

ad. 4
I have COSTCENTER element in 0COSTCENTER_0COSTCENTER dimension, because i'm exporting data by this element to SQL. I think that this is a proof?

In action tab i've got No change setting like you wrote, but I've got another process which is responsible for importing data from SAP to cube and when dimension is changing this process will know it, i think? Or may be i can't see results without run my FIRST process (importing data from SAP) every time? And then should i run this process?

ad. 5
In prolog i've only code for connecting with database.

Metadata is empty.

Data you've seen

And in epilog i've got only this:

Code: Select all

#****Begin: Generated Statements***
#****End: Generated Statements****

ODBCClose('dwstg_conn');

CubeUnload('0COSTCENTER');

So nothing special.

Best regards,
Michael

[em_past]

Hi,

It's me one again. I try describe again my problem and attached some screenshots.

I've published package in FM. In Report Studio i can see that my dimension hierarchy has 5 levels (picture 1). I have to import some attributes from each of level, for example description attribute from level0, level1, level2, level3 and level4.
But in TI process i can't see these levels. In attribute tab i see only all levels in 1 (?) (picture 2)

And after import data to cube there are only 2 hierarchy (picture 3), but level always is 0. And what is more, not all elements have hierarchy.

What can I do to have levels like i've got in ReportStudio?

[jstrygner]

Hi em_past,

Thanks for providing many more details.

At first I thought you will have all your information that you want to export with SQL statement in the SAP BW source that is used by the TI you provided your first code for.
Now I understand, you have all/most of the required information as attributes in the 0COSTCENTER_0COSTCENTER dimension in TM1, because you update it with separate TI process.
Again, I am not 100% sure now what is your expected result, because first you provided SQL INSERT code with following columns: [Comp_code], [Costcenter], ..., [Levels] (14 columns), but then you gave this example with COL_1 to COL_15 (15 columns) that do not look like aligning with the ones from SQL INSERT statement.

Anyway I assume your current problem is concentrated on receiving the value for [Levels] column.
The level you are interested in is probably defined in SAP BW in this LEVEL0 / LEVEL00 / ... / LEVEL04 attribute. In such a case, if you import this value to TM1 0COSTCENTER_0COSTCENTER dimension under levels attribute, you should use (as in your SQL) this ATTRS('0COSTCENTER_0COSTCENTER', COSTCENTER, 'levels') part.
If you will use the ELLEV function, you will retrieve level of element in hierarchy that is defined in TM1 dimension. In your case for ZAP/11110 it will be 0 (as for all leaf level elements in TM1 dimension).
You can easily check level of element in Subset Editor (Edytor podzbiorow) in the properties pane. If I am not wrong, you can even see an example in your screenshot where ZAP/11110 element has Poziom (Level) shown as 0 (partially cut column).
Looking at visible elements in your screenshot of 0COSTCENTER_0COSTCENTER dimension only ZAP H_ZAP and REST_H elements will not have a level value equal to 0 (depending on how deep are the hierarchies beneath those elements it will be level 1 or more).

I hope this will be of any useful value for you