MDX: generate 2 members 'old clients' & &#

问题:

First of all, I need to split all clients in 2 categories:

• 'new client' - came in 2015 year
• 'old client' - came in 2000-2014 years

To do this - I push them into unrelated dimension 'Calendar'. And it works fine (while pushing them to original [Client] dimension - would raize error, I've checked it). But then, after these calculated members are raized, I fail to get sales stats for them. Here is my code:

WITH     
MEMBER [Calendar].[Year].[new clients] AS ([Calendar].[All], {[Client].[Year come].&[2015-01-01]})
MEMBER [Calendar].[Year].[old clients] AS ([Calendar].[All], {[Client].[Year come].&[2000-01-01]:
                                                              [Client].[Year come].&[2014-01-01]})

SET [Client type] AS {[Calendar].[Year].[new clients], 
                      [Calendar].[Year].[old clients]}

SELECT [Measures].[Sales] ON COLUMNS,   
NON EMPTY [Client type] ON ROWS 

FROM [CUBE]

Here is the outcome it gives:

If I replace calculated set [Client type] with original dimension [Client].[Year come], i.e. change 1 code line from

NON EMPTY [Client type] ON ROWS

to

NON EMPTY [Client].[Year come] ON ROWS

then mdx outputs result fine, without errors:

but I need to push figures from [Measures].[Sales] into two rows: old and new clients. Not for each year clients came to company. How can I do that?

回答1:

I think you're script is pretty much done - just try adding Aggregate around the tuples:

WITH 
  MEMBER [Calendar].[Year].[new clients] AS 
    Aggregate(([Calendar].[All],[Client].[Year come].&[2015-01-01])) 
  MEMBER [Calendar].[Year].[old clients] AS 
    Aggregate
    (
      (
        [Calendar].[All]
       ,{
          [Client].[Year come].&[2000-01-01] : [Client].[Year come].&[2014-01-01]
        }
      )
    ) 
  SET [Client type] AS 
    {
      [Calendar].[Year].[new clients]
     ,[Calendar].[Year].[old clients]
    } 
SELECT 
  [Measures].[Sales] ON COLUMNS
 ,NON EMPTY 
    [Client type] ON ROWS
FROM [CUBE];

回答2:

Here is valuable addition to @whytheq's answer. Samples in MSDN article for Aggregate() function use this condition:

WHERE [Mearsures].[Your measure]

I failed to use WHERE, because I have my measure on columns already. That is why I tried to add Filter() func to rows select:

NON EMPTY FILTER([Client type], [Measures].[Sales] > 0) ON ROWS

This boosted mdx speed radically and made use of Aggregate() func absolutely comfortable. But after that, when I added a dozen of other measures, - the speed falled down drammatically again! Any codsiderations how to solve this productivity problem?