HOWTO:SQL Server 7 分布式查询与 OLAP 服务器


概要


本文讲述如何执行 SQL Server 分布式查询,以便从 OLAP 服务多维数据集中检索数据。

更多信息


Microsoft SQL Server 7.0 提供了通过 OLE DB 提供程序执行查询的功能。实现该功能需要使用 Transact-SQL 函数 OPENQUERYOPENROWSET,或使用具有四部分名称(包括链接服务器名称)的查询。例如,



sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'

SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')
您可以在 SQL Server SELECT 语句中使用 OPENROWSETOPENQUERY 函数,以便将查询传递给链接的 OLAP 服务器。查询受限于 OLAP 服务支持的 SELECT 缩写语法,但查询可以包括 MDX 语法。根据 OLE DB 文档的说明,包含 MDX 语法的查询会返回“平展行集”(flattened rowsets)。有关 SQL Server OLAP 服务所支持的 SELECT 语法的详细信息,请参见 OLAP Services Books Online 中的 Supported SQL SELECT Syntax (受支持的 SQL SELECT 语法)主题。


要通过 SQL Server 查询来查询本地或远程 OLAP 服务器数据库,需要在 SQL Server 计算机上安装 MSOLAP OLE DB provider。当您通过 SQL Server 7.0 光盘安装 OLAP 客户端组件时,将同时安装 MSOLAP OLE DB provider。


OpenRowset 和 OpenQuery 示例代码



下面的 T-SQL 代码示例演示如何使用 OpenQuery 和 OpenRowset 函数,针对 OLAP 服务器,设置并使用分布式查询。您需要对数据源名称和类别名称进行相应的更改。


------------------------------------------
--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])' )


备注:在 OLAP OLAP Services Books Online 中的主题 Passing Queries from SQL Server to a Linked OLAP Server (将查询从 SQL Server 传递到所链接的 OLAP 服务器)的代码示例中,有一个 DOC 错误,下面是该代码:
SELECT * FROM OPENQUERY(olap_server,
'SELECT [customer], [quantity] FROM sales')


该代码仅支持有限的 SQL 格式,并且只能指定层次或度量名称。上面的查询将导致如下错误消息:
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')


但是,将该格式的 SQL 语句传递给 OLAP 服务器时速度可能非常缓慢,而且在某些计算机上可能产生超时错误消息:
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.]

采用四部分命名法的链接服务器示例



该 T-SQL 代码演示如何利用四部分名称使用链接服务器来查询 OLAP 多维数据集。在代码中,名为 Olap_server 的链接服务器是在上一个示例中创建的。
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]


尽管使用四部分命名的链接服务器示例运行正常,但它们可能需要很长时间才能将结果返回给客户端。四部分名称语法是 SQLServer 概念;在 T-SQL 命令中使用它以引用链接服务器中的表,而且在进行 OLAP 查询时语法受到限制。SQLServer 可能决定它必须从 OLAP 服务器读取整个事实数据表,并自己执行 GROUP BY,这可能占用较多的资源和较长的时间。


Microsoft 建议通过 OPENROWSETOPENQUERY 来发送 MDX 语句,如前面的示例所示。此方法使 SQLServer 在不分析命令的情况下直接将命令发送到链接的 OLAP provider。命令可以是 MDX,也可以是 OLAP provider 所支持的 SQL 的子集。从 OPENQUERY 所返回的行集可以在其它 SQL 运算符中使用。如果基本 MDX 查询和 GROUP BY 查询返回相对较少的数据(如一屏幕),则在 10 秒以内创建结果集(通常在 5 秒内),与多维数据集的大小无关。如果查询花费的时间较长,则可以使用基于用法的分析向导建立更多的聚合。


性能提示



  • SQLServer 为每个查询打开两个到 OLAP provider 的连接。其中一个连接被后继查询再次使用,这样,如果再次执行命令,第二个查询可能运行得更快。
  • 要提高速度,应当按另一个维度进行分组(因为您要获得更少的数据)。
  • 最坏的情况是,通过 ROLAP 来存储多维数据集并且没有聚合。这时,OLAP 服务器将逆向打开到 SQLServer 的连接,以便获得事实数据表行。在这种情况下,不要使用 SQL 分布式查询。
  • 如果您只需要从 OLAP 服务器或多维数据集文件中获得结果集,请尝试使用 OLE DB C++ 应用程序或 ADO(ADO*MD) 应用程序,直接对 OLAP 服务器或多维数据集文件运行 SQL 或多维查询。
  • SQL Server 安装了某些 OLE DB provider,并将它们配置为进程内加载。由于 MSOLAP provider 不是用 SQL Server 安装的,所以它被配置为进程外加载。强烈建议您更改 OLAP provider 的相关选项,将它设置为进程内加载,这样做将提高 OLAP 查询的性能。请按照下面的步骤进行更改:


    1. Security 文件夹下右键单击 Linked Servers,然后选择 New Linked Server
    2. 对于 Provider Name,选择 Microsoft OLE DB Provider for OLAP Services
    3. 单击 Options
    4. 选中 Allow InProcess 框。
    5. 单击 OK

参考


有关 sp_addlinkedserver 参数的详细信息,请参阅 SQL 7.0 Books Online。


有关设置和使用分布式查询的详细信息,请在 SQL 7.0 Books Online 中搜索 sp_addlinkedserver、OpenQuery、OpenRowset 和相关主题。


要了解有关 OLAP 技术和 MDX 语法的详细信息,请参阅 OLAP Services Books Online。 有关使用 OLAP 服务的安全功能的详细信息,请参见下面的 Microsoft Knowledge Base 文章:
242025 INF:如何设置链接服务器到 OLAP 服务的安全性