Create Dimensions using TI from a Parent-Child Table

[gbehel]

Hello all,

I'm somewhat new to TM1 coding, Im involved in development of Dimensions using TI processes or otherwise for the following case :

Tables tructure as follows :

Parent Child Agrregation
M1 M2 Y
M1 M3 N
M1 M4 Y
M4 M5 Y
M4 M6 N
M6 M7 Y
M6 M8 Y

The final dimention structure to be derived from the above table :

M1
M2 (Y)
M3 (N)
M4 (Y)
M5 (Y)
M6 (N)
M7 (Y)
M8 (Y)

"Y" and "N" in the bracket indicate whether the particular child rolls up to parent or no.

In the above example, M6 and M3 donot roll up to parents. Hence M1 equals (M2+M4) whereas the individual values of M3 are loaded and retained.

Any help or sample code will be highly appreciated. Its a critical requirement for creating all the dimensions for this TM1 model.

Attached is the notepad file giving the above details.
Kind Regards
Girish Behel

[declanr]

Code: Select all

If ( vAggregation @= 'Y' );
         nWeight = 1;
Else;
         nWeight = 0;
EndIf;

Then crack on with a DimensionElementComponentAdd()

[gbehel]

Thanks Declanr,

this takes care of one issue of assigning roll up property to the element. My other issue is how to define the parent child hierarchy from the data-mart dimension table. as you can see in the attached file the paretn/child column needs to be transformed to dimensional structure of TM1...

I will appreciate your help in this regard
--Girish

[declanr]

Thanks Declanr,

this takes care of one issue of assigning roll up property to the element. My other issue is how to define the parent child hierarchy from the data-mart dimension table. as you can see in the attached file the paretn/child column needs to be transformed to dimensional structure of TM1...

I will appreciate your help in this regard
--Girish

If you look at DimensionElementComponentAdd in the reference guide you will see that it covers both aspects. An element only has a weight as part of a consolidation.

[gbehel]

Hi Rodger,

I am still not getting the hang of it. I
referred to DimensionElementComponentAdd. It is clear that one can assign a
child to a parent and define weight for the same. However I can do this
only if i know which is the parent and which is the child. The base table
given to me as follows :

Parent Child Agrregation
M1 M2 Y
M1 M3 N
M1 M4 Y
M4 M5 Y
M4 M6 N
M6 M7 Y
M6 M8 Y

From the above parent/child column, I need to define which the the parent
and the associated child. For example, in the above example M1 is parent of
M2,M3 and M4. M4 is parent of M5 and M6. M6 is the parent of M7 and M8

How do I play around with the TI code to indentify this and araange the
elements in the TM1 hierarchy format.

Hope my query is clear. Your help will be great in getting me started on
this. There are 12 dimensions and all of them have similar structure.

Regards
Girish

[Wim Gielis]

I'm somewhat new to TM1 coding

That is not an issue, but please DO read that Turbo Integrator manual. Specifically, the usage of variables.

[gbehel]

Hello Wim,

can you give me some startup code... i have tried a couple of things but didnt work. the requirement is as follows :

Parent Child
M1 M2
M1 M3
M1 M4
M4 M5
M4 M6
M6 M7
M6 M8

From the above parent/child column, I need to define which the the parent
and the associated child. For example, in the above example M1 is parent of
M2,M3 and M4. M4 is parent of M5 and M6. M6 is the parent of M7 and M8

any suggestion is welcome.

kind regards
Girish Behel

[Alan Kirk]

can you give me some startup code... i have tried a couple of things but didnt work. the requirement is as follows :

Parent Child
M1 M2
M1 M3
M1 M4
M4 M5
M4 M6
M6 M7
M6 M8

From the above parent/child column, I need to define which the the parent
and the associated child.

In your original question you had a third column, which was "Aggregation".

