How to use 3rd dimension in MDX query (ON PAGES syntax)

Question

Hello, I have a problems with 3rd dimension in MDX Query (on MS SQL Server 2005). I can use 3rd dimension in Visual Basic (I have a cube there, using browser I can make 3 dim. queries -- owing to ON PAGES). I snooped it via MS SQL Profiler (it records databases queries). But when I tried to put the query into MS SQL SERVER, only thing what I've received was:

Executing the query ... Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSet Formatting. Results cannot be displayed for cellsets with more than two axes. Execution complete

I tried a few different ways to implement query, but this^ answer, was an only answer from a server.

The question is: What I need to do to use third dim in my OLAP?

Answer 1

When you query Analysis Services with SQL Server Management Studio (SSMS) you get an ADOMD.NET Cellset object. This object represents your query results in a multidimensional fashion, laying out the information in several axes: Axis 0 for the columns, axis 1 for rows, axis 2 for pages, etc.

Although your query may be correct with three axes, SSMS can only render bidimensional restults, hence the error message you get. You need to build an application using ADOMD.NET to consume the cellset, or use a third party tool.

Answer 2

You say you "put the query into MS SQL SERVER" - what exactly do you mean? Did you try the cube browser that comes with Analysis Services? It's pretty rubbish.

I think the problem is as simple as the error message you got - "Results cannot be displayed". In other words, the viewing software can show tables (i.e. 2D info) but doesnt have a way to display 3D info (whether it be in multiple tables, or whatever).

You need to find software that can present cube data more elaborately.