SQL Server Analysis Services

Multidimensional Models Documentation

SQL Server > Analysis Services > Multidimensional Models

Perspectives in Multidimensional Models

Perspectives in SQL Server Analysis Services provide a customized view of a multidimensional cube. They are useful for simplifying complex cubes by showing only the objects (dimensions, hierarchies, measures, and attributes) that are relevant to a specific business role or task. This allows users to interact with the data in a more focused and intuitive way.

What is a Perspective?

A perspective is essentially a subset of the objects within a cube. When a user connects to an Analysis Services database, they can choose which perspective to use. This choice determines which dimensions, hierarchies, measures, and attributes are visible and accessible within that session.

Benefits of Using Perspectives

  • Simplified User Experience: Reduces complexity by hiding irrelevant objects, making it easier for end-users to navigate and understand the data.
  • Improved Performance: Queries might be faster as Analysis Services only needs to consider the objects defined within the active perspective.
  • Enhanced Security: While not a primary security mechanism, perspectives can implicitly enhance security by limiting the visible data elements to users.
  • Role-Based Views: Tailor views for different departments or user roles (e.g., sales, finance, marketing).

Creating and Managing Perspectives

Perspectives are created and managed using SQL Server Data Tools (SSDT) or Visual Studio with Analysis Services projects. Within the cube designer, you can define new perspectives and select which objects to include.

Steps to Create a Perspective (using SSDT):

  1. Open your Analysis Services project in SQL Server Data Tools.
  2. Navigate to the Cube Designer for the cube you want to create a perspective for.
  3. In the Cube Designer, go to the Perspectives tab.
  4. Click New Perspective.
  5. Provide a descriptive name for the perspective (e.g., "Sales Manager View").
  6. In the perspective designer, check the boxes for the dimensions, hierarchies, attributes, and measures you want to include in this perspective.
  7. Save the project and deploy the changes to the Analysis Services instance.
Tip: When defining perspectives, consider the common tasks and questions of the target user group. Start with the essential elements and add more if necessary.

Example Scenario

Imagine a large retail cube with dimensions for products, customers, stores, and time, and measures for sales, profit, and quantity. A "Marketing Analyst" perspective might include:

  • Dimensions: Products (all attributes), Customers (demographics), Time (by year, quarter, month).
  • Measures: Sales Amount, Profit Amount.

A "Store Manager" perspective might include:

  • Dimensions: Products (by category and sub-category), Stores (store details), Time (by week, day).
  • Measures: Sales Amount, Quantity Sold.

Connecting to a Perspective

When users connect to an Analysis Services cube using client tools (like Excel, Power BI, or custom applications), they will typically be prompted to select a perspective if multiple are available. The connection string or client tool configuration can also specify a default perspective.

Example connection string snippet:

Provider=MSOLAP;Data Source=YourServerName;Initial Catalog=YourDatabaseName;Cube=YourCubeName;Perspective=Sales Manager View;

Further Reading