Modelling a payment schedule

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.

Thanks!

How about pre-generating the payment schedule for each loan/bond and storing in a fact table? It would be easy to get the payment schedule for any security. Getting the next payment date and amount for a single security would also be easy with a TOP 1 query. It gets a little trickier trying to get just the next payment date for multiple securities in a single query, but can be done with a subquery.

To make it easier for your users, you could implement computed fields in the account dimension that would return the next payment date and amount, although performance could be an issue as each computed field value returned would need to do a TOP 1 query against the fact table behind the scenes.

In the rare event a payment schedule changes, you would presumably delete the existing future payment schedule fact records for that account and regenerate the payment schedule from the current date forward.

I would second the implemention of a Pre-generated Payment Schedule Forecast Fact table that creates 1 Line for every Payment Due, for the life of the account (at maturity or perpetual).

This approach is slightly different from the one suggested by VHF.

For example:

Column_Name
Account_FK
Account_Open_Date_FK
Account_Maturity_Date_FK
Current_Payment_Due_Date_FK
Next_Payment_Due_Date_FK
Initial_Payment_Indicator
Final_Payment_Indicator
Current_Payment_Number
Next_Payment_Number
Total_Payment_Number
Current_Payment_Amount
Next_Payment_Amount
.........etc

I think you get the point.

The idea is to store every Date reference your users will want to query in the Fact table. There will be some level of maintenance involved as the Payment Dates and Amounts changes though.

This allows you to merge all account types(with differing pay intervals) facts into a single Fact table, provided they share the common grain (1 row per Payment Due).

Another option is to store the Dates or Date_FKs (permissible snowflake) at the Account Dimension, that way you can always filter the various dates through the Account Dimension (without having to repeat the DATE_FK in every Fact table).

It all depends on your environment and what works best for you.

Hope this helps.