Allocating Header Level FACT table Dimensions FKs into Line Level Fact

Kimball talks about allocating Degenerate Dimension into the Line Fact table.

My question is, if there are other header level dimensions, is it advisable to allocate those dimension FKs to the line?

For Example:

Order_Header_Fact
Order_Number
Order_Date_Key
Order_Status_Key
Order_Tax_Amount
Order_Shipping_Cost
....etc

should I allocate the above Dimensions to the line?

Orderline_Fact
Order_Number (DD)
Order_Date_Key
Order_Status_Key
Orderline_Number
Orderline_Create_Date_Key
Orderline_Status_Key
Orderline_Quantity
Orderline_Unit_Price
Orderline_Extended_Price
......etc

In the above example, I wanted to maintain an Order Level Fact table because there is no value in allocating the Order Level Facts into the Orderline level (i.e. Order_Tax_Amount, Order_Shipping_Cost).

Any thoughts? Thanks!

The value of allocating Order header-level facts (such as shipping amount, tax, etc.) into the Order Line fact table is that you can "slice and dice" by any attribute of any dimension related to the Order Line fact table and come up with meaningful aggregates. In addition, you eliminate the need for a "header-level" fact table... queries that join two fact tables in a parent-child relationship generally run slower than a single fact table that joins only dimensions.

As an example, with shipping allocated to the Order Line fact table, you could ask what was total shipping on all green products (assuming color was an attribute in your Product dimension.) If the shipping was stored at a higher level of grain in a separate Order Header fact table, you would not be able to query on shipping for a particular class of products.

Of course, values such as shipping are frequently stored in the order header in the source system, and getting business users to agree on rules for allocating these amounts can sometimes be problematic!

Yes, the line fact table should assume the dimensions of the header fact. But as VHF pointed out, there are advantages to not implementing a header fact at all.

I realize there are advantages to allocating the header level facts down to the line, with only 1 Fact table.

What I am struggling with is, if the only way for me to allocate the facts is to divide it equally among the number of lines, it offers no analytical value at the line level. In this case, would you still recommend allocating it to the line, but only report at the header level rollup?

Thanks.

You could allocate it. Assuming you have at least three measures (sales, tax, shipping) in the fact table, you could also create one or two dummy products (a 'tax' product, a 'shipping charge' product, or an 'other charges' product) in the product dimension to satisfy foreign key requirements and place the values on one or two rows without allocation. It all depends on how the measures will be used.

You should also review your source system. Most commercial ERP packages carry tax information at the line level because of the variety of methods taxes are calculated in different jurisdictions. You may be able to get what you need without dealing with the header.

In my Sales DW I am being pushed by the business users to store shipping as a separate row in the order line fact table, the same way it is stored in the ERP system. Personally, I would rather allocate it across line items (perhaps by extended product weight, but Ralph and Margy warned that allocation rules should be determined by the business/accounting people, not by IT.)

If there is no meaningful way to allocate tax and shipping cost across detail lines it is a tough call. Putting them in their own order line fact row as ngalemmo suggested is probably the next best choice, with a separate header-level fact table as as distant third.

Mixing grains on the fact table is never a good idea. You should either allocate the amounts or store in a different fact table.

BoxesAndLines wrote:Mixing grains on the fact table is never a good idea. You should either allocate the amounts or store in a different fact table.

I would not consider it mixing grains in this context. Just as you would look at a physical order document and see separate lines for tax and freight, there is no reason why you could not do the same in a fact table. The important issue is that these amounts be maintained in separate measures so that an erroneous query doesn't include them in sales revenue. Any resonable product dimension would contain a host of category and classification codes, so it is fairly easy populate attributes for the tax and shipping 'products' that would make them easily distinquishable from products that are sold.

The other thing is that an order header fact table is superfluous in most applications. I have never encountered a situation where I needed to create one and I cannot recall seeing one in other dimensional DW's.

ngalemmo wrote:

BoxesAndLines wrote:Mixing grains on the fact table is never a good idea. You should either allocate the amounts or store in a different fact table.

I would not consider it mixing grains in this context. Just as you would look at a physical order document and see separate lines for tax and freight, there is no reason why you could not do the same in a fact table. The important issue is that these amounts be maintained in separate measures so that an erroneous query doesn't include them in sales revenue. Any resonable product dimension would contain a host of category and classification codes, so it is fairly easy populate attributes for the tax and shipping 'products' that would make them easily distinquishable from products that are sold.

The other thing is that an order header fact table is superfluous in most applications. I have never encountered a situation where I needed to create one and I cannot recall seeing one in other dimensional DW's.

Interesting. This, to me, is clearly a mixed grain fact. It's the classic header/detail example.