Are textual fields ever allowed in fact tables?

View previous topic View next topic Go down

Are textual fields ever allowed in fact tables?

Post  nabils on Thu Jun 25, 2009 7:18 pm

Are there any cases where I can have a textual field such as a description in a fact table?

I currently have a fact table of meeting events (grain: row per meeting) with a number of dimensions such as date, client, location etc. I need to put the meeting subject in the fact table. Is this ok even though it is not a measure (I have not seen any examples of this). Can't move it to a separate dimension as it will always be the same size (no of rows) as the fact.

Any ideas or advice from past experience?

Thanks

nabils

Posts : 1
Join date : 2009-06-25

View user profile

Back to top Go down

Re: Are textual fields ever allowed in fact tables?

Post  ngalemmo on Fri Jun 26, 2009 12:00 pm

Generally, no. The usual argument is it makes the row much wider than it needs to be, which slows down queries.

Another argument is it limits what you can do with the data you collect. If you store it as a dimension there is a lot of other things you can do other than simply displaying the text. One is you need only store each unique text, making it easy to identify meetings with the same subject. You can also parse the text and build keyword lists, making it easy to identify similar subjects. Now, in this application, you may never need to do it, but, by building a dimension, at least you have the opportunity to do it should the need come up.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Are textual fields ever allowed in fact tables?

Post  BoxesAndLines on Fri Jun 26, 2009 12:38 pm

Degenerate dimensions are sometimes text and are valid on the fact table. If you run a cube based BI environment and you have large dimensions, you will end up moving dimension data to the fact table to support drill to detail performance. These are, of course, not desirable due to the reasons ngalemmo mentioned.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Are textual fields ever allowed in fact tables?

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