Modelling Products with Stages

Asking for opinions on how to model a dimension for a product that can go through multiple stages in it's life.

Scenario 1: A credit card has a 2% introductory rate which moves to 5% after 6 months (one after the other)
Scenario 2: A credit card has a 0% introductory rate for balance transfers, but 5% for purchases (concurrent).

The two main facts would be:-

1: The credit card application (card (or card-product), application date, customer - measuring credit limit)
2: The credit card usage (card (or card-product), usage date, usage time, store used in, customer - measuring transaction amount)

I can forsee difficulties in having a single record per product, but I'm not sure one record per product stage would be "right".

What are your thoughts?

Thanks,
Paul

Why wouldn't a dimension row per stage be right? Alternately, you could have a rate dimension, but it would seem to me there woudn't be that many products and adding rate dimension would be overkill.

I agree - just model it like any other dimension, and process the rate changes like any other type-2 attribute.

I'd possibly had additional columns to the dimension (bal_transfer_rate, Purch_rate_change_amount and purch_rate_change_reason). That covers you for any type of rate change in the future.(annual rate adjustment, regulatory change etc).

From what I gather, it really isn't a type 2, but rather a more complex natural key... product/length of time held, or something like that...

The working rate for the product would change based on the length of time a particular customer had the card/product, so, at any point in time, the stage of the product will vary from customer to customer.

Thanks for the replies.

Re: ngalemmo

It is a complex, multi valued natural key dimension that for credit cards would be at product stage granularity.

It would most likely be a type 2 as the default rate for a card could change over time and this historical rate is useful to the business (although they may want to do mathematical analysis on historic rates - would this need a fact table?).


The reason it didn't feel right was because I was thinking of further complications when conforming this product dimension with others across the business that have a slightly different approach (I'm only 40 pages into "The Kimball Group Reader" - but I now realise how I'd underestimated the importance of Conformed Dimensions previously).

i.e. Mortgages - each product stage can also have many penalty stages associated with it, so an example might be

Prod A - 5% for 2 years, then onto a default rate (which can change over the life of a mortgage)
During the first 2 years there is a penalty for redeeming early...

4% of outstanding balance for months 1-6
3% of outstanding balance for months 7-12
2% of outstanding balance for months 13-18
1% of outstanding balance for months 19-24

This would mean the product dimension would be at Product/Stage/Penalty Stage granularity, but not all products across the business would have this level of granularity (credit cards would be higher level - none would be lower).


However, actually writing this down (and reading the replies) does make it seem correct to express the lowest possible grain in the dimension.

I just want to make sure I can't think of any products that may have a lower grain, or anything else that might cause issues later.

Thanks,
Paul