Microsoft KB Archive/218592

= How to perform a SQL Server distributed query with OLAP Server =

Article ID: 218592

Article Last Modified on 7/25/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server OLAP Services
 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q218592



IN THIS TASK
SUMMARY
 * OPENROWSET and OPENQUERY Example
 * Linked server examples with four-part names
 * Performance tips

REFERENCES



SUMMARY
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:
 * Use the OPENQUERY or the OPENROWSET Transact-SQL functions. -or-
 * Use a query with four-part names, including a linked-server name.

For example: sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'

SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1') 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.

back to the top

OPENROWSET and OPENQUERY example
The following Transact-SQL code example demonstrates how to set up and use distributed queries with an OLAP server with the OPENQUERY and the OpenRowset functions. You must change the data source names and catalog name as appropriate. -- --OPENROWSET for OLAP Server --

SELECT a.* FROM OpenRowset('MSOLAP','DATASOURCE=myOlapServer; Initial Catalog=FoodMart;', 'SELECT Measures.members ON ROWS, [Product Category].members ON COLUMNS FROM [Sales]') as a go

-- Example of MDX with slicing --

SELECT a.* FROM OpenRowset('MSOLAP','DATASOURCE=myOlapServer; Initial Catalog=FoodMart;', 'SELECT      { Time.Year.[1997] } ON COLUMNS, NON EMPTY      Store.MEMBERS ON ROWS FROM Sales WHERE ( Product.[Product Category].[Dairy] )') as a

-- -- Linked Server Examples with OPENQUERY --

EXEC sp_addlinkedserver @server='olap_server', @srvproduct='', @provider='MSOLAP',

@datasrc='server', @catalog='foodmart'

go

-- MDX in OPENQUERY --

SELECT * FROM OPENQUERY(olap_server, 'SELECT   { Time.Year.[1997] } ON COLUMNS, NON EMPTY       Store.MEMBERS ON ROWS FROM Sales WHERE ( Product.[Product Category].[Dairy])' ) 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: SELECT * FROM OPENQUERY(olap_server,    'SELECT [customer], [quantity] FROM sales') 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:

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.]

One way to fix the query is to use the following: SELECT * FROM OPENQUERY(olap_server,    'SELECT [unit sales] FROM sales') 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.]

back to the top

Linked server examples with four-part names
The Transact-SQL code example in this section demonstrates the use of a linked server with a four-part name to query a OLAP cube. In the code, the linked server named Olap_server was created in the previous example: Select [Store:Store Name] from Olap_server.FoodMart..[sales] WHERE [Store:Store State]='WA'

go

Select [Product:Product Category], count ([Store:Store Name]) from Olap_server.FoodMart..[sales] WHERE [Store:Store State]='WA' GROUP BY [Product:Product Category] 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.

back to the top

Performance tips
Here are some performance tips:
 * SQL Server opens two connections to the OLAP provider for every query. One of those is reused for later queries; therefore, if you run the command again, the second query might run faster.
 * To increase speed, group by another dimension (because you are getting less data).
 * A worst-case scenario would be when the cube is stored through relational OLAP (ROLAP) and there is no aggregation. Then, the OLAP server opens a connection back to SQL Server to obtain the fact table rows. Do not use a SQL Server distributed query in this case.
 * If you just need a result set from an OLAP server or a cube file, try running the SQL Server or the Multidimensional query directly against OLAP server, or any cube file, by using an OLE DB C++ application or an ADO(ADO*MD) application.
 * SQL Server installs some OLE DB providers and configures those to load in-process. Because the MSOLAP provider is not installed by SQL Server, it is configured to load out-of-process. Microsoft highly recommends that you change the options for the OLAP provider to load as in-process, because this configuration improves the performance of your OLAP queries. To make the change, follow these steps:
 * In the Security folder, right-click Linked Servers, and then click New Linked Server.
 * For the Provider Name, click to select Microsoft OLE DB Provider for OLAP Services.
 * Click Options.
 * Click to select Allow InProcess.
 * Click OK.

back to the top

