TechNet
Export (0) Print
Expand All

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 Power Pivot workbooks does not meet the criteria, it is not included in the following list.

MethodologyFile formatDescriptionLinks
PowerShell.ps1Analysis Services supports the SQL Server PowerShell scripting environment through a SQLAS provider that adds object navigation from the command line, as well as cmdlets for administrative tasks such as backup, restore, processing, and role management.

Additionally, the provider includes a general purpose cmdlet, Invoke-ASCmd, which lets you run TMSL, ASSL-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 Power Pivot workbooks accessed from SharePoint.
PowerShell scripting in Analysis Services
TMSL.jsonTabular Model Scripting Language (TMSL) is an extension of XMLA that provides a command interface and object definitions for Tabular models and databases on a SQL Server 2016 server.

Choose this approach when your requirements include any of the following:

- Model or database is Tabular 1200 compatibility level.
- Your script creates objects directly on a server, or performs both data definition and operational tasks (for example, recreating and processing database).
- Re-use across multiple tools and technologies is a priority. TMSL 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 TMSL script or an SSIS package that contains XMLA.
- XMLA is an application requirement. 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.
Tabular Model Scripting Language (TMSL) Reference

 PowerShell scripting in Analysis Services

 Compatibility Level for Tabular models in Analysis Services
ASSL or XMLA Script.xmlaAnalysis Services Scripting Language (ASSL) is an extension of 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 any of the following:

- Model or database is Multidimensional or Tabular at lower compatibility levels (1050,1100, 1103).
- Your script creates objects directly on a server, or performs both data definition and operational tasks (for example, recreating and processing database).
- Re-use across multiple tools and technologies is a priority. 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.
- XMLA is an application requirement. 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.
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
To create ASSL/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. See Invoke-ASCmd cmdlet.
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.mdxMultidimensional Expression (MDX) language is an industry standard query and expression 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 earlier 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.dmxData 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.dtsxSSIS 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 ObjectsAnalysis 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 TMSL, XMLA, MDX, or DMX scripts that you provide.Programming Administrative Tasks with AMO

Analysis Services Scripting Language (ASSL for XMLA)
Developing with Analysis Management Objects (AMO)
Processing a multidimensional model (Analysis Services)

Community Additions

ADD
Show:
© 2016 Microsoft