DW design question

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!

#2

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.

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