Hola, chicos,
¿Cómo estás hoy?

En esta publicación, demostraré cómo usar el lenguaje XLMA (XML para Análisis) para realizar consultas y enviar comandos a Analysis Services a través de consultas Transact-SQL (T-SQL), que se pueden usar en un trabajo del Agente SQL para automatizar este proceso, permitiéndole automatizar rutinas de copia de seguridad/restauración, por ejemplo, de sus cubos de Analysis Services.

Creación de un servidor vinculado para Analysis Services

Para que pueda enviar consultas XMLA desde SQL Server a Analysis Services, necesitaremos crear un servidor vinculado para Analysis Services.

Creación del servidor vinculado a través de la interfaz de SQL Server Management Studio

Para crear el servidor vinculado a través de la interfaz de SQL Server Management Studio, navegue en el Explorador de objetos hasta "Objetos de servidor" y haga clic derecho en la carpeta "Servidores vinculados", seleccionando la opción "Nuevo servidor vinculado ...".

Defina el nombre de su Servidor Vinculado, seleccione el protocolo “Proveedor Microsoft OLE DB para Analysis Services XX.0”. En el campo nombre del producto y fuente de datos, ingrese el nombre de la instancia donde se encuentran sus Analysis Services.

En esta pantalla, marque los campos "Acceso a datos", "RPC", "Salida RPC" y "Usar intercalación remota" como Verdadero, como se muestra en la siguiente imagen:

Y finalmente, define cómo accederán los usuarios a tu Servidor Vinculado. Marqué la opción "Realizarse utilizando el contexto de seguridad actual del inicio de sesión", para que la conexión se realice utilizando los permisos del usuario de AD en cuestión.

Vale la pena señalar que Analysis Services solo funciona con inicios de sesión de Windows AD y no admite inicios de sesión de SQL Server. Si un usuario de SQL Server (como un usuario de una aplicación) necesita usar este LinkedServer, deberá realizar una asignación de inicio de sesión, donde podrá definir qué inicio de sesión de Windows AD se usará para iniciar sesión en Analysis Services cuando la conexión se realice a través de un inicio de sesión de SQL Server específico.

Creando el servidor vinculado usando la línea de comando (TSQL)

Para crear el servidor vinculado usando la línea de comando (TSQL), simplemente ejecute la siguiente consulta:

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'INTERFACE_CUBOS', @srvproduct=N'VM-DBA\SQL2016', @provider=N'MSOLAP', @datasrc=N'VM-DBA\SQL2016'

-- Impersonate para conexão onde "Usuario_SQL" se conecta no Analysis Services como "VM-DBA\dirceu.resende"
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'INTERFACE_CUBOS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'INTERFACE_CUBOS',@useself=N'False',@locallogin=N'User_SQL',@rmtuser=N'VM-DBA\dirceu.resende',@rmtpassword='minha_senha'

GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'collation name', @optvalue=NULL
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'INTERFACE_CUBOS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Si la conexión se realiza exitosamente tendrás esta vista al acceder al Servidor Enlazado creado:

Cómo identificar la información del cubo

Para realizar una copia de seguridad/restaurar cubos de Analysis Services, necesitaremos información importante sobre el cubo en cuestión. Para hacer esto, cree una nueva conexión a Analysis Services a través del Explorador de objetos de SQL Server Management Studio.

La autenticación es lo mismo que conectarse a través de SQL Server

Haga clic derecho en el Cubo en cuestión y seleccione la opción de propiedades

En esta pantalla, podrá ver el ID de la base de datos y el nombre de la base de datos, que necesitaremos más adelante.

Una forma sencilla de comprobar si el servidor vinculado está funcionando es ejecutar una consulta en él:

SELECT 
    [CATALOG_NAME],
    [CUBE_NAME],
    [LAST_SCHEMA_UPDATE],
    [LAST_DATA_UPDATE],
    [IS_DRILLTHROUGH_ENABLED],
    [IS_LINKABLE],
    [IS_WRITE_ENABLED],
    [IS_SQL_ENABLED]
FROM 
    OPENQUERY(INTERFACE_CUBOS, 'SELECT * FROM $system.MDSchema_Cubes WHERE [CUBE_SOURCE] = 1')

Ahora que ha creado su servidor de interfaz vinculado entre SQL Server y Analysis Services y puede identificar la diferencia entre el nombre y el ID de objetos, como bases de datos, cubos, dimensiones, particiones, etc., ahora puede realizar sus consultas XLMA y enviar comandos a Analysis Services de una manera fácil y práctica.

Para ver todo lo que puede hacer usando XLMA en SQL Server, asegúrese de acceder este enlace.

Eso es todo amigos
Un abrazo y nos vemos en el próximo post.