Managing Analysis Services in SQL Server Management Studio

SQL Server Management Studio provides an environment for managing and working with data mining models and OLAP cubes that already exist in a Microsoft SQL Server 2005 Analysis Services (SSAS) database. You can use Management Studio to connect to an Analysis Services database and complete the following tasks:

  • Process Analysis Services objects, such as cubes, dimensions and mining models.
  • Browse Analysis Services objects.
  • Create Data Mining Extensions (DMX), Multidimensional Expressions (MDX), and XML for Analysis (XMLA) queries.
  • Create scripts that alter, create, or delete Analysis Services objects.
  • Manage Analysis Services databases.

You can process both OLAP objects and data mining objects using Management Studio. To process an object in Management Studio, in Object Explorer, right-click a cube, dimension or mining structure, and then click Process.

For More Information: Processing in Analysis Services

Management Studio provides viewers for browsing cubes, dimensions, and mining structures. Management Studio uses the same viewers that are available in Business Intelligence Development Studio.

To access the viewers, in Object Explorer, right-click a cube, dimension, or mining structure, and then click Browse.

For More Information: How to: Browse Dimension Data in Dimension Designer, Data Mining in SQL Server Management Studio

Management Studio provides a query editor that you can use to create DMX, MDX, and XMLA queries. To access the query editor, on the toolbar, click the button that corresponds to the type of query that you want to create. After clicking the button, you may be asked to provide connection information.

For More Information: Editing Scripts and Files in SQL Server Management Studio, XML for Analysis (XMLA), Data Mining Extensions (DMX) Reference, Multidimensional Expressions (MDX) Reference

You can use Management Studio to create XMLA scripts that alter, create, or delete Analysis Services objects. For example, you can create a script that takes objects from one instance of Analysis Services and recreates those objects on another instance of Analysis Services.

In Management Studio, you can open the new script in the query editor, save the script to a file, or copy the script to the clipboard.

For More Information: XML for Analysis (XMLA), Scripting Objects Using SQL Server Management Studio.

By using Management Studio, you can manage roles on an Analysis Services database. Analysis Services uses roles to manage security for Analysis Services objects and data.

For More Information: Roles (Analysis Services), Granting User Access, Security Architecture

Management Studio also lets you to backup and restore an Analysis Services database. Backing up a database lets you save a particular state of the database and its objects. Restoring lets you restore the database to a previous state.

For More Information: Backing Up and Restoring an Analysis Services Database, Backup Options, Restore Options

In addition, Management Studio can synchronize two SQL Server 2005 Analysis Services databases. Synchronization occurs by copying the data and metadata from a database on a source instance to a database on a destination instance.

For more information: Synchronizing Analysis Services Databases.