data as an attribute on a dimension or a key on the fact table

[lconsalvo]

Hi,
I am in the process of trying to model a mortgage (origination/servicing) data mart. For requirements all I was given at this point is a spreadsheet of attributes. My plan was to try and pull out dimensions and metrics from the list and then have a meeting with the business user. My question is around date attributes. I have identified about 30 attributes that are dates. How do you determine if the date field should be an attribute on a dimension (ie part of the loan dimension) or a key on the fact table pointing back to a role date dimension. I would hate to have 20 - 30 role date dimensions for one fact table. Some examples of date attributes were: loan closing date,first payment due date, next payment due date, purchase date, loan maturity date, last full payment date, escrow last analysis effective date, cancellation date, termination date, appraisal complete date, listing start date, bill due date, next bill due date, check sent date (home equity loans)....etc
I dont think all of these dates should be keys, but what would determine if one should be an attribute or a key...


any suggestions would be appreciated.


Thanks,

[ngalemmo]

Which dates relate to a transaction and which dates relate to the mortgage? The latter are dimensional attrbutes, while the former are FKs or degenerate dimensional values on the fact.

For those that wind up in the fact table the choice is to either store as an FK to the date dimension or simply as a date value (degenerate dimension). If the date requires interpretation (such as identifying a fiscal period, or month), it should be a FK to the date dimension.

[hang]

I think ngalemmo got it spot on.

In general, date attributes in fact should be FK to date dimension as you very likely need to constraint or aggregate your fact on other date related attributes which would have to be worked out on the fly otherwise. The advantage of using date dimension is ease of use, performance and consistency. Another point is the date dimension can cater for meanings other than just date, say N/A, To Be Determined or Invalid Date.

With degenerate date dimension, it's mostly used in form of timestamp with much higher cardinality to cater for minutes and even seconds.

Use yyyymmdd integer smart date key in your date dimension if you want to avoid constructing with numerous joins in your surrogate key pipeline as the date key can be self derived, and you can also dedicate some special values to other meanings, say 0, -1, -2 etc.

[Sponsored content]