PerformancePoint Top 10 Filter in Analytic Grid

Applies To: SharePoint 2010, SQL 2008 R2, PerformancePoint

Ran into a surprising issue with PerformancePoint today when creating a simple Analytic Grid. I had all the measures and dimensions setup correctly and decided it would be best to just show the top 10 rows. Fortunately, the Dashboard Designer has a handy Top 10 filter built in. Unfortunately, it doesn’t work.

When you apply the filter you will get a message like “There are no data rows to display” or “There is no data to display” or even “Drag measures, dimensions, or named sets to Rows and Columns to lay out the report.” This is especially irritating because it works just fine for Analytic Charts.

Some quick research revealed that the problem is with the generation of the MDX (Query) when working with the Analytic Grid and how SQL 2008 R2 handles it. You can request a hotfix for the problem (for SQL 2008 R2), but it apparently doesn’t fully fix the problem.

So, you’re pretty much stuck with a half fix from Microsoft. This is better than nothing and I would recommend doing it. However, if you’re like me and not in charge of the database management and will have to wait for that team to evaluate and apply the hotfix (if they even will), you might be interested in the workaround I’m using.

My workaround is pretty simple and fixes simple Analytic Grids you would like to start with the Top X type filters, but doesn’t fix the problem when these filters are applied on the fly by the end user using the dashboard. Basically, we take advantage of the fact that your grid’s report type can easily be switched in the designer and that Top X filters work fine for Analytic Charts. This solution is inspired by this Question and Answer on TechNet where Dan English delves into the auto generated MDX.

  • Create your report with all the dimensions, formatting, measures, etc. that you want with the exception of the Top X filter.
  • Right-Click on the Grid and select Report Type > Pie Chart
  • Now apply your Top X filter (Right-Click and select Filter > Top 10) – Generally you will select the Top 10 choice in the series section to apply it to one of the column values
  • Switch to the Query tab
  • Click in the text area, Select All (Ctrl-A) and Copy (Ctrl-C)
  • Switch to the Design tab
  • Right-Click and select Report Type > Grid
  • Switch to the Query tab
  • Click in the text area, Select All (Ctrl-A) and Paste (Ctrl-V)
  • Switch back to the Design tab to see it applied correctly

This solution is a little messy but it works until Microsoft releases a Service Pack with a full fix for the issue.

Note about Filters – Since the query has been edited, none of the automatic connection points exist. You will have to manually add parameters (Query Tab) to the report. For instance, I wanted to use a Time Intelligence filter with my top 10 list to show the top 10 for a given time period. With a regular report I would just add the calendar dimension to the Background and then hook up my filter by using the Source value of “Formula”. Now that the Top 10 filter has been applied and I have no automatic parameters, I simply replaced the Where portion of the query that had my background calendar dimension with a parameter (Query Tab). Then in the connection dialog of the dashboard I choose Source value equal to “Member Unique Name”.

OWSTimer Debugger Annoyances

Applies To: SharePoint 2010, Visual Studio 2010

If you’re running Visual Studio on the same machine with SharePoint 2010 you are probably familar with this error message:

