Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cube Claculations Expand / Collapse
Author
Message
Posted Monday, September 1, 2014 1:28 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 739, Visits: 848
SSAS 2012
Bit of a weird issue here. Created some cube calculations.

CREATE MEMBER CURRENTCUBE.[Measures].[SLl] 
AS
CASE WHEN [Measures].[R]= 0 THEN 0
ELSE ([Measures].[I]/[Measures].[R] )
END,
FORMAT_STRING = "00.00%",
VISIBLE = 01 , ASSOCIATED_MEASURE_GROUP = 'LS' ;

for some reason when I have the dimension Product on rows then the Measures I and R may be null (no fact records) , but there is a row for the Product dimension member.

I have tried this In cube browser and Excel 2007. I feel like I am missing something here. When I have done this previously (too many years working in SSIS and not enough SSAS) this work perfectly.

What am I missing.
Cheers
E
Post #1609259
Posted Monday, September 1, 2014 1:30 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 739, Visits: 848
Should say I know this can be filtered out when querying but I want it to be right in the cube in the first place.

Thanks
E
Post #1609261
Posted Monday, September 1, 2014 2:28 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 634, Visits: 1,211
A few things could be causing this....

Firstly your code:
CASE WHEN [Measures].[R]= 0 THEN 0

This returns a zero rather than a NULL so any NON EMPTY precedents in the queries (these are there by default in the cube browser and Excel) will not work as expected. This may be causing your issue.

Secondly the form of your code is not considered the best in terms of performance. Try:
IIF( [Measures].[R]=0, NULL, [Measures].[I]/[Measures].[R] )

The reasons for this are discussed here: http://sqlblog.com/blogs/mosha/archive/2007/01/28/performance-of-iif-function-in-mdx.aspx





I'm on LinkedIn
Post #1609273
Posted Monday, September 1, 2014 2:44 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 739, Visits: 848
Many thanks,
I have been playing with this for a little this morning and have changed it so that if [Measures].[R] = 0 THEN [Measures].[R]

Not happy with that but it does the trick for removing the rows that should be NULLS. Just having a look at the IIF statement link.

Thanks
E
Post #1609283
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse