In many situations for multi-national firms, it's necessary provide a method for converting foreign currencies into a single reporting currency based upon exchange-rate information. We will discuss methods on how to configure Kepion to handle these currency exchange conversions.
Step 1. Create a Model to store exchange-rate information.
Step 2. Create a view in the database that translates from one currency to another. Please ensure the view has the same structure as the partition table in the target Model. In this case, the original data is in Local Currency. When converting, we store the converted value in Report Currency. Here is an example snippet:
/****** Object: View [dbo].[USR_Profit_FX_Current] ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER VIEW [dbo].[USR_Profit_FX_Current]
ROW_NUMBER() OVER(ORDER BY
,(SELECT TOP 1 MemberID FROM [D_Currency Type] WHERE MemberLabel = N'RC') [Currency TypeID]
-- Require Conversion? Yes if local currency != reporting currency and it is a debit, credit account
WHEN DE.[Local Currency] <> DE.[Reporting Currency] AND ADC.MemberID IN (1,2) THEN F.[Value] / FE.[Value]
FROM dbo.[USR_Profit_OPEX_Current] (nolock) WHERE [Currency TypeID] = 1
FROM dbo.[USR_Profit_HR_Current] (nolock) WHERE [Currency TypeID] = 1
FROM dbo.[F_Profit_CoreMG_Revenue Source] (nolock) WHERE [Currency TypeID] = 1
INNER JOIN D_Account DA (nolock)
ON DA.MemberID = F.AccountID
INNER JOIN AccountLogic_AccountType AT (nolock)
ON DA.AccountType = AT.Label
INNER JOIN AccountLogic_DebitCredit ADC (nolock)
ON AT.DebitCreditMemberId = ADC.MemberID
INNER JOIN D_Time DT (nolock)
ON DT.[MemberId] = F.TimeId
INNER JOIN D_Entity DE (nolock)
ON F.EntityID = DE.MemberID
INNER JOIN D_Currency SC (nolock)
ON DE.[Local Currency] = SC.MemberLabel
INNER JOIN D_Currency DC (nolock)
ON DE.[Reporting Currency] = DC.MemberLabel
INNER JOIN dbo.[F_Exchange Rate_CoreMG_Writeback] FE (nolock)
F.ScenarioID = FE.ScenarioID
AND SC.MemberID = FE.[Target CurrencyID]
AND DC.MemberID = FE.[CurrencyID]
AND F.TimeID = FE.TimeID
Step 3. Create a Partition in the Model we want to apply the currency exchange to.
Step 4. In the Partition tab of the partition we created in Step 3, change the data source to the database view name we created in Step 2.
After this is configured, you should be able to see the currency-converted values come through.