Creating query aliases in TSQL and MDX


I recently went through an activity for a project where I needed to create aliases for MDX queries I was building.   The driver was the requirement for parameter selection in an SSRS report that required different measures and dimension attributes returned, based on the selection specified by a user, but in the same report layout.  Being a TSQL instructor during my professional career, I thought it would be interesting to draw a comparison between aliasing in ANSI compliant SQL and MDX.  Aliasing Column names and tables / views in a TSQL query is a simple and straightforward process.    You can give a column a more meaningful name, or expression or use a case statement to determine what you would like to return.  You can use a shorthand notation or descriptive name in a SQL query for a table or view to distinguish it in the FROM and Join clauses, or allow you reference the same table multiple times in the same query.   

In this example, we can see that TSQL is allowing us to perform column aliases for OrderYear through Count_of_Sales, and allows us to alias tables in FROM and JOIN clauses, and also to bring in the same table multiple times.   In this case, the DimDate table is used in a role playing scenario, same table referenced multiple times in the same query.  
 
SELECT fact.[OrderDateKey]
                    , fact.[ShipDateKey]
                    , OrderDate.CalendarYear                            AS OrderYear
                    , OrderDate.[EnglishMonthName]           AS OrderMonthName
                    , ShipDate.CalendarYear                               AS ShipYear
                    , ShipDate.[EnglishMonthName]              AS ShipMonthName
                    , SUM([SalesAmount])                                  AS Internet_SalesAmount
                    , count(distinct [SalesOrderNumber])    AS Count_of_Orders
FROM [dbo].[FactInternetSales] fact
INNER JOIN  [dbo].[DimDate] OrderDate
                    on OrderDate.DateKey = fact.[OrderDateKey]
INNER JOIN  [dbo].[DimDate] ShipDate
                    on ShipDate.DateKey = fact.ShipDateKey
GROUP BY fact.[OrderDateKey]
                                        , fact.[ShipDateKey]
                                        , OrderDate.CalendarYear                           
                                        , OrderDate.[EnglishMonthName]  
                                        , ShipDate.CalendarYear                                                  
                                        , ShipDate.[EnglishMonthName]

 
But how do you do aliasing in an MDX query?   MDX is a bit more rigid and, yes, a bit touchy than writing a query in SQL.   Let us start off by identifying what we would like to alias.  Here are two cases that are good examples.   One item I would like to alias is an individual member (aka tuple).   Common example is creating a calculated member in the Measure dimension (if you want, you can repeat the same attribute multiple times there).  Secondly, would be to create a set for an entire dimension hierarchy with all its members, or a subset of the members, and leverage the defined set on an axis or filter specification.   Referencing a hierarchy multiple times on the display axis (i.e. row and column) will not work in MDX, as it does in SQL.  It gives an error message.   It will only allow you to reference a hierarchy once in a query on an axis, but it will allow you to reference sets, created from the same hierarchy, on both a display axis and as a filter specification. 

Following is the standard syntax for creating a query scoped calculated members and SET in an MDX query.   It will allow us to create a member on the measures dimension with a different name.   

WITH MEMBER [Measures].[c_Internet_Sales_Amount] AS [Measures].[Internet Sales Amount]
                    MEMBER [Measures].[ c_Internet_Sales_Amount2] AS [Measures].[Internet Sales Amount] 
                    MEMBER [Measures].[c_Sales_Amount] AS [Measures].[Sales Amount]
                    SET [myCustomerSet] as [Customer].[Customer].[Customer].members
                    SET [mySalesChannelSet] as [Sales Channel].[Sales Channel].members

SELECT NON EMPTY   {[Measures].[c_Internet_Sales_Amount]
, [Measures].[ c_Internet_Sales_Amount2]
, [Measures].[c_Sales_Amount]  }  ON COLUMNS  ,
                                       [myCustomerSet] on ROWS
FROM [Adventure Works]

 
Using a reporting tool like SQL Server Reporting Services, it is not uncommon to have a parameter that will change what attributes are to be queried, and/or to be displayed in a report.   It is good to have techniques in your toolbox to address challenges that requirements will pose.   One option in SSRS is to have all attributes returned in a query and set the visibility property of the text box or column, based on the value of the parameter, to show or hide what was selected.   But what if it is more than a handful of metrics to hide or show, or I need to specify a set instead of a measure, or need to modify the query itself?    Another option is to provide an alias to the measures returned, or a named set and have conditional logic in a SSRS expression or call to a custom code module in the dataset to pick what you would want returned.         

As we said, you can set the visibility property of a column in a Tablix or text box with an expression based on a parameter, such as this:    
=IIF(Parameters!SalesSource.Value = "INTERNET", FALSE, TRUE)
 
IF you provide an expression for the dataset query, you can use the parameter to dynamically switch between Measures to be returned.   A measure called, [Generic Orders], can be defined with either [Internet Order Count] or [Order Count], based on the parameter selection.   The expression will switch between two different sets based on the parameter value.   

="WITH MEMBER [Measures].[Generic Orders] AS " +
IIF(Parameters!SalesSource.Value = "INTERNET", "[Measures].[Internet Order Count]", "[Measures].[Order Count]")
+ "     SET [mySet] AS " +
IIF(Parameters!SalesSource.Value = "INTERNET", "[Customer].[Customer].[Customer].members", "[Sales Channel].[Sales Channel].[Sales Channel].members")
+ " SELECT NON EMPTY {[Measures].[Generic Orders]} ON COLUMNS
, NON EMPTY {[mySet]} ON ROWS
FROM [Adventure Works]"

The set reference appears to be generic enough, but the caveat is that MDX will know what dimension hierarchy was used to create the set and you will have to expect to receive all possible set sources.   It does not matter if you had same named hierarchies in two different dimensions (ex – Role playing dimensions Time.Time, and ProgramTime.Time).  SSRS remembers the hierarchy that created the set and will look for a field in the dataset to receive.    You will need to create a dataset field for each hierarchy, and in the report layout, hide the one that is not returned.   You will receive a message that one of the hierarchies is missing.   You can ignore the message, but programmatically handle which one is to be displayed.  



An expression in a dataset, or for that matter, a common code module, has some bad news.  If you use an expression, or custom code, for dataset query, you will need to code the fields by hand as there is no metadata for SSRS read.   Good news is that you can copy and paste most of the field source, and only have to plug in the hierarchy for the UniqueName.    
<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Customer].[Customer].[Customer]" />
Or…hint, hint, hint…run a query first to create the fields, and then change it to an expression.          
As you can see you can do aliasing in an MDX query, but first identify what you need to do from the requirements, and use aliases as you need.