Background

Copying an exchange rate from one period into the next sounds like a small piece of configuration. It stops being small once it has to run every close, unattended, for some currencies and not others. Copying the rate manually is the obvious answer, but finding an automated way to do it is the real problem.

What creates the need is lag reporting: When a subsidiary’s balance sheet date differs from the parent’s, it translates at the rate in effect on its own date — so a November-reporting entity consolidated into a December close carries November’s ending and average rates. FCCS, by default, translates using whatever rate sits at the period the data was loaded into. Three constraints shape the design:

1. The rates come from the prior period — the exchange rate copy has consider the rates from the prior period and copy it to the current period, which makes the period mapping the centre of the design.
2. Lag and non-lag entities share currencies — two entities reporting in EUR need different EUR rates in the same period, which is impossible while they share one “From Currency” member and the need to create a EUR_Lag currency arises.
3. The copy runs every close — the integration itself has to automatically execute every month, with no manual export-and-reimport step and no rate keyed in by hand.

The solution

One Quick Mode integration in Data Exchange covers all three, with the FCCS application as both source and target, the Rates cube on both sides of the integration, and the transformation living entirely in the dimension mapping:

View            constant("FCCS_Periodic")
Period          map(PERIOD,Jan:Feb|Feb:Mar|Mar:Apr|Apr:May|May:Jun|Jun:Jul|
                Jul:Aug|Aug:Sep|Sep:Oct|Oct:Nov|Nov:Dec|Dec:Jan)
From Currency   map(FROMCURRENCY,From_EUR:From_EUR_LAG|From_GBP:From_GBP_LAG)
Year            if (PERIOD=="Dec" and YEAR=="FY24") return "FY25"
                else if (PERIOD=="Jan" and YEAR=="FY25") return "FY25"
                else if (PERIOD=="Feb" and YEAR=="FY25") return "FY25"
                else if (PERIOD=="Mar" and YEAR=="FY25") return "FY25"
                else if (PERIOD=="Apr" and YEAR=="FY25") return "FY25"
                else if (PERIOD=="May" and YEAR=="FY25") return "FY25"
                else if (PERIOD=="Jun" and YEAR=="FY25") return "FY25"
                else if (PERIOD=="Jul" and YEAR=="FY25") return "FY25"
                else if (PERIOD=="Aug" and YEAR=="FY25") return "FY25"
                else if (PERIOD=="Sep" and YEAR=="FY25") return "FY25"
                else if (PERIOD=="Oct" and YEAR=="FY25") return "FY25"
                else if (PERIOD=="Nov" and YEAR=="FY25") return "FY25"

Expressions are assigned per dimension from Map Dimensions, using the options icon beside the dimension and then Edit Target Expression. The data load rule filter is set to FCCS_Global Assumptions, the Actual scenario, the ending and average rate accounts, and view as FCCS_Periodic.

Why the Year Mapping Reads the Period Column

Shifting the period handles eleven of the twelve months without incident. December is what decides the design, and only one of the three approaches I considered holds up there:

• Copying Year across unchanged holds from January through November and breaks in December, where December’s rates have to be applied in January of the following fiscal year. The period moves forward and the year stays behind, so the rates land in the wrong year.
• A second, December-only integration returns the right answer but doubles the objects to maintain, and running once a year means it gets far less exercise than the monthly one
• A Conditional expression on Year that reads the source Period column covers all twelve months in one place. Where the source period is December it returns the following fiscal year, and otherwise it returns the source year.

Details worth getting right

• A dedicated lag currency member lets one period hold two rates for the same currency and keeps the lag population visible in metadata; only the pairs listed in the “From Currency” map get copied, so the export mode has to be Merge — Replace clears the rest
• The Year expression has an expiry date The if else statement above covers one December plus the eleven months after it, so the next December matches nothing and those rows load with no target year until an administrator changes the mapping
• Compute Rates has to be rerun after the load, since an integration load doesn’t execute the post-process rate rules and indirect and cross rates hold their old values until it does — a Pipeline keeps the two together

Takeaways

A target expression is not confined to the dimension it writes – One expression can populate Year while reading Period, so a coordinate shift that looks like it needs a second integration often does not

Every hardcoded value is a future task – The Year expression needs updating once a year which can be a reasonable tradeoff for running a single integration rather than two, provided that update is documented as a close activity with a named owner

Couple of assumptions to call out – Both sides of the integration have to be registered against the Rates cube. And the lag is assumed to apply to both the ending and the average rate; if only the closing rate is lagged, drop the average rate account from the data load rule filter