Hi Everybody:

I have a very interesting problem. Let me explain it with the FoodMart
data model.

Let's imagine that in the cube Sales, we want to add a dimension
"regular clients" that will have two members labelled: regular and
irregular. The former is related to the clients that buy something
every year and the latter groups the rest of the clients.

And now the problem appears, because amounts related to regular clients
should change according to the selection chosen in the cube. For
example, if one selects three years 1995 and 1996, 1997 and regular
clients member, the measures values (for example unit sold) ought to
reflects only the clients that buy anything in these 3 years (1995,
1996, 1997).

In other words, the measures values for a specific year will change
depending on other years that are selected. It means that this values
must be calculated dynamically.

I thought about calculated members, but the problem is that I do not
know how to find out on the mdx expression level which years are
selected.

Two solutions come to my mind:

1. To provide "regular client logic" at the client application
level.
2. To use ROLAP instead of MOLAP

Obviously, these solutions does not satisfy me.

Any Ideas?

Thanks in advance

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:

'{[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 ***

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

Maciej,

There are a few ways to access the MDX that ProClarity creates that
might make this easier for you.

The first is the ProClarity MDX editor, while it is probably not
appropriate for your current problem, it does allow a user to imput MDX
directly for a query. It is accessed form the [View] menu item in the
Profesional Clients.

The second is from the KCommand object that is easily accessed using an
addin with VBA. The KCommand has a property named finalMDX that will
give you access to the MDX that will be passed to the OLAP provider.

You can check exactly what query is being run by ProClarity Desktop
Professional client by searching for the query.log file.

I would strongly recommend calling ProClarity technical support or
visiting the ProClarity Best Practices Community where there is sample
code and forum discussing how to do exactly these sort of
modifications.

Also, ProClarity does sell a product called KPI Designer that may be
able to generate the query you are referring to without any coding at
all.

Good Luck!


mkiewra (AT) mail (DOT) fujitsu.es wrote:

One way of deducing the dimension members used in an MDX query is via
the Axis() sets along the query axes. But this works when you have an
idea about which Axis a dimension is located on, so it may or may not be
flexible enough.

As an example of this approach, I've modified the original query so that
the [TimeSet] of selected months is now derived from Axis(1) of the
query (assuming that Time is on rows). The same 3 months are now
directly placed on rows, not via a Named Set. Unfortunately, this query
isn't optimized, so it took very long to return results:

Set [TimeSet] as
'Extract(StrtoSet("Axis(1)"), [Time])'
Member [Customers].[All Customers].[Regular] as
'Aggregate(Filter([Customers].[Name].Members,
Count(NonEmptyCrossJoin([TimeSet],
{[Customers].CurrentMember}, {[Measures].[Unit Sales]}, 1))
= Count([TimeSet])))'
Member [Customers].[All Customers].[Irregular] as
'Aggregate(Filter([Customers].[Name].Members,
Count(NonEmptyCrossJoin([TimeSet],
{[Customers].CurrentMember}, {[Measures].[Unit Sales]}, 1))
< Count([TimeSet])))'

Select [Measures].Members on columns,
CrossJoin({[Time].[1997].[Q1].[1], [Time].[1997].[Q2].[5],
[Time].[1997].[Q3].[9]},
{[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 ***

Jason,

Thank you for your response. You are right the first solution is not
appropriate
for me.

I will try the second solution and I have an additional question: is it
possible to modify the mdx query from the VBA level?

Regards,
Maciej Kiewra


jasonaeiou (AT) yahoo (DOT) com ha escrito:

Yes, you can modify the MDX from the VBA level. To access the KCommand
object, use your client's access to ProClarity Central (online
community for all ProClarity clients) and download the ProClarity
Platform Developer's Guide. There you will find the add-in you need,
sample code showing how to get to the KCommand object in VBA..

I am certain that ProClarity support can also help you access the
KCommand object. You may want to try them out. If you still aren't
having any luck, let me know.

- Jason