Tracking reversals and contras

[TheJFM]

Hi,

I have a situation where financial information in the source system can be amended or reversed.

All records are passed to the DW with each transaction being a fact in the Tran_mvmt table

Dimensions include customer, agent etc with all dimensions being Type 2 with begin and end date

I need to be able to deliver a historic picture reporting a position at any given point in time so I have to sum the values of the facts grouped by the relevant attribute e.g.

select
sum(a.Tran_Val)
,b.agent_name
from
Tran_mvmt a
inner join dim_agent b on
a.agent_id = b.agent_id
where
@Range_Date between a.Begin_Date and a.End_Date
group by b.agent_name


Is this the right way to go?

** Don't reach for the moon when you have the stars. ;-) TheJFM **

 

[johnbionhand]

You're on the right track...

The fact table won't have a date range, it should just have a date - the date (or date and time) of the transaction.

You'll then need to sum the fact records up to the date selected by the user.

You will also need to ensure that you specify which version of the dimension record to use (as there will possibly be multiple records because it's a type 2 SCD)

So, your SQL should look something like...

select  
sum(a.Tran_Val)
,b.agent_name
from 
Tran_mvmt  a
inner join dim_agent b on
                a.agent_id = b.agent_id
where 
a.Tran_Date <= @Reporting_Date and
@Reporting_Date between b.Begin_Date and b.End_Date
group by  b.agent_name

Hope that helps.


John
----------------------------------------
Business Intelligence at your fingertips

http://www.bionhand.com