Bridge Table and Degenerate Dimensions

[rayishome]

This is the scenario I'm trying to solve for:


In a standard Sales Transaction Fact table we store the OrderNumber as a Degenerate Dimension. Picture a Grocery store that creates a display of products, these products will never be sold but are tracked as assets. So there is a Product Display Dimension and Display Facts. The store has decided to track the products that being used in the display against the Order billing sytem so there are facts in the Sales Transaction Fact table for each product on Display. The issue when the displays are put together they are made up of multiple OrderNumbers. With a standard dimension I would create a bridge table and create a Many to Many relationship, but not sure if I should do this with a Degenarate Dimension or not?

Thank you

[ngalemmo]

I don't understand what you are referring to. What 'order number'? Do you mean the purchase order or the sale. You mention grocery and I have never seen a grocer that is able to tie sales with purchase orders. It is real difficult for manufacturers to do it (it requires lot tracking or serial numbers) and would be nearly impossible given the way groceries are stocked.

If you are pulling goods off the shelf to make a display, why would their be multiple orders? Even if there were, why would you care? If it is important, why isn't there a system in place to record the goods used in a single transaction? Like a worksheet someone signs off on after the display has been built?

[rayishome]

Thank you for replying.

I am trying to highlight the challenge without providing specific proprietary details.
With that said, the scenario is similar to the Shopping Basket analysis discussed in the Toolkit, but different. We have Displays, think of them as Promotions via signs in stores etc. We have orders that end up in an Order Transaction Fact table. The Order ID is then a degenerate dimension. However, we now have need to store this order number in several location and join data together using this value due to the use of Displays. In addition to the scenario mentioned earlier where the displays are actually made up of multiple orders we also have need of, for example, a Bridge / Factless Fact table that links the Display's to Orders that contained the same products and or similar products that were on the display. So now the Order ID is no longer just taking on a Degenerate dimension role, but almost a full fledged Dimension and the question I have, is if given linkage between facts on the order number should we consider having a proper order dimension (separate table surrogate keys etc)

[Sponsored content]