Warehouse design - date range problems

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Sean (plugwalsh_at_yahoo.com)
Date: 02/23/04

  • Next message: Ray Higdon: "Re: efficient SQL profiling" 
    Date: 23 Feb 2004 09:23:40 -0800

    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

  • Next message: Ray Higdon: "Re: efficient SQL profiling"

    Relevant Pages

    • UK bank charges
      ... charge from per month to per transaction. ... transactions, then changed the excess charges to exploit the small ... have happened under the traditional payment system. ...
      (uk.misc)
    • Re: Credit card charges!
      ... There are several companies offering "Electronic Wallets" ... some charge you, some the trader, their commission. ... could equally well be from a Credit Card, ... PayPal transactions ...
      (uk.sport.horseracing)
    • Re: OT: Anyone know what Mastercard will charge me for an overseas payment?
      ... Most credit cards charge about 1.5% for non-sterling transactions, ... list it as a separarte charge, others hide it in the exchange rate. ... variation to that contract. ...
      (uk.legal)
    • Re: US credit card use in London
      ... percent charge, this transaction occured on Aug 15, 2006 on a Chase ... VISA card. ... Anybody else have some recent transactions from ...
      (rec.travel.europe)
    • Re: OT: Anyone know what Mastercard will charge me for an overseas payment?
      ... Most credit cards charge about 1.5% for non-sterling transactions, ... list it as a separarte charge, others hide it in the exchange rate. ... variation to that contract. ...
      (uk.legal)