Script Administrative Tasks in Analysis Services

  • Article

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 PowerShell

Windows PowerShell Survival Guide

ASSL or XMLA Script

.xmla

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:

  • Your script creates objects directly on a server, or performs both data definition and operational tasks (for example, recreating and processing database).

  • 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.

Create Analysis Services Scripts in Management Studio

Use Analysis Services Templates in SQL Server Management Studio

Schedule SSAS Administrative Tasks with SQL Server Agent

Developing with Analysis Services Scripting Language (ASSL)

Invoke-ASCmd cmdlet

MDX Script

.mdx

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.

MDX Scripting Fundamentals (Analysis Services)

Use Dynamic Management Views (DMVs) to Monitor Analysis Services

Use Analysis Services Templates in SQL Server Management Studio

DMX Script

.dmx

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.

Create a DMX Query in SQL Server Management Studio

Use Analysis Services Templates in SQL Server Management Studio

SSIS packages

.dtsx

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 Services Execute DDL Task

Analysis Services Processing Task

Data Mining Query Task

Data Mining Model Training Destination

Dimension Processing Destination

Partition Processing Destination

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.

Programming Administrative Tasks with AMO

See Also

Concepts

Analysis Services Scripting Language Reference

Multidimensional Model Object Processing

Other Resources

Developing with Analysis Management Objects (AMO)