Hi,

I have an AS cube with a standard transaction (fact) table, a customer
dimension, a country dimension and a time dimension. Each customer record
will have several transaction records which may or may not have been made
through more than one country. Two of the key measures when analysing our
data is the Average Lifetime and the Average Transaction Frequency of all
the customers within a particular corridor (country).

The Lifetime of a customer is based on the 'duration' that a customer has
been trading which is calculated as the difference in months between the
first transaction date and the last transaction date involving that customer
for the selected period of analysis. The Average Lifetime of a customer
within a corridor would subsequently be the aggregate of the customer
'durations' divided by the number of customers within a selected country.
Similarly, Average Transaction Frequency will be the number of transactions
per month over the lifetime (duration) of a customer.

This is very easy to achieve in SQL using avg, min and max functions.
However, the OLAP environment adds a certain complexity that is currently
beyond me. I can find useful hints on the internet to build something
remotely like I want within the MDX builder but nothing that specifically
relates to creating a measure within an Analysis Services cube.

Can anyone please shed some light on a solution to achieve these two
measures.

Many thanks,
Stuart

The fact table for the standard Foodmart Sales cube seems to similar to
your transaction table, if each sale is considered a transaction. It has
a time dimension down to the month and a customer dimension. The store
dimension can be treated like your country dimension. The relevant Sales
cube measure is [Sales Count], which is a count measure.

Based on these similarities, here is a sample MDX query for Foodmart
Sales that creates calculated measures like [AvgLifetime] and
[AvgFrequency], where the duration is the gap in months between earliest
and latest sales. You can compare query results to underlying fact table
rows:

'Rank(Tail(NonEmptyCrossJoin(
Descendants([Time].CurrentMember, [Time].[Month]),
{Customers.CurrentMember},
{Store.CurrentMember}, 1)).Item(0),
[Time].[Month].Members)
- Rank(NonEmptyCrossJoin(
Descendants([Time].CurrentMember, [Time].[Month]),
{Customers.CurrentMember},
{Store.CurrentMember}, 1).Item(0),
[Time].[Month].Members)'

Member [Measures].[Frequency] as
'Sum(NonEmptyCrossJoin(
Descendants([Time].CurrentMember, [Time].[Month]),
{Customers.CurrentMember},
{Store.CurrentMember}, 1), [Measures].[Sales Count])'

Member [Measures].[AvgLifetime] as
'Avg(Descendants([Customers].CurrentMember,
[Customers].[Name]), [Measures].[Duration])'

Member [Measures].[AvgFrequency] as
'Avg(Descendants([Customers].CurrentMember,
[Customers].[Name]), [Measures].[Frequency])'

Select {[Measures].[AvgLifetime],
[Measures].[AvgFrequency]} on columns,
NON EMPTY {Descendants(
[Customers].[All Customers].[USA].[OR].[Beaverton],
[Customers].[Name]),
[Customers].[All Customers].[USA].[OR].[Beaverton]} on rows
from Sales
where ([Store].[All Stores].[USA].[OR].[Portland],
[Time].[1997])

- Deepak

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