Descriptive Text in Fact table
4 posters
Page 1 of 1
Descriptive Text in Fact table
hi,
i have a fact table for Log information and it has links with dimension like Type, Employee, Location_Code, Date etc.
There are many descriptive text fields for this log data like Log Description, Notes, Log Level etc.
Can these descriptive text fields be part of the Fact table or is it required to have separate dimension for Log descriptive attributes and link with the Fact log table?
thanks,
pullai
i have a fact table for Log information and it has links with dimension like Type, Employee, Location_Code, Date etc.
There are many descriptive text fields for this log data like Log Description, Notes, Log Level etc.
Can these descriptive text fields be part of the Fact table or is it required to have separate dimension for Log descriptive attributes and link with the Fact log table?
thanks,
pullai
PJK- Posts : 4
Join date : 2012-09-26
Re: Descriptive Text in Fact table
Hi Pullai
In the past I have had to include descriptions in a fact table that stored transactions. The comments were specific to each transaction (i.e.: manually entered by the person capturing the transaction), and as such, were mostly unique. This uniqueness motivated the descriptions' inclusion in the fact table as a degenerate dimension (i.e.: I stored the description as a field on the fact, and not in a separate dimension).
In my mind, a description isn't a facet by which the measure in the fact table is viewed - it generally doesn't give context in the same way that Type or Employee attributes would.
On the other hand, there are arguments that suggest that descriptions be stored in a separate junk dimension under certain circumstances (see https://kimballgroup.forumotion.net/t405-booking-number-and-description-in-the-fact-table and http://wiki.ipublic.org/wiki/images/a/ad/Kimball_Design_Tip_48_DeClutter_with_junk_dimensions.pdf).
Hope this helps.
In the past I have had to include descriptions in a fact table that stored transactions. The comments were specific to each transaction (i.e.: manually entered by the person capturing the transaction), and as such, were mostly unique. This uniqueness motivated the descriptions' inclusion in the fact table as a degenerate dimension (i.e.: I stored the description as a field on the fact, and not in a separate dimension).
In my mind, a description isn't a facet by which the measure in the fact table is viewed - it generally doesn't give context in the same way that Type or Employee attributes would.
On the other hand, there are arguments that suggest that descriptions be stored in a separate junk dimension under certain circumstances (see https://kimballgroup.forumotion.net/t405-booking-number-and-description-in-the-fact-table and http://wiki.ipublic.org/wiki/images/a/ad/Kimball_Design_Tip_48_DeClutter_with_junk_dimensions.pdf).
Hope this helps.
min.emerg- Posts : 39
Join date : 2011-02-25
Re: Descriptive Text in Fact table
Put them in a separate dimension, or you will pay the price in terms of performance for any GROUP BY operations.
Whenever people want to view descriptions, they will usually have drilled down, or have a drill through report and be only looking at a hundred at a time as most, in which case joining to another table roughly the same size won't be an issue.
Whenever people want to view descriptions, they will usually have drilled down, or have a drill through report and be only looking at a hundred at a time as most, in which case joining to another table roughly the same size won't be an issue.
Re: Descriptive Text in Fact table
All of the advice I provide are not really rules, they're more like guidelines (arrrrgh matey). As such, there's no right or wrong way. Whatever way works for you, provides the high performance, ease of extensibility, and ease of querying are fine. When faced with this type of scenario, I simply store the data in another table. It's not really a dimension or a fact. It's just a child table that has a one-to-one relationship with the fact table. That way, my fact table is not weighed down with space gobbling textual data but I can easily access it if I need to. If you go this route, you need to create a surrogate key for your fact to make the FK in the text table is only a single column.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Descriptive Fields in Fact Table
» [solved]An explanation of sentence: Any descriptive attribute that takes on a single value in the presence of a fact table
» 1 to 1 Text Data in a Fact Table
» character data in a fact table?
» Free Form Text Attribute in Fact Table
» [solved]An explanation of sentence: Any descriptive attribute that takes on a single value in the presence of a fact table
» 1 to 1 Text Data in a Fact Table
» character data in a fact table?
» Free Form Text Attribute in Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum