Re: How to find out which members are selected from the mdx query level

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello again:

I'm afraid that I did not express myself clearly.
The problem is that I cannot assume the selected time periods are known
nor
construct the query, because my client works with ProClarity.

ProClarity permits the user to select members in each dimension and to
specify the dimension location (row, column or slice). Once this
information is obtained, the ProClarity builds the MDX query that is
sent to the OLAP server. The server response is read by ProClarity and
presented to the user as a data matrix or diagram. It means that I
cannot change MDX query built by the ProClarity Software. The only
thing I can do is to provide calculated members and hide measures.

The idea I had was to hide cube measures (for example units) and to
define calculated members (for example units2). This measure should
behave in the following way:

iif([RegularClients].CurrentMember.Name<>'Regular',
[Measures].[Units],
([Measure].[Units],[Store].CurrentMember,...,[Customer].[RegularCalculatedMember]))

where

[Customer].[RegularCalculatedMember] ought to contain definition
similar to the expression given by Deepak:

'Aggregate(Filter([Customers].[Name].Members,
Count(NonEmptyCrossJoin([SelectedMonths],
{[Customers].CurrentMember}, {[Measures].[Unit Sales]}, 1))
= Count([SelectedMonths])))'

BUT THE PROBLEM IS: how to obtain the set: [SelectedMonths]. This set
(or rather its equivalent) is hidden among the MDX query generated by
the ProClarity, but I do not know how I can access it from the
calculated member definition.

Obviously, if I could replace ProClarity with the application I wrote,
the solution given by Deepak would fit ideally.


I hope that this explanation is clear enough.

Regards,

Maciej Kiewra


> Assuming that the selected time periods are defined in the
> [SelectedMonths] Named Set, this Foodmart Sales query defines [Regular]
> and [Irregular] Customer calculated members, based on fact records
> existing in all periods:
>
> >>
> With Set [SelectedMonths] as
> '{[Time].[1997].[Q1].[1], [Time].[1997].[Q2].[5],
> [Time].[1997].[Q3].[9]}'
> Member [Customers].[All Customers].[Regular] as
> 'Aggregate(Filter([Customers].[Name].Members,
> Count(NonEmptyCrossJoin([SelectedMonths],
> {[Customers].CurrentMember}, {[Measures].[Unit Sales]}, 1))
> = Count([SelectedMonths])))'
> Member [Customers].[All Customers].[Irregular] as
> 'Aggregate(Filter([Customers].[Name].Members,
> Count(NonEmptyCrossJoin([SelectedMonths],
> {[Customers].CurrentMember}, {[Measures].[Unit Sales]}, 1))
> < Count([SelectedMonths])))'
>
> Select [Measures].Members on columns,
> {[Customers].[All Customers].[Regular],
> [Customers].[All Customers].[Irregular]} on rows
> from Sales
> >>
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex.com ***

.

Relevant Pages

  • Re: long running queries
    ... > Analysis services or through Proclarity? ... > that would result in a large data set and complex query, ... manage your user community, give me a shout offline if you want some more ... Some of the client tools will warn you if the query looks like its going to ...
    (microsoft.public.sqlserver.olap)
  • Re: How to find out which members are selected from the mdx query level
    ... The first is the ProClarity MDX editor, ... You can check exactly what query is being run by ProClarity Desktop ... > the solution given by Deepak would fit ideally. ...
    (microsoft.public.sqlserver.olap)
  • Re: Exception of type System.OutOfMemoryException was thrown when running a large query - MDX
    ... Since this query looks like it's generated by Proclarity, ... limited to Proclarity-generated (vs. hand-crafted queries) for this ... calculated - in the latter case, what kind of calculations are involved? ...
    (microsoft.public.sqlserver.olap)