Modeling large sales dimension?

View previous topic View next topic Go down

Modeling large sales dimension?

Post  drwily on Mon Sep 30, 2013 11:12 am

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.





drwily

Posts : 2
Join date : 2013-09-30

View user profile

Back to top Go down

Re:Modeling large sales dimension?

Post  hkandpal on Mon Sep 30, 2013 12:22 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Modeling large sales dimension?

Post  drwily on Mon Sep 30, 2013 12:40 pm

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?

drwily

Posts : 2
Join date : 2013-09-30

View user profile

Back to top Go down

Re: Modeling large sales dimension?

Post  ngalemmo on Mon Sep 30, 2013 1:56 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Modeling large sales dimension?

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