Overview of SSMS Tools for Analysis Services

SQL Server Management Studio (SSMS) provides a rich set of tools for managing and developing Analysis Services (AS) databases. This documentation covers the integrated features within SSMS that enhance your experience when working with multidimensional and tabular models, processing data, designing cubes, and querying data.

Key Features and Functionality

SSMS integrates seamlessly with Analysis Services, offering specialized editors, designers, and management capabilities. Explore the following areas:

  • Object Explorer: Navigate, manage, and perform actions on Analysis Services databases, models, and objects.
  • MDX and DAX Editors: Write, debug, and execute Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX) queries with IntelliSense and syntax highlighting.
  • Model Designers: Visually design and modify both Tabular and Multidimensional models, including tables, columns, measures, relationships, and roles.
  • Query and Performance Tools: Analyze query performance, script objects, and manage processing operations.
  • Data Tools: Connect to and manage Analysis Services instances from a single interface.

Getting Started

To use SSMS with Analysis Services, ensure you have a compatible version of SSMS installed and an Analysis Services instance to connect to.

Important: Always use the latest supported version of SSMS for the best experience and access to the newest features.

Connecting to an Analysis Services Instance

  1. Open SQL Server Management Studio.
  2. In the "Connect to Server" dialog, select "Analysis Services" from the "Server type" dropdown.
  3. Enter the server name of your Analysis Services instance.
  4. Choose an authentication method and provide credentials if necessary.
  5. Click "Connect".

Tabular vs. Multidimensional Models

SSMS supports both Tabular and Multidimensional models. The tools and editors adapt based on the model type you are working with.

  • Tabular Models: Often preferred for their in-memory performance and familiarity to Excel users. DAX is the primary query language.
  • Multidimensional Models: Traditional OLAP cubes, offering powerful semantic layers and extensive customization. MDX is the primary query language.

Common Tasks

This documentation provides in-depth guides and tutorials for each of these tasks.

Example: A Simple DAX Query

Below is an example of a basic DAX query to retrieve total sales from a tabular model. 

EVALUATE
SUMMARIZECOLUMNS(
    'Product'[Category],
    "Total Sales", SUM('Sales'[SalesAmount])
)

Example: A Simple MDX Query

Here's a simple MDX query to get sales by year from a multidimensional model. 

SELECT
    {[Measures].[Internet Sales Amount]} ON COLUMNS,
    {[Date].[Calendar Year].Members} ON ROWS
FROM [Adventure Works DW]