We would like to answer these types of questions with our SSAS 2005 cube:

How many customers purchased a specific product?
How many customers purchase mostly by credit card?
How many customers purchase products costing over $100?
How many customers were first time customers this year?

We have a customer dimension and are trying to avoid creating a customer
fact table with a loaded customer count measure and conformed dimensions. We
would like to instead implement a distinct calculated measure based on the
customer number attribute in the customer dimension. My understanding is
SSAS only allows one distinct count calculated measure per measure group.

My question is how to implement this in SSAS 2005? Currently I have one
calculated measure with the following code:

DISTINCTCOUNT( [Customer].[Customer Number].Members)

This just gets added to the end of the measure groups in the browser window
and is not included within each measure group. How should I proceed from
here?

try something like this as a starting point:
for a "simple" distinct count
count(exists([Customer].[Customer Number].[Customer Number].Members,, "Sales
Measure group"))

and this:
count(exists([Customer].[Customer Number].[Customer Number].Members,<Filter
Here>, "Sales Measure group"))

take care:
[Customer].[Customer Number].[Customer Number].Members return only the
customers
[Customer].[Customer Number].Members will return the "All Customers" member
+ the customers (so 1 more then expected)


look at this for usages:
http://sqljunkies.com/HowTo/9D6C4A2A...9A46E6640.scuk
http://msdn.microsoft.com/library/de.../distinct2.asp

you can found some resources on the net about distinct count and basket
analysis formulas.

SSAS allow more then 1 calculated distinct count by measure group (you can
create any number of calculated members, the measuregroup associated to a
calculation is more for display purpose then for real constraint)
but only 1 "physical" measure with DCount aggregation by measure group.

good luck.


"bhorwatt" <bhorwatt (AT) discussions (DOT) microsoft.com> wrote

thanks for the info and the links. I found some articles on setting this up
with virtual cubes in MSAS 2000, but wasn't finding a lot regarding setting
it up in SSAS 2005 since virtual cubes are handled differently there. I
checked my 2005 SSAS Step by Step book - chapter 5 - and it had instructions
on how to proceed in this in 2005. You create a new measure in the cube
structure tab and select Distinct Count off of the Usage drop-down list.
They only show example off of one measure group, so wasn't sure if it
requires a new measure group for each measure group that you need a distinct
calculated measure on or not. That is the way I have it set up now. I have
not been able to test it yet because am waiting for DBA's to load data into
the DW - just working off empty DW tables now. I am also wondering how the
performance will be using distinct count.

"Jéjé" wrote: