Hi

What's the best way to model this in a data warehouse:

FACTs are charges against a property, but NOT the actual transactions
(ie. payments). It is important to note this difference. A FACT
example can be something like: Monthly Rental, such as when a one-year
tenancy starts, a Monthly Rental fact is created, with a start date
and an end date (1 year later). It effectively means that a charge (a
"tenancy charge") exists on that property. This is different from the
tenancy dimension itself, though (other reasons...). Each month, an
account debit (transaction) is generated - this goes into a different
FACT table, along with any payments (transactions). My problem is with
the charge FACT table, not the transaction FACT table, and it is
because of the slowly changing Type-2 property dimension:

Properties change over time, ie. an attribute would be
number_of_bathrooms. A newly added bathroom will create a new
DIM_PROPERTY dimension record for that property (new surrogate key
record). The DIM_PROPERTY records will thus have record_valid_from &
record_valid_to fields.

PROBLEM: How do I link a charge fact to a property? I can't link a
fact that spans time to a single property record, because that
property changes over time and creates new DIM_PROPERTY records. I
haven't come across this problem before & would appreciate any help.
My options are:

OPTION 1. Break the fact down into "sub-facts" that link to each of
the different property records that exist (are valid) during the
lifetime of the charge (fact). Definitely don't like this option as it
is over complicated & will be a nightmare to maintain.

OPTION 2. Link the charge fact to several DIM_PROPERTY records on the
property_id (natural key). Ensure that any reporting on this FACT
table is done on a point-in-time basis only, not a data range. Prefer
this option but is this a limitation?

OPTION 3. You tell me!

Anyone hit this before? Interested to know how you solved it / any
suggestions.
Thanks
Sean