Degenerate Dimension - Bridge Table

Hi guys,

I have an ORDERITEM fact table and ORDER_ID is the degenerate dimension in here. In some situations ORDER_ID can be included in more than one SHIPPING COMPANY. This is not item based so there is a many to many problem. For example if a product more than one category I can solve this issue by bridge table by using this technique: Connect itemfact table to the product dimension via product_sk then connect product dimension with bridge table. Then bridge table also connected to the category dimension. But in my real problem, there is no ORDER dimension to perform bridge table. What do you dou in this situation? Can I connect the OrderItem fact to the Order_ShippingCompanies bridge table directly ? ( Behave orderId as a dimension like product sk )

Thanks.

The grain of your fact table is Order Item and presumably an Order Item can only be shipped by one Company so why don't you have the reference to the Shipping Company Dim in your Fact table?
Or have I misunderstood the issue?

No, you misunderstood me.
There is no such information in source system. I have information which order has been shipped by which shipping company.

Normally fulfillment/shipping is handled by another fact table with the order ID, line & item as dimensions.

I think I could not understand the situation.

I allocated the some of the header attributes into the detail table as typical order and order line example. So the fact table has number of records as order_item transaction table has. In this situation as you predicted ORDER_ID column is duplicated because multiple ORDER_ITEM_ID may belong to same ORDER_ID. But some ORDER_ID has one shipping company, some ORDER_ID has two. So think that I have this information :

ORDER_ID | SHIPPING_COMPANY_ID
1 | 1
1 | 2
2 | 1
3 | 1
3 | 2
3 | 3

How can I model this situation? This similar to bridge table but exactly not.

ozisamur wrote:
I have information which order has been shipped by which shipping company.

But if you know which Company shipped an Order then you know which Company shipped the Order Items, don't you?

Are you saying the order is in a single shipment, but it may be handled by multiple carriers en-route to the destination (such as truck/rail/boat)? Or is an order sent in multiple shipments, possibly by different carriers?

Yes ngalemmo. An order has multiple carriers. It is enough to show to the customer which orders handled by which carriers. Like that:

ORDER_ID | CARRIER_ID
1 | 1
1 | 2
1 | 3
2 | 2
3 | 2
3 | 3

As I said, ORDER_ID column is DEGENERATE DIMENSION in ORDER_ITEM fact table. There is no dimension which holds the ORDER_ID column. If I built the Bridge table like ORDER_CARRIERS_LK which holds ORDER_ID and CARRIER_ID like above, this table should be connected to the ORDER_ITEM fact table directly due to Order_Id not belong to any dimension table. Am I correct?

I would treat the carrier routing as a separate fact. At issues is what do you do with that information? Normally, this type of information is to perform analysis on the carrier: are costs in line, did the carrier deliver on time …things like that. It is fairly uncommon that the detailed content of the order is needed when looking at carrier performance.