Hello people,
How are you today ?
In this post, I will demonstrate how to use the XML for Analysis (XLMA) language to be able to query and send commands to Analysis Services through Transact-SQL (T-SQL) queries, which can be used in a SQL Agent job to automate This process allows you to automate the backup / restore routines, for example, of your Analysis Services cubes.
Linked Server Creation for Analysis Services
Before you can submit SQL Server XMLA queries to Analysis Services, we will need to create a Linked Server for Analysis Services.
Creating Linked Server through the SQL Server Management Studio Interface
To create Linked Server through the SQL Server Management Studio interface, navigate in Object Explorer to "Server Objects" and right click on the "Linked Servers" folder, selecting the option "New Linked Server ..."
Define the name of your Linked Server, select the “Microsoft OLE DB Provider for Analysis Services XX.0” protocol. In the product name and data source field, type the name of the instance where your Analysis Services is located.
On this screen, mark the fields “Data Access”, “RPC”, “RPC Out” and “Use Remote Collation” as True, as shown in the image below:
Finally, define how users will access your Linked Server. I checked the option “Be made using login's current security context”, so that the connection is made using the permissions of the AD user in question.
It is noteworthy that Analysis Services only works with Windows AD logins, not supporting SQL Server logins. If a SQL Server user (such as an application user) needs to use this LinkedServer, you will need to do a login mapping, where you can define which Windows AD login to use to log in to Analysis Services when the connection is made by a particular login. SQL Server
Creating Linked Server Using Command Line (TSQL)
To create Linked Server using command line (TSQL), simply execute the query below:
|
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 |
If the connection is successful, you will have this view when accessing the created Linked Server:
How to identify Cube information
For backup / restore of Analysis Services cubes, we will need some important information from the Cube in question. To do this, create a new connection to Analysis Services through the SQL Server Management Studio Object Explorer.
Authentication is the same as connecting to SQL Server
Right click on the cube in question and select the properties option.
On this screen you will see the Database Id and Database Name, which we will need later on.
An easy way to check if Linked Server is working is to query it:
|
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') |
Now that you have created your linked server between SQL Server and Analysis Services and can identify the difference between the Name and ID of objects such as Databases, Cubes, Dimensions, Particles, etc., you can now perform your XLMA queries and send commands to Analysis Services easily and conveniently.
To check out everything you can do using XLMA in SQL Server, be sure to go to this link.
That's it guys,
Regards and see you next post.
