More than 80 recipes for enriching your Business Intelligence solutions with high-performance MDX calculations and flexible MDX queries in this book and eBook
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.
Follow these steps to set up the environment for this recipe:
Follow these steps to get a one-dimensional query result with members on rows:
SELECT
{ } ON 0,
{ [Customer].[Customer].[Customer].MEMBERS } ON 1
FROM
[Adventure 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.
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:
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.
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.
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.
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.
Follow these steps to handle division by zero errors:
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’
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.
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
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
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…