HOWTO: SQL Server 7 Distributed Query with OLAP ServerID: Q218592
|
This article demonstrates how to perform a SQL Server distributed query to retrieve data from an OLAP Services cube.
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.
------------------------------------------
--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])' )
SELECT * FROM OPENQUERY(olap_server,
'SELECT [customer], [quantity] FROM sales')
One way to fix that 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.]
SELECT * FROM OPENQUERY(olap_server,
'SELECT [unit sales] FROM sales')
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.]
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]
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