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

Modeling large sales dimension?

3 posters

Go down

Modeling large sales dimension? Empty Modeling large sales dimension?

Post  drwily 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

Back to top Go down

Modeling large sales dimension? Empty Re:Modeling large sales dimension?

Post  hkandpal 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

Back to top Go down

Modeling large sales dimension? Empty Re: Modeling large sales dimension?

Post  drwily 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

Back to top Go down

Modeling large sales dimension? Empty Re: Modeling large sales dimension?

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

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

http://aginity.com

Back to top Go down

Modeling large sales dimension? Empty Re: Modeling large sales dimension?

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