MDX permission error when I call a table in SQL Server instance

Question

I am trying to execute this query in MDX

INSERT INTO MINING STRUCTURE [People1]
([CustID], [Name], [Gender], [Age], [CarMake],[CarModel])
OPENQUERY(Chapter3Data,
'SELECT [Key], Name, Gender, Age, CarMake, CarModel
FROM People')

I have a database Chapter3Data with people table in my SQL Server instance, but I get this error :

Executing the query ...
Either the 'Ehsan\ehsan akbar' user does not have permission to access the 'Chapter3Data' object, or the object does not exist.
Execution complete

whytheq whytheq · Accepted Answer · 2016-10-19T09:18:57

This is not valid mdx:

SELECT [Key], Name, Gender, Age, CarMake, CarModel
FROM People

You need to specify which axis you would like the hierarchies ON - so generally ROWS or COLUMNS - an example might be:

SELECT 
   [Key].[Key].MEMBERS ON ROWS, 
   [Gender].[Gender].MEMBERS ON COLUMS
FROM People;

In SSMS you should be able to open with a connection to the cube server - then you can open an mdx query and test your mdx to ensure it is valid - a good idea before attempting the linked server OPENQUERY function.

MDX permission error when I call a table in SQL Server instance

2 Answers