Edit

Use Analysis Services Templates in SQL Server Management Studio

  • Article

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

SQL Server Management Studio provides a set of templates to help you quickly create XMLA scripts, DMX or MDX queries, create KPIs in a cube or tabular model, script backup and restore operations, and perform many other tasks. Templates are located in the Template Explorer in Management Studio.

This topic includes a list of the templates for multidimensional models and tabular models, and provides examples of how to build an MDX query and XMLA statement by using the Metadata Explorer and the Template Explorer.

This topic does not cover DMX templates. For examples of how to create data mining queries using the templates, see Create a DMX Query in SQL Server Management Studio or Create a Singleton Prediction Query from a Template.

Open an Analysis Services Template

All templates for database engine queries and Analysis Services queries and commands are available in Template Explorer.

To open Template Explorer, select it from the View menu. Next, click the cube icon to see a list of the templates that are available for SQL Server Analysis Services.

Template Explorer, filtered for Analysis Services

To open a template, right-click the template name and select Open, or drag the template into a query window that you already opened. After the query window is open, you can use commands on the toolbar or Query menu to help you build statements:

  • To check the syntax of a query, click Parse.

  • To run a query, click Execute.

    To stop a query that is running, click Cancel Executing Query.

  • View the results of a query in the Results tab at the bottom of the screen.

    Switch to the Messages tab to see the number of records returned, errors, query statements, and any other messages that are associated with the execution of the query. For example, if you execute a DAX statement against a model running in Direct Query mode, you can see the Transact-SQL statement that is generated by the VertiPaq in-memory analytics engine.

Build and Run an MDX Query on a Tabular Model using a Template

This example shows you how to create an MDX query in SQL Server Management Studio, using a tabular model database as the data source. To repeat this example on your computer, you can download the Adventureworks tabular model sample project.

Warning

You cannot use MDX queries against tabular models that have been deployed in Direct Query mode. You can, however, send equivalent queries by using the DAX table queries with the EVALUATE command. For more information, see DAX query arameters.

Create an MDX query from a template

  1. In SQL Server Management Studio, open the instance that contains the tabular model you want to query. Right-click the database icon, select New Query, and then select MDX.

  2. In Template Browser, in Analysis Services Templates, open MDX, and then open Queries. Drag Basic Query to the query window.

  3. Using Metadata Explorer, drag the following fields and measures into the query template:

    1. Replace <row_axis, mdx_set> with [Product Category].[Product Category Name].

    2. Replace <column_axis, mdx_set> with [Date].[Calendar Year].[Calendar Year].

    3. Replace <from_clause, mdx_name> with [Internet Sales].

    4. Replace <where_clause, mdx_set> with [Measures].[Internet Total Sales].

  4. You can execute the query as is, but you will probably want to make some changes, such as adding a function to return specific members. For example, type .members after [Product Category].[Product Category Name]. For more information, see Using Member Expressions.

Create XMLA Script from a Template

The XMLA command templates that are provided in Template Explorer can be used to create scripts for monitoring and updating SQL Server Analysis Services objects, regardless of whether the instance is in multidimensional and data mining mode, or tabular mode. The XMLA templates include samples for the following types of scripts:

  • Backup, restore, and synchronize operations

  • Cancel specified process or command

  • Process an object

  • Discover schema rowsets

  • Monitor server status, including jobs, connections, transactions, memory, and performance counters

Create a backup command script from a template

  1. In SQL Server Management Studio, open the instance that contains the database you want to query. Right-click the database icon, select New Query, and then select XMLA.

    Warning

    You cannot set the context of an XMLA query by changing the restriction list, or by specifying a database in the connection dialog. You must open the XMLA query window from the database that you want to query.

  2. Drag the Backup template into the empty query window.

  3. Double-click the text within the <DatabaseID> element.

  4. In Object Explorer, select the database you want to backup, and drag and drop the database between the brackets of the DatabaseID element.

  5. Double-click the text within the <File> element. Type the name of the backup file, including the .abf file extension. Specify the full file path if you are not using the default backup location. For more information, see Backing Up, Restoring, and Synchronizing Databases (XMLA).

