4 Replies Latest reply: Jul 26, 2010 3:27 PM by Ethan Jewett RSS

Currency Conversion Simulation

Rashmi G
Currently Being Moderated

Hi BPC Experts,

 

We are using BPC 7.5NW version.

 

My project requirement is as given below -

1.  When sales data gets loaded, it would do a Currency Conversion (CC) in reporting currency GBP.

2.  When CC is executed for ACTUAL category data, the ACTUAL data should get converted to GBP with different rates.

     Ex.  CURRENT rate and simultaneously, it should aslo consider the BUDGET rate AND Year End Rate etc. and would generate those many records in the backend.

 

To achieve this we have created a user defined dimension say RATECATEGORY, which has RATE_PERIOD, RATE_CATEGORY, RATGE_YEAR properties.  however, the CC doesn't work for all the RATECATEGORY types (like Budget Rate, Year End Rate etc)

 

How can we use user defined exchage Rates in CC logic?

 

Thanks & Regards,

Rashmi

  • Re: Currency Conversion Simulation
    Ethan Jewett
    Currently Being Moderated

    Hi Rashmi,

     

    Usually I think we would use the CATEGORY dimension to manage different budget vs. actual rates, for example. On the other hand, to manage using year-end vs. average rate, we would usually use the RATETYPE property of the account dimension, since usually a particular account will use either the average rate or the year-end rate but not both.

     

    If I'm not understanding what you are asking, then could you provide an example showing all the different conversions you would like to take place?

     

    Thanks,

    Ethan

  • Re: Currency Conversion Simulation
    Sheldon Edelstein
    Currently Being Moderated

    Hi Rashmi -

     

    As Ethan points out the rate selection is controlled by RATETYPE property in the ACCOUNT dimension.  You could set up some kind of COPY process that moves values from one account to another, with each account having a different RATETYPE property ...but the process would create a lot of duplicated records.  On the plus side you woud still be able to use the delivered currency translation process so it may be something you might want to consider.

     

    A better recommendation would be to crate a custom BADI that is passed a user selection for the Ratetype to be used.  

     

    Regards,

     

    Sheldon

    • Re: Currency Conversion Simulation
      Rashmi G
      Currently Being Moderated

      Hi,

       

      Let me try to explain the business scenario.  This is working fine in the existing lower version (4.2) of BPC.  However the same thing is not supported in 7.5NW.

       

      Scenario -

      When we load the ACTUAL data, we need to simulate the sales amount coming in Local currency (LC) using different rates.

      The Rates that I would like to use would be say -

      Current Rate - which would be of the current rate of the week that I am processing the data  (Rate for EUR to GBP = 1.0)

      Constant Rate - Here I would give RATE_PERIOD = 1, RATE_YEAR = 2010, RATE_CATEGORY = ACTUAL (Rate for EUR to GBP = 1.1)

      Year End Rate - Here I would give RATE_PERIOD = 52, RATE_YEAR = 2009, RATE_CATEGORY = ACTUAL (Rate for EUR to GBP = 1.2)

      To acheive the same we have created a user defined dimension say Rate_Type which has RATE_PERIOD, RATE_YEAR and RATE_CATEGORY

       

       

      Now when we load the weekly data for the week # 18 (W18) for ACTUAL category with Amount as 1000 in LC, we need to get following data in the cube

       

      Week #  Rate Type          Category Type   Currency   Amount

      W18       Current Rate      ACTUAL             LC              1000.00

      W18       Current Rate      ACTUAL             GBP           1000.00

      W18       Constant Rate    ACTUAL             GBP           1100.00

      W18      Year End Rate    ACTUAL             GBP            1200.00

       

      Let me know if I am missing anything for currency simulation in 7.5NW version

       

      Regards,

      Rashmi

      • Re: Currency Conversion Simulation
        Ethan Jewett
        Currently Being Moderated

        Hi Rashmi,

         

        Ok, I am getting a little bit better understanding of what you are trying to accomplish here. Thanks for the further explanation. I'm still not quite sure why you want to do this (or exactly how it was implemented in 4.2), so there may be a simpler way to accomplish your overall goal. That said, I've been meaning to write some thoughts down about the possibilities for doing this type of currency conversion, so this is as good a chance as any. Hopefully this is helpful...

         

        Here are some thoughts and options on the topic in BPC NW 7.0/7.5 that might help you. These are in addition to Sheldon's BADI suggestion and are based on my experience (including the recommendations of what to avoid ;-). Others will probably have other opinions. Depending on your performance requirements and your more specific business requirements, the BADI approach may really be your best option. That said, some other options:

         

        1. Using multiple categories: Using multiple categories for your different rate conversions is probably the most conventional way to address this problem. I recommend considering it, or going for the BADI approach that Sheldon suggests. In your case you might have three categories: ACTUAL (Current Rate), ACTUAL_CON (Constant Rate), and ACTUAL_YE (Year End Rate). Note that you would have to copy your LC values from the ACTUAL category into each of the other two categories before running currency conversion. Because the BPC NW system can deal with far higher overall data volumes in the cube than the MS version could, this duplication of data is not an enormous problem, though it is an issue.

         

        2. Using multiple reporting currencies: For example GBP, GBP_CON, GBP_YE. This is an option I would definitely avoid. In your example it will work because you are only converting from EUR -> GBP, but as soon as you throw another local currency into the mix this will become unmanageable. This is because BPC assumes that your conversion rates within a Rate/Category/Entity/Time combination are commutative, but in the case of mixing Year End and Current rates (for example) this is not necessarily the case.

         

        Now the fancier stuff:

         

        3. Using multiple rate entities: Instead of using multiple categories, you could store your rates under multiple rate entities in the RATE cube. Then you could run your currency conversion 3 times, specifying a different RATEENTITY parameter each time. This will overwrite the results of the previous currency conversion each time, so between runs with the different RATEENTITY parameters you would need to copy the results of the conversion into the proper "Rate Type" dimension member in your application. You could build this into your currency conversion script logic. This approach should work (though I haven't implemented exactly this approach, so prototype rather than trusting my word , and it avoids the data duplication that comes along with multiple categories.

         

        4. Using multiple currency conversion business rules with different "Destination Flow"s specified: You could set up a "flow"/subtable dimension in your financial application and specify three currency conversion business rules for each RATETYPE/FXTYPE combination, each of which uses a different rate from the RATE application and each of which has a different "Destination Flow" parameter in the rule table. This will result in your currency conversion being run three times with three different rates for each account, and the results would be written into three different flow dimension members. Of course, using this option means that you can't use the flow dimension for the purpose it was designed, so go forward with this option carefully. The same caveat applies here about me not having actually implemented this particular currency conversion approach, so proceed carefully for that reason as well.

         

        Hopefully that gives you some ideas.

         

        Ethan

Actions