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.
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
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.
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.
Yes getting YTD and periodic report with currency converted.
I had a really tough time to get this all working through MDX but it's way complicated and time consuming while generating reports.
My Question is "What possible ways this can be achieved if someone from here has already implemented a similar kind of approach)
Well the problem is that there are around 20 dimensions and the level 0 data in ASO cube till date itself is around 3 GB (this is 6 months data) and it is being sourced from 10 different source systems. We have tried with a BSO concept but it is too heavy even for that calculation. Even for 1 month calculation in BSO was taking time.
i suggest you export all your data from ASO and import back there should be some diff in size and then compact your ASO outline using maxl or right clik on ASO outline
then using report script export data and import in BSo (which will be bit hard work ) unless if u can load in BSo again
In BSO maintain only 1 month data which help to calc faster and using jexport or transperent partition can send data to ASo
through this setup would be bit of hardwork for couple of days but in long run its simple and easy way
by the way when u say 20 dimension really? but to calc do u need all 20? you can reduce them in BSO cube for calc
i hope this helps