Managing Helper Tables

A careful look at many-to-many relationships between important dimensions

ASSIGNING THE WEIGHTING FACTORS

The last field in the helper table is the weighting factor. The weighting factor is used to allocate additive numeric facts in the main fact table against the separate values of the multivalued dimension in "correctly weighted" reports. In the banking example, you can report bank balances against individual customers in account ABC123 by multiplying the weighting factor against the balance:

SELECT customer.name, fact.balance*weightingfactor
FROM fact, account, map, customer, month
WHERE fact.accountkey = account.Accountkey
AND fact.monthkey = month.Monthkey
AND account.accountkey = map.Accountkey
AND customer.customerkey = map.Customerkey
AND account.naturalid = 'ABC123'
AND month.monthdate = 'July, 2001' 

The sum of all the balances this query finds will be the correct balance in account ABC123 for July 2001, which is how you know this is a correctly weighted report. If you remove the weighting factor from this query, it yields an "impact report" that associates the full balance of each account with each of its customers. Banks, at least, like both kinds of reports. Although the impact report deliberately overcounts the aggregate balance, it provides a true assessment of the potential control each customer may excercise over the balance in the account.

At any point in time, all the weighting factors in an account have to add up to exactly 1. Therefore, if a customer is added to an account, it makes the most sense to adjust all the weighting factors and introduce a complete set of customer records in the helper table. More complex scenarios are possible, but they probably make the updating process too messy.

UPDATING THE HELPER TABLE

The helper table must be updated whenever:

• Anything in the account dimension changes
• Anything in the customer record changes
• Any customers are added to, or deleted from, an account
• The weighting factors are adjusted.

To keep the updating application as simple as possible, the entire set of customers should be added with identical begin and end dates whenever one of these changes takes place. For current records, the end date should probably be a fictitious date far into the future, so that the SQL given in our examples stays simple.

When you add a new set of customer records for an existing account to the helper table, the previous set of customer records must all have their end dates adjusted. All the begin and end dates for a given account must connect seamlessly over all time so there are no gaps.

Rate This Article Rating: 1 (best) 2 3 4 5 (worst) Comments: Optional e-mail address:

If you introduce postdated changes to either the account dimension or the customer dimension into the data, you need to add a new set of helper table records as well. Presumably you need to split some existing time span defined by begin and end dates to "make room" for the postdated changes. Doing this can trigger some messy additional processing, because a postdated change to a customer record (for instance) may require you to propagate a brand new customer surrogate key forward in time in the mapping table beyond the affected interval. I explored these complex processing issues in my Intelligent Enterpise column, "Backward in Time" (September 29, 2000), which can also be found in my column archive.

If you got this far, and followed all this, you are definitely entitled to a cup of coffee.

Ralph Kimball co-invented the Star Workstation at Xerox and founded Red Brick Systems. You can reach him through his Web site, www.rkimball.com.