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

DW design question

2 posters

Go down

DW design question Empty DW design question

Post  SnowShine429 Sat Feb 16, 2013 6:01 pm

Hi All,

I am creating a data warehouse using SSIS and one of the fact tables am loading is called Orders and it has over 15 million rows. I also need to create a dimension called Orders that will have 3 columns Order Type, Order Source and Order Status. Each of the 3 columns have a very few distinct values(not more than 20). The Orders table in OLTP has a natural key called Order ID(GUID). In the source orders table, there is only one line per order.

Which of the following method should I follow:

1. Create degenerate dimensions in the fact table - I think this would make the reporting slow. For example, if we create a cube on top of the Data Warehouse. The cube would need to query this huge fact table twice. One time to read measures such as quantity, sales etc and second time to read order dimension attributes(order type, order source, order status). This would increase the cube processing time.

2. Create a Junk dimension with the the 3 order columns above.

3. Create Orders dimension - This would be a huge performance bottleneck when joining orders fact tale with the orders dimension table.

Do you guys have any thoughts o this? Thanks for the help in advance!


SnowShine429

Posts : 36
Join date : 2013-02-16

Back to top Go down

DW design question Empty Re: DW design question

Post  ngalemmo Sat Feb 16, 2013 9:55 pm

#2
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

DW design question Empty Re: DW design question

Post  SnowShine429 Sat Feb 16, 2013 10:37 pm

Thank you so much for your reply.

Can you please let me know if it would also be a good idea to have 3 separate dimensions one for each order column. Thanks again.

SnowShine429

Posts : 36
Join date : 2013-02-16

Back to top Go down

DW design question Empty Re: DW design question

Post  ngalemmo Sun Feb 17, 2013 3:36 am

No. It's only a few rows, why make work for yourself and the user?
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

DW design question Empty Re: DW design question

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