Details

Type: Improvement

Status: Open

Priority: Severe

Resolution: Unresolved

Affects Version/s: None

Fix Version/s: Not Planned

Component/s: None

Labels:None
Description
The IS EMPTY and IS NULL operators are not implemented.
Mondrian already has the IsEmpty(<expr>) operator, but
its behavior is probably not correct.
First, we need to implement IS EMPTY and IS NULL operators.
Then, we need to take Michael's examples below, turn
them into testcases, and make the testcases work.
==================================
Michael Bienstein wrote:
Julian,
On NULL vs EMPTY, my take after reading William
Pearson's articles on databasejournal.com was that NULL
is for metadata objects and EMPTY is for a cell value.
E.g. "[Time].[All Time].Parent IS NULL" will evaluate
to true.
Any functions that return a member from parameters that
include a member and that member is NULL also give a
NULL.
E.g. "[Time].CurrentMember.Parent.NextMember" when
applied to [Time].[All Time] will return NULL as well
and will NOT cause a run time exception.
When resolving a tuple's value in the cube, if there is
at least one NULL member in the tuple should return a
NULL cell value.
E.g. "([Time].currentMember.Parent,[Measures].[Sales])"
when resolved in the context where [Time].CurrentMember
is [Time].[All Time] should resolve to NULL as a value.
EMPTY refers to a genuine cell value that exists in the
cube space but that has no value in the underlying data
source.
E.g. "([Product].[All Products].[Ski Boots],
[Geography].[All Geography].[Hawaii])" has no NULL
members in the tuple but has no fact data at that
crossing, so it evaluates to EMPTY as a cell value.
The empty set is neither EMPTY nor NULL.
E.g.
"WITH SET [empty set] AS '{}'
WITH MEMBER [Measures].[Set Size] AS 'Count([empty set])'
SELECT [Measures].[Set Size] on columns"
should give 0 as a result, not NULL and not EMPTY.
Run time errors are BAD things. They should not occur
in almost all cases. In fact there should be no
logically formed MDX that generates them. An ERROR
value in a cell though is perfectly legal  e.g. a
divide by 0.
E.g.
"WITH [Measures].[Ratio This Period to Previous] as
'([Measures].[Sales],[Time].CurrentMember/([Measures].[Sales],[Time].CurrentMember.PrevMember)'
SELECT [Measures].[Ratio This Period to Previous] ON
COLUMNS,
[Time].Members ON ROWS
FROM ..."
For the [Time].[All Time] row as well as the first
year, first month etc, the PrevMember will evaluate to
NULL, the tuple will evaluate to NULL and the division
will implicitly convert the NULL to 0 and then evaluate
to an ERROR value due to a divide by 0.
This leads to another point: NULL and EMPTY values get
implicitly converted to 0 when treated as numeric
values for division and multiplication but for addition
and subtraction, NULL is treated as NULL (5+NULL yields
NULL).
I have no idea about how EMPTY works. I.e. is does
5+EMPTY yield 5 or EMPTY or NULL or what?
E.g.
"WITH MEMBER [Measures].[5 plus empty] AS
'5+([Product].[All Products].[Ski
boots],[Geography].[All Geography].[Hawaii])'
SELECT [Measures].[5 plus empty] ON COLUMNS
FROM ..."
Does this yield EMPTY, 5, NULL or ERROR?
Lastly, IS NULL and IS EMPTY are both legal and
distinct. <<Object>> IS {<<Object>>  NULL} and
<<Value>> IS EMPTY.
E.g.
a) [Time].CurrentMember.Parent IS [Time].[Year].[2004]
is also a perfectly legal expression and better than
[Time].CurrentMember.Parent.Name="2004".
b) ([Measures].[Sales],[Time].FirstSibling) IS EMPTY is
a legal expression.
Michael
Mondrian already has the IsEmpty(<expr>) operator, but
its behavior is probably not correct.
First, we need to implement IS EMPTY and IS NULL operators.
Then, we need to take Michael's examples below, turn
them into testcases, and make the testcases work.
==================================
Michael Bienstein wrote:
Julian,
On NULL vs EMPTY, my take after reading William
Pearson's articles on databasejournal.com was that NULL
is for metadata objects and EMPTY is for a cell value.
E.g. "[Time].[All Time].Parent IS NULL" will evaluate
to true.
Any functions that return a member from parameters that
include a member and that member is NULL also give a
NULL.
E.g. "[Time].CurrentMember.Parent.NextMember" when
applied to [Time].[All Time] will return NULL as well
and will NOT cause a run time exception.
When resolving a tuple's value in the cube, if there is
at least one NULL member in the tuple should return a
NULL cell value.
E.g. "([Time].currentMember.Parent,[Measures].[Sales])"
when resolved in the context where [Time].CurrentMember
is [Time].[All Time] should resolve to NULL as a value.
EMPTY refers to a genuine cell value that exists in the
cube space but that has no value in the underlying data
source.
E.g. "([Product].[All Products].[Ski Boots],
[Geography].[All Geography].[Hawaii])" has no NULL
members in the tuple but has no fact data at that
crossing, so it evaluates to EMPTY as a cell value.
The empty set is neither EMPTY nor NULL.
E.g.
"WITH SET [empty set] AS '{}'
WITH MEMBER [Measures].[Set Size] AS 'Count([empty set])'
SELECT [Measures].[Set Size] on columns"
should give 0 as a result, not NULL and not EMPTY.
Run time errors are BAD things. They should not occur
in almost all cases. In fact there should be no
logically formed MDX that generates them. An ERROR
value in a cell though is perfectly legal  e.g. a
divide by 0.
E.g.
"WITH [Measures].[Ratio This Period to Previous] as
'([Measures].[Sales],[Time].CurrentMember/([Measures].[Sales],[Time].CurrentMember.PrevMember)'
SELECT [Measures].[Ratio This Period to Previous] ON
COLUMNS,
[Time].Members ON ROWS
FROM ..."
For the [Time].[All Time] row as well as the first
year, first month etc, the PrevMember will evaluate to
NULL, the tuple will evaluate to NULL and the division
will implicitly convert the NULL to 0 and then evaluate
to an ERROR value due to a divide by 0.
This leads to another point: NULL and EMPTY values get
implicitly converted to 0 when treated as numeric
values for division and multiplication but for addition
and subtraction, NULL is treated as NULL (5+NULL yields
NULL).
I have no idea about how EMPTY works. I.e. is does
5+EMPTY yield 5 or EMPTY or NULL or what?
E.g.
"WITH MEMBER [Measures].[5 plus empty] AS
'5+([Product].[All Products].[Ski
boots],[Geography].[All Geography].[Hawaii])'
SELECT [Measures].[5 plus empty] ON COLUMNS
FROM ..."
Does this yield EMPTY, 5, NULL or ERROR?
Lastly, IS NULL and IS EMPTY are both legal and
distinct. <<Object>> IS {<<Object>>  NULL} and
<<Value>> IS EMPTY.
E.g.
a) [Time].CurrentMember.Parent IS [Time].[Year].[2004]
is also a perfectly legal expression and better than
[Time].CurrentMember.Parent.Name="2004".
b) ([Measures].[Sales],[Time].FirstSibling) IS EMPTY is
a legal expression.
Michael
{jhyde}, 09/04/2006: assigned_to, 100 
{mkambol}, 11/09/2006: status_id, 1 
{mkambol}, 11/09/2006: close_date, 0 
{jhyde}, 11/13/2006: status_id, 2 
{jhyde}, 11/13/2006: priority, 7 
{jhyde}, 11/13/2006: assigned_to, 1479052 
{jhyde}, 11/13/2006: close_date, 1163102213