Accumulating snapshot table and bridge

View previous topic View next topic Go down

Accumulating snapshot table and bridge

Post  tim_goodsell on Wed Mar 30, 2016 7:25 pm

Hi

I have a loan application accumulating snapshot table which when changes creates a new record with valid from/to dates The PK can either be

a) LoanId + ValidFromDate

or

b) A surrogate key

which is the better method to use ?

Now a loan application can have one or more applicants. I was going to create a bridge table (applicants) that links the customer dimension (PK CustomerKey) with the accumulating snapshot fact table. If I am adding new records to the fact table every time a loan application changes do I need to add new set of records to the bridge table ?

Thanks

Tim

tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Accumulating snapshot table and bridge

Post  BoxesAndLines on Thu Mar 31, 2016 11:03 am

If you are adding a new fact row for every change what you really have is a transaction fact. If you update the row, then you have an accumulating snapshot.

I always include a single column surrogate key in my fact tables. If you do that you will have two valid keys, which is fine by me.

Your bridge table will need to be updated for all new fact rows. I would go type 1 to avoid carrying unnecessary history.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Accumulating snapshot table and bridge

Post  ngalemmo on Thu Mar 31, 2016 11:14 am

That depends on how you build the bridge table.

If the bridge is between application version (fact row) and customer, then yes you do.

If the bridge is between customer group and customer (where customer group represents a unique combination of customers) then no, you do not.  The fact would contain a customer group foreign key which would be universally applicable to any row. Given you may have multiple versions of the same application, this approach would result in a much smaller bridge table and better performance most of the time.

It is best practice to use surrogate keys at all times.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Accumulating snapshot table and bridge

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