Cómo realizar una consulta distribuida de SQL Server con OLAP Server


Resumen


En este artículo se describe cómo realizar una consulta distribuida de SQL Server para recuperar datos de un cubo de OLAP Services (o Analysis Services). Con Microsoft SQL Server versión 7.0 puede realizar consultas en proveedores OLE DB. Para ello, puede:
  • Use las funciones OPENQUERY o OPENROWSET de Transact-SQL. O bien
  • Use una consulta con nombres de cuatro partes, incluido un nombre de servidor vinculado.
Por ejemplo:
sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'

SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')
Puede usar las funciones OPENROWSET u OPENQUERY en una instrucción SELECT de SQL Server para pasar consultas al servidor OLAP vinculado. La consulta se limita a la sintaxis de SELECT abreviada que admite OLAP Services; sin embargo, la consulta puede incluir sintaxis de expresiones multidimensionales (MDX, Multidimensional Expressions). Una consulta que incluye MDX devuelve "conjuntos de filas sin formato" según se describe en la documentación de OLE DB. Para obtener más información acerca de la sintaxis de SELECT que admite OLAP Services de SQL Server, vea el tema correspondiente ("Supported SQL SELECT Syntax") en Libros en pantalla de OLAP Services.

Para consultar una base de datos de un servidor OLAP remoto desde SQL Server, tiene que instalar el proveedor OLE DB MSOLAP en el equipo que ejecute SQL Server. El proveedor OLE DB MSOLAP se instala al instalar los componentes del cliente OLAP desde el CD de SQL Server 7.0.


Ejemplo de OPENROWSET y OPENQUERY

En el siguiente ejemplo de código de Transact-SQL se demuestra cómo configurar y usar consultas distribuidas con un servidor OLAP y las funciones OPENQUERY y OpenRowset. Debe cambiar los nombres del origen de datos y el nombre del catálogo según corresponda.
------------------------------------------
--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])' )
Nota: el tema que trata el paso de consultas desde SQL Server a un servidor OLAP vinculado ("Passing Queries from SQL Server to a Linked OLAP Server") de los Libros en pantalla de OLAP, tiene un error de documentación en el ejemplo de código:
SELECT * FROM OPENQUERY(olap_server,
'SELECT [customer], [quantity] FROM sales')
Sólo se admite una forma limitada de SQL y sólo se pueden especificar nombres de medida o de nivel. Cuando ejecuta la consulta, aparece este mensaje de error:
Servidor: Mensaje 7399, Nivel 16, Estado 1, Línea 1 Proveedor OLE DB 'MSOLAP' informó de un error. [Mensaje devuelto por el proveedor OLE/DB: El nombre de columna 'customer' no es válido. Sólo se puede especificar nombres de nivel o medida.]
A continuación se indica una forma de corregir la consulta:
SELECT * FROM OPENQUERY(olap_server,
'SELECT [unit sales] FROM sales')
Sin embargo, el paso de instrucciones SQL de esa forma a OLAP Server podría ser muy lento y puede aparecer un error de tiempo de espera en algunos equipos:
El proveedor OLE DB 'MSOLAP' informó de un error. [Mensaje devuelto por el proveedor OLE/DB: No se puede abrir la base de datos 'foodmart'] [El proveedor OLE/DB devolvió el mensaje: Error del servidor OLAP: Error en la operación pedida debido a que se agotó el tiempo de espera.]

Ejemplos de servidor vinculado con nombres de cuatro partes

El ejemplo de código de Transact-SQL de esta sección demuestra el uso de un servidor vinculado con un nombre de cuatro partes para consultar un cubo OLAP. En el código, el servidor vinculado denominado Olap_server se creó en el ejemplo anterior:
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]
Aunque los ejemplos de servidor vinculado con un nombre de cuatro partes funcionan bien, pueden tardar bastante en devolver un resultado al cliente. La sintaxis de los nombres de cuatro partes es un concepto de SQL Server; se usa en un comando de Transact-SQL para hacer referencia a una tabla de un servidor vinculado y tiene una sintaxis limitada para las consultas OLAP. SQL Server podría determinar que debe leer toda la tabla de hechos del servidor OLAP y realizar la instrucción GROUP BY él mismo, lo que podría exigir un tiempo y unos recursos considerables.

Microsoft recomienda que envíe una instrucción MDX a través de una función OPENROWSET u OPENQUERY, según se muestra en los ejemplos anteriores. Este método permite que SQL Server envíe el comando directamente al proveedor OLAP vinculado, sin intentar analizarlo. El comando puede ser MDX o el subconjunto de SQL que admita el proveedor OLAP. Puede usar en otros operadores SQL el conjunto de filas que devuelva la función OPENQUERY. En las consultas MDX y GROUP BY básicas que devuelven una cantidad relativamente pequeña de datos (como una pantalla), el conjunto de resultados siempre se debe crear en menos de 10 segundos, generalmente en 5 segundos, independientemente del tamaño del cubo. Si las consultas tardan más, puede crear más agregados con el asistente de análisis en función el uso.


Sugerencias para el rendimiento

A continuación se indican algunas sugerencias para el rendimiento:
  • SQL Server abre dos conexiones con el proveedor OLAP para cada consulta. Una de ellas se reutiliza en las consultas posteriores; por lo tanto, si ejecuta de nuevo el comando, la segunda consulta podría ejecutarse más rápido.
  • Para aumentar la velocidad, agrupe por otra dimensión, porque está obteniendo menos datos.
  • La peor situación se daría si el cubo se almacenara a través de OLAP relacional (ROLAP) y no hubiera agregación. Entonces, el servidor OLAP abriría una conexión de nuevo con SQL Server para obtener las filas de la tabla de hechos. No use una consulta distribuida de SQL Server en este caso.
  • Si sólo necesita un conjunto de resultados de un servidor OLAP o un archivo de cubos, intente ejecutar la consulta multidimensional o de SQL Server directamente con el servidor OLAP, o un archivo de cubos, con una aplicación OLE DB C++ o ADO (ADO*MD).
  • SQL Server instala algunos proveedores OLE DB y los configura para cargarse en proceso. Puesto que el proveedor MSOLAP no ha sido instalado por SQL Server, está configurado para cargarse fuera de proceso. Microsoft recomienda encarecidamente que cambie las opciones del proveedor OLAP de modo que se cargue en proceso, ya que esta configuración mejora el rendimiento de las consultas OLAP. Para hacer el cambio, siga estos pasos:
    1. En la carpeta Seguridad, haga clic con el botón secundario del mouse en Servidores vinculados y haga clic en Nuevo servidor vinculado.
    2. En Nombre de proveedor, haga clic para seleccionar Microsoft OLE DB Provider for OLAP Services.
    3. Haga clic en Opciones.
    4. Haga clic para seleccionar Permitir InProcess.
    5. Haga clic en Aceptar.

Referencias


Para obtener una descripción detallada de los parámetros del procedimiento almacenado sp_addlinkedserver, vea los Libros en pantalla de SQL Server 7.0.

Para obtener más información acerca de cómo configurar y usar consultas distribuidas, busque sp_addlinkedserver, "OPENQUERY", "OPENROWSET" y otros temas relacionados en Libros en pantalla de SQL Server 7.0.

Para obtener más información acerca de la tecnología OLAP y de la sintaxis de MDX, vea los Libros en pantalla de OLAP Services. Para obtener más información acerca de cómo usar la seguridad con OLAP Services, vea el siguiente artículo de Microsoft Knowledge Base:
242025 Cómo configurar la seguridad para un servidor vinculado a Servicios OLAP