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

Time to add Invoice Header Dimension?

3 posters

Go down

Time to add Invoice Header Dimension? Empty Time to add Invoice Header Dimension?

Post  VHF Thu Oct 27, 2011 10:56 am

In modeling a new fact table (for commissions paid) being added to an existing DW, I find myself with two degenerate dimension (DD) fields coming from the invoice header:

Commissions Paid Fact
...
Invoice No (DD) –average length of 10 characters
Customer PO No (DD) –average length of 16 characters
...

I am tempted to pull these out into an invoice header dimension. Replacing these two DD fields with a single 4-byte integer FK would save me an average of 22 bytes per record in the fact table. I don’t care about the storage space, but I do want to keep the fact table narrow for good query performance.

Invoice Header Dimension
Invoice Header Key (PK)
Invoice No
Customer PO No

Obviously, this will be a fast-growing dimension (averaging 1 dimension record for every 10 fact records.) Most queries won’t utilize the invoice header attributes at all, but occasionally users want to drill down filtering by either Invoice No or Customer PO No.

Are two attributes enough to justify factoring out into an actual dimension for high cardinality DDs such an invoice header?

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

Back to top Go down

Time to add Invoice Header Dimension? Empty Re: Time to add Invoice Header Dimension?

Post  ngalemmo Thu Oct 27, 2011 11:18 am

Weither this benefits things will depend a lot on the database platform you are using. You should do some performance testing on both scenarios be for you commit to the change. If the 18 bytes you save is large relative to the overall size of the row, it may improve performance enough to matter, but, then again, it may not matter at all and not improve queries that don't need the data.

Don't do this purely to save space.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Time to add Invoice Header Dimension? Empty Re: Time to add Invoice Header Dimension?

Post  hang Thu Oct 27, 2011 8:01 pm

The only issue I can see is that PO No and Invoice No may exist as DD in in their own fact tables, Purchase Order fact and Invoice fact, and PO No may exist without Invoice No.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

Back to top Go down

Time to add Invoice Header Dimension? Empty Re: Time to add Invoice Header 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