Using MDX-queries with openrowset to access Cubes in Analysis Services

[#1]

Hello

I am trying to build MDX-queries to access our cubes in Analysis Services. We have enabled openrowset in SQL Server but still I am getting anerror when trying to connect.

I hope someone can help.

Error: OLEDB connection failed

Here is my script:

CONNECT TO [Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=peb;Data Source=post-sql01;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error];


SQL SELECT * FROM OPENROWSET
('MSOLAP.4',
'Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=False;Data Source=post-sql01;Initial Catalog=peb;',
'SELECT {[Measures].[Market Volume]} ON COLUMNS, {[Date].[By Calendar Period].[Month].MEMBERS} ON ROWS FROM [DW]')

Thanks

Mike

[MichaelRobertshaw]

Wrong OLEDB Provider. Try just connecting to the Database, not directly to Analysis Services. Let SQLserver use OpenRowSet() to connect using MSOLAP.4, eg:

// Connection string to standard SQL Server instance (note initial catalog to SQL database not required)
CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=post-sql01;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=biserver;Use Encryption for Data=False;Tag with column collation when possible=False];

[#3]

Thanks I am able to connect with SQLOLEDB.1 but I get an error when using OpenRowSet to connect using MSOLAP.4/

This is ok:

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=peb;Data Source=post-sql01;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error];

I get an error (Connection failed) from this:

SQL SELECT * FROM OPENROWSET
('MSOLAP.4',
'Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=False;Data Source=post-sql01;Initial Catalog=peb;',
'SELECT {[Measures].[Market Volume]} ON COLUMNS, {[Date].[By Calendar Period].[Month].MEMBERS} ON ROWS FROM [DW]')

[#4]

I'm doing something similar in my environment. Because my SQL Server (2008 R2) connection is connecting to the same server that hosts my Analysis Services cube, I'm using "OpenQuery" rather than "OpenRowSet". My DBAs tell me that it's lest costly in server overhead. Everything but the SQL command remains the same as with "OpenRowSet". Works beautifully for me. Happy querying.