Balance Snapshot and Lag Window Function
3 posters
Page 1 of 1
Balance Snapshot and Lag Window Function
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
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
Re: Balance Snapshot and Lag Window Function
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.
So, you wind up creating the snapshot you are trying to avoid every time you use the view. Performance would be terrible.
Re: Balance Snapshot and Lag Window Function
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Customer snapshot
» Accumulating Snapshot and Transaction Snapshot
» Can you combine an accumulating and periodic snapshot into a single accumulating periodic snapshot table?!
» Slowly Changing Facts?
» Fact table type
» Accumulating Snapshot and Transaction Snapshot
» Can you combine an accumulating and periodic snapshot into a single accumulating periodic snapshot table?!
» Slowly Changing Facts?
» Fact table type
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum