question

KaarelKivistik-6826 avatar image
0 Votes"
KaarelKivistik-6826 asked KaarelKivistik-0560 answered

Force SCOPE statement for non empty cells only

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.


sql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KaarelKivistik-6826, could below answers help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
dgosbell avatar image
2 Votes"
dgosbell answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KaarelKivistik-0560 avatar image
0 Votes"
KaarelKivistik-0560 answered KaarelKivistik-0560 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered

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]);



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KaarelKivistik-0560 avatar image
0 Votes"
KaarelKivistik-0560 answered

"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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.