Issue with dimension structure

[Cognostm1.ibm]

Hi Gurus,

We have detailed data @ the source level for a Account dim.
ex: A 100
B 100
C 100
D 100
Where as the structure for the Account dimension in cube is @ summary level.
ex: Travel expenses summary element for A, B, C, D account codes.

In target dimension ...Element travel exp should have 400.

Note: we don't want to have detail account structure in the target dimension.

How to achieve this...? Please share your valuable suggestions...

Thanks in advance!!!

Apps

[tomok]

You have two options; 1) build a lookup dimension in TM1 with the account hierarchy, or 2) build a lookup table in your source, assuming it is relational.

If you go the TM1 route you would just look at each record from the source, find the parent in the lookup dimension, and load to that element in your Account dimension. Keep in mind you will need to accumulate the data when posting to TM1.

If you go the relational route you can just join the source table with the lookup table and use GROUP BY to get the totals for loading to TM1.

[Cognostm1.ibm]

Thanks a lot Tomok for your quick response and suggestions.

Can you share how does look up dimensions looks and how can i map a lookup dimension to the Summary dimension in the cube?

I need to load the data from source to the target dimension (summary level).

Thanks,
Appz

[tomok]

Can you share how does look up dimensions looks and how can i map a lookup dimension to the Summary dimension in the cube?

"Lookup dimension" may have been a bad choice of words. I mean create a dimension that has the hierarchy you want. Then while processing the source file you read the Account# and use the ELPAR function into this dimension to find which summary account it should be posted to.

[Cognostm1.ibm]

Hi Tomok,

Please find the account structure enclosed as an attachment.

Note: We should not have account dimension in the Tm1 at detail level. It should be @ summary level marked in green color (Wrong Structure).
And Account dimension in the source has 300 detail item...its not feasible to use ELPAR for 300 items am i right...? Please suggest

Thanks a lot !!! For taking timeout for my queries.


Appz

[tomok]

Since it appears you have very limited knowledge of hierachies and/or TM1, my advice to you is to select the relational option of having a lookup table and then doing a join and group by to get what you want.

[Cognostm1.ibm]

Thank you for your patience...Will go with the relational option.