DW design question

View previous topic View next topic Go down

DW design question

Post  SnowShine429 on 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

View user profile

Back to top Go down

Re: DW design question

Post  ngalemmo on Sat Feb 16, 2013 9:55 pm

#2
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: DW design question

Post  SnowShine429 on 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

View user profile

Back to top Go down

Re: DW design question

Post  ngalemmo on Sun Feb 17, 2013 3:36 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: DW design question

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