mdxiccube

Is there a healthy way to bridge a link between a fact-dimension, where their relationship is only partial?


I'm trying to get a Dimension that only has an incomplete relationship to the measure in question. That if we had two tables A and B with their #'d columns, the set from column A.1 would have column members not present in B.1's set.

In an example scenario, we have three tables:

Items: Supplied by sql view: T_Items

Item ID Item Normal Price
1 Apple $1.99
2 Banana $2.99
3 Grapes $3.99
4 Ham $4.99
5 Beef $5.99

Items on Sale: Supplied by sql view: T_Items_On_Sale

Item on Sale ID Item ID Sale Price Sale Period
1 1 $1.00 1/1 - 1/10
2 1 $1.00 1/21 - 1/31
3 2 $2.00 1/11 - 1/20
4 3 $3.00 1/21 - 1/30
5 2 $1.00 1/1 - 1/10

Sales: Supplied by sql view: T_Sales

Transaction ID Item ID Date Price Quantity
1 1 1/2 1.00 2
2 4 1/23 4.99 1
3 3 1/22 3.00 2
4 1 1/15 1.99 1
5 2 1/4 1.00 1
6 5 1/17 5.99 1

From that above data, I'm trying to arrange something like the following:

Dimension: Key Column: Supplying Datatable:
Items_on_sale Item on Sale ID T_Items_On_Sale
Measure: Supplying Datatable:
Sales T_Sales

Where I make a bridge definitions like the following:

Fact Join Column: Bridge Table: Join Column: Join Column (Dimension table):
Sales.ItemId T_Items_On_Sale T_Items_On_Sale.ItemId Items_on_Sale.ItemId

Items_on_sale's dimension entries would like to relate to the Sales measure based on the Item ID.

In my real scenario, the measure I'm trying to relate to this partially relevant dimension will end up leaving off every single incomplete record, and drastically change the outcome. Is there a healthier way to link such dimensions to these measures?

In my own testing, I've tried:


Solution

  • Thanks for thoroughly explaining the problem. As you point out, bridge tables filter the fact table for only the IDs that are in the bridge table (like an SQL inner join).

    To link the T_Items_On_Sale dimension and keep all information from the fact table, you could either: