I've looked at several posts and haven't found a solution. We have a
distinct count cube which appears to work great. We customized the
aggregations and managed to process all monthly partitions over night.

However, a problem appears when trying to display distinct counts for
multiple items (either in Excel or our in-house ASP application). I've
narrowed the problem down to the cubes inability to handle the
aggregation function on a distinct count measure (below is the error).
This causes a major deployment issue with our existing application. In
general, the end users would like to query the number of customers
using multiple products and/or org segments. Is there a way around
this error? I've looked into calculating the distinct count on the fly
using a 'distinctcount' function – but this isn't possible given the
number of customers at the lowest level. I'm willing to sacrifice
performance for the analytical benefit - however queries which take
hours to run may cause additional "product acceptance" problems. Does
anyone know a good work around?

Data:
Fact Table: 4,500,000 Rows Per Month (cube is partitioned by month)
Org Dim: 400,000 (Multiple Levels with Customer at the Bottom/ ragged
hierarchy)
Product Dim: 2,000 (2 Levels)
Time Dim (3 Levels)

Error:
Formula Error – Aggregations are not supported for the DISTINCT COUNT
measure "Customer Count"


MDX:
WITH
MEMBER [Org].[Total] As 'Aggregate({[Org 1],[Org 2], [Org 3]})'
MEMBER [Time].[Total] As 'SUM({LastPeriods(6,[Time].[Oct 03])})',
SOLVE_ORDER=40
MEMBER [Time].[Average] As 'AVG({LastPeriods(6,[Time].[Oct 03])})',
SOLVE_ORDER=40
SELECT
crossjoin({LastPeriods(6,[Time].[Oct 03]),[Time].[Average],
[Time].[Total]}, {[Measures].[Customer Count]}) ON COLUMNS,
NON EMPTY {TOPCount({Descendants([PRODUCT].[All
Product],[PRODUCT].[PRODUCT CODE])},501,([Measures].[Customer
Count],[Time].[Total]))} ON ROWS
FROM [All]
WHERE ([Org].[Total])

Jeremy,

As you indicated, multiple filters are not supported by the distinct count
aggregate. Your workaround is described in the following MSDN article.

http://msdn.microsoft.com/library/de.../distinct2.asp

If you find that following that workaround will result in you creating a
dimension with millions of members, you may need to break down that
dimension into multiple dimensions like (First 4 digits of customer number,
Last 4 digits of Customer number) and then do a CROSSJOIN between them. You
would then obviously set the visible property of these dimensions to false.

Performance of this calculation will be comparatively slow versus standard
aggregations.


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.






"Jeremy Highsmith" <jhighsmith4 (AT) hotmail (DOT) com> wrote

Thanks for the link. I'm looking into modifying our application down
the road. Do you know if there are changes to the distinct count
functionality in the upcoming "Yukon" version?

"Sean Boon [MS]" <seanboon (AT) online (DOT) microsoft.com> wrote

Yes. Distinct Count will support this scenario in Yukon.

Sean


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Jeremy Highsmith" <jhighsmith4 (AT) hotmail (DOT) com> wrote