Assuming that to still be the case:
- The Metadata and Data tabs will both loop through every row of the data source, agreed?
- Your data source, whatever it is (whether it's a table via an ODBC query or a flat file) should have three columns.
- For each and every row, the TI process will read the values from that row. As mentioned above, it does it once in the Metadata tab, once in the Data tab.
- Those values are assigned to the corresponding variables. If you don't understand the concept of variables, you're going to have serious problems creating a TI process.
- Once you have created the Parent and Child elements (if necessary) using the DimensionElementInsert function, all you have to do is use an If() block in the Metadata tab. Again, If() blocks are basic coding skills. If you don't know them, you won't be able to write code.
- If the value of the Aggregation variable is Y, then the weight = 1. If it's not, the weight =0. I'd suggest that you assign this weighting to a user defined variable. Either that, or get the data source changed so that instead of Y and N it writes 1 or 0 to the file so that you don't have to bother with the If() block.

So the function would look like:

Code: Select all

DimensionElementComponentAdd('YourDimName', Parent, Child, l_ElementWeightFromYourIfBlock);

Note the single quotes around 'YourDimName' indicating that it's a literal string, and the absence of them around the other arguments indicating that they're variables.

[gbehel]

Thanks Alan,

This works...

Regards
Girish

[gbehel]

This is what i coded


#DEIFINING WIEGHT OF THE ELEMENT
IF(vMEMBER_AGG_ATTRIB @='+');
vAGGR_WEIGHT=1;
ELSE;
vAGGR_WEIGHT=0;
ENDIF;

#INSERTING PARENT AND CHILD
vDIM='ACCOUNTS';

DimensionElementInsert(vDIM,'', vPARENT,'C');
DimensionElementInsert(VDIM,'',vMEMBER,'N');
DimensionElementComponentAdd(vDIM,vPARENT,VMEMBER,VAGGR_WEIGHT);

it is throwing some error regarding prolog. nothing is defined in prolog. the error is in a different language and hence unable to guage what the problem is.

Error Reads like this
Fehler : Prolog Prozedurzeile (0) : Die Datenquelle "\\File path\\" Konnte nicht geoffnetwerden.

any help will be higly appreciated

[David Usherwood]

Suggestions...
a Paste the message into Google Translate
b Post it here
??

[gbehel]

fault-line procedure, the data source could not be opened


"fehler prozedurzeile die datenquelle konnte nicht geoffnet werden" from German

howeveri can open the file from hte data source mentioned. infact in the datasource tab of TI, it shows me preview of records. wondering what could be the issue

[tomok]

Whenever you create a TI process that is designed to process data there are always two boxes to define the source. The source that is used to create the process/edit and one to actual run it. Why? Because creating/editing the process uses YOUR rights to the file while actually running the process uses the rights of the TM1 Server. Just because you can access the file does not mean the server will be able to when it comes time to run it. Comprende?

[gbehel]

Agree... this is a case of insufficient rights on the server to read the data saource... i could find the following solution :

Problem(Abstract)

When attempting to execute a Turbo Integrator process in TM1, clicking on the preview window will display the sample values but executing the TI process will generate the following error:
"Prolog procedures line (0) Unable to open datasource.
However, user is able to open the data source manually from the file system.

Cause


Permissions of the account that the TM1 service is running under are insufficient.

Resolving the problem


1- On the machine where the TM1 server instance resides, open the services menu either by clicking Start -> Run and type services.msc or through the control panel.


2- Find the TM1 server instance running which the TI process is being executed against.

3- Click the 'logon' tab.

4- Change from the 'local system' radio button to the 'This Account' radio button.

5- Enter the username and password of a Domain account that is a member of the local Administrators on this machine.

6- Click Apply -> OK.

7- Restart this TM1 service

[paulsimon]

Alternatively you could just store the file on the same machine as the TM1 Service. Then the account that TM1 is running under should be able to access it. However, I would usually set up a TM1 Service under a Domain Account.

Regards

Paul Simon