“An unhandled exception (‘System.Security.Cryptography.CryptographicException’) occurred in OWSTIMER.EXE [#]. The Just-In-Time debugger was launched without necessary security permissions. To debug this process, the Just-In-Time debugger must be run as an Administrator. Would you like to debug this process?”

In fact, you are probably very familiar with this dialog since it will pop up at least once a day. If you haven’t logged in in a while, then you will have multiple windows to cancel debugging in.

The problem is due to a threading issue related to an encryption key used by the OWSTIMER service. In SharePoint 2010 the timer service gets recycled daily (default is 6 AM) using a timer job mysteriously called “Timer Service Recycle”. The details aren’t all that important, but you can read more here and get even more information about how the mistake really occurs here. To summarize, the key isn’t found due to impersonation issues. (BTW, that number at the end of the error message is just the process ID and will change each time.)

Bottom line for me is that the error is not really a problem and can safely be ignored in your logs. The annoyance comes when you have Visual Studio installed and the JIT debugger is enabled.

You can either adjust your settings using the registry, or even better, just open up Visual Studio (2010) and adjust your options. Using the menu, choose Tools > Options. Then expand Debugging from the tree on the left (if it isn’t showing, check the Show all settings box) then choose Just-In-Time. To turn it off, just uncheck all the boxes and press OK:

Now those annoying messages will stop and the people will rejoice. Just remember that it can be very helpful to turn these back on when attempting to debug certain types of things (Custom Timer Jobs for instance), but be sure to bookmark this page because you will forget to turn it back off and then you will be sad again and I don’t want you to be sad.

Verifying Constrained Delegation

Applies To: Active Directory

When using Kerberos with SharePoint 2010 you run into the requirement to use Constrained Delegation all over the place. Basically, even though you have the SPNs setup, you’ll need to specify which services your accounts can delegate to by using Active Directory. This is all covered elsewhere and can be found using some simple Google searches so I won’t go into any more detail.

The problem I run into is that I’m not allowed to set this up in Active Directory in our production environment and I have to trust someone else to do this. I don’t mind that, but I do want to be able to check the settings when troubleshooting. Using the Active Directory tools (Here’s a guide to getting these setup), even if you don’t have permission to edit anything, you can take a look at most of the account information. Unfortunately, the delegation tab has everything disabled.

Names hidden to protect the innocent

If you just have a few items setup, then you can see the first six or so, but you can’t scroll down and you can’t expand the entries. Why Microsoft made this impossible to view is beyond me, but you can get to it using the command line.

Since you’ve got the tools installed, fire up a command prompt (Go ahead and Run as Administrator). Then type this command:

ldifde -f C:\ConstrainedDelegation.txt -d "cn=SharePoint AppPool,ou=SharePoint,ou=Services,dc=MyDomain,dc=Com" -l msDS-AllowedToDelegateTo

This will write a list of all the services the account can delegate to (To see all the properties just leave the msDS-AllowedToDelegateTo off the end). Also, make sure you replace the part in quotes with the actual path to your account.

The easiest way to figure out the correct path is to open the Active Directory Users and Computers and expand the OUs (folders) until you find your account. Then take the display name of the account as the cn= part and work backwords up the “folders” specifying ou= for each. Finally, add the dc= for each part of your FQDN. Usually something.something. In the above example, I had expanded MyDomain.com then the Services folder and then then SharePoint folder to find my account named SharePoint AppPool.

In your face Microsoft! I used your tools to get around your tools! Hopefully this can help relieve some of the frustration of troubleshooting Kerberos errors during installation or configuration. Even with the above tip, you’re gonna want a hanky to cry into and a teddy bear to squeeze.

Wallpaper Happiness in Windows 7

Applies To: Windows 7

This is not the usual technical article, but I wanted to share something I’ve done that makes my day a little better. I’ve taken a bunch of relaxing or neat wallpaper photos and using Windows 7 I’ve set them up to automatically change every minute. This is super easy to do and takes just a few minutes, so… Get on it!

The best way to get some good looking photos is to use a Windows 7 Theme. To get these, just right-click on your desktop and choose Personalize. This will open the Personalization Control Panel:

Then click the Get more themes online link in the control panel:

This will open the Windows 7 Themes gallery site in your browser. Look through the themes and pick one to start with. For me, some of the best were in the RSS dynamic themes category. Terra Dynamic is a good one to start with. Click Download and then choose Open:

This will launch the Personalization control panel and install the theme. If it is one of the RSS dynamic themes you will be prompted to download the attachments, choose to download them:

It may take a few minutes, depending on the theme, to finish downloading all the images. To get at them, click on the Desktop Background button at the bottom of the Control Panel. Then to find the location of these images, right-click on the path above the thumbnails. This may cause a Windows Security warning depending on your settings. If it does, choose OK:

In the Context Menu, choose Properties:

In the properties dialog select the full path in the Location section (Leave out the All in text) and right-click and choose Copy:

Then open a folder and paste the copied path into the path url box and press enter:

This will open the folder with all of the images. Select all the images and copy them to some folder in your My Documents or elsewhere. Then repeat the above process for as many themes as you want.

Dynamic themes (RSS) will store their images in the same location (they’ll just have a different GUID – ugly number in brackets), so you can just leave that folder open. Standard themes will save their images to C:\Users\[Your User Name]\AppData\Local\Microsoft\Windows\Themes and will have a folder that matches the theme name. In those folders will be a folder of images you can copy from.

To uninstall the theme, just delete it’s folder from the themes folder.

Once you’ve assembled a ton of images from all sorts of themes in a single folder, briefly go through them and get rid of any that doesn’t immediately appeal to you. I have around 250 images in my folder. Open the Personalization Control Panel again. Pick any theme (preferably one with sounds you like) and click the Desktop Background button.

Click the Browse button next to the Picture location dropdown and choose your folder. Choose Fill for the Picture Position and set the “Change picture every” to something you like (mine is 1 minute). I also picked Shuffle so that it would pick a random order each time. When you are satisfied, click Save changes.

That’s it! You now have a beautiful new image to look at every minute or so as you are working! The good news is since this is your folder, you can drop images from anywhere in there and they will automatically get added to the rotation.

As you watch them change you can pretend you live in the photo or are at least visiting instead of sitting in a dark cubicle slowly developing diabetes and heart disease! When your boss tells you that your number one priority is a list of like 5 things that are all “equally important” because he/she doesn’t understand priority or your coworker sprays perfume on his feet because they smell so bad, you can just stare at the picture of some field somewhere and put off the madness for one more day.