I have inherited a cube and star schema at my company and need some MDX help
for attrition and churn reporting. The cube is a daily snapshot of a
production system as follows - it has a load date dimension with no <all>
level. Each load date will give you current active customers and cumlative
disconnected customers. So for a given date I can say we have this many
active customers and all total have lost this many. The cube also has a
"end date" dimension that will tell you when a customer left. So if they
are active it is blank and if they disconnected I can tell which day (ie -
Nov 2004 10,000 disconnected). They want to start doing attrition and churn
reporting out of it. I can do it writing SQL fairly easy, however it of
couse is not very dynamic.

For attrition or churn I can grab the number of actives 30 days ago, however
how can I get the number of disconnected since that day 30 days in past? I
would need the 30 days of disconnected to change based on the load date.
Thanks.

Kevin Brooks

There's a number of ways to do this -- sounds like what you're trying to do
is have a calculated measure that adds up the number of disconnects for the
last 30 days. Basically, something like:
([DateTimeDim].[Date].Currentmember.Lag(30):[DateTimeDim].[Date].CurrentMember,
[Measures].[DisconnectCount]).

This assumes, of course, that you have disconnects as a fact table in your
DB, or as least that your schema will support deriving it in a query.
Generally, customer attrition is a big deal, so if it's not stored in a
table in your OLTP system (ie: is it just an attribute of the customer
table?), it'd be worth transforming it into one as part of your reporting
ETL. Eventually, you'll want to know a lot of different things about those
disconnects, most noteably how to prevent them from ever happening....

--RJ

"Kevin Brooks" <jeepnreb (AT) yahoo (DOT) com> wrote