I have a small cube (170,000 rows in the fact table, 18 dimensions) and
I need to be able to nest several dimensions on the rows, which works
perfectly well with base measures but when these are aggregated up into
more usable calculated members, the queries will not return at all.
I can for instance return
SELECT { [Measures].[Total RI Claim Accrual] } ON COLUMNS ,
NON EMPTY { { { [AccYr].[default].[Acc Yr].&[2003] } * {
[Currency].[All Currency].CHILDREN } * { DESCENDANTS(
[Reinsurer].[default].[All Reinsurer], [Reinsurer].[default].[Co Nm] )
} } } ON ROWS
FROM [cAdviceNoteSecurity]

with no problem - there is one AccYr, 3 Currency members and about 50
rows are returned with the CoNm. NB "Total RI Claim Accrual" is the
addition of 4 base members and so the Non Empty Behaviour setting will
not help here.

However, if I add another dimension, Advice ID, thus:
SELECT { [Measures].[Total RI Claim Accrual] } ON COLUMNS ,
NON EMPTY { { { [AccYr].[default].[Acc Yr].&[2003] } * {
[Currency].[All Currency].CHILDREN } *
{ DESCENDANTS( [Reinsurer].[default].[All Reinsurer],
[Reinsurer].[default].[Co Nm] ) } *
{ DESCENDANTS([Advice Id].[default].[All Advice ID], [Advice
Id].[default].[Adv Id]) } } } ON ROWS
FROM [cAdviceNoteSecurity]
then the query will not return at all - yet if I substitute the
calculated member with a base measure, it returns quite happily.

We rarely use the base members as we require aggregations of them in
varying ways, so I don't know how to get around this problem.

I have tinkered with the aggregations on the cube but it has made no
difference at all, it all seems to be linked to the calculated members.
Any suggestions will be very gratefully received.

Rachel Jones

Hi Rachel,

Could you elaborate on the following assumption:

NB "Total RI Claim Accrual" is the
addition of 4 base members and so the Non Empty Behaviour setting will
not help here.
...

With AS 2005, you can specify multiple measures for Non Empty Behavior.
Even with AS 2000, all base measures of the same physical cube will
usually have the same "Non Empty Bahavior" - this won't hold for virtual
cubes, though.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***