PDA

View Full Version : Re : [Mondrian] NULL literal



michael bienstein
02-28-2007, 06:11 AM
My understanding is as follows:
NULL as a value (not literal) is for Members that do not exist physically but do logically such as [Geography].[All Geography].Parent. When you pull a cell value for which one member of the context is NULL, you get a NULL value as a numeric (or String etc) for the cell value as opposed to an EMPTY value which is when the membes all exist but there is no value in the cube at that position.

After that there's lots of tricky things you can do with numeric NULL values like addition, multiplication etc E.g. A is a numeric expression that evaluates to NULL and B is a numeric expression that doesn't. What does A+B evaluate to?

As for the 'IS NULL' construct, that's to test Member expression and numeric expression for having the NULL value given that NULL=NULL yields false. You can also use NULL explicitly in Range function:
[Time].[1997].FirstChild : NULL

Here's an example of an MDX query that should test a problem (not tested anywhere):

WITH MEMBER [Time].[1997].[Test] AS '[Time].[1997]+0'
SELECT
{[Time].[1997], [Time].[1997].[Test]} on COLUMNS,
{[Store].[All Stores].Parent} on ROWS
FROM [Warehouse Sales]

Michael

PS: I've got code for running multiple tasks going but without test cases. Found some time. Taking longer than expected. Will post by next week but don't want to disturb your efforts to release.











___________________________________________________________________________
D

Richard Emberson
02-28-2007, 12:51 PM
I've never seen the MDX specification - I think that
whatever MS does is the spec. Anyway,
Mosha Pasumansky is the author(??) of MDX and
from a couple of his web pages I find:

"First, it notices, that for A*B will be NULL
if either A or B is NULL."
- half way down the page
http://www.mosha.com/msolap/articles/multiplication_perf.htm


"and NULL/NULL is defined as NULL in MDX."
1/8 of the way down the page
http://www.feedshow.com/show_items-feed=fdef23d0a3fde71fe9631652cff7ea79

So I assume that for expressions like a+b,a-b,a*b,a/b if either
a or b are NULL then:
NULL+b == b
a + NULL == a
NULL - b == -b
a - NULL == a
a*NULL == NULL
NULL*b == NULL
a/NULL == NULL
NULL/b == NULL
and if both are NULL then the result is NULL.

The NULL Literal when compiled become an ConstantCalc
instance.
The change I believe is needed is in the ConstantCalc
constructor. If the constructor parameter 'o' which
is of type Object is a Java null, then the instance
variables have the values:

Object o = null;
int i = FunUtil.IntegerNull
double d = FunUtil.DoubleNull

Richard


FYI

For Empty I've found this page:
http://msdn2.microsoft.com/en-us/library/aa216761(SQL.80).aspx

specifically:
"When the empty cell value is an operand for any of the numeric
operators (+, -, *, /), it behaves like the number zero."

michael bienstein wrote:[color=blue]
> My understanding is as follows:
> NULL as a value (not literal) is for Members that do not exist
> physically but do logically such as [Geography].[All Geography].Parent.
> When you pull a cell value for which one member of the context is NULL,
> you get a NULL value as a numeric (or String etc) for the cell value as
> opposed to an EMPTY value which is when the membes all exist but there
> is no value in the cube at that position.
>
> After that there's lots of tricky things you can do with numeric NULL
> values like addition, multiplication etc E.g. A is a numeric expression
> that evaluates to NULL and B is a numeric expression that doesn't. What
> does A+B evaluate to?
>
> As for the 'IS NULL' construct, that's to test Member expression and
> numeric expression for having the NULL value given that NULL=NULL yields
> false. You can also use NULL explicitly in Range function:
> [Time].[1997].FirstChild : NULL
>
> Here's an example of an MDX query that should test a problem (not tested
> anywhere):
>
> WITH MEMBER [Time].[1997].[Test] AS '[Time].[1997]+0'
> SELECT
> {[Time].[1997], [Time].[1997].[Test]} on COLUMNS,
> {[Store].[All Stores].Parent} on ROWS
> FROM [Warehouse Sales]
>
> Michael
>
> PS: I've got code for running multiple tasks going but without test
> cases. Found some time. Taking longer than expected. Will post by
> next week but don't want to disturb your efforts to release.
>
>
>
>
> ------------------------------------------------------------------------
> D