HOWTO: SQL Server 7 Distributed Query with OLAP Server

ID: Q218592

The information in this article applies to:

Microsoft SQL Server OLAP Services version 7.0
Microsoft SQL Server version 7.0

SUMMARY

This article demonstrates how to perform a SQL Server distributed query to retrieve data from an OLAP Services cube.

MORE INFORMATION

Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using 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 OPENQUERY function within a SQL Server SELECT statement to pass queries to the linked OLAP server. The query is limited to the abbreviated SELECT syntax supported by OLAP Services, but the query can include 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 "Supported SQL SELECT Syntax" topic in OLAP Services Books Online.

You need to install MSOLAP OLE DB provider on the SQL Server machine in order to query a local or remote OLAP server database from SQL Server query. MSOLAP OLE DB provider is installed when you install OLAP client components from SQL Server 7.0 CD.

OpenRowset and OpenQuery Example Code:

The following T-SQL code example demonstrates how to set up and use distributed queries with an OLAP server with OpenQuery and OpenRowset functions. You need to change the data source names and catalog name as appropriate.


------------------------------------------
--OpenRowset for OLAP Server
------------------------------------------

SELECT a.* FROM OpenRowset('MSOLAP','DATASOURCE=myOlapServer; USER ID=; PASSWORD=;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; USER ID=; PASSWORD=;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 OLAP Services Books Online topic "Passing Queries from SQL Server to a Linked OLAP Server" has a DOC 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. The above query will give an 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 that query is to use the following:


SELECT * FROM OPENQUERY(olap_server,
     'SELECT [unit sales] FROM sales')

However, passing SQL statements of the above form to OLAP Server might be very slow and may give a timeout error on some machines:

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

Linked Server Examples with Four-Part Names:

This T-SQL code 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 SQLServer concept; it is used within a T-SQL command to refer to a table in a linked server, and has a limited syntax for OLAP queries. SQLServer might decide it must read the entire fact table from OLAP Server and perform the GROUP BY itself, which might take significant resources and time.

Microsoft recommends sending an MDX statement via OpenRowset or OpenQuery as shown in examples above. This method lets SQLServer send the command directly to the linked OLAP provider, without trying to parse it. The command could be MDX or the subset of SQL that the OLAP provider supports. The rowset returned from OPENQUERY can be used within other SQL operators. For basic MDX queries and GROUP BY queries returning a relatively small amount of data (like a screenful), the result set should always be created in less than 10 seconds, usually within 5 seconds, irrespective of the size of the cube. If queries take longer, more aggregations can be built using the usage-based analysis wizard.

Performance Tips:

SQLServer opens two connections to the OLAP provider for every query. One of those is reused for subsequent queries, so if you execute the command again, the second query might run faster.

To increase speed, group by another dimension (because you are getting less data).

The worst-case scenario is when the cube is stored via ROLAP and there is no aggregation. Then the OLAP server opens up a connection back to SQLServer to obtain the fact table rows. Do not use SQL Distributed query in this case.

If you just need a result-set from OLAP server or cube file, try running the SQL or multi-dimensional query directly against OLAP server or cube file using 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. It is highly recommended that you change the options for the OLAP Provider to load as in-process, because this configuration will improve performance of your OLAP queries. Follow the steps below to make the change:
1. Right-mouse-click Linked Servers under the Security folder and choose New Linked Server.
2. For Provider Name choose Microsoft OLE DB Provider for OLAP Services.
3. Click Options.
4. Select the Allow InProcess box.
5. Click OK.

REFERENCES

For more details on setting up and using distributed queries, search on sp_addlinkedserver, OpenQuery, OpenRowset and related topics in SQL 7.0 Books Online.

To learn more about OLAP technology and MDX syntax, refer to OLAP Services Books Online.

Additional query words: kbOLAP700


Keywords          : kbDatabase kbOLEDB kbSQLServ kbVC 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbhowto

Last Reviewed: March 20, 1999