As most of you know, when loading in OneStream, data is always loaded in the Local currency of the entity. Standard functionality is available in the flow dimension to enable data loads and input in different currencies (for example, alternative input currency). The flow dimension contains specific Member properties that affect the input currency and translation. Application examples of these settings include a treasury process where bank balances are imported in the bank currency (which is different from the entity's local currency) and then translated into the entity's currency. All of this uses standard functionality in OneStream. See the OneStream manual for more information: OneStream - Flow Dimension. This blog is intended to highlight some of the lesser known standard functionality in OneStream and how to leverage it.
Flow Processing Type
The Flow Processing setting determines how amounts are handled. The first two options(Is Alternate Input Currency and Is Alternate Input Currency for all Accounts) are used for the input/import side of the configuration.
Note that 'Input View for Adjustments' must be set to 'True' in the Account dimension.
The last two options(Translate Using Alternative Input Currency, Input Local and Translate Using Alternative Input Currency, Derive Local) are used to translate amounts entered in an alternative input currency into a converted value.
- Translate Using Alternate Input Currency, Input Local: Setting to translate a specific input currency.
- Translate Using Alternate Input Currency, Derive Local: Setting to translate a specific input currency. The member for the alternate input currency must be defined. The input amount is translated into the entity's currency.
Steps for a bank account imported in bank currency and translated into local currency
Account dimension
- BankAccounts: Set the flow currency to the bank currency (for example, USD) and set 'Use Alternate Input Currency in Flow' to 'True'.
Flow dimension
- Flow member by bank currency (e.g. USD_BC, EUR_BC):
Note: Flow Processing Type = Is Alternate Input Currency.
Parent of these members = 'Bank Currency'.
- Flow member by Local currency (e.g. USD_LC, EUR_LC):
Note: Flow Processing Type = Translate Using Alternate Input Currency, Derive Local. The source member for Alternate Input Currency is the XXX_BC member (e.g. USD_BC for the USD_LC member).
Parent of these members = 'TranslatedAmounts'. Only this parent will aggregate into the upper Flow member.
- Create an alternate hierarchy(Currency Constraints) for the account constraints by currency. For each currency, both XXX_BC and XXX_LC members are placed under a parent (for example, USD parent, with USD_BC and USD_LC as children).
Result
The screenshot below shows a USD bank account translated to the local entity currency (EUR) using a (fictitious) exchange rate. The 'USD' in the input cell provides visual help for the end user(ShowCurrency = True in the CellFormat).
The above configuration allows you to import/enter amounts in a currency other than the entity currency. Based on the exchange rates in the system, the amounts are translated by the OneStream engine into the entity's local currency. This configuration is simplified, and it is up to you how you set up your cube and data model.
Have questions or need help with a similar configuration? Feel free to get in touch!
.webp)