Using Alias in MDX

Just like in SQL, the alias can also be used in MDX. By using alias in MDX, we can make the query to be simpler and clearer, and improve the performance of the query. Because the parts are contained in the alias would be just executed once, and the results would be cached. In addition some errors will raise if you don't use alias.

Think about the requirement that finding out the maximum value and the minimum value and the span of those based on the sale units of some store that greater than specific number, 20,000 for instance. The MDX query may like the one below:

 

WITH 
MEMBER [Measures].[Max] AS 
Max( 
    Filter( 
        Descendants( 
            [Store].[Store City].CurrentMember, 
            [Store].[Store City].[Store City] 
        ), 
        [Measures].[Unit Sales] > 20000 
    ), 
    [Measures].[Store Sales] 
) 
MEMBER [Measures].[Min] AS 
Min( 
    Filter( 
        Descendants( 
            [Store].[Store City].CurrentMember, 
            [Store].[Store City].[Store City] 
        ), 
        [Measures].[Unit Sales] > 20000 
    ), 
    [Measures].[Store Sales] 
) 
MEMBER [Measures].[DistanceOfMaxMin] AS [Measures].[Max] - [Measures].[Min] 

 

The filter function is used to select the stores which the quantity of sale units is greater than 20,000. And the Min or Max function is used to calculate the maximum value and the minimum value based on the data before. However the filter function runs 2 times, it is not necessary to do like that. We may take advantage of the result that is carried out by the first filter function. Consequently we should use alias for the first filter function. We may use the alias instead of the actual filter function to perform the filter in the second place. In MDX, the usage of the alias just like that in SQL query. But the keyword 'AS' is required, not optional. The code below is the modification of the query above:

 

WITH 
MEMBER [Measures].[Max] AS 
Max( 
    Filter( 
        Descendants( 
            [Store].[Store City].CurrentMember, 
            [Store].[Store City].[Store City] 
        ), 
        [Measures].[Unit Sales] > 20000 
    ) AS [Alias_Filter_Gt2W], 
    [Measures].[Store Sales] 
) 
MEMBER [Measures].[Min] AS 
Min( 
    [Alias_Filter_Gt2W], 
    [Measures].[Store Sales] 
) 
MEMBER [Measures].[DistanceOfMaxMin] AS [Measures].[Max] - [Measures].[Min]

 

We can use the query below to retrieve the result, and the figure 1 is the result of the query:

 

SELECT 
{ 
    [Measures].[Max], 
    [Measures].[Min], 
    [Measures].[DistanceOfMaxMin] 
} ON COLUMNS 
FROM [Warehouse and Sales] 

 

Figure 1. The result of the MDX query with alias

 

It is referred at the beginning of this article that some errors will raise sometimes if we don't resort to alias. So we can look at the code first below:  

WITH 
MEMBER [Measures].[Avg Quantity] AS 
[Measures].[Unit Sales]/[Measures].[Sales Fact 1997 Count], 
FORMAT_STRING = "###,###,###,##0.00" 
MEMBER [Measures].[Result] AS 
Sum( 
    Descendants( 
        {[Time].[Time].CurrentMember} AS [Alias_Reference Time], 
        [Time].[Day] 
    ), 
    iif(
        [Measures].[Avg Quantity] < ([Measures].[Avg Quantity], [Alias_Reference Time].Item(0)), 
        1, 
        0 
    ) 
) 
SELECT 
{ 
    [Measures].[Avg Quantity], 
    [Measures].[Result] 
} ON COLUMNS, 
{ 
    [Product].[Product].Children 
} ON ROWS 
FROM [Warehouse and Sales] 
WHERE [Time].[Quarter].[Q4] 

 

The goal of this query is to find out the times which the average sale units of some product in specific timespan is greater than the average of the sale units in the same whole time span. We can see the set named {[Time].[Time].CurrentMember} is followed by an alias. We should do that because there are 2 interim results. The one is the average number in the whole time span. The other one is the average number of sale units for specific product. Both the 2 results depend the same time span. So we must use alias to provide a reference of a specific time span to the query. Otherwise we will get the wrong output. And the problems is not easy to find out in this case, for some are still right. The reason is that the query may get the same time span reference at various percentage if we don't use alias in our query. That means we will only get the incorrect result at a percentage, not a certain case. So it is the most reliable for us to use alias in our MDX query.

The usage of alias can bring many advantages for our work, such as code resuing, clearing the code structure and so forth, even it is considered to be required in some cases. Therefore it does make sense to us for our work.

The Chinese edition of this article