Alias MDX Query SSAS

Question

WITH MEMBER [Measures].[NetPromoterScore] AS (IIF(([Measures].[Net Promoter Score] = 1/0 OR 
[Measures].[Net Promoter Score] = -1/0 OR ISEMPTY([Measures].[Net Promoter Score]) 
OR [Measures].[Avg Revenue Per Unit] = 1/0 OR [Measures].[Avg Revenue Per Unit] = -1/0 OR 
ISEMPTY([Measures].[Avg Revenue Per Unit]) ), NULL, [Measures].[Net Promoter Score])) 

MEMBER [Measures].[AvgRevenuePerUnit] AS (IIF(([Measures].[Net Promoter Score] = 1/0 
OR [Measures].[Net Promoter Score] = -1/0 OR ISEMPTY([Measures].[Net Promoter Score]) 
OR [Measures].[Avg Revenue Per Unit] = 1/0 OR [Measures].[Avg Revenue Per Unit] = -1/0 
OR ISEMPTY([Measures].[Avg Revenue Per Unit]) ), NULL, [Measures].[Avg Revenue Per Unit]))

SELECT NON EMPTY
       { {[Measures].[NetPromoterScore],[Measures].[AvgRevenuePerUnit]} }
       ON COLUMNS ,
       NON EMPTY
       {{Hierarchize(DrilldownLevel({[Roles].[Enterprise Role].[ALL]}))}}
       DIMENSION  PROPERTIES MEMBER_CAPTION,  MEMBER_UNIQUE_NAME
       ON ROWS
  from Enterprise 
 WHERE (FILTER( [Employees].[EID].[EID],[Measures].[Avg Revenue Per Unit]> 700),
        {[Areas].[Area].&[3]},
        {[Markets].[Market].&[1]},{[Regions].[Region].&[2]},{[Locations].[Location].&[6],[Locations].[Location].&[6]},
        {[Dates].[Date].&[20130219]:[Dates].[Date].&[20130318]})

As you see I have aliased [Net Promoter Score] column name with [NetPromoterScore] and [Avg Revenue Per Unit] column name with [AvgRevenuePerUnit]. But in my C# code, the names used are [Net Promoter Score] and [Avg Revenue Per Unit] and I can't change these previous name with new aliased name because I have to make changes in dozens of files and then I have to test whole application. Can I alias twice in the query above to get same previous column names or can I without aliasing get the same result from the query above?

FrankPl FrankPl · Accepted Answer · 2013-10-08T12:47:56

Actually, in the sense of MDX, you have not aliased the members, but created new members.

I see two possibilities how you can solve this:

Move these calculations to the cube calculation script, i. e. rename the original measures in the cube, and define calculated measures with the name that you need and the same calculation that you have in your query. You would then make the original renamed measures invisible. This would be the best solution in my eyes, as it would let every cube user benefit from the improvement, without breaking external code or queries.
Depending on how exactly you reference the measures in your C# code (you did not post samples), you could just use the caption property of the measures, i. e. re-write the member definitions as follows:

WITH MEMBER [Measures].[NetPromoterScore] AS IIF(...), CAPTION = 'Net Promoter Score' MEMBER [Measures].[AvgRevenuePerUnit] AS IIF(...), CAPTION = 'Avg Revenue Per Unit'

Alias MDX Query SSAS

1 Answers