Change the precision of the FxRate

[JonKunert]

I am wondering if anyone has changed the precision of the FxRate?  It shouldn't be to hard.  What I am wondering is if changing it would cause other issues ?  

[Krishna]

@JonKunert  - Why you want to change it? The FX rate is typically come from external system. example FXLOADER. You can override it but the result of the currency translation will also change. 

[JonKunert]

we are trying to match what is in our gl.  We want to change the translation otherwise there would be no reason to change it.  I know it’s not a huge difference but it is what the business wants. 

[MarcusH]

I am not sure I understand exactly what the problem/question is. As far as I know FX Rates can be entered with up to 9 decimal places (at least I haven't found anything to suggest otherwise). Are you wanting to change existing FX Rates or just new/future rates? Changing the accuracy of the FX Rates when there is already translated data will obviously have an impact (probably very slight). If it's future rates then there's no problem.

[RobbSalzmann]

@Marcus is correct.  In OneStream FXRates are stored as Decimals with 9 digits of precision to the right of the decimal.

[JonKunert]

Ok, we get our rates from Bloomberg.  They only have six decimals.   So for KRWUSD, the value would be  .000745, which isn't very precise and less for other currencies.  So we use the inverse of USDKRW 1339.44 which is 0.000746580661.  Workday creates the inverse automatically and stores 12 decimal places; OS will only store 0.000746580. While the change isn't huge, the business has asked if it is possible.  The change is easy to make; what I am asking is if the change would break something else,

[RobbSalzmann]

Compounding precision errors are so much fun! 🙂 
Its not possible to get past the 9 decimal precision in OneStream.  The reason for this is the underlying tables in the repository all have numeric columns set to decimal((28,9).  This means that the number stored is up to 28 digits, 9 of which are to the right of the decimal,  I'd say this is set up this way to directly couple to the decimal primitive, .Net's most precise (to 16Bytes) numerical storage type.

OneStream appears to have standardized on 9 place precision.  The tradeoff to add more precision is losing scale, digits to the left of the decimal. 

I think the worst case here is a small loss in precision which probably wont show until you reach highly aggregated numbers.  The difference between systems given the above on 100,000,000 KRW works out to about $0.06 - 0.08 USD given the proposed intersystem precision changes.  If your reports show values in the thousands or millions, you'll never see this.

In a way, this is to be expected doing FX in more than one system.  To be fair, even the Bloomberg rate is a composite of bank inputs.  

[JonKunert]

Actually changing the number of decimals is super easy.  Using Business rules.  Create a new table with the changes. Copy data from old to new table,  rename tables, rename primary Keys.  And done.   

[RobbSalzmann]

Sounds like you've got this all figured out.  
Enjoy!

[JonKunert]

I have that part figured out,  What I dont know is if the change would would cause other issues

[JackLacava]

Modifying an internal application table doesn't sound like a supported strategy. Stuff might break in unexpected places.

The supported way would be to do custom translation, using business rules. At that point you can do anything you want, including pulling rates from custom tables. Please don't modify Onestream application tables directly, if you ever have an issue Support will not like that.

[RobbSalzmann]

What are the "old tables" ?

[JonKunert · Accepted solution]

Hi Everyone,

I wanted to update everyone. I heard back from support and Yes you can change the number of decimals.  But they discouraged the method I was going to take.  Below is their response in case anyone else needs this info in the future,  

**************OS Support Response**************

Here is the answer from one of our Consultants:

If you submit more than 9 decimals into the exchange rates will not cause any issues. You should test the 9 decimals and see if it resolves their issues.

If the  you decides to create a new table to hold more than 9 digits then you have to check all accounts that are marked as Asset/Liability/Revenue and Expenses are linked to the new table for translating.

The supported way would be to do custom translation, using business rules. At that point you can do anything you want, including pulling rates from custom tables. Please don't modify OneStream application tables directly, if you ever have an issue it will be difficult for us to Support it.

This can also affect KPI depending on the account type. Account type denotes how an account will translate using default logic.

We would still advise that default rates are uploaded, regardless of translation approach.

If you would like to further discuss, we can set a short call with our Remote Consultant.

Hope this helps.

[Krishna]

@JonKunert - Thanks for the update and Make Sense!