Transfer Value from 1 Reporting Unit and Department to another RU and Dept

Hello,

Is it possible to transfer or map a Staff Cost to a different RU and Dept. using Dataflow and then apply the corresponding FX rate? Please see table below

Currently, I have a Temp Cube without the RU and Dept and store the value in there. And then I intersect it with Temp - To cube which has the flag for the To entities.

Cube C = (Temp - Without RU and Dept) x (Temp - To) x FX rate

However, this approach ignores the From details since in the Temp - Without RU and Dept cube I am just aggregating the values.

Entity/Value	From	To
Staff	Staff_1	Staff_1
Reporting Unit	RU_A	RU_E
Department	Dept_A	Dept_C
Cost	100	100*(FX rate)

Find more posts tagged with

Dataflow

Accepted answers

Robert Barkaway

Okay so essentially you follow these steps:

create your copy dimensions, say Reporting Unit [c] and Department [c]
create mapping cubes mapping Reporting Unit to Reporting Unit [c] and Department to Department [c] one to one (it’s easiest to do this in the same data reader you use to populate the copy dimensions), let’s call them Map RU and Map Dept
create your exchange rate cube dimensioned by Reporting Unit, Department, Reporting Unit [c] and Department [c], let’s call it FX Rate RU Dept
populate that cube with your exchange rates
in your procedure your source amount is sitting in your original cube by Reporting Unit and Department
create a temp cube by Reporting Unit, Department, Reporting Unit [c] and Department [c], say Temp Converted Amount
make your target selection on Reporting Unit [c] and Department [c] (if necessary you can make your selection first on Reporting Unit and Department and transfer the select using Select Based On with your Map RU and Map Dept cubes)
flow your source cube into Temp Converted Amount converting it using your exchange rate cube, you now have your calculated target amount by Reporting Unit, Department, Reporting Unit [c] and Department [c]
create a second temp cube by Reporting Unit [c] and Department [c], let’s call it Transfer Converted Amount
flow Temp Converted Amount into Transfer Converted Amount, you now have your target amount just by Reporting Unit [c] and Department [c], this means you can drop your selection on the source Reporting Unit and Department and select the target Reporting Unit and Department (you can pass the selection back from Reporting Unit [c] and Department [c] again using Select Based On with Map RU and Map Dept)
Now you can just flow Transfer Converted Amount into your original cube and you should have your correctly converted amount by Reporting Unit and Department

Does that make sense?

All comments

Robert Barkaway

Hi Efraim, the usual way to do something like this is to create a copy of the RU and Department entities so that you can have your exchange rates dimensioned by both the sending and receiving RU/Dept combinations, you can also then use those copy dimensions to dimension the cubes you use in your calculation so that you calculate the final amount by the copy dimensions. Finally you have one to one mapping cubes between the original and copy dimensions to allow you to transfer selections between them easily, so that once you calculate your final amount against the copy dimensions you can then transfer it to a cube dimensioned by the original dimensions. Does that make sense?

Efraim

Hello Robert,

Thanks for the response! I think I understand the last part - the mapping between original and copy dimensions. Can you please elaborate on using Copy dimensions on the calculation? If you can rewrite the expression I have above that would be amazing.

Robert Barkaway

Okay so essentially you follow these steps:

create your copy dimensions, say Reporting Unit [c] and Department [c]
create mapping cubes mapping Reporting Unit to Reporting Unit [c] and Department to Department [c] one to one (it’s easiest to do this in the same data reader you use to populate the copy dimensions), let’s call them Map RU and Map Dept
create your exchange rate cube dimensioned by Reporting Unit, Department, Reporting Unit [c] and Department [c], let’s call it FX Rate RU Dept
populate that cube with your exchange rates
in your procedure your source amount is sitting in your original cube by Reporting Unit and Department
create a temp cube by Reporting Unit, Department, Reporting Unit [c] and Department [c], say Temp Converted Amount
make your target selection on Reporting Unit [c] and Department [c] (if necessary you can make your selection first on Reporting Unit and Department and transfer the select using Select Based On with your Map RU and Map Dept cubes)
flow your source cube into Temp Converted Amount converting it using your exchange rate cube, you now have your calculated target amount by Reporting Unit, Department, Reporting Unit [c] and Department [c]
create a second temp cube by Reporting Unit [c] and Department [c], let’s call it Transfer Converted Amount
flow Temp Converted Amount into Transfer Converted Amount, you now have your target amount just by Reporting Unit [c] and Department [c], this means you can drop your selection on the source Reporting Unit and Department and select the target Reporting Unit and Department (you can pass the selection back from Reporting Unit [c] and Department [c] again using Select Based On with Map RU and Map Dept)
Now you can just flow Transfer Converted Amount into your original cube and you should have your correctly converted amount by Reporting Unit and Department

Does that make sense?

Efraim

Thanks a lot for the clear steps!