Olá pessoal,
Como vocês estão hoje ?
Neste post, vou demonstrar como utilizar a linguagem XLMA (XML for Analysis) para conseguir realizar consultas e enviar comandos para o Analysis Services através de queries Transact-SQL (T-SQL), que podem ser utilizadas em um job do SQL Agent para automatizar esse processo, permitindo que você automatize as rotinas de backup/restore, por exemplo, de seus cubos do Analysis Services.
Criação do Linked Server para o Analysis Services
Para que você consiga enviar as queries XMLA do SQL Server para o Analysis Services, precisaremos criar um Linked Server para o Analysis Services.
Criando o Linked Server pela interface do SQL Server Management Studio
Para criar o Linked Server pela interface do SQL Server Management Studio, navegue no Object Explorer até “Server Objects” e clique com o botão direito na pasta “Linked Servers”, selecionando a opção “New Linked Server…”
Defina o nome do seu Linked Server, selecione o protocolo “Microsoft OLE DB Provider for Analysis Services XX.0”. No campo product name e data source digite o nome da instância onde está o seu Analysis Services.
Nesta tela, marque como True os campos “Data Access”, “RPC”, “RPC Out” e “Use Remote Collation”, conforme a imagem abaixo:
E por fim, defina a forma como os usuários acessarão o seu Linked Server. Eu marquei a opção “Be made using login’s current security context”, para que a conexão seja feita utilizando as permissões do usuário AD em questão.
Vale ressaltar que o Analysis Services só funciona com logins Windows AD, não suportando logins SQL Server. Caso um usuário SQL Server (como o usuário de uma aplicação) precise utilizar esse LinkedServer, você precisará fazer um mapeamento de login, onde você poderá definir qual login Windows AD será utilizado para logar no Analysis Services quando a conexão for feita por um determinado login SQL Server.
Criando o Linked Server utilizando linha de comando (TSQL)
Para criar o Linked Server utilizando linha de comando (TSQL), basta executar a query abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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 |
Caso a conexão seja feita com sucesso, você terá essa visão ao acessar o Linked Server criado:
Como identificar as informações do Cubo
Para o backup/restore dos cubos do Analysis Services, vamos precisar de algumas informações importantes do Cubo em questão. Para isso, crie uma nova conexão com o Analysis Services pelo Object Explorer do SQL Server Management Studio.
A autenticação é igual à conexão pelo SQL Server
Clique com o botão direito sobre o Cubo em questão e selecione a opção propriedades
Nesta tela, você poderá visualizar o Database Id e Database Name, que precisaremos mais a frente
Uma forma fácil de verificar se o Linked Server está funcionando, é realizar uma consulta nele:
|
1 2 3 4 5 6 7 8 9 10 11 |
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') |
Agora que você já criou o seu linked server de interface entre o SQL Server e o Analysis Services e já consegue identificar a diferença entre o Name e o ID de objetos, como Databases, Cubos, Dimensões, Particições, etc, você já pode realizar suas queries XLMA e enviar comandos para o Analysis Services de forma fácil e prática.
Para conferir tudo o que você pode fazer utilizando o XLMA no SQL Server, não deixe de acessar esse link.
É isso aí pessoal,
Um abraço e até o próximo post.
Portuguese 
English