Currency Conversion 2.0

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Currency Conversion 2.0

    Hello,

    I'm trying to create some currency conversions using the rules in 2.0.

    I have followed the examples in the guide and am able to do a fixed conversion (where I hard code the currency code I want to translate from (e.g. EUR) into the rule definition for the USD equivalent).

    However, when I try to embed the palo.data function to call the currency of the country by referring to the attribute cube for the region dimension, I get #NULL returned.

    Any ideas as to what the problem might be? I've broken in down into the various steps, and it seems like it's just the last bit (looking up the currency code) that is causing problems.

    Thanks in advance.

    Alex

    P.S. I've attached the rule definitions and can upload other pieces as needed.
    Files
    • rules2.txt

      (489 Byte, downloaded 629 times, last: )
  • RE: Currency Conversion 2.0

    Hi Alex,
    first of all I would check what currency code is returned by adding a text elem and populate it with the currency code that by rule:

    ['testtext']=palo.data("Revenue_Budget","#_Regions","Country Budget Currency",!'Regions')

    Then check, if you have an exchange rate for that currency in your cube Budget Currencies.

    I did some currency calculation with demo2 similar to the way you implemented it. Exrates are filled there for july 2007 and the rule is like:

    ['EUR'] = N:['localcurr'] * palo.data("demo_palo2","CURRENCY",
    palo.data("demo_palo2","#_opstmt_entity","local currency",!'opstmt_entity'),
    !'opstmt_year',!'opstmt_month')

    Maybe this helps.

    Greetings from Cologne

    Holger
  • RE: Currency Conversion 2.0

    Hi Holger,

    Thanks for your suggestions.

    I built the test element and it does return the correct code currency (EUR) for the country. I have been using your rules guide example as a model.

    I modified slightly from your base example:

    As this is an annual budget, i only have year and currency_code in my currency cube - is this ok, or do i need to do it monthly?

    Also, I didn't create a Currency dimension in the main cube, as i only want to covert some measures (e.g., value/price, not quantities). Instead, I created additional elements in the Measures dimenions (e.g,. Unit Price (LOC) and Unit Price (USD) and want to convert the Unit Price (LOC) to Unit Price (USD) based on the currency associated with the country. then i can build up Revenue in USD.

    Could it be an issue with element types (string/numeric) for the link between cubes?

    Thanks for your insights,

    Alex
  • RE: Currency Conversion 2.0

    Hi,

    what does only
    ['test']=palo.data("Revenue_Budget","Budget Currencies",!'FY',palo.data("Revenue_Budget","#_Regions","Country Budget Currency",!'Regions') return?

    Is FY also a Dimension of the cube with test element? If not, thats the problem. You must provide a valid "FY"-Element for the Budget Currencies-Cube, so may be you have to provide the corresponding year dimension name of the target cube with leading "!".

    Holger
  • RE: Currency Conversion 2.0

    Hi Holger,

    Yes, FY dimension is in both the "Revenue Budget" cube and the "Budget Currency" Cube. (Assumed this was necessary to make the "join" between cubes.)

    I've tried to break it down into parts (some pictures attached):

    I can pull the Country Budget Currency based on the country:
    ['Test 2'] = palo.data("Revenue_Budget","#_Regions","Country Budget Currency",!'Regions') >>> returns EUR

    I can pull the FX rate stored in the currency cube for EUR:
    ['Test 3'] = palo.data("Revenue_Budget","Budget Currencies",!'FY',"EUR" >>> returns 1.41 (value in cube for EUR FY'09)

    But when I try to nest the first function in the second

    ['Test 4'] = palo.data("Revenue_Budget","Budget Currencies",!'FY',palo.data("Revenue_Budget","#_Regions","Country Budget Currency",!'Regions'))
    >>> returns #NULL!

    Attribute element is text (EUR) but element in currency cube is numeric (the element is called EUR, but the value is 1.41)

    Seems like this should work but something seems to be missing ... !@@!$##

    Thanks for any other ideas or things I might try to test.

    Best regards,

    Alex
    Files
  • RE: Currency Conversion 2.0

    Let me have a look at it. Don't know if I got time today. I will try to answer as soon as possible.

    >> Attribute element is text (EUR) but element in currency cube is numeric (the element is called EUR, but the value is 1.41)

    Thats okay, due to element type concerns the value you want to save.

    Holger
  • RE: Currency Conversion 2.0

    Okay, I had a first look.

    What happens if you hardcode Currency ISO in Rule for Test4?

    ['Test 4'] = palo.data("Revenue_Budget","Budget Currencies",!'FY',"EUR")

    instead of

    ['Test 4'] = palo.data("Revenue_Budget","Budget Currencies",!'FY',palo.data("Revenue_Budget","#_Regions","Country Budget Currency",!'Regions'))


    Holger
  • RE: Currency Conversion 2.0

    Hi Holger,

    Yes, that works fine.

    When I hard code "EUR" it returns the correct exchange rate (Test 3 in my earlier note)

    And when I do the attribute lookup, it returns the correct currency code (Test 2).

    However, when I try to create the combined rule (test 4), and embed the currency lookup from Test 2 inside of the exchange rate call from Ttest 3, that's when I get the #Null! error.

    Test 2 and Test 3 work fine independently (see screen shot attached), but for some reason, not together. Strange. All of the syntax looks okay, the components work, but when combined, it doesn't work.

    Am I embedding the lookup in test 4 properly? I just replaced <"EUR"> with the palo.data function.

    Thanks,

    Alex
  • RE: Currency Conversion 2.0

    Hi Holger,

    I tested again with a new metric (test 5) based on your last note (similar to my existing test 3.

    In both cases, when I hard-code "EUR", it returns the right value.

    The problem seems to be in the embedded reference, when I try to return the EUR value from the palo.data function. What's strange is that the palo.data function on its own (Test 2) works fine (it returns "EUR"). It's only when I nest it inside Test 4 (like in the example in the guidelines), that I get the null problem.

    Would be most greatful for any other advice. Really would like to know if I'm doing something wrong or if it's a bug of some sort (in which case I can start working on a plan b ... :)

    Thanks in advance,

    Alex
  • RE: Currency Conversion 2.0

    No hidden spaces, but your suggestion gave me another idea:

    I create a new currency code "1" and the associated rules. I assigned Country "NL" to currency code 1.

    Now, Test 4 (the rule with the nested palodata function) still returns NULL for the EUR currency countries, but NL (which as currency code "1" Assigned) returns blank. So it does seem like there is some impact due to either the element name is text or numeric??

    Unfortunately, although it returns blank (which is better than NULL) it still doesn't return the FX rate (which is what we want).

    Any idea what it could mean that it returns BLANK and not NULL? I hope this is some sort of hint that might help us find the explanation.

    Thanks,

    Alex
  • RE: Currency Conversion 2.0

    Did you try to delete all other rules to avoid any sideeffects?
    Is it possible that you send me an extract of your DB without any data but with rules? I checked the EUR Rule for palodemo2 one more time and it works fine and it is pretty the same architecture than yours so I really don't understand. It must be a very, very small thing that we simply don't recognize. #@!$%"!!! :)

    Holger
  • RE: Currency Conversion 2.0

    Ok - will send it to you later today.

    Also tested again with your demo2 DB and even created a new, very simply db - both seem to work fine. so i agree it must be some little thing somewhere causing a conflict.

    thinking maybe something to do with consolidated elements? or perhaps conflicting rules as you suggest.

    will try to get you the extract later today.

    thanks again for your help and persistence on this issue. the rules are just a fantastic feature (one of the best parts of 2.0 imho) and I'd really like to get this working - would be great to have a multi-currency budgeting tool in place in a couple of days (vs. commercial apps with their extensive development and deployment times ...)

    thanks,

    alex
  • RE: Currency Conversion 2.0

    Okay. I will have a look at it.
    One thing I recognized (but its more a feature than a bug) is that #null is returned on consolidated level if one of the children returns #null. this hapens e.g. in demo-db when you delete on attribute entry "local currency" in opstmt_entity dimension.

    Holger
  • Hi Holger,

    Attached is the db (i just sent the whole thing, as it has very little data at the moment - hadn't started to populate until I resolved the currency issue).

    Let me know if this is sufficient or if I can send anything else.

    I am going to start layering things on in my new test db to see if it breaks and if so at what point. hopefully it will work okay (although it would be nice to understand what caused the issue).

    the only wrinkle (as you'll see in the db attached) is that I created two sets of metrics, one called Unit Price and one called ASP. The idea was to separate the "data entry" metric from the "reporting" metric (i.e., unit price is only really used to enter prices, whereas ASP is used for reporting). maybe this is causing confusion in the rules engine, since they both measure similar things (in theory, this should work, though).

    Please let me know if you see anything in the db and I'll let you know how the testing goes in the new db.

    Thanks,

    Alex
    Files
  • Hi Holger,

    Ok - in the simple test db things work. I only have five dimensions:

    Currency Codes, FY, Regions, Products, Measures

    and two cubes: Currency Rates (Currency Codes and FY) and Budget (FY, Regions, Products, Measures)

    When I add in the other dimensions (the ones I want in production), I start to get NULL values ...

    Currency Codes, FY, Regions, Products, Measures, Months, Datatypes, Entities, Channels

    But I get null even when I'm at the base level of analysis (so the aggregation issue you described earlier shouldn't apply?)

    Does this give you any further ideas? I'll keep testing (one additional dimension at a time) to try to find breaking point.

    Thanks,

    alex
  • Sorry, I did not have much time to check your data model. But got one more idea. heck if you have any duplicate element names in your dimension (same elementname in more than one dimension). And you don't need the atributes for your dimension "currency_codes", because elementnames are the same somehow.

    Holger
  • I got it!!!
    Seems to be an error in PALO Excel AddIn. Open a view to edit your Currency Attributes in #_Regions and e.g. have a look at Region "BE". EUR appears but what is saved when you reenter "EUR" is "EUR ". Edit it by doubleclick on EUR cell, then you can see (and delete) the trailing space. Then everythings works fine. Puh, that was hard to find.

    Holger
  • Hi Holger,

    Sorry for not replying sooner - was traveling end of last week.

    Wow - don't think I would ever have found the error. Thanks.

    Does this mean I need to manually edit every entry by clicking on the value? Or is there a way to update in bulk somehow (about 80 countries or so and two currency codes per country (one for the country's official currency and one for the "budget currency").

    Can you add this issue to the bug log? Do you know if it only affects attribute cubes or other things, too? Just want to try to understand whether it might pop up elsewhere.

    Thanks again for your help and persistence in tracking this one down.

    Best,

    Alex