Managing Analysis Services Using 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 Analysis Services 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.
Note
You cannot use SQL Server Management Studio to develop, manage, or query multidimensional data sets that were built by using PowerPivot for Excel. For more information about how to work with these instances, see Analysis Services in SharePoint integrated mode (PowerPivot for SharePoint).
Processing Analysis Services Objects
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 Analysis Services Objects
Browsing Analysis Services Objects
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
Creating Queries
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, Data Mining Extensions (DMX) Reference, Multidimensional Expressions (MDX) Reference
Scripting Analysis Services Objects
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:Scripting Objects Using SQL Server Management Studio.
Managing Analysis Services Databases
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:Security Roles (Analysis Services - Multidimensional Data), Granting User Access
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:Managing Backing Up and Restoring (Analysis Services), Backup Options, Restore Options
In addition, Management Studio can synchronize two SQL Server 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.
See Also