11 Replies Latest reply: Oct 1, 2013 3:56 PM by DanPressman RSS

    FX translation in ASO cube

    MickeyGreen

      I've heard that it is possible to perform dynamic currency translations via mdx member formulas in an ASO cube, but so far I haven't been able to get much of anything to work.

       

      I have a cube that contains among others, a Projects dimension, a Country dimension and a Currency Dimension, as well as attribute dimensions for Project Currency and Country Currency.

       

      Every Country is tagged with a Country Currency attribute in the form EUR, GBP, JPY, etc.

      Every Project is tagged with a Project Currency attribute in the form EUR (P) , GBP (P), JPY (P), etc.

      The exchange rates are stored in the Currency dimension with member names in the form 1EUR, 1GBP, 1JPY, etc.

       

      We have a Base Currency member in the Currency dimension where all raw data is loaded, and to complicate matters, revenue values are stated in the project currency and cost values are stated in the country currency.

       

      I'm trying to add a "US Dollar" member to the Currency dimension that dynamically converts revenues according to the project currency attribute and costs according the country currency attribute, using the corresponding exchange rates.

       

      Any ideas?

        • 1. Re: FX translation in ASO cube
          DanPressman

          The Best solution is to add a local currency column for each local currency.  Since this is ASO it will not hurt that only one column is used for each entity (ASO is relatively insensitive to dimensional irrelevancy - only the length of the bitmap will be changed and maybe even that will not grow).

           

          Then Add columns for each of the reporting currencies you want and a formula that multiplies each  localCurrency) * ConversionRate and adds them up - Only one should have non-zero value for both the rate and the value.  If you have rates that remain constant for the year you can do this after calculating YTD (Which you will do with stored dimensions I hope) and your cube will be fast.

           

          If your rate changes monthly you can not used a stored dimension YTD - unless you do the multiplication before you load the data and simply load to both local currency and converted currency.

          • 2. Re: FX translation in ASO cube
            DanPressman

            On rereading I perhaps did not make one point clear.  When discussing currency conversion you can do all of your roll-ups before the multiplication (as long as they are all of the same rate) so you do not have to convert to USD before you rollup accounts or divisions etc.

            • 3. Re: FX translation in ASO cube
              MickeyGreen

              When you say add a local currency column for each local currency, are you actually talking about a "dimension" for every local currency?

              • 4. Re: FX translation in ASO cube
                DanPressman

                Sorry - No - what I meant was create a currency dimension as follows:

                 

                Currency

                    Local

                         curr1_loc   Data will be loaded to these

                         curr2_loc

                         ....  more currencies other than Euro and USD

                         Euro_loc

                         USD_Loc

                   Converted

                         USD

                            curr1_loc_Converted_to_USD   Formula on these

                            curr2_loc_Converted_to_USD   Formula on these

                            ....  more currencies other than Euro and USD

                            Euro_loc_Converted_to_USD   Formula on these

                            USD_Loc     No formula needed here <shared>

                         Euro

                            curr1_loc_Converted_to_Euro   Formula on these

                            curr2_loc_Converted_to_Euro   Formula on these

                            ....  more currencies other than Euro and USD

                            Euro_loc     No formula needed here <shared>

                            USD_Loc_Converted_to_Euro   Formula on these

                 

                Add more converted currencies as needed.

                • 5. Re: FX translation in ASO cube
                  Avneet

                  Hi MickeyGreen,

                   

                  Assuming all your data is stored in base currency(Currency Dimension) of each country, and you might have FX_Rates stored at some combination which will be used for Currency conversion. If that is the case you just have to make your currency dimension as Dynamic Hierarchy and you have to put MDX formula in Currency Dimension

                   

                   

                  EX:

                  Currency

                       Base /* Input/Calculated Data for all countries */

                       USD /* Based on Attr condition  USD = Base * FX_rates */

                   

                  Hope this helps !!!

                   

                  Avneet

                  • 6. Re: FX translation in ASO cube
                    MickeyGreen

                    Thanks DanP,

                     

                    One more question relating to the approach you've outlined: What is the most efficient "placement" of the exchange rates themselves in the outline for both performance on the formulas as well as the proper aggregation of converted values in non-zero level members?

                    • 7. Re: FX translation in ASO cube
                      MickeyGreen

                      Thanks again DanP,

                       

                      I finally go this method working, and it's pretty fast too, but I just encountered another bump in the road...

                       

                      The USD translated values are all zero when I try to use attributes in retrieves, but I can't find any way to manipulate the solve order for attribute dimensions and members.

                       

                      Any ideas?

                      • 8. Re: FX translation in ASO cube
                        MickeyGreen

                        From previous post:  The USD translated values are all zero when I try to use attributes in retrieves, but I can't find any way to manipulate the solve order for attribute dimensions and members.

                         

                        Based on my testing and what I've been able to find online, it appears that this is a limitation (at least in 11.1.1.3) in ASO functionality and I'll have to satisfy needs for these aggregations via alternate hierarchies. Not ideal, but at least it gets the job done.

                        • 9. Re: FX translation in ASO cube
                          DanPressman

                          Attribute dimensions are alternates to their base dimensions.  They are therefore stored hierarchies and all stored hierarchies are computed at the same time.  You have to use the same technique you used for the primary dimension.  I do not know what you are doing but if done right it should work without issue.  What does the solution you have look like?

                          • 10. Re: FX translation in ASO cube
                            MickeyGreen

                            It took quite a bit of trial and error testing on the solve orders to get the translated values to aggregate correctly in the standard dimensions. But like you said, attribute dimensions are stored, and therefore have a fixed solve order value of zero. The attribute retrieves work fine for the loaded local currency values, but I can't even get a single, zero-level USD translated value to retrieve properly with the attribute selected.

                            • 11. Re: FX translation in ASO cube
                              DanPressman

                              The problem was (after looking at the otl you sent to me) is that you changed the YTD "Stack" to dynamic instead of stored.

                               

                              But what dimension were you concerned about the attributes for?