How to load data from multiple elements in source to single element....?

[Amritha]

Hi Gurus,

We have detailed data @ the source level for a Account dim - (No hierarchies).
ex:
A 100
B 100
C 100
D 100
like these we have around 500 accounts.

Where as the structure for the Account dimension in Tm1 should only have elements such as Travel expenses, Admin expenses etc .... Users are not doing budget at account level...So we need to eliminate the account codes.

ex: Travel expenses is summary of A, B, C, D account codes.

      Admin expense is summary of Acct 201, 205, 206, 207, 208 ...

This hierarchy mapping is given by the users ....not coming from source.

In TM1 ...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

[dusherwo]

If you have a relational engine to hand, put the user mapping in there and do a JOIN/GROUP BY to get your totals
If not:
a    load the user mapping into a dimension,
b    create a cube using that dimension,
c    load the data in there,
d    either rule or TI the totals into your 'real' cube.

[lav4you]

Hi Amritha,

Create an account dimension in TM1 with all possible account codes.
Create a mapping cube mapping  for all account code with corresponding TM1 element or add this mapping as attribute to account code dimension.
write process to pickup this mapping while loading data from ODBC/ASCII to target cube.

Regards,
Jitesh