Hi,

I have list of customers in a table that has a cust_id, a first spend date
(when the account becomes active), a last spend date (when the account
becomes inactive) and a lapse date (when there has been no spend for at
least 12 months since the last spend date). I need to create a report via an
analysis service cubes that has 3 'classifications' in the rows (Active
Accounts, Inactive Accounts and Lapsed Accounts) and a defined time period
across the columns (in months). The detail will be a count of customers that
fulfill one of the three classification criteria. I have displayed a basic
example below:

Jan02 Feb02 Mar02 Apr02.
Active Customers 875 903 967 945
Inactive Customers 204 227 265 310
Lapsed Customers 59 62 79 101


The aggregate of the three numbers will always be increasing as long as the
company keeps trading and will represent a total count of all the customers
that have ever existed in the database.

Can anyone please suggest a way of achieving this as I am struggling to get
my head round the solution.

Many thanks,
Stuart

An approach adapted from that discussed in this recent thread, where
there were 2 date dimensions, may work:

http://groups.google.com/groups?hl=e...EHA.2540%40TK2
MSFTNGP15.phx.gbl&prev=/groups%3Fnum%3D25%26hl%3Den%26lr%3D%26group%3Dmi
crosoft.public.sqlserver.olap%26start%3D50

There would be 3 date dimensions, say [FirstDate], [LastDate] and
[LapseDate], with identical structures but joining to the 3 respective
fact table fields. A "Count" measure could be defined on the cust_id
column, then 3 calculated measures created for [ActiveCustomers],
[InactiveCustomers] and [LapsedCustomers].

A couple of issues would then have to be resolved:

- if the dates are down to the day level, but reporting is at the
monthly level, how is a transition in customer status during a month
counted (eg: if customer becomes inactive on Mar.16, do they count as
both active and inactive in Mar'04)?

- If reports are only required by a single date dimension,
(as in the sample report data) then one of the 3 date dimensions could
be re-used as a report axis. Otherwise, another date dimension would
need to be added.

The MDX calculations would sum the "Count" measure over all combinations
of dates that meet the criteria for the 3 defined Customer states:
Active, Inactive and Lapsed.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!