ow do i model this???

View previous topic View next topic Go down

ow do i model this???

Post  Glen on Fri Jul 09, 2010 6:47 am

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

Glen

Posts : 1
Join date : 2010-07-09

View user profile

Back to top Go down

Re: ow do i model this???

Post  BoxesAndLines on Fri Jul 09, 2010 8:55 am

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: ow do i model this???

Post  ngalemmo on Fri Jul 09, 2010 11:22 am

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: ow do i model this???

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