Order Line Fact

Hello,

Is there a good reason to create two separate facts Order Line and Order? Order Line fact is Transactional information and Order is like a summary table (without the Product Dim), is that correct? I have an issue that I have a Dimension of Approvals that is at both an product level and order level. So I am wondering if I need two separate facts because of this?

You don't need an order level fact table (usually). Does not the approval level of an order apply to the lines of the order? Why wouldn't order approval be a dimension (or attributes in a dimension) off of the order line? If there are independent line level statuses as well, you would simply have two FKs off the line, one for order status the other for line status.

Well, that's what I thought that the order approval applies to the order line. But individual items may require approval by way of approval reasons. Then if the order itself contains any items that needed approval then the entire order needs to be approved... Order approval is a dimension off of the order line...but it refers to the line item. I need the one for order as well.

So if I understand what you are saying. Load two FKs from the same Dimension...one in reference to the Item (ItemApproval_Skey) and the other to the Order (OrderApproval_skey). Correct?

Sorry it helps if I repeat it. ;o)

Yes.

If the skeys are part of the primary key then they can't be null right?

No. It is not a good idea to have null FK's in a fact table. It messes up queries. Instead, have the key point to a 'not applicable' dimension row.

Ok great. Thanks