Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for March, 2012

Using Perspectives with PerformancePoint Services

Posted by denglishbi on March 28, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

One of the great features of using PerformancePoint Services in SharePoint Server is the ability to be able to drill down to (or cross drill) and perform additional ad-hoc analysis.  This features is built into the analytical reports, both grid and chart, as well as the decomposition tree.  Here is what the feature looks like with each of these options along with the output in an existing dashboard page in SharePoint:

Analytical Reports (Chart and Grid)

Initial Report Drill Down To menu Final Result
image image image
Sales chart displaying data by Product Category Able to Drill Down To any of the 14 dimensions that are in cube Home Appliance sales data drilled down to Machine Type – unrelated dimension to sales
image image image
Sales grid displaying data by Product Category Able to Drill Down To any of the 14 dimensions that are in cube Home Appliance sales data drilled down to Geography Region Country

Now the one thing you might have noticed when using this feature is that the list of available dimensions (entities) that are available in the list can become overwhelming at times if you are utilizing cubes that include many dimensions and fact tables (measure groups).  It is quite common that when cubes are setup this way that not all of the dimensions are related to each fact table.  Like if we look at this dimension usage design you can see that there are 14 dimensions and 7 measure groups and that there are quite a few instances where the dimensions are not related to the measure groups.  This can produce odd results based on how the measure group is setup in the cube such as the chart results above after the sales data was analyzed by machine type.


In these cases you then have to evaluate whether or not it even makes sense to include the fact tables in the same cube.  That is one option of course when designing your model, another is the ability to limit the items that you present to your users to help limit the scope.  The way you can do this is with the use of perspectives.  A perspective is a way that you can limit the scope of items that are presented to a user to help remove the clutter of items that are available.  This is subset of the cube and can then be exposed to users to select from so that they can quickly perform analysis on just the items that they need to reference.  Perspectives are not an option to use for security purposes, just an option to use to filter the items for analysis.

NOTE: In Analysis Services perspectives are only available as an Enterprise Edition feature (which is also available in the Developer and Evaluation Editions) with SQL Server (2005 through 2008 R2 versions). Now with SQL Server 2012 perspectives are also available in the new Business Intelligence Edition of the product (for both Multidimensional and Tabular) as well as in the new version of PowerPivot for Excel.

Before we explore this let’s take a quick look at the other type of visualization available for analysis that is provided in PerformancePoint that allows you to cross drill, the decomposition tree.

Decomposition Tree

Initial Report Drill Down To menu Final Result
image image image
Home Appliance sales amount broken out by Product Subcategory Able to Drill Down To only the related dimensions in the cube Washers & Dryers sales amount broken out by Channel Name

Notice anything different here? This type of report has built-in logic that only displays the related dimensions available to the measure that you are analyzing.  Pretty cool.

Ok, now back to our initial issue that we saw with the Analytical Reports.  If we look at the cube designer above and go to the perspectives tab we can see that three perspectives (IT Machine, Inventory, and Sales) have been defined for particular areas to focus in on the items that are relevant for their analysis:


So if we go back into PerformancePoint and if we setup our data source to reference the perspective instead of the cube we can then design our Analytical Reports off that data source.  In doing so we will then be able to limit the items presented to our end-users just like the decomposition tree does so that it only displays items relevant for analysis.

Here is an example of the chart initially displayed and the Drill Down To list based on a reference to the Sales perspective instead of the Operation cube in our Analysis Services database.


Now instead of being presented with all 14 dimensions we only see 11 of them.  In this case the Scenario dimension is setup in the Sales perspective and that is needed to evaluate the Sales Quota data.  We can see that in the decomposition tree that it filtered that dimension out.  Now you have the ability to control what is available for users by the use of perspectives.  These can also be used with out tools such as Report Designer, Report Builder, Excel, etc.  When users connect to your database they can select from a drop-down list and connect to a perspective if you have them defined and also if you are using a Edition of SQL Server that supports this.

