Use MDX to select Dimension-Members instead of Measure Values

mdx

Is it possible, with MDX, to select Dimension Members instead of Measure Values?

Here is what I currently do (simplified):

SELECT 
    {[Time]...} ON 0,
    {[Gender]...} ON 1
FROM [Cube];

Result:

  2008 2009
M  1    -
W  -    2

This is my desired result (in my dataset the returned cell amount would be much lower than in the above result):

Gender Time
M      2008
W      2009
W      2009

Or alternatively:

Gender Time Measure
M      2008    1
W      2009    2

In my project I need to select from 5 different dimension axes, some of them containing several hundred members, leading to a cellset of about 8,000,000 cells, which executes very slow (about 30 seconds in my setup). I already tried to improve the performance with the NONEMPTY function but it is still too slow. My desired results would only return about 400,000 cells (querying the SQL source table requires only 2 seconds, so this is the speed at which the desired data can be retrieved in theory).

Obviously, in this special situation querying the relational source table would be much faster, however, the OLAP server should be single-point-of-truth so I cannot just query the source database.

Are there any MDX functions that allow this kind of query or do you have any other tipps on how to improve the performance of aforementioned query?

Best Answer

You can achieve your desired results by using a crossjoin (the * notation) and putting the dimensions on the columns axis. Here's an example based upon the Adventure Works SSAS DB. 

select {} on 0, 
{
[Customer].[Gender].[Gender].members
*
[Date].[Calendar Year].[Calendar Year].members 
}  on 1
from [Adventure Works]

enter image description here

Related Question