Descriptive Text in Fact table

View previous topic View next topic Go down

Descriptive Text in Fact table

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

View user profile

Back to top Go down

Re: Descriptive Text in Fact table

Post  min.emerg on 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 http://forum.kimballgroup.com/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

View user profile

Back to top Go down

Descriptive Text in Fact table

Post  PJK on Thu Sep 27, 2012 4:10 am

Many thanks for the details

PJK

Posts : 4
Join date : 2012-09-26

View user profile

Back to top Go down

Re: Descriptive Text in Fact table

Post  John Simon on 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

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Descriptive Text in Fact table

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

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

View user profile

Back to top Go down

Re: Descriptive Text in Fact table

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