Are textual fields ever allowed in fact tables?
3 posters
Page 1 of 1
Are textual fields ever allowed in fact tables?
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
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
Re: Are textual fields ever allowed in fact tables?
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.
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.
Re: Are textual fields ever allowed in fact tables?
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
![-](https://2img.net/i/empty.gif)
» Textual values in dimension tables
» Textual Facts in the fact table
» Textual Fact - Junk Dimension
» Textual fact or Junk dimension?
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Textual Facts in the fact table
» Textual Fact - Junk Dimension
» Textual fact or Junk dimension?
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum