Degenerate Dimension - Bridge Table

View previous topic View next topic Go down

Degenerate Dimension - Bridge Table

Post  ozisamur on Tue Nov 25, 2014 12:43 am

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.

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Degenerate Dimension - Bridge Table

Post  nick_white on Tue Nov 25, 2014 10:11 am

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?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Degenerate Dimension - Bridge Table

Post  ozisamur on Tue Nov 25, 2014 1:12 pm

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


ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Degenerate Dimension - Bridge Table

Post  ngalemmo on Tue Nov 25, 2014 1:14 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Degenerate Dimension - Bridge Table

Post  ozisamur on Wed Nov 26, 2014 12:39 am

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

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Degenerate Dimension - Bridge Table

Post  nick_white on Wed Nov 26, 2014 3:14 am

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?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Degenerate Dimension - Bridge Table

Post  ngalemmo on Wed Nov 26, 2014 3:17 am

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Degenerate Dimension - Bridge Table

Post  ozisamur on Tue Dec 23, 2014 4:20 am

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?

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Degenerate Dimension - Bridge Table

Post  ngalemmo on Tue Dec 23, 2014 4:11 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Degenerate Dimension - Bridge Table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum