Where to put account status

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  November 14, 2014 at 4:05 am

  #295100

  Hi guys, I am putting together the scheme for a DW to report on property tenancies and their balances.

  I have a FACT table for the Tenancy Status that shows whether the Tenant is a current or a former tenant (and some other status changes which are not relevant to this discussion)

  I have a DIM table for the Tenancy which includes a termination date (which is NULL if the tenant has not given notice or actually quit)

  The Tenancy may or may not have an outstanding debt and this debt may be spread across a number of sub account (main rent, Service charge, personal charge or ad-hoc).

  I have a FACT table for the Tenancy Balance by TenancyID and sub Account ID with a BalanceAmount measure which is updated with a DateID date stamp every time the balance changes (NOTE That there may be multiple current FACT records for a Tenancy with different DateIDs for each sub account) I need to understand the difference betwen Current Tenant debts and Former Tenant debts. The status of the tenancy may change without a change in the balance records.

  I'm trying to work out the best method of storing and retreiving the current/former status for any given date. My first though was to store the IsCurrent on the balance records and insert new records with the termination date when the tenant quits the property but this poses challenges in the aggregation as there is more than one record for each tenancy (because of sub-accounts) so I would have to do a SUM() of the balance and a COUNT DISTINCT() of the TenancyIDs and the status of the tenancy is not really an attribute [or measure] of the balance, but of the tenancy so this feels like the wrong place to store it

  The second option is to join it to the FACT_TenancyStatus table but this introduces dependencies between FACT tables which I think is poor design (might as well leave it in 3NF). Also the join would have to be a <= inequijoin as the status may not change with each balance change.

  The third option would be to compare the report date to the DIM_Tenancy.TerminationDate and derive the Current/Former status from there, but this may be computationally expensive (My report requirement is to generate weekly data points of SUM(BalanceAmt) by Current/Former so some tenancies will change status within the timeframe of the report.

  Which option would you go for, and why; what are the downsides

  Or is there another alternative I havn't considered?

  Obiron