Dimension or Fact - product ratings

I have a retail dimensional model that includes a product dimension, which is type 2, and is truly a slow change dimension.

New requirements are to add product ratings to the product. for purposes of this question, there are 2 new attributes: number or ratings and the average rating which is a whole number between 1 and 5. Product ratings can change often, sometimes daily for a large retailer. The rating data comes as part of the product feed. My question is how should this be modeled? I don't want to add the attributes to the current product dimension as it will modify my slowly changing dimension to one that gets updated often. but the business requirements includes dimension browsing, like "report all products with a 5 star rating". I don't want to create a separate independent dimension because that wouldn't allow for the browsing queries. I could create a snowflake dimension, but that doesn't seem right either. I could create a fact, but it definitely is not factual data. we receive a product feed daily and we detect change by product when updating the product dimension. if i created a fact, then i would have to detect change from the previous fact in order to create a new one.

Any help would be appreciated.

Thanks!

Hi,

the rating change for a product should be stored in a Fact table and for a product you should have one rating which will be active. It will be some thing like a transaction fact table. You may have some products where the rating may change daily and some may have same rating for months.


thanks
Himanshu