MDX via OPENQUERY

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.

Select
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,
Select
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 *