MDX Query creating distinct count member

[RKru]

Hi,
I've been struggling with what I think should be a fairly easy MDX
query to build.
I have a cube containing two dimensions:
[Segment]
-Segment1
-segment2
-Segment3
-Segment4

[Flight Month] (not a time dimension)
-Jan 2002
-Feb 2002
-Mar 2002
-Apr 2002
-May 2002
etc

and Measure
[Distinct Company] which is a distinct count of company_id

I have an application that allows the user to specify which months
they would like to view data for and then use this to build the MDX
query. So if the user selects Jan 2002, Feb 2002, March 2002 then I
want to show the distinct companies flying in these months by segment.

I want to try and create a member that will contain only the selected
months. Something like:

with member [Flight Month].[Include Months] as 'Sum({[Flight
Month].[Month Name].[Oct 2003], [Flight Month].[Month Name].[Nov
2003], [Flight Month].[Month Name].[Dec 2003]})'

select {[Flight Month].[Include Months]} on columns,
{[Segment].[All Segment].[Segment1], [Segment].[All
Segment].[Segment2],[Segment].[All Segment].[Segment3], [Segment].[All
Segment].[Segment4]} on rows
from Flight_BA
where ([Measures].[Distinct Company])

The problem with this is that I can't Sum a distinct count because I
will be counting the same companies more than once. Wrong results.

Can anyone point me in the right direction? I need a distinct count of
companies over all the months specified.

Any help greatly appreciated

rolff

[Deepak Puri]

This is a well-known limitation of the Distinct Count aggregation, that
will be removed in SQL Server 2005.

There are some possible approaches that avoid using a Distinct Count
Measure. For your case, assuming that the number of distinct company_id
isn't prohibitively large, you can create a hidden dimension like
[Company], with a [CompanyID] leaf level. This joins to the company_id
field of the fact table. Then, build a Named Set from the user-specified
months, and use it to compute [Distinct Company]:

>>
With Set [Include Months] as
'{[Flight Month].[Month Name].[Oct 2003],

[Flight Month].[Month Name].[Nov 2003],

[Flight Month].[Month Name].[Dec 2003]}'
Member [Measures].[Distinct Company] as
'NonEmptyCrossJoin([Company].[CompanyID].Members,
{[Segment].CurrentMember},
[Include Months], 1).Count'

Select {[Measures].[Distinct Company]} on columns,

{[Segment].[All Segment].[Segment1],
[Segment].[All Segment].[Segment2],
[Segment].[All Segment].[Segment3],
[Segment].[All Segment].[Segment4]} on rows
from Flight_BA
>>

Here is an earlier post that describes this approach:

http://groups.google.com/groups?selm=3D6E2CC7.7C0CCF58%40dsslab.com&oe=U
TF-8&output=gplain
>>
..
The only way to get what you want is to write the query yourself, and
you can't
use DistinctCount(). Something like

NonEmptyCrossJoin (
{[Product Name].Members},
{[Time].[1997].[Q1], [Time].[1997].[Q2] },
{[Store].CurrentMember},
{[Customer].CurrentMember},
... // other dimensions' current members
,1 // return just the product dimension
).Count
..
>>

- Deepak

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

[RKru]

Deepak Puri <deepa...@progressive.com> wrote in message news:<eMF8A8UG...@TK2MSFTNGP10.phx.gbl>...

Thanks Deepak. That did the trick.

R