Concetinate 2 dimensions to 1 dimension

[Former Member]

Hi all,

I have a 2 dimensions (Account and costcenter) in my Finance application. Now the data from the finance application should be transferred to another application (sales application), but there my account dimension has to be a concetinate of the members of the original account and costcenter dimension.

Example FINANCE APP : ACCOUNT1.MEMBER=600000 COSTCENTER.MEMBER=100 should be transferred to SALES APP on ACCOUNT2.MEMBER=600000_100.

I can not work with a property because for each account there can be 50 costcenters.

Has somebody an idea how to resolve this problem ?

Thx in advance

Bernard

[i045436]

You can create a custom DTS package whic in the first step is rnning a SQL query whihc is moving data and the second step shoud be the BPC task "Load&Process" to process the SSAS cubes.

The sql query should be something like:

Insert into <new fact table>

(select Account+CostCenter, <all other comun dimension> from tblfac2<app> with no lock

UNION ALL

select Account+CostCenter, <all other comun dimension> from tblfactwb<app> with no lock

UNION ALL

select Account+CostCenter, <all other comun dimension> from tblfact<app> with no lock)

Hope this can help you,

Mihaela

[Former Member]

Mihaela,

I don't know how to create custom DM packages, neither how to create an SQl query.

Is this something i have to do in microsoft visual studio ?

Do you have an example of such a package ?

[i045436]

Hi,

The easiest way is to build a custom SQL query to do this. I suppose you have an important amount of data that must be moved and this should be the fast way to do it.

Other solutin can be to export data into files and import it into second application using the right transformation file(not a solution for a big amount of data).

Best regards,

Mihaela

[Former Member]

If you are asking for design guidance, Option 1 is to add the Cost Center dimension to your Sales application for continuity. If you don't want to do that change, you still should not have any issues moving data from Finance to Sales or Sales to Finance. Your options include using DM functions with Data extracts, conversion files and transformation files. or you may try to develop Script logic processes to transfer data on demand.

I don't see this as a problem, but rather you need to determine the process you wish to utilize to transfer the data. If you use the DM strategy, you will have more control. You can export from finance a file. In Sales, you may import the file, and convert the 2 fields into 1 field for the Sales application during the data load process.

That is what I would try first. Hope this helps.

[Former Member]

That is indeed what i'm looking for, a script logic to do the transfer, because it is a process that has to be done each month, and with a lot of records. The work arround with export and import does not seem a user friendly solution to me.