Data Warehouse: Modelling a future schedule

Question

I'm creating a DW that will contain data on financial securities such as bonds and loans. These securities are associated with payment schedules. For example, a bond could pay quarterly, while a mortage would usually pay monthly (sometimes biweekly). The payment schedule is created when the security is traded and, in the majority of cases, will remain unchanged. However, the design would need to accomodate those cases where it does change.

I'm currently attempting to model this data and I'm having difficulty coming up with a workable design. One of the most commonly queried fields is "next payment date". Users often want to know when a security will pay next. Therefore, I want to make it as easy as possible for them to get the next payment date and amount for each security.

Also, users often run historical queries in which case they'd want the next payment date and amount as of a specific point in time. For example, they may want to look back at 1/31/09 and query the next payment dates (which would usually be in February 2009 for mortgages). It's also common that they want to query a security's entire payment schedule, which might consist of 360 records (30 year mortgage x 12 payments/year).

Since the next payment date and amount would be changing each month or even biweekly, these fields wouldn't seem to fit into a slow-changing dimension very well. It would probably make more sense to use a fact table, but I'm unsure of how to model it. Any ideas would be greatly appreciated.

Answer 1

Why not store the next payment date as the amount of days from the date of the current payment?

Further clarification:

There would be a fact for every past payment linked to some date dimension. Each one of these facts will have a field next payment in which will be an integer. The idea is that the date of the current payment + next payment in will be the date of the next payment fact. This should be able to cater for everything.

Answer 2

Next payment date is an example of a "fact-free fact table". There's no measure, just FK's between at least two dimensions: the security and time.

You can denormalize the security to have a type-1 SCD (overwritten with each load) that has a few important "next payment dates".

I think it's probably better, however, to carry a few important payment dates along with the facts. If you have a "current balance" fact table for loans, then you have an applicable date for this balance, and you can carry previous and next payment dates along with the balance, also.

For the whole payment schedule, you have a special fact-free fact table that just has applicable date and the sequence of payment dates on into the future. That way, when the schedule changes, you can pick the payment sequence as of some particular date.

Answer 3

I would use a table (securityid,startdate, paymentevery, period) it could also include enddate, paymentpershare

period would be 1 for days, 2 for weeks, 3 for months, 4 for years.

So for security 1 that started paying weekly on 3/1/2009, then the date changed to every 20 days on 4/2, then weekly after 5/1/2009, then to monthly on 7/1/2009, it would contain:

1,'3/1/2009',1,2
1,'4/2/2009',20,1
1,'5/1/2009',1,2
1,'7/1/2009',1,3

To get the actual dates, I'd use an algorithm like this:

To know the payment dates on security 1 from 3/5/2009 to 5/17/2008:

Find first entry before 3/5 = 3/1
Loop:
 Get next date that's after 3/5 and before the next entry (4/2 - weekly) = 3/8
 Get next date that's before next the entry (4/2) = 3/15
 Get next date that's before next the entry (4/2) = 3/22
 Get next date that's before next the entry (4/2) = 3/29
Next date >4/2 switch to next entry:
Loop:
 Get next date that's after 4/2 and before the next entry (5/1 - every 20 days) = 4/22
Next date 5/12 is AFTER next entry 5/1, switch to next entry
Loop:
 Get next date that's after 5/1 and before the lastdate (5/17 - weekly) = 5/8
 Get next date that's before the lastdate = 5/15
Next date > 5/17

The dates between 3/5/2009 and 5/17/2008 would be 3/8,3/15,3/22,3/29,4/22,5/8,5/15