• Article

Developing and Implementing Using SQL Server Management Studio

In Microsoft SQL Server Analysis Services, you create an Analysis Server Scripts project in SQL Server Management Studio based on the Analysis Server Scripts template. If no solution is currently loaded in SQL Server Management Studio, creating a new Analysis Server Scripts project automatically creates a new solution. Otherwise, the new Analysis Server Scripts project can be added to the existing solution or created in a new solution.

Note

You can define individual scripts and perform administrative actions within SQL Server Management Studio without the use of an Analysis Server Scripts project. The purpose of the Analysis Server Scripts project is to group related scripts together for development, management, and source control purposes.

You use the following basic steps to create an Analysis Server Scripts project in SQL Server Management Studio:

  1. Define an Analysis Server Scripts project by selecting the Analysis Server Scripts project template in the SQL Server Management Studio template group of the Add New Project dialog box and specifying a name and location for the new project.

  2. Create a connection in the Connections folder of the Analysis Server Scripts project in Solution Explorer.

  3. Create Multidimensional Expressions (MDX), Data Mining Extensions (DMX), and XML for Analysis (XMLA) scripts in the Scripts folder of the Analysis Server Scripts project in Solution Explorer.

  4. Add any miscellaneous files, such as text files that contain notes on the project, in the Miscellaneous folder of the Analysis Server Scripts project in Solution Explorer.

For more information about how to use Solution Explorer to add new connections, scripts, and miscellaneous files to an Analysis Server Scripts project, see Using Solution Explorer.

Project Folders

An Analysis Server Scripts project contains the folders listed in the following table, which are used to organize items included in the project.

Item

Description

Connections

Contains connection strings to Analysis Services instances, against which the scripts contained by the Analysis Server Scripts project can be executed. You can have multiple connections in an Analysis Server Scripts project, and you can choose a connection against which to run a script contained by the project at the time of execution.

Scripts

Contains MDX, DMX, or XMLA scripts that can be executed using connection information contained in the project or connection information provided at execution time.

Scripts can be used to retrieve data, perform administrative actions such as processing, and create or modify objects on an Analysis Services instance.

Miscellaneous

Contains other files associated by the user with the Analysis Server Scripts project, such as text or HTML files. These files are ignored by SQL Server Management Studio.

File Types

A SQL Server Management Studio solution can contain several file types, depending on what projects you included in the solution and what items you included in each project for that solution. For more information about file types for solutions in SQL Server Management Studio, see Files That Manage Solutions and Projects. Typically, the files for each project in a SQL Server Management Studio solution are stored in the solution folder, in a separate folder for each project.

The project folder for an Analysis Server Scripts project can contain the file types listed in the following table.

File type

Description

Analysis Server Scripts project definition file (.ssmsasproj)

Contains metadata about the folders shown in Solution Explorer, as well as information that indicates which folders should display files included in the project.

The project definition file also contains the metadata for Analysis Services connections contained in the project, as well as metadata that associates connections with script files included in the project.

DMX script file (.dmx)

Contains a DMX script included in the project.

MDX script file (.mdx)

Contains an MDX script included in the project.

XMLA script file (.xmla)

Contains an XMLA script included in the project.

Analysis Services Templates

When adding new MDX, DMX, or XMLA scripts to an Analysis Server Scripts project, you have the option of using Template Explorer to locate Analysis Services templates, which are a collection of predefined scripts or statements that demonstrate how to perform a specified action. Template Explorer is available on the View menu and includes templates for SQL Server, Analysis Services, and SQL Server Compact 3.5 SP1. For more information about templates in SQL Server Management Studio, see Using SQL Server Management Studio Templates.

The item templates, which are listed in the following table, are available when you click Analysis Server on the Template Explorer toolbar.

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.