I have a few questions about the evaluation of a named set which is
saved on a MSAS server (MSAS 2000 SP-3).

Let's say that I have a simple cube with a single dimension and two
measures. No calculated cells, no calculated members or custom rollup
formulas or custom members - just raw data.
Then, let's say that I create a named set on the server using the
following MDX expression:

Order([Dimension1].Members, [Measures].[M1], DESC)

When I run an MDX query from a client and position the named set on one
of the axes, I see that the set is ordered by the value of M1 in
descending order. This tells me that the named set is evaluated against
the raw data, which is calculated (obtained) at calculation pass 0.

Then, let's say that I create a calculated cell. The calculated cell
parameters are:

Sub-cube: [Measures].[M1]
MDX Expression: ([Measures].[M2])
Calculation Pass: 1
Calculation Depth: 1
Solve Order: 0

This calculated cell basically masks measure M1 and replaces its values
with the values of M2.

Now, when I run the same MDX query in the client application, I see
that the named set is still ordered by the values of M1, not M2.
On the other hand, if I define a named set in the client MDX query
using the "WITH SET" construct, the set is evaluated against the values
populated by the calculated cell.

I did all this and my logical conclusion was that the server-defined
named set is evaluated only against data calculated in calculation pass
0 and since the calculated cell reports data in calculation pass 1, the
calculation cell has no impact on the named set evaluation.

Then I used CalculationCurrentPass() in the MDX expression of the named
set just to prove that my theory is correct.
However, to make things more confusing, CalculationCurrentPass()
returned 1 when it was calculated during the evaluation of the named
set's expression.

My questions are:
When exactly is a server-defined named set evaluated?
Against the data of which calculation pass?
Is there a way to make a server-defined named set use the data returned
by a calculated cell (which calculates at calculation pass 1 or
higher)?

Thanks in advance!

Vassil

The answer to your question is simple. The only thing which controls what is
evaluated when is controlled by the order of Command objects in DSO.
Therefore if CREATE SET statement is before CREATE CELL CALCULATION
statement, then CREATE SET is evaluated using pass 0. However, if CREATE SET
is after CREATE CELL CALCULATION, then it uses the higher pass. Now,
Analysis Manager UI does some reordering of commands, such that all
calculated members come together, all named sets come together, all actions
come together etc - I don't remember the exact sequence. If for your needs
this isn't satisfactory - you will have to write custom DSO code.
In AS2005 you can put statements inside MDX Script in any order you want,
and UI will never mess with it.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLog/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.
==================================================

"Vassil Kovatchev" <vassil.kovatchev (AT) gmail (DOT) com> wrote

From the AS Performance Guide:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
which provides an answer to your question as to when a named set is
evaluated. (my emphasis added below)
"Limit the Use of Complex Named Sets
Named sets are useful for sharing common definitions, such as the current
month or last eight quarters. A named set can contain a constant set of
members or an expression that resolves to a set. --** DAW begin ** Named
sets are evaluated and resolved at connection time for each client
connection, regardless of whether the client actually uses the named set in
a query. ** DAW end **

Experience has shown that simple named sets perform well, but that complex
named sets (such as an expression filtering for the top five customers) can
reduce query responsiveness, because each complex named set requires the
scanning of many cells by Analysis Services at connection time. For example,
if you have one million customers and have a named set containing an
expression that evaluates the top five customers, Analysis Services must
evaluate all one million customers to determine the top five customers for
each client that establishes a connection. This query is a very expensive
query against a large cube with many dimension members. If you have multiple
named sets, each must be resolved at connection time before the client can
begin querying.

To improve performance in this scenario, consider enumerating the top five
customers in the named set definition, rather than calculating the top five
customers with every connection. You can then periodically update the
contents of the named set by changing the named set definition (this does
not require any processing)."

Hope that helps.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Vassil Kovatchev" <vassil.kovatchev (AT) gmail (DOT) com> wrote

Mosha, thanks for the quick response.
This is exactly what I was hoping for - answer from an insider.
I will try to reorder the command statements with DSO.

Thanks again!

Vassil

Mosha Pasumansky [MS] wrote:

Follow up:

I looked at the order of the commands in the Commands collection using
DSO and these were my observations:

* The CREATE SET command was after the CREATE CELL command

* The commands were not grouped by type: I had 5 commands in this
order:
1. Calculated cell
2. Calculated member
3. Calculated member
4. Named set
5. Calculated member

I played around with the UI and I noticed that I could control the
order of the commands by simply re-creating the command object
(calculated cell, calculated member, named set, etc.). The final order
of the commands in the clsCube.Commands collection would be the
chronological order of the creation of these commands.
Additionally, I tried to put the CREATE SET before the CREATE CELL
(which doesn't make much sense, but hey, I had to try) - this didn't
help. The set would still be evaluated with data coming from
calculation pass 0. I forced the CREATE SET to be right after the
CREATE CELL - same thing.

It is important to note that for every test I would re-connect the
client to the server in order to re-evaluate the named set.

On the other hand, when I sneak the CalculationCurrentPass() function
in the named set's expression, it returns 1.
The set expression I used to inspect the calculation pass at the time
of evaluation is:
{[Dimension1].Members.Item(CalculationCurrentPass())}
Then I compare the only item in this set with the item contained in
named set defined as:
{[Dimension1].Members.Item(1)}

The items are the same, which, I believe, means that either the named
set is evaluated at calculation pass 1 or that the
CalculationCurrentPass() function does not function properly when a
named set is evaluated on the server.
Or, maybe the pass IS number 1, but the data has not been calculated
yet?

The bottom line is, this is very confusing and I still have no way to
make a server-side defined named set evaluate against the data
calculated by a server-defined calculation cell.

Any help is greatly appreciated!

Thanks,
Vassil


Vassil Kovatchev wrote:

I think that your CREATE CELL CALCULATION statements do not set passes
correctly.
Below is the sequence of statements which works:

create set sales.x1 as 'filter(country.members, [store sales] < 10)'
create cell calculation sales.cc for '(country.members)' as '1',
calculation_pass_number=2
create set sales.x2 as 'filter(country.members, [store sales] < 10)'

You will see that x1 has Canada and Mexico, because their [Store Sales] are
NULL, but x2 has all 3 countries, because cell calculation sets their Store
Sales to 1.
However, if you were to use

create set sales.x1 as 'filter(country.members, [store sales] < 10)'
create cell calculation sales.cc for '(country.members)' as '1'
create set sales.x2 as 'filter(country.members, [store sales] < 10)'

Then you would see no difference between x1 and x2.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLog/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.
==================================================