So this may be the wrong place, and may seem pretty basic, but I'm new to building up cubes, so please forgive me.
Imagine a setup described by the following:
CREATE TABLE [dbo].[Orders] (
OrderNumber int,
OrderDate smalldatetime,
ProductNumber int,
UnitPrice money,
Qty int
)
CREATE TABLE [dbo].[Products] (
ProductNumber int,
ProductName varchar(30),
CreatedDate smalldatetime,
EndDate smalldatetime NULL
)
INSERT INTO Products VALUES (1,'My Product','1/1/2011','5/5/2011')
INSERT INTO Products VALUES (1,'My New Product','5/6/2011',NULL)
INSERT INTO Orders VALUES (1,'1/3/2011',1,1)
INSERT INTO Orders VALUES (2,'6/6/2011',1,1)
SELECT * FROM Orders o INNER JOIN Products p ON o.ProductNumber = p.ProductNumber AND o.OrderDate BETWEEN p.CreatedDate and p.EndDate
As you can see, my product numbers get reused and I need to be able to see what the product was at the time the order was placed. Easy enough to do with SQL, but I'm having trouble trying to build a cube in SSAS that takes it into consideration.
Any help would be appreciated.