Adding attribute to a Dimension

[Cyberbond07]

Hi there

We have a senario where in a calendar year a single customer could be any of two statusses, ie. When he joins he is New, when he comes back after 2 months we class him as regular. When he lapses for 1 year and then returns he gets re-classed as Lapsed.

So the question is how would you handle that in a analytical sense. Would you create a dimension on it's own or create a bridge table for it. Or would you add it to the fact table? The kind of reporting we would like to do is to see the amount of each class of donor for a given time period.

 

[RiverGuy]

Add a customer row each time an attribute changes.

 

[johnherman]

I would create a child table to Customer called CustomerStatus.

CustomerStatusTable
CustomerStatusID
CustomerID (foreign key to Customer Table)
CustoemrStatus
StatusEffectiveDate

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[FacultyTDWIDAMA]

Both answers above are correct and different solutions to the same problem.

Consider that if you are capturing history (in the case of appending, rather than updating or in other words adding a time dimension to the data), than RiverGuy's answer would be applicable. Note that this is not a normalized solution and data will be increasing each time the customer status changes. Johnherman's answer is a normalized solution (optimizing space) but increases the number of tables to manage, open, etc. Johnherman's answer would also be considered the best practice since: 1) data is not duplicated that would not required (i.e.: customer name, etc), and 2) customer status changes would be captured (along with the date) and could be reported on quickly and efficiently.