Hi All,

I would like to see your approach of calculating churn based on the intersect of dimension members with MDX. Do you believe it is advisable to do it directly in the cube based on the dimension with more then 10 million records?

Here's the problem.

I'd like to calculate the following:

Clients this month - clients the same month previous year.

Simple right? Well, it gets trickier. The comparison needs go down to the grain which is the client, meaning, i cannot compare raw numbers (which are distinct count measures in the cube btw) but the actual client ID's. I tried playing around with intersect but i really need a simple example first before I start adding complex logic to the calculation (like fixed parameters for one batch and not the other, conditional logic over another dimension's attribute etc..).

Any good ideas and approaches to solving this problem?

Thanks! 

Hi L,

It's a pity my demo site isn't still online. Here's a blog about churn. http://richardlees.blogspot.com.au/2008/12/blog-post.html

The mdx might look something like

with  
member Measures.LastMonth as count(filter(Customers.Customer.Customer,(Measures.NetSales,[Time].FiscalCalendar.prevmember)<>0))
member Measures.RetainedThisMonth as count(filter(nonempty(Customers.Customer.Customer,Measures.NetSales),(Measures.NetSales,[Time].FiscalCalendar.prevmember)<>0))
member measures.R as 
iif(Measures.LastMonth=0,null,
Measures.RetainedThisMonth/Measures.LastMonth)
, format_string="#,#.00%" 
select {LastMonth,RetainedThisMonth, measures.R } on 0
,nonempty([Time].FiscalCalendar.[Month],Measures.NetSales)  on 1

from sales

Hope that helps,

Hi Richard, thanks!

I tried this but after one hour i got a timeout. I guess doing a filter on the Customer dimension with more than 10 million customers will not do the trick.

Another idea?

My idea was to maybe do this for each fact line in the dwh with the etl and skip MDX completely. But i would like to exaust all MDX possibilites before we make that decision.

Luka