Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
A perspective allows you to control who sees what. By default, users of your model can see every table and every column (other than those hidden from client view):
Typically, users can see all of the tables and columns not hidden from client view.
What perspectives allow you to do is to present different tables to different people:
You can choose to be a biologist or a retail expert, to see different combinations of tables/fields.
For our example, this is what a Biologist might see:
The tables you might see if you used the Biologist perspective.
You can also use perspectives in pivot tables:
When you create a pivot table, you can choose which perspective to use.
Choosing the Biologist perspective as above would show you the following pivot table field list only:
All of the other tables and columns are either hidden from client view or are not contained within the Biologist perspective.
The above shows that perspectives are really well implemented in SSAS Tabular and easy to use. However, they don't provide any security - if you know what you're doing, it's easy to hack a pivot table to see the whole of the moon, rather than just the sliver that the perspective is choosing to show you.
The rest of this blog shows how to create and use perspectives in SSAS Tabular.
This page shows you how to set up and use perspectives in a tabular model.
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.
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.
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:
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.
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.
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.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.