Aggregated Facts and Value Bands
Aggregated Facts as Dimension Attributes
Business users are often interested in constraining the customer dimension based on aggregated performance metrics, such as filtering on all customers who spent over a certain dollar amount during last year or perhaps over the customer’s lifetime.
Selected aggregated facts can be placed in a dimension as targets for constraining and as row labels for reporting. The metrics are often presented as banded ranges in the dimension table. Dimension attributes representing aggregated performance metrics add burden to the ETL processing, but ease the analytic burden in the BI layer.
| customer_key | name | lifetime_spend | lifetime_spend_band |
|---|---|---|---|
| C-100 | Alice | $5,500 | $5k - $10k |
| C-101 | Bob | $250 | $0 - $1k |
Dynamic Value Bands
A dynamic value banding report is organized as a series of report row headers that define a progressive set of varying-sized ranges of a target numeric fact. For instance, a common value banding report in a bank has many rows with labels such as “Balance from 0 to $10,” “Balance from $10.01 to $25,” and so on.
This kind of report is dynamic because the specific row headers are defined at query time, not during the ETL processing. The row definitions can be implemented in a small value banding dimension table that is joined via greater-than/less-than joins to the fact table, or the definitions can exist only in an SQL CASE statement.
The value banding dimension approach is probably higher performing, especially in a columnar database, because the CASE statement approach involves an almost unconstrained relation scan of the fact table.
Value Banding Dimension Table
| band_name | min_balance | max_balance |
|---|---|---|
| Small Balance | 0 | 9.99 |
| Medium Balance | 10 | 24.99 |
| Large Balance | 25 | 999.99 |