Currency Conversion

[anoops81]

Hi All ,

We have a reporting cube . The data for this cube is loaded via a TI process from tables.We have currency dimension also in the cube which contains many currencies. Multiple currencies are used in the source tables .

Now the requirement is that,

When the user selects a particular currency from the cube, then all the calculations should change into that particular currency. For example if he selects CAD , then calculations should be shown in CAD.

Since the source itself has different currencies , what I have done is divide the amount with conversion rate of USD to get respective USD amount (via TI ).

Code: Select all

V_netfin=netfin\cellgetn('currency cube',Currency,YQ,'REP_USD');

"currency cube" has the converion rates from one currency to another. Pls find the screenshot attached.

currency_cube.JPG (196.42 KiB) Viewed 6726 times

in the rules part of the reporting cube I have written the follwoing rule,

Code: Select all

['NetFin' ]  = N:DB('Cube_Under Validatiaon', !Time, !Deal_Dim, !PL_Dim, !BS_Dim, !Currency, !Collateral_Dim, !Index, 'Vnetfin')  * DB('Currency Cube', !Currency, !Time, 'REP_USD');

feeders;
['Vnetfin' ] => ['NetFin' ] ;

Now When I select any other currency from the cube , the data is not getting populated for netfin.

Am I missing anything here ? Please guide me if I am going in the wrong direction .

Regards
Anoop

[ellissj3]

I see you're using 2 cubes as DB's in your rule. Do you have a feeder from each cube sending this data to the target cube?

[anoops81]

Hi elliss,

I am writing the rules in the "Cube Under Validation". Since it is multiplying , I have written the feeder for only one value.

Code: Select all

feeders;
['Vnetfin' ] => ['NetFin' ] ;

The reporting cube is too much heavy. It has a dimension with 40 k elements. So When I write a feeder statement at the currency_cube (Source cube), it is not getting saved.

Code: Select all

['REP_USD' ]  => DB('Cube_Under Validatiaon', 'All Year', 'FAS DEALS', 'FAS PnL', 'All BSLA', !Currency, 'FAS PnL', 'All Index', 'Vnetfin');

Regards
Anoop

[ellissj3]

Hello,

Code: Select all

['Vnetfin' ] => ['NetFin' ] ;

The above looks good. I am focusing on the below statement.

The reporting cube is too much heavy. It has a dimension with 40 k elements. So When I write a feeder statement at the currency_cube (Source cube), it is not getting saved.

I have worked with dimensions much larger than this. If you want the rule calculation to work for all 40k elements, you'll have to feed each element (either by feeding the overall consolidation or using a !Element in the feeder and the rule. I recognize that you're trying not to overfeed, but I think you're actually underfeeding your rules. This could cause the lack of calculation you're seeing.

The act of feeding these elements could negatively impact the performance of your cube, so I would recommend a full testing schema in a development environment as well as regression testing to evaluate the new performance of the cube vs. what it was before.

[anoops81]

Hi Elliss,

I was able to save the Feeder statement

Code: Select all

['REP_USD' ]  => DB('Cube_Under Validatiaon', 'All Year', 'FAS DEALS', 'FAS PnL', 'All BSLA', !Currency, 'FAS PnL', 'All Index', 'Vnetfin');

I had to wait for some minutes to get it saved in the currency cube. But there is no impact on the reporting cube. The values for 'vnetfin' seems to be the same.

I am feeding by using overall consolidation thinking that I need this calculation to be working for all the elements in that particular dimension.

Regards,
Anoop

[ellissj3]

Have you tried navigating to the lowest level in the cube ( where the rule should calculate) then using the rule tracer? The application itself is a bit archaic, but could prove useful in this case.

[ellissj3]

You can also try to trace the feeders to determine if the cell is being fed.

[anoops81]

Hi Ellis,

I was able to implement the conversion logic for the required measure elements.

I face another challenge now , the data is coming from database which has currency as a dim. Now the issue is that the conversion logic is applied only to amounts. There are other measure elements also like interest rate , origination date etc .

Now when the user selects R_currency for seeing the amount in his local currency , he is getting nulls for all other measure elements. Please find the screenshot below,
Here, the last row with currency 'USD ' is coming from database and it is being converted to Reporting currencies (R_Currency) using the logic

currency_rate.JPG (95.85 KiB) Viewed 6640 times

Rep currency = rep currency + conv rate for other currencies. Is there any way to populate other measure elements (like interest rate and date). I tried implementing some rules for doing the same like

Code: Select all

['Interest rate','R_Currency'] =N:  DB('Cube_Under Validation', !Time, !D_Dim, !P_Dim, !B_Dim, 'Input_currency', !C_Dim, !Index, 'Interest rate');

feeders;
['interest rate' ]  =>['R_Currency','interest rate' ] ;

Feeder in source cube
['Input_currency' ]  => DB('Cube_Under Validatiaon', !Time, 'All Ds', 'All P', 'All B', 'R_Currency', 'All C', 'All Index', 'Interest Rate');

But the cell was still showing blank. I think this is not the right way to implement it.

Suggestions are most welcome .

Thanks,
Anoop

[tomok]

I normally don't do currency conversion via a rule unless the data needs to be dynamic. If this is truly a reporting cube, not a planning and forecasting one, then you would be better served doing your conversions in TI.