Analysis Services (SSAS) - How to query information and process command line (XLMA) commands through SQL Server

Views: 777
Reading Time: 4 minutes

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 by selecting "New Linked Server…"

Define the name of your Linked Server, select the protocol “Microsoft OLE DB Provider for Analysis Services XX.0”. In the product name and data source field type the name of the instance where your Analysis Services is located.

In this screen, mark as True the fields “Data Access”, “RPC”, “RPC Out” and “Use Remote Collation” as the image below:

And finally, define how users will access your Linked Server. I checked “Be made using login's current security context” so that the connection will be 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:

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:

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.