Aggregation for calculated members

Question

I'll represent AW prototype of my task to make it easy to reproduce. It's not a real environment.

I want to distinct count my products these have no sells, so create calc member:

WITH
MEMBER [Measures].[Dead products count] AS
    SUM([Product].[Product].[Product].Members,
        IIF([Measures].[Sales Amount] > 0, NULL,1)
    )
    ,FORMAT_STRING = '#,0'
    ,SOLVE_ORDER = 10
SELECT
    NON EMPTY {
    {[Date].[Calendar Year].[Calendar Year].AllMembers}
    } DIMENSION PROPERTIES PARENT_UNIQUE_NAME, CHILDREN_CARDINALITY ON COLUMNS,
    {
    {[Measures].[Dead products count]}
    } DIMENSION PROPERTIES PARENT_UNIQUE_NAME, CHILDREN_CARDINALITY ON ROWS
FROM [Adventure Works]

Cool, now I want to know my measure value aggregated by two years (ie 2005-2006):

WITH
MEMBER [Measures].[Dead products count] AS
    SUM([Product].[Product].[Product].Members,
        IIF([Measures].[Sales Amount] > 0, NULL,1)
    )
    ,FORMAT_STRING = '#,0'
    ,SOLVE_ORDER = 10
MEMBER [Measures].[Dead products count 2005-2006] AS
    Aggregate({[Date].[Calendar Year].[All].&[2005],[Date].[Calendar Year].[All].&[2006]}
    ,
    [Measures].[Dead products count]
    )
    ,FORMAT_STRING = '#,0'
    ,SOLVE_ORDER = 10
SELECT
    NON EMPTY {
    {[Date].[Calendar Year].[Calendar Year].AllMembers}
    } DIMENSION PROPERTIES PARENT_UNIQUE_NAME, CHILDREN_CARDINALITY ON COLUMNS,
    {
    {[Measures].[Dead products count],
    [Measures].[Dead products count 2005-2006]}
    } DIMENSION PROPERTIES PARENT_UNIQUE_NAME, CHILDREN_CARDINALITY ON ROWS
FROM [Adventure Works]

Nope, error occurs because it can't aggregate the calculated measure. I find two others ways to solve it: create calculated set (but it works too slow for a large number of members) and VisualTotals:

WITH
MEMBER [Measures].[Dead products count] AS
    SUM([Product].[Product].[Product].Members,
        IIF([Measures].[Sales Amount] > 0, NULL,1)
    )
    ,FORMAT_STRING = '#,0'
    ,SOLVE_ORDER = 10
MEMBER [Measures].[Dead products count 2005-2006] AS
    (
        (VisualTotals
        ({[Date].[Calendar Year].[(All)] + [Date].[Calendar Year].[All].&[2005] + [Date].[Calendar Year].[All].&[2006]})
        ).Item(0)
    ,       
    [Measures].[Dead products count]
    )
    ,FORMAT_STRING = '#,0'
    ,SOLVE_ORDER = 10
SELECT
    NON EMPTY {
    {[Date].[Calendar Year].[Calendar Year].AllMembers}
    } DIMENSION PROPERTIES PARENT_UNIQUE_NAME, CHILDREN_CARDINALITY ON COLUMNS,
    {
    {[Measures].[Dead products count],
    [Measures].[Dead products count 2005-2006]}
    } DIMENSION PROPERTIES PARENT_UNIQUE_NAME, CHILDREN_CARDINALITY ON ROWS
FROM [Adventure Works]

Yep, it works! So what's my question? Just add a new similar measure:

WITH
MEMBER [Measures].[Dead products count] AS
    SUM([Product].[Product].[Product].Members,
        IIF([Measures].[Sales Amount] > 0, NULL,1)
    )
    ,FORMAT_STRING = '#,0'
    ,SOLVE_ORDER = 10
MEMBER [Measures].[Dead products count 2005-2006] AS
    (
        (VisualTotals
        ({[Date].[Calendar Year].[(All)] + [Date].[Calendar Year].[All].&[2005] + [Date].[Calendar Year].[All].&[2006]})
        ).Item(0)
    ,
    [Measures].[Dead products count]
    )
    ,FORMAT_STRING = '#,0'
    ,SOLVE_ORDER = 10
MEMBER [Measures].[Dead products count 2007-2008] AS
    (
        (VisualTotals
        ({[Date].[Calendar Year].[(All)] + [Date].[Calendar Year].[All].&[2007] + [Date].[Calendar Year].[All].&[2008]})
        ).Item(0)
    ,
    [Measures].[Dead products count]
    )
    ,FORMAT_STRING = '#,0'
    ,SOLVE_ORDER = 10
SELECT
    NON EMPTY {
    {[Date].[Calendar Year].[Calendar Year].AllMembers}
    } DIMENSION PROPERTIES PARENT_UNIQUE_NAME, CHILDREN_CARDINALITY ON COLUMNS,
    {
    {[Measures].[Dead products count],
    [Measures].[Dead products count 2005-2006],
    [Measures].[Dead products count 2007-2008]}
    } DIMENSION PROPERTIES PARENT_UNIQUE_NAME, CHILDREN_CARDINALITY ON ROWS
FROM [Adventure Works]

Is this bug or feature? I expect VisualTotals and Item(0) to return redefined All member however it doesn't work for two and more calculated measures, but one.

P.S. Please don't offer to add measure DistinctCount, because fact table contains zeroes and negative values and I don't want to manage it on the storage level for certain reasons.

Update0: after some time of reconsidering the issue I understood the following: it seems VisualTotals() recalculates [All] member once per select, meantime I recalculate it few times a select and it returns the last recalculated member or error message (depends on case). So I have an idea to add extra invisible Date dimensions to the cube for recalculations of All members within. It's tricky but it must sort it out.

Marc Polizzi Marc Polizzi · Accepted Answer · 2014-12-27T00:22:50

As soon as it is actually used, the VisualTotals() function is changing the hierarchy aggregation rules within the statement. I do not know what it means to change twice the aggregation rules and the effect is not explained in the documentation I've read.

But to count the unique products over two years, you might be able to re-use the idea mentionned in the CurrentOrdinal() function (note item() is 0-based and currentOrdinal() is 1-based).

Aggregation for calculated members

1 Answers