Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

ow do i model this???

3 posters

Go down

ow do i model this??? Empty ow do i model this???

Post  Glen 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

Back to top Go down

ow do i model this??? Empty Re: ow do i model this???

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

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

Back to top Go down

ow do i model this??? Empty Re: ow do i model this???

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

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

http://aginity.com

Back to top Go down

ow do i model this??? Empty Re: ow do i model this???

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum