Hi,

How can I specify "Non empty behaviour" of calculated member if it is
cession or query scope CM?
Or I schoult write always IIF(<NonEmptyBehaviourMember> is null, null, <CM
Expression>)?

Thankth,

Vladimir Chtepa

For example, add Non_Empty_Behavior = '[Measures].[Store Sales]' after the
definition of the calculated member.

Ohjoo Kwon



"Vladimir Chtepa" <vc.clean_this (AT) diacom-systemhaus (DOT) clean_this.de> wrote in
message news:OR$1bAkJFHA.2936 (AT) TK2MSFTNGP15 (DOT) phx.gbl...

Hi,

Non_Empty_Behavior = '[Measures].<SelectedMeasure>'

Could you tell me please, why these expressions produce not equal result?

member [Measures].[MyMember] as '1', Non_Empty_Behavior = '[Measures].[Unit
Sales]'
and
member [Measures].[MyMember] as 'IIF(IsEmpty([Measures].[Unit Sales]), NULL,
1)'

Thanks,

Vladimir Chtepa

"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> schrieb im Newsbeitrag
news:#ozaNfkJFHA.2980 (AT) TK2MSFTNGP10 (DOT) phx.gbl...

Hi Vladimir,

I think that the short answer is: 'Non_Empty_Behavior' is only a
performance hint, that may or may not be used. This post by Chris Webb
takes a peek behind the scenes:


http://groups-beta.google.com/group/...rver.olap/msg/
564b42dd3c403588
Take the following example:

WITH MEMBER MEASURES.DEMO AS 'IIF(MEASURES.[UNIT SALES]>25000, 1,
NULL)',
NON_EMPTY_BEHAVIOR='[MEASURES]*.[UNIT SALES]'
SELECT
{MEASURES.[DEMO]}
ON 0,
NON EMPTY
CROSSJOIN(
[Product].[Product Family].MEMBERS, GENDER.GENDER.MEMBERS) ON 1
FROM SALES


You would expect it to simply assume that since Unit Sales is not null
for
this set of tuples, Measures.Demo will not be null (even though this is
not
actually true), and so in turn empty cells should appear despite the NON
EMPTY clause. However, if you run it on Foodmart 2000, you'll see that
even
though I've lied about the NON_EMPTY_BEHAVIOR of this calculated
measure,
Analysis Services hasn't believed me and has still filtered out the
empty
cells.

The key to getting it to believe my lie is the NON EMPTY THRESHOLD
connection string property (default value=5000). This is the lower
threshold
for the number of tuples(?) that must be evaluated for Analysis Services
to
decide that it is worth the effort of using the new algorithm; in the
above
example it's still using the old algorithm because the query is so
small.
So, if you rerun the above query with NON EMPTY THRESHOLD=1 then you'll
see
that the result set has changed, and it has now believed my lie: empty
cells
appear in the result set. The same thing happens with values up to 5,
but as
soon as you use NON EMPTY THRESHOLD=6 you'll see that it has gone back
to
the old algorithm and empty tuples are filtered again.

NB as with all this semi-documented stuff, it should be used at your own
risk.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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