As I mentioned in the Note above, in SQL Server 2012 perspectives are available now in the Business Intelligence Edition as well as Enterprise.  You can also defined perspectives now in the new release of PowerPivot for Excel as well.


When you are in the PowerPivot Window you will need to enable the Advanced Mode option in the file menu and then you will see the Perspectives button in the Advanced tab of the Ribbon.  This will only be active if you have data imported into the PowerPivot Window.  These PowerPivot files can then be utilized in PerformancePoint and I highlighted the new features in a previous posting (I did not mention perspectives though…) – Using PerformancePoint Services (PPS) with PowerPivot SQL Server 2012 RC0.


As you have seen it can be very helpful to limit the scope of items presented to a user for analysis.  As cubes get larger and more entities are added for analysis along with fact tables it can become quite overwhelming to end-users.  We can help out the users by providing perspectives which can provide a limited scope of items that are needed for analysis and that are related.  This can be very helpful and simplify things for end-users and can reduce confusion.  I would highly recommend that before you dive into creating lots of Analytical Reports that you define perspectives in your models.  The reason for this is because you cannot change the data source an Analytical Report is associated with once you have created it.

Happy analysis!

Posted in PerformancePoint | Tagged: , | 1 Comment »

PASSMN March 2012 Meeting

Posted by denglishbi on March 9, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

The next Minnesota SQL Server User Group meeting is coming up in a couple of weeks. This month we have a couple of SQL Server MVPs teaming up, Thomas LaRock (@SQLRockstar) and Jason Strate (@StrateSQL), and they will be speaking on SQL Server performance tuning – this is guaranteed to be a fun and interactive presentation (I just have a hunch). Don’t forget the new meeting location which is at the Minneapolis Microsoft Technology Center in Edina.

Minnesota SQL Server Users Group

The sponsor for this month’s meeting is Confio Software.

Location: 3601 West 76th Street, Suite 600 Edina, MN 55437

Date/Time: Tuesday, March 20 from 3 to 5 p.m. Note this be starting promptly at 3 p.m., to enable our speaker to catch a flight

Live Meeting:


  • 3:00-3:15 : Registration / hospitality / networking
  • 3:15-4:30 : Choose Your Own Adventure: Performance Tuning (Thomas LaRock and Jason Strate)
  • 4:30-4:45 : Local New Presenter!
  • 4:45-5:00 : Closing comments/prize giveaways

Please click here for meeting details and to RSVP for the event

Choose Your Own Adventure: Performance Tuning (Thomas LaRock, Confio Software and Jason Strate, Digineer) – Life is all about choices, and when it comes to SQL Server it is no different. If you don’t have a methodology for researching questions and resolving issues then you need to get one, and soon. Join Microsoft MVPs Jason Strate from Digineer and Thomas LaRock from Confio Software as they help guide you while YOU make the choices in this interactive performance tuning adventure.

If you want to check out the upcoming PASSMN meetings that are planned check out Steve Hughes post here – PASSMN Rocks 2012!  You definitely don’t want to miss next month’s, great lineup.

Posted in Training | Tagged: , | Leave a Comment »

SQL Server 2012 RTM is here!

Posted by denglishbi on March 7, 2012

[tweetmeme source=”denglishbi” only_single=”false”]

Yesterday I was at the Minneapolis Microsoft Technology Center and I received the news short after 11 a.m. that SQL Server 2012 went RTM and the evaluation version is available to download.sql2012

The SQL Server 2012 Virtual Launch is going on today – http://sqlserverlaunch.com, so don’t miss out on that. I heard that over 20,000 people are registered, so this is going to be one of the largest online launch events.

You can grab the downloads here – http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx.

On this site you will find the links for:

To find out what has changed in the new version of SQL Server 2012 checkout the online material available here – What’s new in Microsoft SQL Server 2012.

Also, don’t forget that the SQL Server 2012 Special Ops Tour is going on as well.  The one for Minneapolis still has seats available.  Make sure you register for that today as well – http://specialopstour.com.


Posted in SQL Server | Tagged: | Leave a Comment »