Time to add Invoice Header Dimension?
3 posters
Page 1 of 1
Time to add Invoice Header Dimension?
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?
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
Re: Time to add Invoice Header Dimension?
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.
Don't do this purely to save space.
Re: Time to add Invoice Header Dimension?
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
Similar topics
» Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
» Time dimension design for cross time zone and custom calendar warehouse, 1min granularity
» "Junk" dimension looking more like a "Header" dimension
» Header Level Dimension for a Fact Table
» Time Dimension, NULLs and Time datatype
» Time dimension design for cross time zone and custom calendar warehouse, 1min granularity
» "Junk" dimension looking more like a "Header" dimension
» Header Level Dimension for a Fact Table
» Time Dimension, NULLs and Time datatype
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum