MS SQL Server OLAP - AS/Pivot Table bug?

Author

AS/Pivot Table bug?

MarkFrank

2005-08-30, 11:25 am

Hi all,

I'm running an Office XP/AS2K environment. When I create a distinct count
measure in my cube then put any dimensinon on the page axis (in Excel)
selecting multiple members, my Grand Totals go to pound value.

Is this known/expected?

Thanks,

Mark

Brian Altmann

2005-08-30, 11:25 am

It is not a bug, it is a limitation of AS2K distinct count measures.
This article includes a detailed treatment of the problem and possible
solutions:

http://msdn.microsoft.com/library/d...l/distinct2.asp

HTH,

--
Brian Altmann
BI Specialist
Huddle Group S.A (www.huddle.com.ar)
www.geocities.com/brianaltmann/olap.html

"MarkFrank" wrote:

> Hi all,
>
> I'm running an Office XP/AS2K environment. When I create a distinct count
> measure in my cube then put any dimensinon on the page axis (in Excel)
> selecting multiple members, my Grand Totals go to pound value.
>
> Is this known/expected?
>
> Thanks,
>
> Mark

Deepak Puri

2005-08-30, 11:25 am

This is a known issue with AS 2000, since selecting
multiple members results in a calculated member:

http://groups.google.com/group/micr....olap/msg/ce7f0
089923ae1c9[color=da
rkred]
This is a well-known issue with the Distinct Count aggregate in AS 2000.
You can either try AS 2005 (Yukon) or apply alternate techniques, as in
MSDN paper below.

http://groups-beta.google.com/group...erver.olap/m...

Newsgroups: microsoft.public.sqlserver.ola_p
From: "Mosha Pasumansky [MS]"
Date: Wed, 30 Mar 2005 10:22:55 -0800
Subject: Re: Distinct Count in Visualtotals function

DistinctCount is non-additive, and functions such as Aggregate,
VisualTotals
etc cannot compute it over arbitrary set in AS2K. In Yukon, however,
such
functionality is implemented.

====================
==========___=======
=============
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL__og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
====================
==========___=======
=============

http://msdn.microsoft.com/library/d...y/en-us/dnsq...

Analysis Services: DISTINCT COUNT, Basket Analysis, and Solving the
Multiple Selection of Members Problem

Amir Netz
Microsoft Corporation

Updated May 18, 2004

Applies to:
Microsoft SQL Server 2000
Microsoft SQL Server 2000 Analysis Services

Summary: This article discusses ways to meet information demands and
take full advantage of Analysis Services 2000 by illustrating the use of
calculated members and multidimensional expressions (MDX).
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links