XMLA
Introduction
Extensible Markup Language for Analysis (XMLA) is an XML based, exclusive protocol used to handle communication between clients and a SQL Server Analysis Services database. It is SOAP based and designed to be a standard access point for any multidimensional source. With version 5.2 of Loome Integrate, users can now add SSAS Azure connections.
Connection String
To Connect to XMLA, set the Data source and Initial Catalog properties. Additionally, Set the Integrated Security property.
Connection String Parameters
| Parameter | Description |
|---|---|
| Data Source | Set this to the hostname of the server. |
| Initial Catalog | Set this to the Database Name. |
| Integrated Security | Set this to use the Windows Authentication. |
Connecting to XMLA Connector
Set the ‘Data Source’ parameter to your server address, in the case of cloud server, or the server name, in the case of a local network server.
Set the ‘Initial Catalog’ parameter to the name of the database, in your server.
Fill in the user credentials to authenticate the connection.
If using the same credentials as your windows session, set the ‘Integrated Security’ parameter to SSPI, instead of the username, password.
Data Migration
To Check for the data migrations:
Add a new connection in Loome Integrate as shown.
- Go to tasks and click on Connections.
- Add a new connection Using Add New Connection Option.
- Choose XMLA Connector from the available Connector Options.
Using the connection string parameters created (You can Specify the Username and password either in the connection string or in the box Shown Below), Verify the connection using the VERIFY CONNECTION Option in Loome Integrate. Once the Connection is verified, Insert the Connection Using Insert Option.
Create a Job Using ADD A JOB option in Loome Integrate, add a data migration task to the job.
Create a New Task by right clicking job list and then Add a New Task Option. If you want to edit an Existing Task Use Edit Task.
Choose a XMLA Command Task.
You will need to learn the analysis services scripting language for XMLA. Follow this link as a reference for what you want to accomplish. https://docs.microsoft.com/en-us/sql/analysis-services/scripting/analysis-services-scripting-language-assl-for-xmla
Name the Task. Connect to the Source Connection and Type the required XMLA Command.
Execute the Job. Check for results and the details of the data migrated in Execution History.
