When you run a basic MDX query directly against the cube, it returns only those measures and dimensions you have specified in the query. Note that [Customer Geography] is a hierarchy.

Non empty [Measures].[Sales Amount] ON Axis(0),
[Customer].[Customer Geography].[State-Province].Allmembers ON Axis(1)
FROM [Adventure Works]

MDX Query run directly against the cube









When you run the same query via a linked server using OPENQUERY, you get a column for each level in the hierarchy.


select * from OPENQUERY (AWCUBE,
Non empty [Measures].[Customer Count] ON Axis(0),
[Customer].[Customer Geography].[State-Province].Allmembers ON Axis(1)
FROM [Adventure Works]’)


MDX query run via OPENQUERY using a linked server









Both the Country and the State-Province have been returned. Very useful!


Leave a Reply

Your email address will not be published. Required fields are marked *