Transaction fact without obvious transaction type field

View previous topic View next topic Go down

Transaction fact without obvious transaction type field

Post  Mikedobing on Thu Nov 01, 2012 5:25 pm


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.

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?



Last edited by Mikedobing on Fri Nov 02, 2012 5:18 am; edited 2 times in total (Reason for editing : Extra info)


Posts : 1
Join date : 2012-11-01

View user profile

Back to top Go down

Re: Transaction fact without obvious transaction type field

Post  BoxesAndLines on Fri Nov 02, 2012 12:09 pm

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.

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

View user profile

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