Transaction fact without obvious transaction type field

Hi

We need to load a claim transaction fact with such events as "claim opened", "claim cancelled" etc. The problem is we don't have a natural source table with a transaction type field to use. Instead we have a claim table at source with different date fields for creation, completion and cancellation, all in the one row.

With this in mind, how do I create a transaction fact? My current thinking is that we use separate queries and union them together with an artificial transaction type key, ie


Insert in your claimfact
Select claim key, creation date as transaction date, 1 as claim count, 1 as transaction typeid
Where creation date is not null
Union all
Select claim key, completion date, 1,2 as transaction typeid
Where completion date is not null

Wed then populate a transaction type dimension based on the transaction type ids we created artificially.
Ie

Dim transaction type contains

Transactiontypeid. Transaction type
1. Claim created
2. Claim completed


What are your thoughts on this? Is this a valid method for inferring a transaction type for our fact table?

Thanks

Mike

There's nothing wrong with deriving a type code from the different dates. If your dates are static, I would also consider an accumulating snapshot fact table. There's no pivoting of the source data in that case.