MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook
More than 80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries in this book and eBook
Skipping axis
There are situations when we want to display just a list of members and no data associated with them. Naturally, we expect to get that list on rows, so that we can scroll through them nicely. However, the rules of MDX say we can’t skip axes. If we want something on rows (which is AXIS(1) by the way), we must use all previous axes as well (columns in this case, which is also known as AXIS(0)).
The reason why we want the list to appear on axis 1 and not axis 0 is because a horizontal list is not as easy to read as a vertical one.
Is there a way to display those members on rows and have nothing on columns? Sure! This recipe shows how.
Getting ready
Follow these steps to set up the environment for this recipe:
- Start SQL Server Management Studio (SSMS) or any other application you use for writing and executing MDX queries and connect to your SQL Server Analysis Services (SSAS) 2008 R2 instance (localhost or servernameinstancename).
- Click on the New Query button and check that the target database is Adventure Works DW 2008R2.
How to do it…
Follow these steps to get a one-dimensional query result with members on rows:
- Put an empty set on columns (AXIS(0)). Notation for empty set is this: {}.
- Put some hierarchy on rows (AXIS(1)). In this case we used the largest hierarchy available in this cube – Customer hierarchy of the same dimension.
- Run the following query:
SELECT
{ } ON 0,
{ [Customer].[Customer].[Customer].MEMBERS } ON 1
FROM
[Adventure Works]
How it works…
Although we can’t skip axes, we are allowed to provide an empty set on them. This trick allows us to get what we need – nothing on columns and a set of members on rows.
There’s more…
Notice that this type of query is very convenient for parameter selection of another query as well as for search. See how it can be modified to include only those customers whose name contains the phrase “John”:
SELECT
{ } ON 0,
{ Filter(
[Customer].[Customer].[Customer].MEMBERS,
InStr(
[Customer].[Customer].CurrentMember.Name,
‘John’
) > 0
)
} ON 1
FROM
[Adventure Works]
In the final result, you will notice the “John” phrase in various positions in member names:
The idea behind
If you put a cube measure or a calculated measure with a non-constant expression on axis 0 instead, you’ll slow down the query. Sometimes it won’t be so obvious, sometimes it will. It will depend on the measure’s definition and the number of members in the hierarchy being displayed. For example, if you put the Sales Amount measure on columns, that measure will have to be evaluated for each member in the rows. Do we need those values? No, we don’t. The only thing we need is a list of members; hence we’ve used an empty set. That way, the SSAS engine doesn’t have to go into cube space. It can reside in dimension space which is much smaller and the query is therefore more efficient.
Possible workarounds
In case of a third-party application or a control which has problems with this kind of MDX statement (i.e. expects something on columns and is not working with an empty set), we can define a constant measure (a measure returning null, 0, 1 or any other constant) and place it on columns instead of that empty set. For example, we can define a calculated measure in the MDX script whose definition is 1, or any other constant value, and use that measure on the columns axis. It might not be as efficient as an empty set, but it is a much better solution than the one with a regular (non-constant) cube measure like the Sales Amount measure.
Handling division by zero errors
Another common task is handling errors, especially division by zero type of errors. This recipe offers a way to solve that problem.
Not all versions of Adventure Works database have the same date range. If you’re not using the recommended version of it, the one for the SSAS 2008 R2, you might have problems with queries. Older versions of Adventure Works database have dates up to the year 2006 or even 2004. If that’s the case, make sure you adjust examples by offsetting years in the query with a fixed number. For example, the year 2006 should become 2002 and so on.
Getting ready
Start a new query in SQL Server Management Studio and check that you’re working on Adventure Works database. Then write and execute this query:
WITH
MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Bad] AS
[Date].[Calendar Year].[Calendar Year].&[2006] /
[Date].[Calendar Year].[Calendar Year].&[2005],
FORMAT_STRING = ‘Percent’
SELECT
{ [Date].[Calendar Year].[Calendar Year].&[2005],
[Date].[Calendar Year].[Calendar Year].&[2006],
[Date].[Calendar Year].[CY 2006 vs 2005 Bad] } *
[Measures].[Reseller Sales Amount] ON 0,
{ [Sales Territory].[Sales Territory].[Country].MEMBERS }
ON 1
FROM
[Adventure Works]
This query returns 6 rows with countries and 3 rows with years, the third row being the ratio of the previous two, as its definition says.
The problem is that we get 1.#INFM on some cells. To be precise, that value (the formatted value of infinity), appears on rows where the CY 2005 is null. Here’s a solution for that.
How to do it…
Follow these steps to handle division by zero errors:
- Copy the calculated member and paste it as another calculated member. During that, replace the term Bad with Good in its name, just to differentiate those two members.
- Copy the denominator.
- Wrap the expression in an outer IIF() statement.
- Paste the denominator in the condition part of the IIF() statement and compare it against 0.
- Provide null value for the True part.
- Your initial expression should be in the False part.
- Don’t forget to include the new member on columns and execute the query:
MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Good] AS
IIF ([Date].[Calendar Year].[Calendar Year].&[2005] = 0,
null,
[Date].[Calendar Year].[Calendar Year].&[2006] /
[Date].[Calendar Year].[Calendar Year].&[2005]
),
FORMAT_STRING = ‘Percent’ - The result shows that the new calculated measure corrects the problem – we don’t get errors (the rightmost column, compared to the one on its left):
How it works…
A division by zero error occurs when the denominator is null or zero and the numerator is not null. In order to prevent this error, we must test the denominator before the division and handle the case when it is null or zero. That is done using an outer IIF() statement.
It is enough to test just for zero because null = 0 returns True.
There’s more…
SQLCAT’s SQL Server 2008 Analysis Services Performance Guide has lots of interesting details regarding the IIF() function:
http://tinyurl.com/PerfGuide2008
Additionally, you may find Jeffrey Wang’s blog article useful in explaining the details of the IIF() function:
http://tinyurl.com/IIFJeffrey
Earlier versions of SSAS
If you’re using a version of SSAS prior to 2008 (that is, 2005), the performance will not be as good. See Mosha Pasumansky’s article for more info:
http://tinyurl.com/IIFMosha