Balance Snapshot and Lag Window Function

View previous topic View next topic Go down

Balance Snapshot and Lag Window Function

Post  jflanner on Tue Jun 18, 2013 10:14 am

Having read the books - I understand that a daily snapshot should - by definition - be taken every day.  I am in the specialty credit card space.  Ours is not a daily use card.  Typically - our card is used only once or twice per month.

So I can't give it up.  I don't want to insert 31 rows where only a small number of changes have occurred.  I want to implement the snapshot as a view.  I would insert into a FactChange table only when a change in balance is observed.


For the below example uses cross join, left outer join and lag to accomplish ALMOST exactly what I want - a 20 row result set addressing 2 accounts over 10 days.  The lag gets the previous value and I can coalesce for the FIRST day of inactivity.  It fails on the second, third, yada yada because the previous day as null. 

What I need is lag(c.Balance, LastNonNull)  That LastNonNull is the part I can't get my head around.  Can anyone help??



-------------------------------


create table DimDate
(
    DateKey                  int not null,
    constraint PK_Date primary key clustered (DateKey)
);
go

insert into DimDate values
(20130601),
(20130602),
(20130603),
(20130604),
(20130605),
(20130606),
(20130607), 
(20130608),
(20130609),
(20130610);
go

create table DimAccount
(
    AccountKey                  int not null,
    constraint PK_Account primary key clustered (AccountKey)
);
go

insert into DimAccount values
(1),
(2);
go

create table FactBalanceChange
(
    DateKey                  int not null,
    AccountKey               int not null,
    Balance                  money not null,
    constraint PK_BalanceChange primary key clustered (DateKey, AccountKey),
    constraint FK_BalanceChange_DateKey foreign key (DateKey) references DimDate(DateKey),
    constraint FK_BalanceChange_AccountKey foreign key (AccountKey) references DimAccount(AccountKey)
);
go

insert into FactBalanceChange values
(20130601, 1, 10.00),
(20130601, 2, 10.00),
(20130604, 1, 5.00),
(20130607, 2, 5.00),
(20130609, 1, 4.00),
(20130609, 2, 4.00);
go

create view FactBalanceSnapshot as

    select 
        d.DateKey, 
        a.AccountKey, 
        coalesce(c.Balance, lag(c.Balance, 1) over (partition by a.AccountKey order by d.DateKey)) as Balance
    from 
            DimDate d
        cross join 
            DimAccount a
        left outer join
            FactBalanceChange c
                on d.DateKey = c.DateKey
               and a.AccountKey = c.AccountKey;  
go 


select * from FactBalanceSnapshot

jflanner

Posts : 5
Join date : 2012-06-04

View user profile

Back to top Go down

Re: Balance Snapshot and Lag Window Function

Post  ngalemmo on Tue Jun 18, 2013 12:09 pm

There is a fundamental issue using LEAD/LAG in a view.  The database must materialize the view before it can apply external predicates.  In other words, it will create a full snapshot image then use it in the query.  The reason for this is any filters you may use in the query, if pushed down to the view, may alter the results of the LAG.   This would introduce ambiguity into the view, which is not allowed.

So, you wind up creating the snapshot you are trying to avoid every time you use the view.  Performance would be terrible.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Balance Snapshot and Lag Window Function

Post  BoxesAndLines on Tue Jun 18, 2013 2:11 pm

Why don't you want to use a daily snapshot?  After six months, drop the dailies and just keep month end.  In the grand scheme of storage, it's probably less than what's on your PC's hard drive.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Balance Snapshot and Lag Window Function

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