Use Analysis Services Templates in SQL Server Management Studio
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 includes the following sections:
Open an Analysis Services Template
Build and Run an MDX Query on a Tabular Model using a Template
Create XMLA Script from a Template
Generate a Schema Rowset Query using an XMLA Template
Analysis Services Template Reference
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 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 xVelocity in-memory analytics engine (VertiPaq).
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 Parameters for DAX Queries.
Create an MDX query from a template
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.
In Template Browser, in Analysis Services Templates, open MDX, and then open Queries. Drag Basic Query to the query window.
Using Metadata Explorer, drag the following fields and measures into the query template:
Replace <row_axis, mdx_set> with [Product Category].[Product Category Name].
Replace <column_axis, mdx_set> with [Date].[Calendar Year].[Calendar Year].
Replace <from_clause, mdx_name> with [Internet Sales].
Replace <where_clause, mdx_set> with [Measures].[Internet Total Sales].
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
.membersafter [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 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
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.
Drag the
Backuptemplate into the empty query window.Double-click the text within the <DatabaseID> element.
In Object Explorer, select the database you want to backup, and drag and drop the database between the brackets of the DatabaseID element.
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. For more information, see Analysis Services Schema Rowsets.
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
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.
Open Template Explorer, and drag the template, Discover Schema Rowsets, into the blank query window.
In the template, replace the RequestType Element (XMLA) element with the following text:
<RequestType>MDSCHEMA_INPUT_DATASOURCES</RequestType>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 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 Analysis Services Scripting Language (ASSL) Database element to create an Analysis Services database and a data source for storing remote partitions. |
|
| Delete | Demonstrates how to use the XMLA Delete command to delete an existing 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 Analysis Services database from an existing backup file. |
|
| Synchronize | Demonstrates how to use the XMLA Synchronize command to synchronize another Analysis Services database with the current 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) Reference Analysis Services Scripting Language (ASSL) Reference