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.


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.

  • You're right about the query, forgot to set the EndDate condition to be ISNULL(p.EndDate, GETDATE()). I'll try what you've suggested and report back the results. Thanks. – Evan M. Nov 3 '11 at 14:21
  • I built the cube I described using the data you provided last night. Also, I'm sure you know this, but your data source isn't very good. A permanent solution would be to ETL this data out and create proper relationships & keys, this is kind of a quick and dirty solution. – Kenneth Nov 3 '11 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.