Differing NON EMPTY behaviour in Analysis Server 2000 and 2008

0
votes

We currently run a front-end Manager Information tool that connects to a SQL analysis server to provide flexible reporting. Essentially the front-end builds up some MDX which it then executes on Analysis Services, and prettifies the result as a table.

We are currently upgrading from SQL 2000 to SQL 2008 and have come across a difference in the use of NON EMPTY in MDX.

This is the MDX query in question

SELECT NON EMPTY {[Measures].allMembers} ON ROWS, NON EMPTY {} ON COLUMNS FROM Sales

When run on Analysis Server 2000 it returns the list of Measure member names for the rows, but when run in Analysis Server 2008 nothing is returned.

Looking at the MDX I think 2008 is working as expected, and 2000 is wrong, but it has sort of broken our front-end as it is showing nothing in the case where this MDX is run (the user is then expected to drag on some columns after this you see, but with nothing showing, they can't do this).

So, is there a reason for a difference. Was this a 'bug' in 2000 that was fixed in 2008? Or is there some configuration option somewhere that can affect what is returned? (Essentially I want to avoid to having make a change to our front-end to cope with this!)

Thanks.

1
mdxolapssas

1 Answers

0
votes

It's been long enough since I've used SSAS 2000 that it doesn't feel like yesterday. I can't recall seeing a breaking change like this. However, your NON EMPTY on the columns feels spurious if the intent is to issue a query just for the members. (It's perfectly valid, I find this more robust with UIs than using the metadata objects.)

NON EMPTY becomes affected by the implicit slicers, and your cube structure may have changed somewhere. If your top/default members in all non-measures dimensions give you all NULL cell values, you'll drop the measures.

Suppose you run the same query as 

SELECT NON EMPTY {[Measures].allMembers} ON  COLUMNS FROM Sales

and

SELECT  {[Measures].allMembers} ON COLUMNS FROM Sales

in the SQL Server Management Console. Do you get values?

If you do, try to run these in the exact same environment as a user (for example, a debug version of their app). Security filters and default member settings are two things that could be leading to NULL rows correctly being filtered.