Order header amount fields

[DBArch]

Hi,

We're in the process of designing an Orders Data Mart and I have a basic question on what to do with order-header type information such as Misc. Charges and Freight Charges.

Do these belong in a separate 'order dimension' along with Order number, Order Date, etc.; or, should this information be placed in the Order fact table? Or??

I'm concerned that that these amounts may be summed up at the line item level by users / report developers by mistake if the design is wrong.

Thanks in advance!

 

[kishleo]

what is the grain of your fact table, if your grain is the lowest i mean if it is at item level, you can add Misc. Charges and Freight Charges to the fact table,
you no need to include Misc. Charges and Freight Charges in the dimension as it may give you problem if you want to use the same order dimension accross the fact tables. i dont think so your client has so much to analyse based on Misc. Charges and Freight Charges accross the facts, so i suggest you to degenerate to the fact table.

Kishore MCDBA

 

[johnherman]

Separate order fact table.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[DBArch]

Thanks Kishleo and John for your input and suggestions. It seems there are at least 3 alternatives, each placing the over all order's shipping charges, etc, into a fact table.

One: We could create a separate line-item for shipping charges and place this in the main fact table. Not sure I like this one as it would create an artificial line in the Order that was never there in the first place.

Two: We could distribute the shipping charges out among all line items for the order by dividing the total shipping charge for the order by the number of line items and placing this in distributed amount on each line item within the single fact table.

Three: We could create another Orders Fact table, as you suggest John. But I'm not sure how this would work with OLAP tools in slice / dice operations.

Any suggestions on which is preferable, or are there other options that would be better?

Thanks and regards!

 

[johnherman]

If you feel comfortable allocating freight, etc across the line items either by weight or item counts, that would solve your problem without adding another fact table. I will be very important to educate the users that such an allocation has been done, though.

Most modeling methods would prefer that you have a separate order header fact table. You could allocate the freight if you want, but also have it stored separately at the order where it belongs. You can also allocate it across the OLAP structures when you build them.

Or keep it in both?

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright