Hi Jose

When you need to expand the grain of a table it's usually best to implement it as a custom view in the DSA and map the fields from there to the DW.

Best regards,
Andri Heidberg

Hi José

Have you read my guide regarding this?
https://legacysupport.timextender.com/hc/en-us/articles/115005972746-Improve-execution-times-by-splitting-exchange-rates-out-on-dates

Anyway I have done the same for that exact table based on experience I got from that guide.

First I have this stored procedure

CREATE PROCEDURE DSA]..spPayRates]
@iBEntity int = NULL,
@iMaxBEntity int = NULL
AS
BEGIN
CREATE TABLE #TempTable
(
>BusinessEntityID] int,
>RateChangeDate] datetime,
>RateChangeDateEnd] datetime,
>Rate] decimal(38,4),
>PayFrequency] int
)
WHILE(@iBEntity <= @iMaxBEntity)
BEGIN
INSERT INTO #TempTable
(
>BusinessEntityID],
>RateChangeDate],
>RateChangeDateEnd],
>Rate],
>PayFrequency]
)
SELECT BusinessEntityID],
>RateChangeDate],
ISNULL(LEAD((RateChangeDate]) OVER (ORDER BY BusinessEntityID]),(SELECT MAX((DateValue]) FROM DSA]..Date])) AS RateChangeDateEnd],
>Rate],
>PayFrequency]
FROM HR]..EmployeePayHistory]
WHERE BusinessEntityID] = @iBEntity

SET @iBEntity = @iBEntity + 1
END
SELECT * FROM #TempTable
DROP TABLE #TempTable
END

Secondly I have a Custom Table Insert in a table.

DECLARE @1 as INT = (SELECT MIN((BusinessEntityID]) FROM HR]..EmployeePayHistory])
DECLARE @2 as INT = (SELECT MAX((BusinessEntityID]) FROM HR]..EmployeePayHistory])
DECLARE @Temp TABLE
(
>BusinessEntityID] int,
>RateChangeDate] datetime,
>RateChangeDateEnd] datetime,
>Rate] decimal(38,4),
>PayFrequency] int
)
INSERT @Temp EXEC DSA]..spPayRates]
@iBEntity = @1,
@iMaxBEntity = @2
SELECT BusinessEntityID],
>DateValue] AS Date],
CASE WHEN PayFrequency] = 1 THEN Rate]/30.4375 ELSE Rate]/14 END AS Rate],
>PayFrequency]
FROM @Temp
LEFT JOIN DSA]..Date] AS D ON RateChangeDate] <= D..DateValue] AND RateChangeDateEnd] > D..DateValue]

This 2 part script is based on the fact that you don't always gets a straight array of dates and if I did not do this it would mix them up.

Thank you Thomas and Andri!