Header/Detail Both should be two facts?

View previous topic View next topic Go down

Header/Detail Both should be two facts?

Post  ozisamur on Wed Nov 05, 2014 3:25 pm

I have famous Order and Order Line tables. The suggested method is that allocate the fields in the header table to detail table. In the OLTP system in the ORDER table there are some information like this:

Customer_ID
Invoice_Number
Cargo_ID
OrderStatus_ID
Currency_ID
Store_ID
OrderShipmentStatus_ID
etc...

In the Order_Line table ( in the OLTP ) :

Order_ID
Product_ID
Quantity
Price
Tax

etc.

Should I just create one fact table ( OrderLine ) and then allocate the all fields ( Store_ID, Customer_ID, OrderShipmentStatus_ID, OrderStatus_ID, Currency_ID, InvoiceNumber etc. ) in the OLTP ORDER table into the OrderLine Fact table?

What if I create two fact tables ? One for ORDER and the other one for ORDERLINE?
In the ORDER fact I put the customer, store, order status information.
In the ORDERLINE fact I put the customer, product, itemtype etc. information ( specific the item )

What do you think about it?

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Header/Detail Both should be two facts?

Post  ngalemmo on Thu Nov 06, 2014 5:52 pm

In a star schema, fact tables are independent of all other fact tables by design. You should not intentionally create dependencies. The generally accepted approach is to create one table at the detail level.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Header/Detail Both should be two facts?

Post  zoom on Fri Nov 14, 2014 2:37 pm

Based on your sales volume, a query performance would be slow if you have one large number of detail trans and you do SUM to show summary. It is better to create two fact tables… one to show summary on the order and another to show order detail.
You also need total amount payment in your Order Fact table.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Header/Detail Both should be two facts?

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