Value Banding

View previous topic View next topic Go down

Value Banding

Post  hurrican3dev on Sun Aug 26, 2012 7:50 pm

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

hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Value Banding

Post  ngalemmo on Tue Sep 04, 2012 3:46 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Value Banding

Post  hurrican3dev on Wed Sep 05, 2012 8:31 pm

thank you kindly!

hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

another approach?

Post  PHough on Thu Sep 06, 2012 1:07 pm

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...

PHough

Posts : 3
Join date : 2009-02-04

View user profile

Back to top Go down

Re: Value Banding

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum