Creating and using XMLA scripts

XMLA (or XML for Analysis) is based on Microsofts Analysis Services Scripting Language. For full reference of this click here.

Step 1 - Add

To add a new XMLA script, right click an SSAS database Module in the navigator, and select New Script. If a script already is added to a Module, right click the Script Folder node instead.

From the Add dialog, select Add Empty and click Next.

 PS6_XMLA_Add1.PNG

 Enter a Name and select XMLA as Type. Optionally add a description and click Finish.

PS6_XMLA_Add2.PNG

 

Step 2 - Edit the script

 Select the new script from the Navigator. 

PS6_XMLA_Edit.PNG

Select Script Command section. This is where you can put the XML script. You can:

  • auto create a role/permission script from an existing SSAS by selecting XML section and click Import XMLA ->  Import Roles and Permissions. This requires that the SSAS database exists and that Roles and Permissions exist (e.g. added by other tools).
  • paste XMLA scripts from e.g. Microsoft SQL Management Studio or other tools. You can script objects (e.g. dimensions, measure groups or partitions), or you can script actions (e.g. process). 
  • create a script manually, ref Microsoft reference.
After you have saved the script, you can test it with the Execute Script button.

Step 3a - Map as Script Extension to a Module

 A script can be associated to processing tasks of the SSAS Module. Select the SSAS Module from the Navigator.

PS6_XMLA_Extension.PNG

From the Script Extensions section, you can select a script in the right section and click Add (or double click the script). 

You must then tell when to execute the added script. The Execute When dropdown has a set of predefined places in task execution (click here for options) where the script can execute.

Also you can Enable/Disable it, and select Stop/Continue the rest of the tasks it the script fails.

Step 3b - Add to Dataflow

Alternately, you can add the script in a Dataflow by adding an XMLA Step and select the script.