Skip to main content

Improve execution times by splitting data out on individual dates


Thomas Lind
Community Manager
Forum|alt.badge.img+5

At times your result returns data based on a date range, it could be a start and stop date or just one of each.

When using a lookup to fetch data from these tables your join rule would have to be 

StartDate <= Date and EndDate >= Date

This is not an effective lookup and it will make the execution of this table slow.

This guide will show how to make this fast by using the Date table to fill out the dates between each so the join will be 

Date = Date

Split exchange rates out on individual days

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

This is based on a AX 2012 R2 data source. So to use that you add a new custom view and the following script.

CREATE VIEW [BU].[CurrencyExchangeRate] AS

SELECT [Exchange Rate Type], 
	(1/([EXCHANGERATE]/100)) AS [EXCHANGERATE], 
	D.[DateValue] AS [Date], 
	[EXCHANGERATECURRENCYPAIR], 
	[From Currency], 
	[To Currency] 
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 from currency and a to currency. This is Joined with the date table with a fromdate less than and equal to date and a todate greater than and equal to date.

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.

The exchange rate only contains a 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 [TMP].[vCurrencyExchangeRates] AS

WITH CTE AS(
SELECT [CurrencyCode], 
	ISNULL(DATEADD(DAY, 1, LAG(ToDate, 1, NULL) OVER (PARTITION BY CurrencyCode ORDER BY ToDate)),DATEADD(yy, DATEDIFF(yy, 0, [ToDate]), 0)) AS [FromDate],
	[ToDate], 
	ISNULL([ExchangeRate],1) AS [ExchangeRate] 
FROM [TMP].[ExchangeRates]
)

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

I apply it like so. 

To avoid it continuing when another currency is hit, I use the Partition By part in the LAG function. Then it will only add the previous date for the current currency and not use it for the next one.

The exchange rate only contains a start date

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.

Here is the custom view I made

CREATE VIEW [DWH].[CurrencyExchangeRates] AS

WITH CTE AS
(SELECT [DW_Account], 
	[Currency Code],
	ISNULL([Exchange Rate Amount],0) AS [ExchangeRate], 
	[Relational Currency Code],
	ISNULL([Relational Exch. Rate Amount],0) AS [RelationalExchangeRate], 
	[Starting Date], 
	ISNULL(DATEADD(DAY, -1, LEAD([Starting Date]) OVER (PARTITION BY [DW_Account], [Currency Code] ORDER BY [Starting Date])),GETDATE()) AS [Ending Date],
	[IncrementalTimeStamp]
FROM [DWH].[Currency Exchange Rate])

SELECT DW_Account, 
	[Currency Code],
	[ExchangeRate],
	[Relational Currency Code],
	[RelationalExchangeRate],
	[DateValue] as [Currency Date],
	[IncrementalTimeStamp]
FROM CTE
LEFT JOIN [DWH].[Date] AS D ON [Starting Date] <= D.[DateValue] AND [Ending Date] >= D.[DateValue]

This is essentially the same as when you only have the to date. First get the start and stop dates and then use that to get the dates in-between. Additionally I also use the Partition By to make sure it stops adding the next date when the currency or account changes. In that case it would return a null value, so I put it into a IsNull() function that returns the current date if it is so.

Did this topic help you find an answer to your question?

0 replies

Be the first to reply!

Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings