MS SQL Server OLAP - Distinct Count

Author

Distinct Count

sevenhills

2005-04-27, 7:23 am

Hr Cube in FoodMart 2000
-----------------------------------
[Measures].[Number of Employees] Measure has Distinct Count Aggregate
function

Query 1:-
------------
with member [Time].[Multi] as 'sum ({[Time].[1997].[Q1].[1],
[Time].[1997].[Q1].[2],
[Time].[1997].[Q1].[3],
[Time].[1997].[Q2].[4],
[Time].[1997].[Q2].[5],
[Time].[1997].[Q2].[6],
[Time].[1997].[Q3].[7],
[Time].[1997].[Q3].[8],
[Time].[1997].[Q3].[9],
[Time].[1997].[Q4].[10],
[Time].[1997].[Q4].[11],
[Time].[1997].[Q4].[12]})'
Select non empty HIERARCHIZE (DISTINCT ({
[Employees].[(All)].Members})) on rows,
Non empty {
[Measures].[Number of Employees]} on columns
From [HR]
Where ([Time].[Multi])

From the Query 1:-
I got value is 7,392, but I suppose to get value 616 how I will get 616 from
the above query

Query 2:---
----------------
with member [Time].[Multi] as 'sum ({[Time].[1997]})'
Select non empty HIERARCHIZE (DISTINCT ({
[Employees].[(All)].Members})) on rows,
non empty {
[Measures].[Number of Employees]} on columns
From [HR]
Where ([Time].[Multi])

From the Query 2 I got value 616

If I gave Aggreagate instead of sum I am getting # Err.

Deepak Puri

2005-04-27, 8:25 pm

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/msg/
cab6a5f8a03e8de1? hl=en[color=darkred]

Newsgroups: microsoft.public.sqlserver.olap
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.
====================
==========__========
============[color=d
arkred]

http://msdn.microsoft.com/library/d...y/en-us/dnsql2k
/html/distinct2.asp[color=darkred]
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.developersdex.com ***

Sponsored Links