Value Bands

Business users often need to filter and group customers or other dimensional entities based on numeric 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.

Dimensional modeling provides two related techniques for addressing these requirements:

• Aggregated Facts as Dimension Attributes
• Dynamic Value Bands

Although both techniques involve numeric values and ranges, they solve different problems. Aggregated facts are calculated during ETL, whereas dynamic value bands are defined when a report or query runs.

Aggregated Facts as Dimension Attributes

Selected aggregated facts can be calculated and stored as attributes 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. These dimension attributes representing aggregated performance metrics add burden to the ETL processing, but ease the analytic burden in the BI layer.

Example:

The sales fact table stores individual sales amounts. During ETL, the warehouse calculates each customer's lifetime spending and stores the result in the customer dimension:

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 groups a numeric fact measure into a series of ranges, or bands. Each range becomes a report row or category.

A retail bank might count accounts by balance range: under $10, $10 to $25, and so on. The ranges are defined at query time, not during ETL, which distinguishes this from the banded attributes above. That suits exploratory analysis, where changing the ranges shouldn't require reprocessing data.

Two ways to implement dynamic value bands:

1. SQL CASE expression

The ranges are written directly into the query:

CASE WHEN balance <   10 THEN 'Small Balance'
     WHEN balance <   25 THEN 'Medium Balance'
     WHEN balance < 1000 THEN 'Large Balance'
     ELSE                     'Jumbo Balance' END

Nothing needs to exist beforehand, making this the quickest way to try out a set of ranges. But the definition lives inside one query, so every report repeats it and the copies drift. Ordering is manual too. Sorting by the band label gives alphabetical order - Large, Medium, Small - so the query has to sort by something else, usually the same expression repeated or a second CASE producing a sort key.

2. Value Banding Dimension

The ranges are stored as rows and joined to the fact on a range condition (balance >= min_balance AND balance < max_balance) rather than an equality:

band_key	band_set	band_name	min_balance	max_balance	sort_order
1	RETAIL_DEFAULT	Small Balance	0.00	10.00	1
2	RETAIL_DEFAULT	Medium Balance	10.00	25.00	2
3	RETAIL_DEFAULT	Large Balance	25.00	1,000.00	3
4	RETAIL_DEFAULT	Jumbo Balance	1,000.00	999,999.99	4

Definitions sit in one place, editable by the business without changing SQL, with sort_order keeping bands in numeric order. The band_set column lets several schemes coexist, so retail and wealth-management views can use different thresholds against the same fact table.

Both approaches scan the fact table - the range join doesn't avoid that, and a CASE expression is often faster. The dimension table wins on maintainability, not performance. Use CASE for one-off analysis; move to a dimension table once the same bands appear in more than one report.

Inclusive vs Exclusive Boundaries

Bands written as 0–9.99, 10–24.99 leave a gap: a balance of $9.995 matches nothing and vanishes from the report. Touching boundaries (0–10, 10–25) evaluated as >= min AND < max guarantee every value lands in exactly one band.

Make sure the top band's ceiling is above the highest possible value. A ceiling of 999.99 silently drops every account above it.