When we released version 17.5.1 we also introduced a new method of generating lookups.

Some of the old features of lookups was deprecated. All was explained in the release notes of that. Release notes 17.5

Some experienced that a lookup using a currency exchange rate got slow.

The reason for this can be a >= is used to get the exchange rate, or if using subselect and partition by. All these will be slower than a simple group by.

What we suggest instead is to create a view that splits out the currency exchange rate table into days.

Split exchange rates out pr day

There is two types of views you can create. It depends on what fields your exchange rate tables contains.

In both cases you will need a date dimension. Since you most likely will use this in the DSA/Stage db, so start by creating a Date dimension.

The exchange rate table contains both start and stop date

Add a new custom view and add the following script.

CREATE VIEW [DSA].[CurrencyExchangeRate] AS

SELECT (1/([EXCHANGERATE]/100)) AS [EXCHANGERATE], 
	D.[DateValue] AS [Date],
	[FROMCURRENCYCODE],
	[TOCURRENCYCODE],
	[EXCHANGERATETYPE]
FROM [EXCHANGERATE_V] AS E
LEFT JOIN [Date_V] AS D ON E.[VALIDFROM] <= D.[DateValue] AND E.[VALIDTO] >= D.[DateValue]

Remember that using parameters makes it better at coping with changes in names and so forth.

The data source table contains a exchange rate, a validfrom date, a validto date, a fromcurrency and a tocurrency. This is merged with the date table with a from less than and equal to date and a to greater than and equal to.

The next step is to use the view instead of the std exchangereate table. The join should look like this.

The date is now equal instead of larger than / smaller than.

This is how the lookup script looks in the code.

-- Copy data for the conditional lookup field 'EXCHANGERATE' (Algorithm: One lookup, Group by)

; WITH CTE AS
(
	SELECT
		MAX(S.[EXCHANGERATE]) AS [~Value]
		, S.[TOCURRENCYCODE]
		, S.[FROMCURRENCYCODE]
		, S.[Date]
	FROM [DSA].[AX CurrencyExchangeRate] S
	GROUP BY
		S.[TOCURRENCYCODE]
		, S.[FROMCURRENCYCODE]
		, S.[Date]
)
UPDATE R
SET
	R.[EXCHANGERATE] = CTE.[~Value]
FROM [DSA].[SALESLINE_R] R
INNER JOIN [DSA].[SALESLINE_T] T ON
	T.[DW_Id] = R.[DW_Id]
INNER JOIN CTE ON
	CTE.[TOCURRENCYCODE] = T.[ACCOUNTINGCURRENCY]
	AND CTE.[FROMCURRENCYCODE] = T.[CURRENCYCODE]
	AND CTE.[Date] = T.[CONFIRMEDDLV]

Previously it would have looked like this.

-- Copy data for the conditional lookup field 'EXCHANGERATE' (Algorithm: One lookup, Cross apply)

UPDATE R
SET
	R.[EXCHANGERATE] = S1.[~Value]
FROM [DSA].[SALESLINE_R] R
INNER JOIN [DSA].[SALESLINE_T] T ON
	T.[DW_Id] = R.[DW_Id]
CROSS APPLY
(
	SELECT
		MAX(S.[EXCHANGERATE]) AS [~Value]
	FROM [DSA].[EXCHANGERATE] S
	WHERE
		S.[FROMCURRENCYCODE] = T.[CURRENCYCODE]
		AND S.[TOCURRENCYCODE] = T.[ACCOUNTINGCURRENCY]
		AND S.[VALIDFROM] <= T.[CONFIRMEDDLV]
		AND S.[VALIDTO] >= T.[CONFIRMEDDLV]
	HAVING COUNT(1) > 0
) S1

So previously it chose the Cross Apply method. With the date equal date change it chooses Group By and this performs better.

The exchange rate only contains a start or stop date

The difference when you only have a To Date, or a Start Date, is that we need to get the date that the next row in the table has. We use the LAG if it is Start and LEAD if it is End. It also should be mentioned that those features are a SQL2012 feature, so if your server is older this link will help you.

Below is the script you can use if you have a to date. I use the CTE to create a table that contains a FromDate and a ToDate. The rest is similar to before.

CREATE VIEW [DSA].[CurrencyExchangeRates] AS

WITH CTE AS
(SELECT 
	[CurrencyCode],
	[ExchangeRate],
	LAG([ToDate]) OVER (ORDER BY [CurrencyCode]) AS [FromDate],
	[ToDate]
FROM [ExchangeRates_V])

SELECT [CurrencyCode],
	[ExchangeRate],
	[DateValue] AS [Date] 
FROM CTE
LEFT JOIN [Date_V] AS D ON [FromDate] <= D.[DateValue] AND [ToDate] > D.[DateValue]

And here is how the script looks if you only have a Start date. It is pretty much the same, except i use LEAD instead of LAG

CREATE VIEW [DSA].[Currencies] AS

WITH CTE AS
(SELECT DW_Account, 
	[Currency Code],
	[Exchange Rate Amount], 
	[Starting Date],
	LEAD(E.[Starting Date]) OVER (ORDER BY E.[Currency Code]) [End Date]
FROM [Currency Exchange Rate] E)

SELECT DW_Account, 
	[Currency Code],
	[Exchange Rate Amount],
	[DateValue] as [Currency Date]
FROM CTE
LEFT JOIN [Date] AS D ON [Starting Date] <= D.[DateValue] AND [End Date] > D.[DateValue]

Also if you want to be 100% sure there is no null values, you might need to set up some conditions, so you will get a ultimate start and stop date if no one is specified.

The rest is the same as previously explained.