FromCurrency Lookupfield CurrencyCode from table COMPANYINFO (dataareaid = dataareaid) – ToCurrency (VARCHAR(3)) Date (DATETIME) Rate (Numeric 4) Create a Script Action as below and add it as a Pre Script to data cleansing on the ExchangeRates table. Make sure you have a Time table called Time or modify the script accordingly. Also make sure the time table is above the ExchangeRates table in the tree INSERT INTO [dbo].[ExchangeRates_R] ( [DataAreaId] ,[FromCurrency] ,[ToCurrency] ,[Date] ,[Rate] ,[DW_SourceCode] ) select [DataAreaId] ,'MST' ,b.currencycode ,a.datevalue ,b.exchrate ,'FillScript' from dbo.Time_V a left join dbo.ExchRates_V b on a.datevalue > b.Fromdate and a.datevalue <= b.todate where b.currencycode is not null Create a table called ReportingCurrencies containing the following fields: CurrencyCode (VARCHAR(3)) Name Lookupfield TXT from Table Currency (CurrencyCode = CurrencyCode) – Add Custom Data to the ReportingCurrencies table for the currencies you want to use for reporting. Make sure you are using the exact same currency code as those in the ExchangeRates table from the data source. Create the following dimensions on the OLAP side: Reporting Currency: Key: ReportingCurrencies.CurrencyCode Name: ReportingCurrencies.Name Transaction Currency: Key: Currency.CurrencyCode Name: Currency.TXT Create a cube for transactions containing your transactions and the table ExchangeRates as fact-tables Setup dimension relations Transactions fact-table: Company.DataAreaID Time.Date Transaction Currency.CurrencyCode ExchangeRates fact-table:
FromCurrency – Lookupfield CurrencyCode from table COMPANYINFO (dataareaid = dataareaid) ToCurrency (VARCHAR(3)) Date (DATETIME) Rate (Numeric 4) Create a Script Action as below and add it as a Pre Script to data cleansing on the ExchangeRates table. Make sure you have a Time table called Time or modify the script accordingly. Also make sure the time table is above the ExchangeRates table in the tree INSERT INTO [dbo].[ExchangeRates_R] ( [DataAreaId] ,[FromCurrency] ,[ToCurrency] ,[Date] ,[Rate] ,[DW_SourceCode] ) select [DataAreaId] ,'MST' ,b.currencycode ,a.datevalue ,b.exchrate ,'FillScript' from dbo.Time_V a left join dbo.ExchRates_V b on a.datevalue > b.Fromdate and a.datevalue <= b.todate where b.currencycode is not null Create a table called ReportingCurrencies containing the following fields: CurrencyCode (VARCHAR(3)) Name – Lookupfield TXT from Table Currency (CurrencyCode = CurrencyCode) Add Custom Data to the ReportingCurrencies table for the currencies you want to use for reporting. Make sure you are using the exact same currency code as those in the ExchangeRates table from the data source. Create the following dimensions on the OLAP side: Reporting Currency: Key: ReportingCurrencies.CurrencyCode Name: ReportingCurrencies.Name Transaction Currency: Key: Currency.CurrencyCode Name: Currency.TXT Create a cube for transactions containing your transactions and the table ExchangeRates as fact-tables Setup dimension relations Transactions fact-table: Company.DataAreaID Time.Date Transaction Currency.CurrencyCode ExchangeRates fact-table: