The year is 2038; following the event known as The Global Souffle Incident, on 15 July the entire world population woke up speaking nothing but French. Mon dieu!
OneStream gurus the world over were swiftly summoned to add French support to systems and applications. Pas de problème, you said, and gingerly added a new culture to the configuration file, restarted IIS, set your own culture to French, and started translating all your metadata descriptions to the flowing tongue of Hugo and Prevert.
All looked marveilleux, until you launched a routine consolidation and... the numbers looked wrong. You launched another one, and you got formula errors. Pas possible! You didn't touch any business rule! What could have gone wrong...?
This sort of issues is, unfortunately, not limited to contrived post-apocalyptic scenarios. The Culture, assigned to users in Security settings, will affect how business rules are run in a number of contexts. Good Consultants™ should strive to write their code in a robust way that can survive any type of culture switch. What does that require?
We won't cover all the related topics here, but the main problem is often to format numbers in a culture-neutral way. For example, as you probably know, conventions differ across cultures on separator characters to mark thousands and decimals. This is not a problem as long as you work with numerical .Net objects (Decimal, Double, etc), but rears its ugly head as soon as you convert them to Strings; and if you drop those Strings in Calculate formulas, things might take a bad turn. How can we avoid this? In classic OneStream tradition, there are a few different ways to do that.
I'll start with the one I personally find the cleanest, at least when working with formulas. Too many people don't know that we can actually define Formula Variables to be used in calculations. There are a few examples out there (including in the excellent OneStream Financial Rules and Calculations Handbook) of leveraging those variables for DataBuffers, but we can have variables of different types too, including numeric ones. Using variables, we completely bypass String conversion or rounding, and ensure the calculation will always run correctly. Voici un exemple:
Dim myValue as new Decimal(10.5)
api.Data.FormulaVariables.setDecimalVariable("someFactor", myValue)
api.Data.Calculate("A#Target = A#Source * $someFactor")
Decimal objects happen to be what DataCell.CellAmount also returns, and it's the best representation of financial data in .Net environments. Some people might use Double objects instead, which are lower-level but prone to rounding surprises; unless you know the ins and outs of CPU architectures, you should probably stay away from those... but if you really have to, there is an equivalent .setDoubleVariable() method.
This approach has one drawback: it only works for formulas (i.e. with api.Data.Calculate, .GetDataBufferUsingFormula, or .GetDataCell). What about contexts that absolutely cannot avoid a string conversion...?
In practice, those circumstances are less common than you might think. Numbers getting into OneStream can typically be handled with the options available in DataSource configuration and Forms. Internally, OneStream will always reason in terms of Invariant Culture, an abstract culture (roughly similar to US English), which is what will be used in configuration properties or exported XML files. In some circumstances, it will try to convert that into user's culture formats when exporting numbers. Keeping that in mind should be enough to handle corner cases - although the overall recommendation is always to try and avoid converting any number to a String.
The seasoned integrator at this point would probably shout "Mon frère, you forget les SQL queries!" - and he'd be wrong! We'll cover SQL in another post, because it deserves its own space.
As mentioned earlier, there will probably be other areas of your application that you'll need to examine carefully when supporting multiple cultures, typically in Business Rules peppering your Dashboards, integrations, and Marketplace solutions; but by following the tips above, at least your imported and calculated numbers should always look good.
And that's all, folks! Or rather, c'est tout. Until next time, au revoir!
