Header/Detail Both should be two facts?
3 posters
Page 1 of 1
Header/Detail Both should be two facts?
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?
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
Re: Header/Detail Both should be two facts?
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.
Header/Detail Both should be two facts?
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.
You also need total amount payment in your Order Fact table.
zoom- Posts : 97
Join date : 2010-08-23
Location : Chicago
Similar topics
» Header/Detail Fact Problem
» Header/Detail Fact Granularity
» data model for 2 fact tables (Header / Detail scenario)
» Data Modelling -- linking Header and Detail Fact Tables.
» the master detail tables facts
» Header/Detail Fact Granularity
» data model for 2 fact tables (Header / Detail scenario)
» Data Modelling -- linking Header and Detail Fact Tables.
» the master detail tables facts
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum