Display different dimensions on columns and rows with SSAS and MDX

1
votes

Hi I'm new to SSAS and MDX and I need to produce a ssrs report based on an OLAP cube. I can't figure out how to display different dimension members on columns and rows. The dimensions are rating and time on columns and clients and statuses on rows like this:

              | a rated | b rated  | c rated | Year-3 | Year-2 | Year-1 | Year* | Total|
good clients  |
medium clients|
bad clients   |
total clients |
status 1      |
status 2      |
status 3      |
total status  |
----------------------------------------------------------------------------------------

Year corresponds to the current year.

How can I achieve this with MDX?

4
ssasmdx

4 Answers

0
votes

in MDX you can put members of different dimensions into a tuple if you want to display them on the same axis. Something like:

select  ([Time].[Calendar].[Year], [Rate].[rating].children) on columns,
select  ( ... , ... ) on rows
from [cube]

may help solve your problem.

0
votes

An axis of an MDX SELECT statement is a set of tuples of same "dimensionality". That means that all tuples must contains the members of the same dimensions. So you cannot have an MDX set made of members (a member is a tuple) of different dimensions as you're requesting.

That being said, I do not understand: "the ratings and the time columns are independent". What does that mean exactly ?

0
votes

An MDX query can support up to 128 specified axes, but very few MDX queries will use more than 5 axes. For the first 5 axes, the aliases COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS can instead be used.

Try to use the above axes to achieve what you are expecting like querying two different dimensions.

0
votes

The query below might work for you. You mentioned you dont have "all" members in your cube, so you need to replace the "all" in the query with respective default members. I have tried to make a similar senario in Adventure works. The cells have internetSales amount

               | topSeller | Bottom Seller  | 2011| 2012 | 
Topcustomers   |
Bottomcustomers|
North America  |
Europe         |

//create 
//SET [Adventure Works].[TopCustomers] AS TopCount(([Customer].[Customer].Members,[Sales Territory].[Sales Territory Country].[All]), 3,[Measures].[Internet Sales Amount])
//SET [Adventure Works].[BottomCustomers] AS bottomCount(([Customer].[Customer].Members,[Sales Territory].[Sales Territory Country].[All]), 3,[Measures].[Internet Sales Amount])
//SET [Adventure Works].[NorthAmerica] AS ([Customer].[Customer].[All],{[Sales Territory].[Sales Territory Country].&[United States],[Sales Territory].[Sales Territory Country].&[Canada]})
//SET [Adventure Works].[Europe] AS ([Customer].[Customer].[All],{[Sales Territory].[Sales Territory Country].&[France],[Sales Territory].[Sales Territory Country].&[Germany],[Sales Territory].[Sales Territory Country].&[United Kingdom]})
//
//SET [Adventure Works].[TopSellers] AS TopCount(([Product].[Model Name].Members,[Date].[Calendar Year].[All]), 3,[Measures].[Internet Sales Amount])
//SET [Adventure Works].[BottomSellers] AS BottomCount(([Product].[Model Name].Members,[Date].[Calendar Year].[All]), 3,[Measures].[Internet Sales Amount])
//SET [Adventure Works].[2011] AS ([Product].[Model Name].[All],[Date].[Calendar Year].&[2011])
//SET [Adventure Works].[2012] AS ([Product].[Model Name].[All],[Date].[Calendar Year].&[2012])
//

select 
{
([Measures].[Internet Sales Amount],[TopSellers]),
([Measures].[Internet Sales Amount],[BottomSellers]),
([Measures].[Internet Sales Amount],[2011]),
([Measures].[Internet Sales Amount],[2012])
}
on columns,

{
([TopCustomers]),
([BottomCustomers]),
([NorthAmerica]),
([Europe])
}
on rows
from [Adventure Works]

The result looks like below Result