This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Using tabular model perspectives to restrict which tables a user sees Part two of a two-part series of blogs
Perspectives in SSAS tabular are windows which allow you to control who sees which bits of your model. They're really easy to set up, as this blog shows, but aren't a substitute for setting security roles. An overview of perspectives in tabular models Creating and using perspectives in SSAS Tabular (this blog) This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Using tabular model perspectives to restrict which tables a user sees

Part two of a two-part series of blogs

Perspectives in SSAS tabular are windows which allow you to control who sees which bits of your model. They're really easy to set up, as this blog shows, but aren't a substitute for setting security roles.

An overview of perspectives in tabular models
Creating and using perspectives in SSAS Tabular (this blog)

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 18 February 2016

Creating and using perspectives in SSAS Tabular

This page shows you how to set up and use perspectives in a tabular model.

Creating perspectives

Assuming you're editing a model, here's how to create a perspective of it (onto it? over it?). First go into the Perspectives menu:

Choose to create a new perspective from the menu.

Now choose to create a new perspective:

Here we've already created one perspective called Retail expert, and are about to add another.

You can now tick the tables and columns you want to be visible in your new perspective, as well as giving it a name:

Here we're letting biologists view the classification of products bought, but that's about all.

When you've finished, select OK.

Using perspectives within a model

There's a drop down menu on the main SSAS toolbar which allows you to change perspective for a model:

Simply click on the drop arrow and choose to view one of the perspectives that you've created.

Note that if you didn't include id fields in your perspective, your tables would now appear unrelated:

How the tables would appear if you'd unticked the id fields when creating the Biologist perspective.

However, the id fields (and relationships between them) are still part of your model, so that you'll still get sensible pivot tables based on it:

Even though you can't see the id columns or the relationships joining them, they still form part of your model, allowing you to aggregate data correctly.

Creating pivot tables based on a perspective

When you create a pivot table from SSAS tabular, you can choose the perspective:

Choose from the drop down list who you want to be!

Excel displays only those tables and columns which are:

Included in the model; and
Not hidden from client view; and
Included within your perspective.

Here's what you'd see for our Biologist perspective:

You can only see columns which are included in the perspective and which aren't hidden from client view.

Changing the perspective for a data connection

If you have a connection (for example, in Excel), you can change which perspective it uses as follows. First display the connection you want to change:

In Excel you'd first choose to click on this tool on the Data ribbon.

Choose to change the properties of this connection:

Click on this button to change the properties of your connection.

You can now fill in the dialog box which appears as shown below:

Step 1: click on the Definition tab; Step 2: type in a valid perspective name; Step 3: select OK.

Note that you can also type in the command text as the word Model, to revert to viewing everything.

Changing the perspective when browsing a cube in SSMS

If you're in Management Studio browsing a tabular model, you can change which perspective you're using too:

Click on the build button next to the word Model, as shown above.

You can then select the perspective that you want to show:

Choose which of the perspectives you've created that you want to use.

And that's about everything you can do with perspectives. I really like the way Microsoft have implemented them: nice and simple, which is always a good thing in software.

Parts of this blog
An overview of perspectives in tabular models Creating and using perspectives in SSAS Tabular (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR