I was at a client and needed to do some currency conversion for an application I was writing (BSO). In their case they had over 50 different currencies they would need to convert to USD (and only to USD). Their requirement was no maintenance to the calc scripts would be necessary if a new currency was added. The build of the currency dimension and loading of currency rates was automated in earlier versions of the project. I got to thinking about the possibilities and the first thing that came to mind was to parse out a UDA and use it in my script. But alas, there is no function to do that. One idea down.
Next, I thought about an external table where I could pass a cost center number and pull in the currency rate for it. I would need a Custom Defined Function (CDF) to do this. This was a possible solution as my cube is built from Essbase Studio and I could write a SQL query to get the currency for any cost center and link that to the currency table to pull in the rate. Sadly, the client did not want to use CDFs in their environment. Plus, I don’t have one written to do that and am unsure of the speed as I have about 20k cost centers it would have to work against.
So finally, I thought of attribute dimensions. The client is on 11.1.2.1 and I remembered seeing some new attribute functions recently, I don’t remember what version but I think it was 11.1.1.1. So I looked through the tech reference and there was what I was looking for. @AttributeSVal. The function returns the string attribute value for the selected member. So I started experimenting.
First a little background. My cube has the following dimensions
Accounts – This is a standard account structure. It contains a balance sheet and multiple versions of a P&L. Each account is tagged with the type of rate it uses for conversion as a UDA (AVG or EOM). It also has a section for statistical accounts. Included in this section buried are Average_rate, and EOM_Rate. (and a couple of others)
Periods – nothing special here, periods rolling to quarters
Years – just a list of years. Nothing special
Currency – This dimension has two parts. First are two members USD and Local. Data is loaded in as local and has to be converted to USD. The second part of this dimension contains all of the currency codes used in the system
Scenario – again nothing Actual and multiple forecast members.There are also some calculated members in this.
Organization. This is their entity structure. I won’t bore you with the structure, but the bottom level is cost center and this is where currency conversion needs to be done.
Products –again nothing special, This is a typical product hierarchy.
To this I added a new attribute dimension called Cost Center_Currency - It had the same members as are in the second part of the currency dimension (all the currency codes) but to make the member names unique, I prefixed them with the three letters “CUR” For example “USD” would be “CURUSD” and “EUR” would be “CUREUR”. I then associated this attribute dimension with the Organization dimension and wrote a quick load rule to populate the associations between cost center and the attribute dimension. I made sure if any cost center was missing an attribute member, I defaulted it to CURUSD. I figured is there was no currency conversion USD was always set to 1 anyway.No harm, no foul.
Now for the fun part, creating the calc script. I won’t bore you with the set statements or aggregations, etc, but just the currency conversion part. I’ll warn you at this point, I have changed the dimensions from the client to make it generic and am not cutting and pasting code but typing it in by hand, so there could be minor typos in it.
First a fix statement. I need to make sure I am at level zero Organization and other dimensions. I use substitution variables for the year and period I am going to calculate. I am going to replicate the script so I have different versions for Actual and forecast (I use different budget rates for forecasts). Also note if the account does not have a UDA then I assume it is not convertible and make USD equal to Local. (This is because there are some non monetary items in the account structure). Product has multiple rollups so I only grab the primary one and I only want the income statement and Balance sheet from Accounts.
Fix(&Year,&Per,Actual,@LEVMBRS(“Organization”,0),@relative(“All products”,0),@relative(“Income Statement”,0), @relative(“Balance Sheet”,0))
“USD(
IF (@ISUDA(“Accounts”,”AVG”)
“USD” = Local * @MEMBER(@SUBSTRING(@ATTRIBUTESVAL(“CostCenter_Currency”),3))->”No product”->”No Organization”->”Average_Rate”;
ElseIF (@ISUDA(“Accounts”,”EOM”)
“USD” = Local * @MEMBER(@SUBSTRING(@ATTRIBUTESVAL(“CostCenter_Currency”),3))->”No product”->”No Organization”->”EOM_Rate”;
ELSE
“USD” = “Local”;
END
)
EndFIX
So what does this do. First, I check to see what rate to use.(the IF statement). Next, I am getting the local value for each account. The
@MEMBER(@SUBSTRING(@ATTRIBUTESVAL(“CostCenter_Currency”),3))
is taking the value of the attribute dimension for the cost center we are working on and getting a substring of it starting in column 3. (remember when we created the dimension we added a prefix CUR to be beginning of the actual currencies). Once we have the currency code back, it is returned as a string, so using @member we turn it back into a member name. I then am using the cross dimension operators to define the intersections in the other dimensions where rates are stored.
The one issue I has was I forgot to tag my “No organization” member with an attribute value at first. When it got to that member, it crashed the calculation.
There you have it, a dynamic calc script to calculate currency conversion that does not need to be maintained. In my case, the dimensions were not huge so it ran pretty quick. I did find if I added dimensions to the cube, it slowed down considerably. I think the best use of this type of code would be in a business rule where you are fixing on one or a few cost centers but it could be used on bigger sets of data as well.
Posts
