I want to view historic data for any given day in a more performing/faster way.
I have the following dummy tables and dummy data:
CREATE TABLE dbo.DateDim (
[Date] date,
DayNumber int
)
-- insert dummy values
INSERT INTO dbo.DateDim (
[Date],
[DayNumber]
)
VALUES ('2020-01-01',1),
('2020-01-02',2),
('2020-01-03',3),
('2020-01-04',4),
('2020-01-05',5)
-- create productHistory table
CREATE TABLE dbo.ProductHistory (
ProductId int,
Price int,
[Status] int,
ValidFrom datetime,
ValidTo datetime
)
-- insert dummy values
INSERT INTO dbo.ProductHistory (
ProductId,
Price,
[Status],
ValidFrom,
ValidTo
)
VALUES (
1,230,2,'2020-01-01 00:00:00:00','2020-01-02 08:10:30:00'),(1,250,3,'2020-01-02 08:11:00:00','2020-01-03 11:10:31:00'),
(1,235,2,'2020-01-03 11:12:34:00','2020-01-04 11:12:34:00'),(1,285,4,'2020-01-04 11:14:36:00','9999-01-05 00:00:00:00'
)
Using the dbo.DateDim and the dbo.ProductHistory table, I want to see how data looks like for any given Day in a table in PowerBI.
To achieve this, I did the following:
-- my current logic
SELECT D.*, P.*
FROM dbo.DateDim AS D
INNER JOIN dbo.ProductHistory AS P
ON D.Date >= P.ValidFrom and D.Date <= P.ValidTo
WHERE D.Date >= P.ValidFrom
AND D.Date <= P.ValidTo
The issue is that I have 100k unique rows in my data set with many columns. Generating a simple view in the way that I currently did gets me 100k (unique records in my table)*365 (days in a year)*7 (how many years I currently go back) = 255,5 million rows. The view takes too long to generate.
My question therefore: How do I accomplish this in a performing/fast way? As mentioned, the end goal is to click on a date filter in PowerBI, which allows you to see how the data looked like for any given day. I am open for other methods than a simple view.