Script Administrative Tasks in Analysis Services
You can automate Analysis Services administrative tasks by writing or generating scripts that can be executed manually or scheduled through SQL Server Agent. The following table summarizes the scripting options available to you and provides links to more information.
All of the methodologies listed below support scripts that can be saved to a file and executed as an independent operation. Because the Data Analysis Expression (DAX) language used for tabular models and PowerPivot workbooks does not meet the criteria, it is not included in the following list.
Analysis Services supports the SQL Server PowerShell scripting environment through a new provider that adds object navigation from the command line, as well as new cmdlets for administrative tasks such as backup, restore, processing, and role management.
Additionally, the SQL Server PowerPivot (SQLPS) provider includes a general purpose cmdlet, Invoke-ASCmd, which lets you run XMLA, MDX, or DMX script files from within a PowerShell session.
Analysis Services PowerShell scripting is supported for both multidimensional and tabular models, but not for PowerPivot workbooks accessed from SharePoint.
ASSL or XMLA Script
Analysis Services Scripting Language (ASSL) is an extension to XMLA that provides data access to objects and operations on an Analysis Services instance that runs in tabular or multidimensional mode. ASSL includes data definition and command language support, enabling the complete expression of Analysis Services objects and operations in an XML format. Scripts that use the objects and commands provided by ASSL are saved as .xmla files. Within the context of Analysis Services, it is common practice to refer to ASSL as XMLA script.
Choose this approach when your requirements include the following:
To create XMLA script, you can use the script generator in Management Studio. At the object level, right-click an object to generate script that creates, alters, or deletes an object. At the command level, such as for processing, backup or restore, aggregation design, or another command, you can generate script using the Script feature in the dialog box, choosing options that place the script in a new window, file, or clipboard. You can also write XMLA script manually in a text or code editor, or use a template in Template Explorer.
To run the script, use one of these approaches:
Multidimensional Expression (MDX) language is an industry standard query language for analytical data sources that is also part of the XMLA specification.
You can create a standalone MDX script file that queries data or system information. For example, Dynamic Management Views (DMV) that expose information about local server operations and server health are accessed via the MDX Select statement.
MDX script will run on both multidimensional and tabular mode servers. You can run the script interactively from SQL Server Management Studio, or from a PowerShell session using Invoke-ASCmd.
Data Mining Extensions (DMX) is a data definition, data manipulation, and data query language for data mining models. You can use a template as a way to get started.
SSIS provides tasks and data flows that create, modify, delete, and process Analysis Services objects, including data mining models. You can schedule a package to run using SQL Server Agent.
Analysis Management Objects
Analysis Management Objects (AMO) is managed interface that programmers can use to develop custom applications that automate administrative operations. Using AMO, you can develop a custom application that runs XMLA, MDX, or DMX scripts that you provide.