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.