Technical: Microsoft – SQL Server – Analysis Services – MDX Query – Error – hierarchy already appears in the Axis1 axis

Technical: Microsoft – SQL Server – Analysis Services – MDX Query – Error – hierarchy already appears in the Axis1 axis

Introduction

Getting up to speed with SQL Server Analysis Service Cube Browsing and Reporting. And, finding that I need to get comfortable with MDX.

MDX is a query language for querying OLAPS.

Data Source View

DataSourceView


Fact Table Browser

What we are trying to do, that is filter on specific Country IDs and Names, is easy to do when using a query or reporting tool. Before Filtering Browser-Before-Filtering

Post Filtering Browser-After-Filtering

Query

But, when we resort to MDX and try to do same, we find out that we have to work a bit harder. In the next two examples, we attempt to filter by using the where clause.

Error Message :- The MDX function CURRENTMEMBER failed because current coordinate is empty.

In this scenario, we pass in a non-existence Country ID (47)


select

	{
		[Measures].[GDP Amount]

	} on COLUMNS

	, {

		[Country].[Country ID]

	  } on ROWS

from  [DBLAB]

where (

		{

			 [Country].[Country ID].&[47]

		}

	  )

Here is our error message:

Executing the query ...
The MDX function CURRENTMEMBER failed because current coordinate is empty.
Execution complete

Error Message – The Geography hierarchy already appears in the Axis1 axis.

In this scenario, we pass in an existing Country ID (4)


select

	{
		[Measures].[GDP Amount]

	} on COLUMNS

	, {

		[Country].[Country ID]

	  } on ROWS

from  [DBLAB]

where (

		{

			 [Country].[Country ID].&[4]

		}

     )

 

Error Message:

The Country ID hierarchy already appears in the Axis1 axis.

Resolution

There are a couple of ways to address our little problem.

Our proposed solution involves introducing our filtering at the Row sub-section.

To do so we can employ the exists or the Filter expressions.

Fix Problem by using exists keyword



select

	{
		[Measures].[GDP Amount]

	} on COLUMNS

	, EXISTS
		(
			  [Country].[Country ID].Members
			, {
			         [Country].[Country ID].&[3]			
			       , [Country].[Country ID].&[4]
                           }

	       ) on ROWS

from  [DBLAB]

Fix Problem by using “filter” keyword


select

	{
	    [Measures].[GDP Amount]

	} on COLUMNS

	, FILTER
		(
	 	    [Country].[Country ID].Members
			, (
				    ([Country].[Country ID].Member_value = 2)
				 or ([Country].[Country ID].Member_value = 3)
			  )	 

	    ) on ROWS

from  [DBLAB]

References

References – MDX Query

References – Restricting the Query with Query and Slicer Axes

References – Blogs

 

References – MDX Query – Q/A

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s