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.

Methodology

File format

Description

Links

PowerShell

.ps1

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.

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.

You require maximum script reuse across multiple tools and technologies. XMLA scripts can be added to Analysis Services command tasks in SQL Server Agent, referenced in SSIS packages, or referenced in PowerShell script.

The script must run unattended. You can use SQL Server Agent to schedule a job that contains XMLA script or an SSIS package that contains XMLA.

You have application requirements for using XMLA. XMLA is an interface that does not require a managed code environment. You can execute XMLA script in an application that does not use the .NET Framework.

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:

Use Management Studio to directly create or modify objects on an Analysis Services instance.

Use SQL Server Agent to schedule a job that includes an Analysis Services command task.

Use the Invoke-ASCmd cmdlet to run the script in a PowerShell session.

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.

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.