Generate a Schema Rowset Query using an XMLA Template

The Template Explorer contains only one template for schema rowset queries. To use this template, you must be familiar with the requirements of the individual schema rowset that you want to use, including any required elements, and the columns that can be used as restrictions.

Note that many of the schema rowsets have also been exposed as Dynamic Management Views (DMV) for simplicity. By using the corresponding DMV, you can query the schema rowset using syntax like that of Transact-SQL. For example, the following queries return the same results, but one is in XML format, and one is in a tabular format. For more information about DMVs, see Use Dynamic Management Views (DMVs) to Monitor Analysis Services.

DMV that returns a list of all schema rowsets available as DMVs:

SELECT * FROM $system.DISCOVER_SCHEMA_ROWSETS

XMLA command that returns list of available schema rowsets:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">  
<RequestType>DISCOVER_SCHEMA_ROWSETS</RequestType>  
    <Restrictions>  
<RestrictionList>  
</RestrictionList>  
</Restrictions>  
    <Properties>  
<PropertyList>  
   </PropertyList>  
</Properties>  
</Discover>

Get a list of data sources for a tabular model using a schema rowset query

  1. In SQL Server Management Studio, open the instance that contains the database you want to query. Right-click the database icon, select New Query, and then select XMLA.

    Warning

    You cannot set the context of an XMLA query by changing the restriction list, or by specifying a database in the connection dialog. You must open the XMLA query window from the database that you want to query.

  2. Open Template Explorer, and drag the template, Discover Schema Rowsets, into the blank query window.

  3. In the template, replace the RequestType Element (XMLA) element with the following text: <RequestType>MDSCHEMA_INPUT_DATASOURCES</RequestType>

  4. Click Execute.

    Expected results:

    <CATALOG_NAME>AW Internet Sales Tabular Model_ 24715b71-ea74-4828-aefc-d4c12c15db64</CATALOG_NAME>   
<DATASOURCE_NAME>SqlServer localhost AdventureWorksDW2012</DATASOURCE_NAME>   
<DATASOURCE_TYPE>Relational</DATASOURCE_TYPE>   
<CREATED_ON>2011-10-12T20:27:05.196667</CREATED_ON>   
<LAST_SCHEMA_UPDATE>2011-10-12T20:27:05.196667</LAST_SCHEMA_UPDATE>   
<DESCRIPTION />   
<TIMEOUT>0</TIMEOUT>   
<DBMS_NAME>Microsoft SQL Server</DBMS_NAME>   
<DBMS_VERSION>11.00.1724</DBMS_VERSION>

Analysis Services Template Reference

The following templates are provided for working with Analysis Services databases and the objects within the database, including mining strictures and mining models, cubes, and tabular models:

