Value Banding

Hi,

I have a situation that is described (simplified) below. It involves correctly setting up dimensions for value-banding.

For example :

Fact Table is Orders
------------------------

FactOrders stores an amount (call it AMT)

It has an OrderNumber and a CustomerNumer on the Fact table.

There is an age key that is related to an age dimension. It represents the customer's age at date of order (integers 1-110).
There are other dimensions as well.

FactOrders
(
OrderNumber
, CustomerNumber
, StoreNumber
, OrderDate
, AgeKey
, AMT
)

DimAge
(
AgeKey
,Age
)

I need to set up value bands for the customer's ages.
A requirement is that there can be multiple value bands for a given age.

Example :

DimAgeGroup

Age Category :
Type 1
Value Band 1 : 0 - 18
Value Band 2 : 19 - 99

Age Category :
Type 2
Value Band 1 : 0 - 21
Value Band 2 : 21 - 59
Value Band 3 : 60 - 99

If there are many ages on the FactOrders table, and many different value bands, does this necessitate a bridge table,
between DimAge and DimAgeGroup?

Thanks,
Rick

If all you have for age is the age, then just store it as a degenerate dimension. The banding would be implemented using a bridge between age on the fact and the age groups. Since you have multiple groups, the bridge would contain an ID for the collection of bands and have multiple rows for each different collection that relates to that age.

thank you kindly!

We would apply this as two alternative hierarchies (effectively denormalized parent attributes) of the DimAge entity thus:

DimAge
(
AgeKey
,Age
,Age_Cat_Type1_Band
,Age_Cat_Type2_Band
,... etc.
)

with the band values in the form you have defined them valid for the respective Age values - E.g. Age_Cat_Type1_Band value "1 to 18" simply repeated for all Ages of 1 through 18 and Age_Cat_Type2_Band value "1 to 21" simply repeated for all Ages of 1 through 18 and then continued to Age 21, etc...