Free text fields in Transaction table
2 posters
Page 1 of 1
Free text fields in Transaction table
I am modeling a fact table that has a source transaction table which has a lot of free text fields, around 15-20 field. I know it is not logic to put those fields in the fact table. Also I know that we have 2 options to model it, either to have a separate dimension for each free text field, or to have a junk dimension to have them all combined together.
When I did query distinct all those fields I found that the combination resulted in records count near to the main transaction table records count, so in your opinion, which is the more accurate and suitable design, option 1 , option 2, or other options ?
When I did query distinct all those fields I found that the combination resulted in records count near to the main transaction table records count, so in your opinion, which is the more accurate and suitable design, option 1 , option 2, or other options ?
a_sherbeeny- Posts : 15
Join date : 2009-02-04
Re: Free text fields in Transaction table
Although the guiding principle says don't create a dimension that has similar size as fact table, in this case, I would still put 15+ text attributes in a single dimension to slim down fact table significantly, but without junking them if the size is close to the fact table. I would just increment the surrogate key value for each fact row that carries all the texts without worrying about reusing the same combination. If there are any repeated combinations, they are very rare anyway, and it's not worth junking them.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» Free form text in transaction table
» Free Form Text Attribute in Fact Table
» Calculating Fact table sizes and free form text
» character data in a fact table?
» Modelling free text comments
» Free Form Text Attribute in Fact Table
» Calculating Fact table sizes and free form text
» character data in a fact table?
» Modelling free text comments
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum