CODEWITHSUNDEEP
mdxcubecross-joinssas-2008
Pho
Pho

Reputation: 108

SSAS Cube calculated member totals wrong

I am learning on the job. I have a table of minutes spend in each bed. I want to calculate percentage utilisation of the beds. I have created the calculated member in my cube in SSAS 2008

CREATE MEMBER CURRENTCUBE.[Measures].UtilisationPercent
 AS avg( 
    crossjoin(
      descendants([bedlabel].[hierarchy].currentmember, [Bedlabel].[Bedlabel])
     ,descendants([Date].[date].currentmember, [Date].[Day])
    )
    ,[Measures].[Utilisation Mins]
   ) 
/ (24*60), 
FORMAT_STRING = "Percent", 
NON_EMPTY_BEHAVIOR = { [Utilisation Mins] }, 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Bedstay'  ;

This actually works fine if i slice by year/month or ward/bed.

But the total utilisation shown in the cube browser is wrong. How can i control the way the utilisation total is calculated? Can i create a calculated member in a way that the crossjoin will work at a higher level than leaf? 

January 2011    Utilisation %

-- added after MEZ's comment

Ok, thanks for the tip. I tried scoped assignment following this 

SCOPE([Measures].[Utilisation Percentage]);     
    SCOPE([Date].[Date].MEMBERS);     
        SCOPE([bedlabel].[hierarchy].MEMBERS);     
            THIS = [Measures].[Utilisation Mins] / 
                    (60*24
                    * count(descendants([bedlabel].[hierarchy].currentmember, [Bedlabel].[Bedlabel])) 
                    * count(descendants([Date].[date].currentmember, [Date].[Day]))) ;     
        END SCOPE;     
    END SCOPE;     
END SCOPE;

I got the same results as with the crossjoin method. I do not see a difference in AS2008 between scoped assignment result and my old crossjoin method? And it still totals up in surreal numbers that i cannot explain.

Upvotes: 0

Views: 1531

Answers (3)

Pho
Pho

Reputation: 108

This is a permutation of what Sourav_Agasti suggested and it works now. I think the avg + the nonemptycrossjoin worked better than avg + coalesceempty. 

CREATE MEMBER CURRENTCUBE.[Measures].UtilisationPercent
 AS avg( nonemptycrossjoin(
       descendants([bedlabel].[hierarchy].currentmember,[bedlabel].[hierarchy].[Bedlabel])
       ,descendants([Date].[date].currentmember,[date].[date].[day]))
       , [Measures].[Utilisation Mins]/ (24*60)), 
FORMAT_STRING = "Percent", 
NON_EMPTY_BEHAVIOR = { [Utilisation Mins] }, 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Bedstay';

This also worked, based on MEZ's suggestion. 

SCOPE([Measures].[Utilisation Percentage]);
  scope ([bedlabel].[hierarchy].members);
      scope ([date].[date].members);      
          this = avg( nonemptycrossjoin(
            descendants([bedlabel].[hierarchy].currentmember,[bedlabel].[hierarchy].[Bedlabel])
            ,descendants([Date].[date].currentmember,[date].[date].[day]))
            , [Measures].[Utilisation Mins]/ (24*60)) ;
          non_empty_behavior (this) = [Utilisation Mins] ;
      end scope;
  end scope;
END SCOPE;

The difference to AS2000 is that i only have to do this, plus specify average as the aggregation method on the calculated measure. I also didn't have multiple hierarchy. The syntax seems much simpler to me. 

avg(nonemptycrossjoin(descendants([Unit].currentmember,[Unit].[Bed label]),descendants([Date].currentmember,[date].[day])), [Measures].[Utilisation Min]/ (24*60)) * 100'

Thank you for all who tried to help!

Upvotes: 1

whytheq
whytheq

Reputation: 35557

Not an answer but maybe a way to dig into what is happening.

Please try adding the following measure:

CREATE MEMBER CURRENTCUBE.[Measures].UtilisationCount
 AS Count( 
    crossjoin(
      descendants([bedlabel].[hierarchy].currentmember, [Bedlabel].[Bedlabel])
     ,descendants([Date].[date].currentmember, [Date].[Day])
    )

Then in your query add this new measure, on columns, hopefully x,y,z,j,q will be some numbers - what is q?:

January 2011    UtilisationCount   Utilisation %

Bed1            x                   98.86%
Bed2            y                   88.38%
Bed3            z                   82.63%
Bed4            j                   94.67%
Total           q                   894.35%

Upvotes: 0

SouravA
SouravA

Reputation: 5243

It could be because the empty tuples are creating issues. Using CoalesceEmpty function will replace EMPTY cells with a 0. Try the code below:

CREATE MEMBER CURRENTCUBE.[Measures].UtilisationPercent
 AS avg( crossjoin(descendants([bedlabel].[hierarchy].currentmember, [Bedlabel].[Bedlabel]), 
descendants([Date].[date].currentmember, [Date].[Day])), CoalesceEmpty([Measures].[Utilisation Mins], 0) )
/ (24*60), 
FORMAT_STRING = "Percent", 
NON_EMPTY_BEHAVIOR = { [Utilisation Mins] }, 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Bedstay'  ;

Since the AVG doesn't seem to be working, check the below script out too:

CREATE MEMBER CURRENTCUBE.[Measures].a
as 
SUM(
(descendants([bedlabel].[hierarchy].currentmember, [Bedlabel].[Bedlabel]) * descendants([Date].[date].currentmember, [Date].[Day])),
[Measures].[Utilisation Mins]),
NON_EMPTY_BEHAVIOR = { [Utilisation Mins] }, 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Bedstay'  ; 

CREATE MEMBER CURRENTCUBE.[Measures].b 
as
COUNT(
(descendants([bedlabel].[hierarchy].currentmember, [Bedlabel].[Bedlabel]) * descendants([Date].[date].currentmember, [Date].[Day]))
,EXCLUDEEMPTY
),
NON_EMPTY_BEHAVIOR = { [Utilisation Mins] }, 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Bedstay'  ; 

CREATE MEMBER CURRENTCUBE.[Measures].C
AS
[Measures].A/[Measures].B,
FORMAT_STRING = "Percent", 
NON_EMPTY_BEHAVIOR = { [Utilisation Mins] }, 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Bedstay'  ;

Upvotes: 0

Related Questions