Force SCOPE statement for non empty cells only

Question

HI!

In CUBE SCOPE statement, how can I limit the cube space for the script below? Budget Amount 2- EDIT is a real measure that already has values for some of the dimension combinations below. I want to overwrite the value 0-->0.00000000001 for non empty cells only. Currently it is too slow to use, I am thinking it evaluates this for empty cells also. How to I use NON EMPTY, VALIDMEASURE or smt similar in this... ?

SCOPE([Measures].[Budget Amount 2 - EDIT]);
SCOPE(LEAVES([Account]));
SCOPE(LEAVES([Cost Centre]));
SCOPE(LEAVES([Financial Dimension 2]));
SCOPE(LEAVES([Financial Dimension 3]));
SCOPE(LEAVES([Financial Dimension 5]));
THIS=IIF([Measures].[Budget Amount 2 - EDIT]=0,0.000000000000001,[Measures].[Budget Amount 2 - EDIT]);
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;

Thanks,
Kaarel.

Answer 1

In MDX blanks are equal to 0 so checking for =0 will also overwrite blanks. You should explicitly check for non-empty by doing something like the following in your assignment.

THIS=IIF( NOT( ISEMPTY( [Measures].[Budget Amount 2 - EDIT] ) ) ,0.000000000000001,[Measures].[Budget Amount 2 - EDIT]);

Although this is still likely to be slow as you are crossjoining the leaves of 5 dimensions and forcing the engine to re-aggregate on the fly. So if you can - you would be better off implementing this in your ETL or in your views/DSV and taking it out of the calc script. This also feels like its a work around for some other issue, so maybe looking for an alternate solution for that would be better.

Answer 2

"No you can't scope on measure values. If you only want to overwrite 0 values then you would need to include this in your assignment"

Ok, that is clear.

Have you got any advise on how to optimise THIS= assignment further? Can ValidMeasure() be used somehow? Something to tell the engine that it can reduce the subspace for IIF statement based on [Measures].[Budget Amount 2 - EDIT] being not an empty cell.

Thanks,
Kaarel.

Answer 3

Can I force it for only those cells which have value for [Measures].[Budget Amount 2 - EDIT]?

No you can't scope on measure values. If you only want to overwrite 0 values then you would need to include this in your assignment

I also need to overwrite only where value=0 not any other values. Your MDX doesn't take that into account.

You can just add that to the IIF condition

THIS=IIF( NOT( ISEMPTY( [Measures].[Budget Amount 2 - EDIT] ) ) AND [Measures].[Budget Amount 2 - EDIT] = 0 ,0.000000000000001,[Measures].[Budget Amount 2 - EDIT]);

Answer 4

Hi!

Thanks for the answer.

"This also feels like its a work around for some other issue, so maybe looking for an alternate solution for that would be better."

The measure is a writeback partition measure for financial budget, which I'm changing from Excel writeback. This is a special case where I can't allocate weighted values for empty on 0 cells from Excel what-if-analysis. That is the reason for the whole 0--->0.000000000000001 script, so that the weighted allocation writeback would always work. Sure, I can do it in SQL/ETL, but it is cumbersome since this measure aggregates data from 2 partitions: writeback & source partition. I would need to check the aggregated results for 0.

"Although this is still likely to be slow as you are crossjoining the leaves of 5 dimensions and forcing the engine to re-aggregate on the fly"

Can I force it for only those cells which have value for [Measures].[Budget Amount 2 - EDIT]? Cube space size would be reasonable then. I also need to overwrite only where value=0 not any other values. Your MDX doesn't take that into account.

Since it is a "money/currency" type measure there are no blanks involved. Only cells with actual values: 0 or actual numbers or empty cells (which I'm trying to leave out of the SCOPE).

Thanks a lot,
Kaarel.