Time to add Invoice Header Dimension?

View previous topic View next topic Go down

Time to add Invoice Header Dimension?

Post  VHF on 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

View user profile

Back to top Go down

Re: Time to add Invoice Header Dimension?

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

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

View user profile http://aginity.com

Back to top Go down

Re: Time to add Invoice Header Dimension?

Post  hang on 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

View user profile

Back to top Go down

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