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.
