Fact table with non-numeric measure

View previous topic View next topic Go down

Fact table with non-numeric measure

Post  cridal on Fri Mar 27, 2009 4:31 pm

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.

cridal

Posts : 9
Join date : 2009-03-27

View user profile

Back to top Go down

Re: Fact table with non-numeric measure

Post  BoxesAndLines on Sat Mar 28, 2009 12:05 am

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Fact table with non-numeric measure

Post  tropically on Mon May 18, 2009 12:01 pm

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

tropically

Posts : 13
Join date : 2009-05-12

View user profile

Back to top Go down

If you use the date as the surrogate key

Post  Jeff Smith on Mon May 18, 2009 1:33 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Fact table with non-numeric measure

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