ow do i model this???

Hi,

I have ran into a problem and was wondering if anyone could help me, I’ll try and explain.

We are in the process of designing a data mart from a Siebel application system.

There are for example 2 tables that store orders information.
Orders(1 row per order) and orders extended detail(more the 1 row per order). (In fact i have this issue with 5 tables)

If i keep these 2 tables in the mart i then either need to create a dimension with all the order Ids (millions row dimension table) or join the 2 fact tables at report time (performance issues).

If i try and merge the table in the ETL to generate 1 huge fact table, i also get problems with counts. i.e. number of distinct orders versus distinct products.

Also because of data quality issues, some data is repeated in both tables and don't match, the line from the business is that we only flag these issues and not fix.

So we need to display both attributes.

I just hope someone has experienced something similar before.

Thanks in advanced

Put the order id in the fact table as a degenerate dimension. Model the required remaining order attributes as different dimensions (e.g. order status dim). Put all dates on the fact as dimensions as well. This should help alleviate the need for the order dim. If you find you still need an order dim, you have removed all "dimensionable" data elements and the size should be significantly smaller.

Agree. I've modeled orders for many different industries (Retail, CPG, etc) and never found the need for an order dimension. Model at the line level, put the order ID as a degenerate dimension, and place the order header attributes in dimensions off the line (some may be proper dimensions, others may be junk, depending on the attributes in question).

You can avoid an order fact table by placing order level charges (such as freight) on the line in separate columns with a dummy product (i.e. Freight Charge) or by allocating the charge against the individual lines. Sales tax is typically captured at the line level since different jurisdictions may tax different products at different rates.

Its easy enough to get a count of order by doing a count distinct on the order ID column.