Modeling large sales dimension?

I am trying to create a dimensional model for sales orders.

The problem I am having difficulty with is deciding how to break ups the source systems table into a fact and dimensions.

On the source system the sales table has 218 columns. After stripper out attributes I know are shared with other facts, I have created 1 Fact table and I have 2 dimensions one with audit info (username, workstation, etc) and another one with everything else, sales_order_d. These dimensions have 8, and 73 columns respectively.

The part I have trouble with is the sales_order_d has a 1-1 join with my fact table. The sales order fact has millions of records, so my sales_order_d dimension also has millions of records. From what I understand I should try to keep dimension record count lower than the facts.

Should I be splitting up my sales_order_d into multiple smaller dimensions, based on a logical grouping so I have smaller dimensions? (Number of records wise)

Another thing is that I have other fact tables that need information from the sales order dimension.

Hi ,

what type of columns are making up the sales_order_d where in the dimension table is related to the fact table on a one to one .


thanks

order number, and line number for the sale as well as order number and line number for a related order. There are also a few free form text fields.

Should i make the order number, and related order number a DD on the fact table? and create a dimension for the free form text fields?

There is usually no reason for a sales order dimension. Break it down into degenerate values and multiple dimensions as appropriate. You can usually put free form text into a single dimension with multiple foreign keys on the fact.