Accumulative snap shot

This is an extension of my previous question, I feel this needs to be in a separate thread so started a new one

http://forum.kimballgroup.com/t1327-multiple-granular-levels-for-fact-table


This is Loan domain and I am trying to create an accumulative snap shot table at loan level with following columns

on 01-jan-2011 this snap shot would be like

Code:




    LOAN_KEY,LOAN_DATE,DEPOSIT_DATE,RETURN_DATE,CLEAR_DATE,WRITEOFF_DATE,LOAN_PAIDOFF_DATE,
    LOAN_STATUS,CHECK_STAUS,
    DUE_AMT,RTN_FEE,LATE_FEE

    sample data

    10001,01-jan-2011,null,null,null,null,null,
    Open,Held,
    2000,0,0




on 03-Feb-2011 this snap shot would be like

Code:



    LOAN_KEY,LOAN_DATE,DEPOSIT_DATE,RETURN_DATE,CLEAR_DATE,WRITEOFF_DATE,LOAN_PAIDOFF_DATE,
    LOAN_STATUS,CHECK_STAUS,
    DUE_AMT,RTN_FEE,LATE_FEE

    sample data


    10001,01-jan-2011,30-jan-2011,null,03-feb-2011,null,30-jan-2011,
    Close,Clear,
    0,0,0


Do I need to store only the current day snapshot of this information or I need to store this information for each business day(i.e. daily?)

What is the best practice for this?


Thanks,
Hesh.

After reading through the other thread, it appears that you need both a Periodic Snapshot and an Accumulating Snapshot. In that case, I would encourage you to read the Kimball tip below...

Also, you said...

Do I need to store only the current day snapshot of this information or I need to store this information for each business day(i.e. daily?)

What is the best practice for this?

If the business needs the level of detail where a daily snapshot should be taken, then yes.

Read through this Kimball Tip, then go back to your business sponsors and find out what is needed for their analytics.

(link not posted)

note: Google "kimball tip 42" (I still can't post links, still a noob...)