Defining an Analysis Server Scripts Project

Article
12/03/2008

In Microsoft SQL Server 2005 Analysis Services (SSAS), 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:

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.
Create a connection in the Connections folder of the Analysis Server Scripts project in Solution Explorer.
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.
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.

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 2005 Compact Edition. 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.

Change History

Release	History
14 April 2006	Changed content: Updated Analysis Services Templates section

Share via

Defining an Analysis Server Scripts Project

Project Folders

File Types

Analysis Services Templates

See Also

Concepts

Other Resources

Help and Information

Change History

Additional resources