Calculating RFM

I'm in the process of developing a Kimball-method CRM data warehouse and I'd like to have Recency, Frequency, Monetary "scores" at the customer level. Am I better off creating these in the relational database and feeding them to SSAS or have SSAS do the aggregations and assign the "scores"?

Also, has anyone located any SSIS transform scripts that would calculated RFM scores? This is probably a pretty easy task in VB.net but I haven't gone down that road yet.

Steve

Not strictly a Data Mining question, but here goes ...

How often do you want to calculate a score. Typically the Frequency and Monetary scores are only calculated monthly, quarterly or annually rather than being continuously updated. In this case, you would not really want to implement them as OLAP calculations, as the conditional logic in the calculation may get complicated.

You may well be better calculating these in a scheduled job in your ETL process, inserting them as facts, and having the OLAP cube roll them up as appropriate. I don't have SSIS scripts to calculate RFM, but as you say, a reasonably easy task.

BTW, here are some interesting articles on RFM that you may find useful too ...

A 3-part detailed guide ...

http://www.clickz.com/showPage.html?page=961901

http://www.clickz.com/showPage.html?page=971591

http://www.clickz.com/showPage.html?page=983891

A short piece on the theory ...

http://www.dbmarketing.com/articles/Art245.htm