Category Item template Description
DMX\Model Content Content Query Demonstrates how to use the DMX SELECT FROM <model>.CONTENT statement to retrieve the mining model schema rowset content for a specified mining model.
Continuous Column Values Demonstrates how to use the DMX SELECT DISTINCT FROM <model> statement with the DMX RangeMin and RangeMax functions to retrieve a set of values in a specified range from continuous columns in a specified mining model.
Discrete Column Values Demonstrates how to use the DMX SELECT DISTINCT FROM <model> statement retrieve a complete set of values from discrete columns in a specified mining model.
Drillthrough Query Demonstrates how to use the DMX SELECT * FROM Model.CASES statement with the DMX IsInNode function to perform a drillthrough query
Model Attributes Demonstrates how to use the DMX System.GetModelAttributes function to return a list of attributes used by a model.
PMML Content Demonstrates how to use the DMX SELECT * FROM <model>.PMML statement to retrieve the Predictive Model Markup Language (PMML) representation of the mining model, for algorithms that support this functionality.
DMX\Model Management Add Model Demonstrates how to use the DMX ALTER MINING MODEL STRUCTURE statement to add a mining model
Clear Model Demonstrates how to use the DMX DELETE * FROM MINING MODEL statement to delete the content of a specified mining model.
Clear Structure Cases Demonstrates how to use the DMX DELETE FROM MINING STRUCTURE statement to clear mining model structure cases
Clear Structure Demonstrates how to use the DMX DELETE FROM MINING STRUCTURE statement to clear a mining model structure
Create from PMML Demonstrates how to use the DMX CREATE MINING MODEL statement with the FROM PMML clause to create a mining model from a PMML representation.
Create Structure Nested Demonstrates how to use the DMX CREATE MINING STRUCTURE statement with a nested column definition list to create a mining model with nested columns.
Create Structure Demonstrates how to use the DMX CREATE MINING STRUCTURE statement to create a mining model.
Drop Model Demonstrates how to use the DMX DROP MINING MODEL statement to delete an existing mining model.
Drop Structure Demonstrates how to use the DMX DROP MINING STRUCTURE statement to delete an existing mining structure.
Export Model Demonstrates how to use the DMX EXPORT MINING MODEL statement using the WITH DEPENDENCIES and PASSWORD clauses to export a mining model, including the data source and data source view on which the mining model depends, to a file.
Export Structure Demonstrates how to use the DMX EXPORT MINING STRUCTURE statement using the WITH DEPENDENCIES clause to export a mining structure, including all of the mining models contained by the mining structure and the data source and data source view on which the mining structure depends, to a file.
Import Demonstrates how to use the DMX IMPORT FROM statement using the WITH PASSWORD clause to perform an import .
Rename Model Demonstrates how to use the DMX RENAME MINING MODEL statement to rename an existing mining model.
Rename Structure Demonstrates how to use the DMX RENAME MINING STRUCTRE statement to rename an existing mining structure.
Train Model Demonstrates how to use the DMX INSERT INTO MINING MODEL statement to train a mining model inside a previously trained structure.
Train Nested Structure Demonstrates how to combine the DMX INSERT INTO MINING STRUCTURE statement with the SHAPE source data query to train a mining model that contains nested columns with data that contains nested tables, retrieved using a query, from an existing data source.
Train Structure Demonstrates how to combine the DMX INSERT INTO MINING STRUCTURE statement with the OPENQUERY source data query to train a mining structure.
DMX\Prediction Queries Base Prediction Demonstrates how to combine a DMX SELECT FROM <model> PREDICTION JOIN statement with the OPENQUERY source data query to execute a prediction query against a mining model using data, retrieved using a query, from an existing data source.
Nested Prediction Demonstrates how to combine a DMX SELECT FROM <model> PREDICTION JOIN statement with the SHAPE and OPENQUERY source data queries to execute a prediction query against a mining model using data that contains nested tables, retrieved using a query, from an existing data source.
Nested Singleton Prediction Demonstrates how to use a DMX SELECT FROM <model> NATURAL PREDICTION JOIN clause to execute a prediction query against a mining model using a single value, explicitly specified in the prediction query, in a column whose name matches a column in the mining model and which contains a set of values in a nested table created using a UNION statement whose names also match to nested columns in the mining model.
Singleton Prediction Demonstrates how to use a DMX SELECT FROM <model> NATURAL PREDICTION JOIN statement to execute a prediction query against a mining model using a single value, explicitly specified in the prediction query, in a column whose name matches a column in the mining model.
Stored Procedure Call Demonstrates how to use the DMX CALL statement to call a stored procedure
MDX\Expressions Moving Average-Fixed Demonstrates how to use the MDX ParallelPeriod and CurrentMember functions with a naturally ordered set to create a calculated measure that provides a moving average of a measure over a fixed number of time periods contained by a hierarchy in a time dimension.
Moving Average-Variable Demonstrates how to use the MDX CASE statement within the Avg function to create a calculated measure that provides a moving average of a measure over a variable number of time periods contained by hierarchy in a time dimension.
Periods to Date Demonstrates how to use the MDX PeriodsToDate function in a calculated member.
Ratio to Parent Demonstrates how to use the MDX Parent function to create a calculated measure that represents a ratio percentage of a measure for each child of a parent member in a specified hierarchy.
Ratio to Total Demonstrates how to use the All member to create a calculated measure that represents a ratio percentage of a measure for each member in a specified hierarchy.
MDX\Queries Basic Query Demonstrates a basic MDX SELECT statement from which you can construct an MDX query.
KPI Query Demonstrates how to use the MDX KPIValue and KPIGoal functions to retrieve key performance indicator (KPI) information in an MDX query.
Sub-select Query Demonstrates how to create a MDX SELECT statement that retrieves information from a subcube defined by another SELECT statement.
With Calculated Member Demonstrates how to use the MDX WITH clause in a SELECT statement to define a calculated member for an MDX query.
With Named Set Demonstrates how to use the MDX WITH clause in a SELECT statement to define a named for an MDX query.
XMLA\Management Backup Demonstrates how to use the XMLA Backup command to back up an SQL Server Analysis Services database to a file.
Cancel Demonstrates how to use the XMLA Cancel command to cancel all running operations on the current session (for users other than administrators or server administrators), database (for administrators), or instance (for server administrators.)
Create Remote Partition Database Demonstrates how to use the XMLA Create command with the SQL Server Analysis Services Scripting Language (ASSL) Database element to create an SQL Server Analysis Services database and a data source for storing remote partitions.
Delete Demonstrates how to use the XMLA Delete command to delete an existing SQL Server Analysis Services database.
Process Dimension Demonstrates how to use the XMLA Batch command, combined with the Parallel element and the Process command, to update the attributes of a dimension by using a parallel batch operation.
Process Partition Demonstrates how to use the XMLA Batch command, combined with the Parallel element and the Process command, to fully process a partition by using a parallel batch operation.
Restore Demonstrates how to use the XMLA Restore command to restore an SQL Server Analysis Services database from an existing backup file.
Synchronize Demonstrates how to use the XMLA Synchronize command to synchronize another SQL Server Analysis Services database with the current SQL Server Analysis Services database using the SkipMembership option for the SynchronizeSecurity tag.
XMLA\Schema Rowsets Discover Schema Rowsets Demonstrates how to use the XMLA Discover method to retrieve the contents of the DISCOVER_SCHEMA_ROWSETS schema rowset.
XMLA\Server Status Connections Demonstrates how to use the XMLA Discover method to retrieve the contents of the DISCOVER_CONNECTIONS schema rowset.
Jobs Demonstrates how to use the XMLA Discover method to retrieve the contents of the DISCOVER_JOBS schema rowset.
Locations Demonstrates how to use the XMLA Discover method to retrieve the contents of the DISCOVER_LOCATIONS schema rowset, specifying the path of the location backup files.
Locks Demonstrates how to use the XMLA Discover method to retrieve the contents of the DISCOVER_LOCKS schema rowset.
Memory Grant Demonstrates how to use the XMLA Discover method to retrieve the contents of the DISCOVER_MEMORYGRANT schema rowset.
Performance Counters Demonstrates how to use the XMLA Discover method to retrieve the contents of the DISCOVER_PERFORMANCE_COUNTERS schema rowset.
Sessions Demonstrates how to use the XMLA Discover method to retrieve the contents of the DISCOVER_SESSIONS schema rowset.
Traces Demonstrates how to use the XMLA Discover method to retrieve the contents of the DISCOVER_TRACES schema rowset.
Transactions Demonstrates how to use the XMLA Discover method to retrieve the contents of the DISCOVER_TRANSACTIONS schema rowset.

See Also

Multidimensional Expressions (MDX) Reference
Data Mining Extensions (DMX) Reference
Analysis Services Scripting Language (ASSL for XMLA)
Analysis Services Scripting Language (ASSL for XMLA)