Connect tables in a cube based on historical data
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 &amp; 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.