CODEWITHSUNDEEP
sql-server-2012ssasmdx
Geoff
Geoff

Reputation: 9340

Calculated SSAS Member based on multiple dimension attributes

I'm attempting to create a new Calculated Measure that is based on 2 different attributes. I can query the data directly to see that the values are there, but when I create the Calculated Member, it always returns null.

Here is what I have so far:

CREATE MEMBER CURRENTCUBE.[Measures].[Absorption]
 AS sum
(

    Filter([Expense].MEMBERS, [Expense].[Amount Category] = "OS"
           AND ([Expense].[Account Number] >= 51000 
           AND [Expense].[Account Number] < 52000))
    ,

    [Measures].[Amount - Expense]
), 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Expense';

Ultimately, I need to repeat this same pattern many times. A particular accounting "type" (Absorption, Selling & Marketing, Adminstrative, R&D, etc.) is based on a combination of the Category and a range of Account Numbers.

I've tried several combinations of Sum, Aggregate, Filter, IIF, etc. with no luck, the value is always null.

However, if I don't use Filter and just create a Tuple with 2 values, it does give me the data I'd expect, like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Absorption]
 AS sum
(

    {( [Expense].[Amount Category].&[OS], [Expense].[Account Number].&[51400]  )}
    ,

    [Measures].[Amount - Expense]
), 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Expense';

But, I need to specify multiple account numbers, not just one.

Upvotes: 2

Views: 11671

Answers (2)

whytheq
whytheq

Reputation: 35605

I don't go near cube scripts but do you not need to create some context via the currentmember function and also return some values for correct evaluation against the inequality operators (e.g.>) via the use of say the membervalue function ? 

CREATE MEMBER CURRENTCUBE.[Measures].[Absorption]
 AS sum
(
    [Expense].[Amount Category].&[OS]
    *
    Filter(
       [Expense].[Account Number].MEMBERS,
       [Expense].[Account Number].currentmember.membervalue >= 51000 
       AND 
       [Expense].[Account Number].currentmember.membervalue < 52000
    )
    ,

    [Measures].[Amount - Expense]
), 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Expense';

EDIT

Dan has used the range operator :. Please make sure your hierarchy is ordered correctly and that the members you use with this operator actually exist. If they do not exist then they will be evaluated as null:

Against the AdvWks cube:

SELECT 
  {} ON 0
 ,{
      [Date].[Calendar].[Month].&[2008]&[4]
    : 
      [Date].[Calendar].[Month].&[2009]&[2]
  } ON 1
FROM [Adventure Works];

Returns the following:

enter image description here

If the left hand member does not exist in the cube then it is evaluated as null and therefore open ended on that side:

SELECT 
  {} ON 0
 ,{
      [Date].[Calendar].[Month].&[2008]&[4]
    : 
      [Date].[Calendar].[Month].&[1066]&[2] //<<year 1066 obviously not in our cube
  } ON 1
FROM [Adventure Works];

Returns:

enter image description here

Upvotes: 1

Dan
Dan

Reputation: 10710

In general, you should only use the FILTER function when you need to filter your fact table based on the value of some measure (for instance, all Sales Orders where Sales Amount > 10.000). It is not intended to filter members based on dimension properties (although it could probably work, but the performance would likely suffer).

If you want to filter by members of one or more dimension attributes, use tuples and sets to express the filtering:

CREATE MEMBER CURRENTCUBE.[Measures].[Absorption]
AS 
    Sum( 
       {[Expense].[Account Number].&[51000]:[Expense].[Account Number].&[52000].lag(1)} *
       [Expense].[Amount Category].&[OS],
       [Measures].[Amount - Expense]
    ), 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Expense';

Here, I've used the range operator : to construct a set consisting of all [Account Number] members greater than or equal to 51000 and less than 52000. I then cross-join * this set with the relevant [Amount Category] attribute, to get the relevant set of members that I want to sum my measure over.

Note that this only works if you actually have a member with the account number 51000 and 52000 in your Expense dimension (see comments).

An entirely different approach, would be to perform this logic in your ETL process. For example you could have a table of account-number ranges that map to a particular accounting type (Absorption, Selling & Marketing, etc.). You could then add a new attribute to your Expense-dimension, holding the accounting type for each account, and populate it using dynamic SQL and the aforementioned mapping table.

Upvotes: 3

Related Questions