I'm trying to create a calculated measures member that will provide a minimum
value per day.

Easy enough; but a large number of cells have -1 in them, and where this is
the case, I need to take the next smallest number instead. I've tried getting
the Filter() function to get rid of the cells with -1, but don't seem to
quite get the results I need.

Any advice?

Will a base measure with "Minimum" aggregation function work? The source
column for this measure can be a SQL case statement like (in case you
don't use a view):

else myfact.minfield end

- Deepak

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

Hi Deepak, thanks for the reply.

This was the first thing I tried - I used a case statement in my view to
substitute Nulls in the rows that had a -1 value. The Min() aggregation
method happily casts this to a 0 however. Leaving me in much the same
position as before >:-[

I think I really need to get the Filter function working in the measure
somehow.

I'll post if I get a result that works.

Peter

"Deepak Puri" wrote:

Hi Peter,

Many of the built-in cube aggregation functions treat a null as 0 - this
is something that you should be aware of in cube design. That is why I
suggested substituting -1 with a large number (like 1000), so that it
would never get selected as the minimum.

You can certainly pursue Filter() further, but performance will be
better using built-in aggregation functions.


Here's a Microsoft support INF on nulls in cubes:

http://support.microsoft.com/default...b;en-us;244650
Article ID : 244650
Last Review : February 24, 2004
Revision : 1.0

This article was previously published under Q244650

SUMMARY

A NULL value, in OLAP Services, represents an empty cell. In relational
products such as Microsoft SQL Server 7.0 or later, a NULL value
represents an unknown value. In online analytical processing (OLAP) a
NULL value means that we know the cell is empty.

For calculations, OLAP Services treats NULLs as a "0", unless the
expression involves only NULLs, in which case the result is a NULL.
...

- Deepak

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

<lightbulb goes on above head>

Hi Deepak,

So *that's* what you were suggesting! I'm afraid that looking at this for
too long has rather dulled my senses and I completely missed your point.

As an MDX newbie, I can see that there's a good deal I need to understand
better - I think that using Filter() function is going nowhere due to it
returning a set, and I can't work out whether I can create a measures member
with what it will give me...

Thinking about your suggestion, my initial reaction was that it wouldn't
work, as I also derive a "daily high" measure from the same column - but of
course, if I modify the view, then the measures could come from separate
columns! Genius!

I'll read the article you linked to - and try the "big number" solution
tonight.

Many thanks for your help,

Peter

"Deepak Puri" wrote:

Peter,

Keep in mind that you don't have to add a new column to your fact table
view - instead, you can use a SQL expression in the Measure's "Source
Column":

http://groups-beta.google.com/group/...rver.olap/msg/
a01187d2b535191e?dmode=source
References: <DEB7B79A-EA6A-43F5-A138-DC2ABEBB8776 (AT) microsoft (DOT) com>
X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
Subject: RE: "Simple" calculation
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <u80qGBS0EHA.1308 (AT) TK2MSFTNGP09 (DOT) phx.gbl>
Newsgroups: microsoft.public.sqlserver.olap
Date: Mon, 22 Nov 2004 21:50:01 -0800
NNTP-Posting-Host: 67.41.129.85
Lines: 1

Defining a SQL view with a calculated column like "sale" should work -
this column would drive a cube "sum" measure.

A similar result can be achieved, without a new view, by entering a SQL
formula, like "price * quantity", in the "Source Column" property of the
cube measure. This is discussed as a "derived measure" in the article
below:

http://www.databasejournal.com/featu...10894_3394681_
2
Unlike calculated members (including, of course, calculated measures),
whose values are created at runtime, based upon the MDX expression(s)
they contain, a derived measure, just as any other cube measure, is
calculated during the cube processing cycle, and is stored within
Analysis Services. In the case of the calculated member, only its
definition is stored, and the values it renders are calculated in memory
when specified in a query. Because only the definition is stored, cube
processing time ("build time") is not materially increased, and, indeed,
depending upon the environment, this might be an appropriate tradeoff.
In our present scenario, however, query processing is the more important
concern, so we lose the primary benefit behind the choice of a
calculated measure to provide the needed values.

Derived measures differ from "ordinary" measures because they take
advantage of the flexibility that Analysis Services offers us in
modifying the source column property for a given measure. Because they
are stored in the cube file, as we have mentioned, they typically mean
more efficient query processing. Derived measures, by their nature, are
calculated prior to the creation of aggregations. (In contrast,
calculated measures are calculated after aggregations are created.) In
general, derived measures make sense if they will be called upon
frequently, as in reporting scenarios such as that of our hypothetical
information consumers. Calculated measures might be a better choice for
values that we expect to be used infrequently, again because the
tradeoff is tipped more in favor of a lower overhead on the cube
processing side, where query response time might not be as high a
priority.

We are limited to creating derived measures from the columns of the fact
table, since MSAS essentially only offers these columns as options in
the measure creation process. However, as many of us have found, a view
can be created to contain columns that lie outside the physical fact
table, making this limitation a bit less restrictive than it might
appear at first blush. Derived measures can extend well beyond simple
math, as we shall see in our example, and can apply conditional logic
and other nuances in derivation of the measure. The syntax obviously has
to fit the database
..

- Deepak

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

Ah yes, well it works a treat. I made the measure private and built a
calculated member which replaced the leaf-level "999999" values with nulls (I
haven't read the Null article yet!).

Thanks once again for your help. 60 million-odd records, five years data,
eight measures, fairly ordinary hardware and the response time is never more
than a few seconds. Astonishing stuff!

Regards,

Peter

"Deepak Puri" wrote: