Connect tables in a cube based on historical data

Question

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.

Answer 1

First your query doesn't look right, as you aren't accounting for NULL end dates. Fix that first, so that you have a flattened view of both tables.

Once, you have the query working properly, you will use it as your DSV. In the DSV editor add that query as a 'named query'. That named query will be the source for your cube.

Once the cube is created off of that view, add your QTY & UnitPrice as measures. You can use ProductNumber, ProductName, CreatedDate, and EndDate as dimension attributes, depending on what you want to see. The important part is that you build the dimension off of the same named query that the measures came from.

Here is the important part: Once you have the facts and measure setup, go to the 'Dimension Usage' tab on the cube editing window. You should only have one relationship to configure - set it to Fact.

You're done. A date dimension could be useful here, but that isn't related to your question.