Should the informational columns be on the fact or dimension

View previous topic View next topic Go down

Should the informational columns be on the fact or dimension

Post  vanamali on Wed Oct 30, 2013 4:37 pm

I have some textual, numeric information only columns on a policy. Should I put it in a policy fact or a policy dimension

vanamali

Posts : 7
Join date : 2013-10-30

View user profile

Back to top Go down

Re: Should the informational columns be on the fact or dimension

Post  BoxesAndLines on Wed Oct 30, 2013 7:00 pm

Are they columns you add or sum? Put those in the fact. Everything else goes into dimensions.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Should the informational columns be on the fact or dimension

Post  vanamali on Thu Oct 31, 2013 9:46 am

They are not additive.

They are just for information purpose on the reports. Users will not even search based on those columns.


vanamali

Posts : 7
Join date : 2013-10-30

View user profile

Back to top Go down

Re: Should the informational columns be on the fact or dimension

Post  cjtravis on Thu Oct 31, 2013 11:33 am

Information that adds context and is not additive (as mentioned earlier) belongs on the dimension(s).

cjtravis

Posts : 3
Join date : 2013-10-30

View user profile

Back to top Go down

Re: Should the informational columns be on the fact or dimension

Post  vanamali on Thu Oct 31, 2013 11:49 am

Thank you.

Is it same if the contextual information is about the transaction (e.g. Invoice Type for Invoice Transaction')

vanamali

Posts : 7
Join date : 2013-10-30

View user profile

Back to top Go down

Re: Should the informational columns be on the fact or dimension

Post  cjtravis on Thu Oct 31, 2013 12:07 pm

Yes, those are common attributes that describe the transaction, which belong on a dimension. I recommend you look into Degenerate and Junk dimensions to help you if you feel you have attributes that help describe a fact, but may not belong on a standard dimension.

cjtravis

Posts : 3
Join date : 2013-10-30

View user profile

Back to top Go down

Re: Should the informational columns be on the fact or dimension

Post  vanamali on Thu Oct 31, 2013 12:08 pm

Cool! Thank you very much for the answer and the tip.

vanamali

Posts : 7
Join date : 2013-10-30

View user profile

Back to top Go down

Re: Should the informational columns be on the fact or dimension

Post  simmo2013 on Tue Nov 05, 2013 5:05 am

I would agree to use a degenerate dimension if they attribute is going to be something like order# or PO#, if you use Junk or any other dimension then you have to maintain a dimension table the size of the fact table!

simmo2013

Posts : 6
Join date : 2013-11-05

View user profile

Back to top Go down

Re: Should the informational columns be on the fact or dimension

Post  ngalemmo on Tue Nov 05, 2013 1:43 pm

That is generally not the case. If you set up junk dimensions correctly, they are usually orders of magnitude smaller than the fact table. High cardinality values, such as order #, belong as degenerate dimensions, as you have stated.

The only issue is free-form descriptive text. Such data should be in its own junk dimension, with one row for each unique string. I tend not to worry about the cardinality of such a dimension as its use would be minimal and on a very small subset of facts, if used at all. If you have an application that requires frequent text searches, there are additional structures you would implement to support that more effectively. The other thing, if these descriptions are entered by an employee, they tend to fall into patterns. You often see the same descriptions repeated or left blank, so there are usually far fewer unique descriptions than the number of facts.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Should the informational columns be on the fact or dimension

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