Loans and interest rate indexes

[Sealeopard]

I am in the process of modeling a loan servicing data mart that will be used for analytics processes. One of the processes will look at the loan's interest rate and margin and make certain calculatiosn with regards to discounts that have been applied ot the loan and to validate the accuracy of the interest rate. I will therefore have a loan snapshot fact table (monthly grain, 1+M records per month, 20+ years of history) that contains the effective interest rate, loan balances, loan statuses. The applicable interest rate index (fixed, LIBOR, PRIME, ...) is in an interest rate index dimension that includes additional information as to the specific calculation of the index base rate, whether it's recalculated monthly, quarterly, yearly. There can be 15-20 different indices. I am planning to link the interest rate index dimension directly to the loan snapshot fact table, otherwise I would have to resolve it by way of the credit agreement dimension and product dimension. However, where would I store all these interest base rates? Technically, they belong into a fact table as measures with an effective begin and end date linked to the interest rate index dimension, grain in this fact table would be monthly as well, but then I would need to duplicate interest rates for three moths if it's only recalculated quarterly. Alternatively, I have been thinking of modeling the interest base rates as part of an interest rate index SCD type 2.

I don't think I should store the index rates as a measure (or degenerate dimension) on the loan snapshot fact table. Doesn't feel right to take up a lot of redundant space for a data element that is already stored in a different fact table.

The analytics processes will look at the impact of changing interest rates (by way of changing interest base rates) (margin is constant). We will also need to group by index rate bands

This is actually becoming a generic problem for us as we will need to utilize other data elements that woudl belog into other fact tables as part of analytics processes. At that point I'm traversing across multiple dimensions in order to combine data from various fact tables. Compounding this problem is that I will also need to build this same lan snapshot fact table with a weekly grain which would make the table over four times the size of the monthly fact table.

[ngalemmo]

I don't think I should store the index rates as a measure (or degenerate dimension) on the loan snapshot fact table. Doesn't feel right to take up a lot of redundant space for a data element that is already stored in a different fact table.

Don't be silly. Space is not an issue. Do what makes sense.

[Sealeopard]

Yes, space is not an issue if you think of a single data element. But where do you draw the line of taking data elements from one fact table and adding them to another fact table in order to simplify derived calculations or analytics processes? I need the loan-level interest rate to calculate weighted average interest rates/margins (loan balance as weighting factor). Next up is a weighted average credit score with the credit score being a measure related to a person fact table. I have another 30-40 data elements that from a loan's perspective are indirect measures as they are measures related to entities (person, product) that are dimensions to the loan fact. However, the loan's measures are a function of these other entities measures (credit score goes down, then loan performs worse, then loan balance doesn't decrease as fast). So, do I now replicate these 40 data elements onto the loan-level fact table? Or do I set up a view that provides the appropriate joins.

[ngalemmo]

Top priorities in a design should be performance and ease of use. Space utilization is somewhere at the bottom of the top-10 list.

So, generally, you should favor eliminating the need to combine fact tables in a query (very costly) by replicating measures in another fact table. Whereas, using values from a dimension introduce less cost to a query.

But, it depends. If the measure from the other fact is not stable or available at the time the other fact table is being loaded, it may introduce significant complexities to the load operation. In such cases you are better off keeping each fact atomic and building a third aggregate fact to resolve performance issues. In this case you are sacrificing space for process simplicity and better performance.

It also depends with dimension related values. For example, you have a product dimension with a standard cost value. I will usually store standard cost in the fact table because the value is subect to change (time dependent) and I prefer to avoid implementing type 2 dimensions (ease of use). If the measure is dependent of an intersection of dimensions (almost all of them are), it belongs in a fact table.

I will almost never store derived measures in a fact table if they are easily resolved with a column expression. I will make sure the fact contains all the base measures needed for the calculation.

[BoxesAndLines]

Put the interest rate in the fact table. You don't need a start or end date since the Loan Fact is a snapshot. Interest rate band is a dimension. Same for FICO band, credit score band, etc. Things are still good for you though. My loan snapshot fact is a daily snapshot. After awhile, it drops to the EOM snapshot only to keep space utilization reasonable. It's a rare day when someone hits a snapshot from two years or older.

[Sealeopard]

Thanks to both of you for the valuable feedback. Looks like I'll start with the KISS principle and keep as much info in the fact table. Interesting comment about the product dimension not storing the price in order to avoid SCD2.

[Sponsored content]