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

Descriptive Text in Fact table

4 posters

Go down

Descriptive Text in Fact table Empty Descriptive Text in Fact table

Post  PJK Wed Sep 26, 2012 8:16 am

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

PJK

Posts : 4
Join date : 2012-09-26

Back to top Go down

Descriptive Text in Fact table Empty Re: Descriptive Text in Fact table

Post  min.emerg Wed Sep 26, 2012 9:14 am

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.

min.emerg

Posts : 39
Join date : 2011-02-25

Back to top Go down

Descriptive Text in Fact table Empty Descriptive Text in Fact table

Post  PJK Thu Sep 27, 2012 4:10 am

Many thanks for the details

PJK

Posts : 4
Join date : 2012-09-26

Back to top Go down

Descriptive Text in Fact table Empty Re: Descriptive Text in Fact table

Post  John Simon Thu Sep 27, 2012 7:18 pm

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

http://jsimonbi.wordpress.com

Back to top Go down

Descriptive Text in Fact table Empty Re: Descriptive Text in Fact table

Post  BoxesAndLines Thu Sep 27, 2012 9:24 pm

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
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Descriptive Text in Fact table Empty Re: Descriptive Text in Fact table

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