6 Replies Latest reply: Dec 30, 2013 1:52 AM by Vasavya Chowdary RSS

PVA type currency calculation in ASO

user10132385 Journeyer
Currently Being Moderated

Hi All

 

Was just wondering, If anyone has done PVA (Periodic Value Method) type currency conversion in ASO which is similar to what is done in HFM?

 

Loading the YTD data in to essbase sourced from GL and have to do the Periodic conversion, currency conversion (PVA method for Flow Accounts). I am not getting a clue of how should i start.

Approach 1

1. I am considering having an analytic dimension, load the data to YTD and use MDX for periodic and QTD conversion.

2. User a separate scenario to do the currency conversion. In this scenario, I have to write a CASE statement for each month (periodic) to do the PVA type conversion for PL accounts and it is becoming really complicated. I had to override currency conversion by having a higher solve order for DTS. This has increased my retrievals time

3. Along with that came the Historical rate conversion for few equity accounts to calculate the CTA. This added another complication where I have a common parent in the Accounts hierarchy where there is A1 to be translated at Hist rate and A2 to be translated at current rate and adding this to formula made retrievals even slow

 

I was wondering If i can do the currency conversion in currency dimension but not sure how will get benefited from this

 

Approach 2

 

Use procedural calcs to do the DTS, currency conversion, CTA calculation and just maintain stored accounts and stored scenarios. This means I have to do lot of calculations and I have around 19 dimensions and I had issues earlier where it reached the limit of member combinations.

(OR)

do only currency conversion using procedural calcs but this might be of not much help as it would have to do the periodic calculation in ASO for every level zero combination and would take again and AFAIK in ASO we cannot calculate where the source is dynamic.

 

I am stuck with only option to have a BSO cube to calculate at level0, export and load in ASO (OR) have a staging environment where it calculates everything and load it to ASO.

 

 

Does anyone had a similar kind of implementation done / provide any alternative approach or suggestions which can help to take it further as I am out of ideas now.

 

Regards

Amarnath

ORACLE | Essbase

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points