1

I am designing a Fact table to report on loan volume. The grain is one row per loan transaction. A loan has a few major milestones that we report on: In order of sequence, these are Lock Volume, Loan Funding Volume and Loan Sales Volume.

I have Lock Date, Loan Funding Date and Loan Sale Date as FK (there are other dimensions in addition to these) in the Fact table to role playing dimensions off my DimDate table.

My question is, should I create separate Fact Tables to report volume for each major milestone or should I keep all of this in one Fact Table and use a "far in the future" date (e.g., 12/31/2099) for a milestone on a loan that has not been met?

I have read the Kimball books but I didn't find a definitive answer(if one even exists).

Thanks

EyeBikeRide
  • 13
  • 3

2 Answers2

0

You may profit from immutable design, by setting the granularity more fine to the milestone level.

This gives you columns

 transaction_id
 milestone_type  
 milestone_date

in you fact table. The actual milestone of a transaction is the milestone from the last (most recent) record.

The one adavatage is that you may add new milestone types in the future, but the main gain is, that you never update your fact table - you use inserts only.

You may safe rollback a wrong ETL load, simple by deleting the records; which is while using updates much complicated.

You may also implement more complicated state diagrams, e.g. in case when some milestone is revoked and the transaction falls back in the previous state.

The question if you use one fact table or more depends on the fact if your milestones are homogenous or not. If the milestones have distinct attributes, you may get a more clean desing using dedicated fact tables, but the queries get complicated.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • 1
    I like this approach, and I'd then implement OP's original design as an aggregate fact table based on this transactional table to handle reporting on details such as days-between-states. – Ron Dunn Aug 07 '18 at 23:48
0

You would rather have only one Fact Table.

That following question and its conversation answer pretty well to the general question of " One or multiple fact tables? ", but maybe not to how to deal with your specific problem of dates.

Fed Zee
  • 391
  • 3
  • 8