Header/Detail Fact Problem

View previous topic View next topic Go down

Header/Detail Fact Problem

Post  ozisamur on Mon Oct 27, 2014 2:29 pm

Hi guys,

In OLTP I have 2 fact tables : ORDERs and ORDER_LINEs .
ORDER_TABLE
----------------

ORDERIDORDER_DATECUSTOMER_SKSTORE_SKORDER_SHIPMENT_TYPE_SK
12014-10-10 01:00:00123
22014-10-10 01:00:00233
32014-10-10 02:00:00332

For an ORDER there are multiple ORDER_LINE. I don't draw a table for order line. It has multiple product items for each order.

In dimensional modeling I can push the some of the ORDER attributes to the ORDER_LINE fact ( customer_key, transaction_date ). And my orderItems fact now have this fields:

ORDER_ID(DD), CUSTOMER_SK, TRANSACTION_DATE, PRODUCT_SK, PRICE etc.

But I have ORDER_SHIPMENT_TYPE in the header table ( ORDERS ). And it specifies the shipment status of that order ( not order item level ). If the ORDER_SHIPMENT_TYPE is 1, it means that it  has given to the shipper, if it is 2, the parcel is on its way, if it is 3 the parcel has been delivered. And so this status changing should be tracked ( something like scd ) ( accumulating snapshot not fit because the # of steps is not certain )

So my question is, where should I put the ORDER_SHIPMENT_TYPE key in my dimensional modeling when I have ORDER and ORDER_ITEM tables in my OLTP environment?

Thanks.

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  BoxesAndLines on Mon Oct 27, 2014 3:40 pm

Put it in your Order Shipment Type transaction fact table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  ozisamur on Mon Oct 27, 2014 5:05 pm

BoxesAndLines wrote:Put it in your Order Shipment Type transaction fact table.

Well, how should I connect with my OrderItem (FACT) and the OrderShipmentType (FACT) ? What dimension should be used as conformed?

Thanks.

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  BoxesAndLines on Mon Oct 27, 2014 10:47 pm

Order ID seems like a great candidate.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  ozisamur on Tue Oct 28, 2014 12:45 am

Great, but as a best practice I should not link the 2 fact tables ( ORDER_STATUS_FACT and ORDER_ITEM_FACT ). There should be a conformed dimension.
And also I should not create ORDERS table as a conformed dimension to link those 2 fact tables. Becase as a best practice header table (ORDERs) should not be treated as a dimension.
I am in hesitance. Should I create ORDERs dimension table to link two fact tables ?

Thanks.

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  nick_white on Tue Oct 28, 2014 3:35 am

The Order Id is a Degenerate Dimension and so joining Facts using it doesn't break the best practice of joining facts by Conformed Dims.

However, I understand the point you are making and my understanding of the theory is as follows (others may be able to give a better explanation):

The rule about joining via conformed dimensions applies to what I would call analytical queries i.e. high-level summaries of the data your star schemas contain. Where the resultset you want contains data from multiple facts, you query each fact (and its related dims) independently and then join the resultsets from each query using the conformed dims. You don't join all the tables together in a single query.
However, where you have multiple facts, usually in the same fact table, that all relate to the same "parent" record and you need to know which relate to the same parent record, then it is OK to use an ID, such as Order ID, to link them. You would use the ID to query just this table to get the resultset you need and then, if necessary, join this resultset to the results of querying other facts using conformed dims.

Hope this helps

nick_white

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

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  ozisamur on Tue Oct 28, 2014 5:15 am

As far as I understand If I have an ORDER_ITEM fact ( ORDER_ID, ORDER_LINE_ID, ORDER_DATE, STORE_ID etc. ), and ORDER_SHIPMENT_STATUS fact ( ORDER_ID, ORDER_SHIPMENT_STATUS_TYPE_ID, SHIPMENT_STATUS_CHANGE_DATE ), I can join both two tables ( ORDER_ITEM and ORDER_SHIPMENT_STATUS ) via ORDER_ID column.

Is that correct?
Thanks for all help.

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  ngalemmo on Tue Oct 28, 2014 11:16 am

You can join on an aggregate of the two tables. Fact tables are assumed to have a M:M relation between them. Two facts are aggregated then joined on the common dimensions(or attributes). The aggregation eliminates the M:M relationship.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Header/Detail Fact Problem

Post  ozisamur on Wed Oct 29, 2014 1:20 pm

In ORDER_ITEMS_FACT fact table, ORDER_ID column is duplicated, because it is allocated from header into the detail table. Consider this data:

ORDER_ID|ORDER_ITEM_ID|PRODUCT_ID|CUSTOMER_ID ...
1 | 1 | 2 | 5
1 | 2 | 3 | 5
1 | 3 | 4 | 5

Same ORDER_ID with many different order items, products.

When I am looking for the status of an order, I get the ORDER_ID value from ORDER_ITEMS_FACT fact table and then I go to the ORDER_STATUS_FACT table. But because of ORDER_ID is duplicated in the ORDER_ITEMS table I would get duplicated rows. Should I run 2 different query ( by using distinct order_id ) then merge together in BI tool ?


ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  BoxesAndLines on Wed Oct 29, 2014 2:45 pm

I would create a type 1 Order Status dimension that contains the current status for your order items fact.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  ozisamur on Wed Oct 29, 2014 4:30 pm

I will store the current status of an order in the ORDER_ITEM_FACT like this:

ORDER_ID | ORDER_ITEM_ID | PRODUCT_ID | CUSTOMER_ID | ORDER_STATUS_ID
1 | 1 | 2 | 3 | 2
1 | 2 | 3 | 3 | 2
1 | 3 | 4 | 3 | 2
2 | 4 | 2 | 2 | 4
2 | 5 | 3 | 2 | 4
2 | 6 | 3 | 2 | 4


And history is stored in another fact table ORDER_STATUS_FACT like this:

ORDER_ID | ORDER_STATUS_ID | START_DATE | END_DATE | IS_CURRENT
1 | 1 | 2014-10-10 | 2014-10-15 | N
1 | 2 | 2014-10-15 | 2014-10-19 | N
1 | 3 | 2014-10-19 | 9999-12-31 | Y


What do you think about it ? ORDER_STATUS_FACT table behaves like scd 2 dimension table.

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  ngalemmo on Wed Oct 29, 2014 5:04 pm

I guess I'm a bit confused about the notion of an order status in an analytic database. From my experience with distributors and manufacturers, an order isn't an order until it is accepted and released. Prior to that it is considered an indication or quote. The fulfillment (shipping) and invoicing of an order are separate, independent business processes that are usually handled by separate fact tables with references (degenerate dimensions) back to the order line. Potentially you can have 4 subject areas: indications (not common), orders, fulfillment and invoicing. If necessary, you can create an aggregate from these to provide an end-to-end picture.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Header/Detail Fact Problem

Post  BoxesAndLines on Wed Oct 29, 2014 5:06 pm

Don't like it. Just build the type 1 status dimension. It will only have as many rows as there are distinct statuses. It will answer the majority of your questions.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  BoxesAndLines on Wed Oct 29, 2014 5:29 pm

ngalemmo wrote:I guess I'm a bit confused about the notion of an order status in an analytic database. ...

The telecoms track it as all one process. Submitted, accepted, processing, provisioning, etc. I used an accumulating snapshot to report stage, lags, fallout, how many completed, etc. It was populated from the Order status fact. So I think the OP is almost on the right track.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  ozisamur on Thu Oct 30, 2014 12:34 am

BoxesAndLines wrote:Don't like it.  Just build the type 1 status dimension.  It will only have as many rows as there are distinct statuses.  It will answer the majority of your questions.  

I could not understand exactly what you said. What is the structure of table do you suggest and how can I join it with Order_Items_Fact table? By the way OrderId is duplicated in that fact table.

Thx

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

Post  BoxesAndLines on Thu Oct 30, 2014 12:22 pm

Create an Order Status dimension. This table has all distinct order statuses. Add this table to both fact tables.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Header/Detail Fact Problem

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