Fact table with non-numeric measure

I have a transactional fact table containing results of inspections. The results are non-numeric, but do represent a measurement, that is inspection result (like "pass", "fail", "pass with infraction", "failure with violation", etc...). In the source system they are represented with an ID and an associated description and stored in let's say Results table.



1) Is creating DimResults dimension out of Results table the standard approach? Now the result has an additional key, the surrogate, which makes its way into the fact table. That in turn makes the fact table "factless" and any aggregations are limited to counts (unless you join over to DimResults in addition).



2) If the results table is the source system actually has some intelligence regarding the assignment of IDs to descriptions reflecting the severity of the result, like

0 - "pass"

1 - "pass with infraction"

2 - "fail"

3 - "failure with violation"



Do you actually try to preserve this in assigning surrogate keys appropriately (i.e., 0 natural key to 0 surrogate key, etc), so that you maintain the option of running aggregate queries for averages right in the fact table? In that case you lose the decoupling value of surrogate keys as they actually retail business meaning (contrary to R. Kimball tenets). In addition you have the collision with "0" surrogate key, which is supposed to be tied to "unknown" dimension values. On the other hand, you gain the analytical value and simplicity of running average queries without needing to join to dimension.

1. Sounds like a reasonable approach to me. I have found that factless fact tables are a rather common occurrence.

2. You don't want your surrogate keys to have intelligence. If they do, they're no longer a surrogate key. The only exception to this rule is the date dimension where I have been known to use the date as the primary key, e.g. PK = 20090326 is for 03/26/2009.

I like the approach of using the surrogate key in the date dimension as the date itself. Good idea.

I can understand the value of using the date as the surrogate key in the date dimension, particularly for fact tables that are partitioned on the date. But, keeping the surrogate key an integer that starts with 1 for the first date and goes up by 1 does have some advantages. It makes it very easy to calculate the number of days between 2 dates (just subtract). I would suggest creating a column in the date dimension that is a numeric representation of the date (10/27/2009 as 20091027) and put this column in fact tables that need to be partitioned.