Article ID: 218592 - View products that this article applies to.
This article was previously published under Q218592
This article describes how to perform a SQL Server distributed query to retrieve data from an OLAP Services (or Analysis Services) cube. With Microsoft SQL Server version 7.0, you can perform queries against OLE DB providers. To do this, you can:
You can use the OPENROWSET or the OPENQUERY function in a SQL Server SELECT statement to pass queries to the linked OLAP server. The query is limited to the abbreviated SELECT syntax that is supported by OLAP Services; however, the query can include Multidimensional Expressions (MDX) syntax. A query that includes MDX returns "flattened rowsets" as described in the OLE DB documentation. For more information about the SELECT syntax supported by SQL Server OLAP Services, see the "Supported SQL SELECT Syntax" topic in OLAP Services Books Online.
To query a local or a remote OLAP server database from SQL Server, you have to install the MSOLAP OLE DB provider on the computer that is running SQL Server. The MSOLAP OLE DB provider is installed when you install the OLAP client components from the SQL Server 7.0 CD.
Note The "Passing Queries from SQL Server to a Linked OLAP Server" topic, in OLAP Services Books Online, has a documentation bug in the code example:
Only a limited form of SQL is supported, and only level or measure names can be specified. When you run the query, you receive this error message:
One way to fix the query is to use the following:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSOLAP' reported an error. [OLE/DB provider returned message: Column name 'customer' is invalid. Only level or measure names can be specified.]
However, passing SQL statements in that form to OLAP Server might be very slow, and you may receive a timeout error on some computers:
OLE DB provider 'MSOLAP' reported an error. [OLE/DB provider returned message: Cannot open database 'foodmart'] [OLE/DB provider returned message: OLAP server error: The operation requested failed due to timeout.]
Although linked server examples with a four-part name work fine, they may take a long time to return a result to the client. The four-part name syntax is a SQL Server concept; it is used in a Transact-SQL command to refer to a table in a linked server, and it has limited syntax for OLAP queries. SQL Server might determine that it must read the whole fact table from OLAP Server and perform the GROUP BY itself, which might take significant resources and time.
Microsoft recommends that you send an MDX statement through an OPENROWSET or an OPENQUERY function, as shown in the earlier examples. This method lets SQL Server send the command directly to the linked OLAP provider, without trying to parse it. The command can be MDX or the subset of SQL that the OLAP provider supports. You can use the rowset returned from the OPENQUERY function in other SQL operators. For basic MDX queries and GROUP BY queries that return a relatively small amount of data (like a screenful), the result set must always be created in less than 10 seconds, generally in 5 seconds, irrespective of the size of the cube. If queries take longer, you can build more aggregations by using the usage-based analysis wizard.
For a detailed description of the sp_addlinkedserver stored procedure parameters, see SQL Server 7.0 Books Online.
For more details about setting up and using distributed queries, search on sp_addlinkedserver, "OPENQUERY", "OPENROWSET", and related topics, in SQL Server 7.0 Books Online.
To learn more about OLAP technology and MDX syntax, see OLAP Services Books Online. For information about using security with OLAP Services, see the following article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/242025/ )How to setup security for linked server to OLAP Services
Article ID: 218592 - Last Review: July 25, 2005 - Revision: 6.3
Contact us for more help
Connect with Answer Desk for expert help.