Are textual fields ever allowed in fact tables?

Question

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

Answer 1

What you are describing sounds like a dimension derived from other dimensions rather than a fact table. I have done this many times where I have a structure of a primary key, a combination of foreign keys and a string column to denote a name. Product Definition comes to mind as an example. Shipping location (with its various lookups tied in) comes in as another.

Consider the following example: Locations: Fort Lauderdale, West Palm Beach, Miami

Each location may have multiple shipping locations. A shipping location has various attributes as in Packing system, Converyor belt system, Product Weight Range, Types of Pickups. All of these are in lookup tables.

So, I have a table called ShippingLocation with the following columns
- ShippingLocationId(PK)
- PackagingSystemId (FK)
- ConveyorBeltTypeId (FK)
- ProductWeightRangeId (FK)
- ShippingLocationName VarChar (200)

It seems very logical to me that the name of the shipping location would be in the same place where the shipping location is defined and it's attributes are defined. The only possible normalization I see here is that I can take it to a 1-to-1 table. IMO, that is a useless normalization.

Answer 2

This link seemed to answer my question: http://www.ralphkimball.com/html/designtipsPDF/DesignTips2003/KimballDT48DeClutter.pdf