PerformancePoint Troubleshooting

I recently had to do some troubleshooting for PerformancePoint in SharePoint 2013. There were several different things that ended up being wrong with this particular farm, so I figured I’d do a write up on the problems I encountered and how they were resolved. The primary problems in the order I encountered them were:

  1. The user was completely unable to open Dashboard Designer.
  2. The user was unable to successfully connect to SharePoint with dashboard designer, or when they did none of the data source or content libraries would be available
  3. The user was unable to create or save data sources in dashboard designer once connected to SharePoint
  4. The PerformancePoint service application was throwing errors any time I tried to access the “manage” or “property” settings in Central Administration

I think the best way to approach these problems would be in the order that they were encountered, so let’s get to it.

1. The user was completely unable to open Dashboard Designer

When the user tried to open the Dashboard Designer from their PerformancePoint content library they were greeted with the following error message:

Cannot retrieve application. Authentication error

In this scenario, the user was logged into their computer locally (ie not using a domain account), however they were logging into SharePoint using a domain account. To get around this issue we used the “Windows Credential Manager” to tell SharePoint what account it should be using for authentication. To setup the Windows Credential Manager, follow these steps:

  1. Navigate to the Control Panel in your Windows OS
  2. Locate and click on the “Credential Manager” icon in the control panel
  3. Next to the “Add a Windows credential” link
  4. In the textbox labeled “Internet or network address” type in the address of the SharePoint site you’re logging into. You’ll want to exclude the http:// though, and you do not need to include any sub-site directory. For example, if I was opening Dashboard Designer from a sub-site with an address of http://portal.contoso.com/BITestSite all I would need to enter is portal.contoso.com
  5. Type in the appropriate credentials in the “User name” and “Password” textboxes. Don’t forget to include the domain in your username (domain\username)

    clip_image001
  6. Click OK

The above seemed to resolve the problem of not being able to open Dashboard Designer. SharePoint must check the login of the Windows machine somewhere behind the scenes.

2 - The user was unable to successfully connect to SharePoint with dashboard designer, or when they did none of the data source or content libraries would be available

Now that we can actually get the PerformancePoint Dashboard Designer to open, we quickly ran into our 2nd issue. In this particular case every time the user opened PerformancePoint Dashboard Designer, the designer would open but would not be connected to SharePoint. First we tried manually entering in the SharePoint URL into dashboard designer and connecting. This can be done by doing the following:

  1. Click on the “Jewel” button at the top left of dashboard designer
  2. Click the “Designer Options” buttons from the resulting menu
  3. In the resulting options menu, click the “Server” tab
  4. Type the URL of the top level SharePoint site

    (This URL should be the to the top level site, not a sub-site or a specific library. For example if I’m opening Dashboard Designer from the PerformancePoint content library located at http://portal.contoso.com/SSRSClass/Lists/PerformancePoint%20Content/By%20Content%20Type.aspx all I want to enter as the SharePoint URL is “http://portal.contoso.com” entering an incorrect URL here will most likely result in the following error message: “Cannot contact site at the specified URL xxxxx There is no Web Named xxxxx”)

    clip_image003
  5. Click connect and enter in your log on credentials when prompted

At this point we started getting the following error: “The URL must be in one of the following zones: Local, Intranet, or Trusted”

Well, that seems simple enough to fix. Just add the site to your trusted sites. That can be done by following these steps:

  1. Navigate to the Windows Control panel
  2. Click on the “Internet Options” icon
  3. Click on the “Security” tab
  4. Click on “Trusted Sites” (looks like a green checkmark)
  5. With Trusted Sites highlighted, click on the “Sites” button
  6. Enter the top level URL into the first textbox show in the image below (in my case this would be http://portal.contoso.com) and click the “Add” button

    If you get an error stating “Sites added to this zone must use the https:// prefix. This Prefix assures a secure connection” simply uncheck the box labeled “Require server verification (https:) for all sites in this zone” on the Trusted sites screen before clicking the Add button.

    clip_image004
  7. Click the “Close” button on the Trusted sites window
  8. Click “OK” on the Internet Properties window
  9. Try to connect Dashboard Designer manually by entering the URL (as described earlier)

This time we were able to successfully connect, and the lower left hand corner of Dashboard Designer was displaying our user name and site. However, Dashboard Designer may still not work properly. If you continue to experience problems, read the next section.

3 - The user was unable to create or save data sources in Dashboard Designer once connected to SharePoint

Even though Dashboard Designer is now working, you might still run into problems. I’ve seen different error messages in different environments when users are trying to perform different actions. A few examples of error encountered (typically when trying to create or save a data source) are as follows:

“An unexpected system error has occurred. Additional details have been logged for your administrator.”

“Access denied. You do not have permission to connect to the server. Please contact your system administrator.”

“The data source no longer exists or you do not have permissions to view it. Additional details have been logged for your administrator.”

I’ve even seen some cases where users can create content (kpi’s, dashboards, etc) but can’t create or save data sources at all. These errors seem to come about when the account that is running the application pool for PerformancePoint doesn’t have the correct permissions. So there are a few things that we need to figure out here. First we need to figure out what application pool PerformancePoint is running under. Secondly we need to figure out what account is running that application pool. Lastly, we need to give that account the appropriate permissions. To do all of this we’re going to use some PowerShell cmdlets.

  1. Log into the SharePoint server that has Central Administration installed on it
  2. Open “SharePoint 2013 Management Shell”
  3. If this is your first time using the management shell you’ll want to copy and paste in the following command

    Add-PSSNapin microsoft.sharepoint.powershell
  4. Run the following command to return a list of all the PerformancePoint properties

    Get-SPPerformancePointServiceApplication
  5. Scroll back up to the top, and locate the name of the application pool being used. In my case below, it’s “Default SharePoint Service App Pool” shown highlighted in yellow in the image below

    clip_image006
  6. Next we’ll want to figure out what account is being used to run the application pool shown above. To do that run the following command without quotes:

    Get-SPServiceApplicationPool

    clip_image008 
  7. As you can see in the image above, in my case the account “CONTOSO\TestNoAccess2” is being used

  8. Now that we’ve got our account, all we need to do is give it the appropriate permissions to our web application. Run the following commands replacing the web application and app pool account with your own information:

    $w = Get-SPWebApplication -Identity http://portal.contoso.com
    $w.GrantAccessToProcessIdentity("Contoso\TestNoAccess2")

If all has gone well, you should now be able to create data sources etc inside of SharePoint designer.

4 - The PerformancePoint service application was throwing errors any time I tried to access the “manage” or “property” settings in Central Administration

This last error I encountered came when I was trying to use Central Administration to access any of the settings for the PerformancePoint Service Application. The error would occur any time I tried to click on the “Manage” or “Properties” buttons for PerformancePoint. PerformancePoint would also throw an error if I tried to access those screens by clicking on the Service Application itself and then clicking “PerformancePoint Service Application Settings”. The error I kept getting was:

“Sorry, something went wrong

An entry with the same key already exists.”

It should be noted, that other than not being able to access these screens, PerformancePoint was working just fine. I tried restarting services, deleting and recreating both the PerformancePoint and Secure Store service applications in every order imaginable(a solution I had read on a few other blogs) but still couldn’t get rid of the error. Ultimately I ended up calling in some reinforcements in the form of SharePoint admin extraordinaire Stephen Wilson. He noticed that there were actually 2 “Access Services” service applications installed with the exact same name. One was for 2010, and one was for 2013, but both were just named “Access”. We were able to re-create the error in my dev environment with ease by simply recreating the duplicated Service Application name scenario with Access in my environment.

So…what to do about it? If you know only one of the Access Service Applications are being used, delete the one that isn’t being used, that immediately corrected the problem in my development environment.  However if you don’t know, you might want to tread lightly. The only reason I needed to get to those screens anyway was set the unattended service account, an act that can be achieved via PowerShell pretty easily. For example, you can use the script below to both create an account in the Secure Store and associate it with PerformancePoint:

Set-SPPerformancePointSecureDataValues -ServiceApplication "PerformancePoint Services" -DataSourceUnattendedServiceAccount (New-Object System.Management.Automation.PSCredential "CONTOSO\YourUserName", (ConvertTo-SecureString "YourPassword" -AsPlainText -Force))

Just add your specific PerformancePoint Service App name, and the user id and password.

You can actually get and set just about any value you want using PowerShell. Here’s a list of all the PowerShell commands for PerformancePoint:

http://technet.microsoft.com/en-us/library/ee906547(v=office.15).aspx

As I like to say ….it’s just that easy! Well I think that just about wraps up this post, hope you found it helpful.

The Dark Art of the Excel Services Workbook Data Refresh

In my last blog post we talked about how to create an Excel Services dashboard that pulls in information from SharePoint lists using OData feeds. This post is a continuation of that, but can also be applied to other data sources your Excel workbook might be connected to. In this walkthrough we’ll be taking a look at how you can get your dashboard to refresh in the following ways:

  1. User manually refreshes the workbook
  2. Workbook automatically refreshes every time it’s opened
  3. Workbook automatically refreshes every x minutes (even while it’s open, or in a web part)

In this walkthrough I’ll be using SharePoint 2013 on prem. If you’re going to follow along you’ll need to have access to (and some familiarity with) SharePoint Central Administration….or you’ll need to become good friends with your SharePoint administrator (They’re probably a really nice person, you should take them out to lunch). While this walkthrough is meant to be read in order, I’ve decided to break it up into section as follows for readability (hopefully)

  1. Configuring the unattended service account for Excel Services
  2. Configuring authentication options in your Excel workbook for manual refresh
  3. Setting the cache duration - (aka why you’re not seeing your data change immediately)
  4. Make your workbook refresh as soon as it’s opened (and kill that refresh warning)
  5. Make your workbook refresh right before your eyes while it’s open
  6. Make Excel web parts refresh right before your eyes

1 - Configuring the unattended service account for Excel Services

One of the first things we need to consider when we talk about an Excel Services workbook refreshing is:

  • What account will be used to access the data that Excel is going to refresh/go get?

After all, security is kind of a big deal, particularly when it comes to data. There are several ways to approach this, but we’re going to use what is probably the most common method and set up a service account, i.e. an account that is used specifically for getting data from our data source (in this case a SharePoint list). When we want to create a service account for these purposes in SharePoint, we call it an “Unattended Service Account”. Several service applications inside of SharePoint (including Excel) support the assignment of an unattended service account to them. Once an unattended service account has been set for a service application, it will use that account every time the service application needs to go get data.

OK great, so how do we set that up? I’m glad you asked! First we need to create what’s called a “Target Application ID”.  Target Application ID’s are basically a set of credentials that we want to use for something (in our case accessing data) and they live in their own SharePoint service application called the “Secure Store”.  Once you have a Target Application ID created, you can assign it to another service application (like Excel Services) as the “Unattended Service Account”.  To configure a Target Application ID in the Secure Store, we’re going to need access to SharePoint Central Administration. If you have no idea what Central Administration is or how to get to it, now would be a good time to go get your SharePoint administrator to help you along. During the steps to follow, we will be changing settings that have to potential to break existing workbooks that may already be using these services if someone has already configured them for other purposes.

The first thing we’re going to do is manually configure our Target Application ID in the Secure Store service application. Follow these steps:

  1. Open Central Administration
  2. Click on “Manage service applications” under the “Application Management” heading
  3. Locate the Secure Store service application and click on it (not the proxy).

    If you’re having problems locating your Secure Store, it’s possible your administrator named it something different. Look in the “Type” column for “Secure Store Service Application” to help you find it. If you still can’t find it, it’s possible that the service application hasn’t been created yet. If this is the case you’ll need to create it. I suggest going here and watching the video to help you along: http://technet.microsoft.com/en-us/library/ee806866(v=office.15).aspx
  4. Click the “New” button at the top left hand corner
  5. Type “BlogTestAccount” for the “Target Application ID” and then make the necessary adjustments to make your screen match the screenshot below

    clip_image001
  6. Click “Next”
  7. Click “Next” again to accept the default “Field Name”, “Field Type” etc.
  8. In the box labeled “Target Application Administrators” enter an account that you want to be able to make changes to this Target Application ID(probably your own account at very minimum).
  9. In the box labeled “Members” you want to enter the users and groups who will be allowed to use this this service account. If you have specific users that are having problems refreshing your workbook later, there’s a good chance it’s because you haven’t included them here.

    clip_image003
  10. Click “OK”

You should now see “BlogTestAccount” in your list of Target Application ID’s.  The next thing we need to do is actually assign an account to “BlogTestAccount” for it to use when it tries to get new data. To do that, follow these steps:

  1. Place a check in the Target Application ID box next to “BlogTest Account”
  2. In the ribbon at the top of the screen, click on the “Set” button in the “Credentials” group (not the set button in the permissions group)
  3. Type the user name (including domain) and password for an account that has access to the data you’re going to be refreshing (aka your SharePoint list). Be sure to type this correctly, as this screen will not validate your account other than confirming that the passwords match each other

     clip_image004
  4. Click “OK”

Alright now that we’ve got our new Target Application ID all ready to go, we need to decide if we want Excel Services to use it as the default account every time it tries to go get data. Alternatively you can manually configure each of your workbooks individually to use a Target Application ID (we’ll cover how to do that a little later). If you want to have Excel use your “BlogTestAccount” Target Application ID as its unattended service account, follow these steps:

  1. Open Central Administration
  2. Click on “Manage service applications” under the “Application Management” heading
  3. Locate the Excel service application and click on it (not the proxy).

    If you’re having problems locating Excel, it’s possible your administrator named it something different. Look in the “Type” column for “Excel Services Application Web Service Application” to help you find it. If you still can’t find it, it’s possible that the service application hasn’t been created yet. If this is the case you’ll need to create it. I suggest going here and watching the video to help you along: http://technet.microsoft.com/en-us/library/jj219698(v=office.15).aspx
  4. Click on “Global Settings”
  5. Scroll down to the very bottom of the “External Data” section and select “Use an existing Unattended Service Account”
  6. In the box labeled “Target Application ID” type the name of the Target Application ID we just created “BlogTestAccount”

    clip_image005
  7. Click ”OK”

Congrats! We’ve finished configuring our unattended service account. Now it’s time to put it to some good use.

2 - Configuring authentication options in your Excel workbook

We’re not going to cover how you create an Excel workbook that connects to a SharePoint list using OData in this walkthrough, because I’ve already covered that here: http://tavislovell.com/create-a-dashboard-from-your-sharepoint-list-data-using-excel-services-and-the-excel-web-access-web-part/

So if you haven’t already read that post, go do so now and create yourself an Excel workbook. If you’ve already read that post, let’s get down to business and talk a little bit about authentication. With your Excel workbook open, follow these steps to get to the authentication options:

  1. In the ribbon at the top, click on the “Data” tab
  2. In the “Connections” group of the data tab, click the “Connections” button

    clip_image006
  3. Highlight the connection in your workbook, and then click the “Properties” button

    clip_image007
  4. Click on the “Definition” tab of the resulting Connection properties window
  5. Click on the “Authentication Settings” button at the bottom of the window

    clip_image008

At this point you should be staring directly into the eye of the beast (aka looking at the “Excel Services Authentication Settings” screen as shown below).

clip_image009

Let’s have a little talk about this screen, for it is here where I’ve seen many a man stumble and fall on their quest for Excel workbooks that refresh. There are only 3 options, but we must choose wisely. Let’s cover them all though.

OPTION 1 - “Use the authenticated user’s account”: In this scenario when the Excel workbook tries to refresh itself, it’s going to try to get the new data by pretending it’s you and using your account. HOWEVER, there’s a good chance it is going to fail at that task unless you have Kerberos setup in your environment. With that said, we’re going assume that you do not have Kerberos setup, so this option is of no use to us.

OPTION 2 - “Use a stored account”: This is a viable way for us to get the job done. Remember when we created our Target Application ID (BlogTestAccount) in the first section of this walkthrough? Well if we wanted to use that specific account to go get our data when the workbook refreshes, we would simply select this options and then type in “BlogTestAccount”. Feel free to use this option.

OPTION 3 - “None”: With this option, when the workbook refreshes it’s going to use the Target Application ID that was set as the unattended service account for the Excel Services service application (we also did this in the first section of this walkthrough). So this is also a perfectly good option to use assuming you were following along.

To finish this section simply choose option 2 or 3 as described above and then click “ok” back to the workbook and then save your workbook.

**NOTE** for whatever reason I’ve seen the changes above sometimes revert back to option 1 when using OData as a data source. For some reason this seems to happen almost without fail when I set these options as I’m creating a new data connection. After you save your workbook, I highly suggest going back into these settings and double checking that they’re still set appropriately.

At this point you should be able to upload your workbook to SharePoint, open it and manually refresh it using the data tab at the top. Don’t be surprised if you update the SharePoint list with a few new values, then manually refresh your Excel workbook only to see it’s not getting the new data. That’s because your data is being cached, and we’ll talk about that in the next section. The main goal here is that you’re not getting any errors when you try to refresh the workbook, and if that’s the case you’re in good shape. If you get a warning about the refresh, that’s also ok, we’ll deal with that in a bit as well. Now let’s take a look at that caching problem of ours.

3 - Setting the cache duration - (aka why you’re not seeing your data changes)

So you want to see the data in your workbook refresh on the spot, and always get the most up to date data whenever you manually refresh. Well, we could do that, but the cache is really there for a reason. It’s protecting your resources so they don’t grind to a halt if a bunch of people are constantly hitting refresh. The default cache for the refresh is 5 minutes, and personally I don’t think that’s all that long to wait. However if you wanted to adjust the amount of time that data is cached, here’s how you would do it:

  1. Open Central Administration
  2. Click on “Manage service applications” under the “Application Management” heading
  3. Locate the Excel service application and click on it (not the proxy)
  4. Click on “Trusted File Locations”
  5. You should have one entry with an address of “http://”. Click on it
  6. Scroll down to the bottom until you get to the “External Data” group
  7. Look for the heading “External Data Cache Lifetime” as shown below

clip_image010

As you can see there are two different types of refresh you can configure the cache for, and they’re each measured in seconds (300 seconds works out to 5 minutes). Thus far we’ve only talked about manual refreshing, so if you’d like to change that value simply change it in the “Manual refresh” textbox and then click OK at the bottom of the screen. In a bit we’ll be taking a look at how you can have your workbooks automatically refresh every time they’re opened, or even while someone has it open and is currently looking at it. To change that cache value, simply change the value listed in the “Automatic refresh (periodic / on-open” textbox. If you’re on a dev farm with not many users, feel free to set the cache values to zero if you want to test stuff out, but in a production environment remember that the cache is your friend.

4 - Make your workbook refresh as soon as it’s opened (and kill that refresh warning)

At some point you might grow tired of having to manually refresh your workbook every time you open it. Can’t we just make the refresh happen auto-magically? Yes we can, and here’s how:

  1. Open your workbook in Excel
  2. In the ribbon at the top, click on the “Data” tab
  3. In the “Connections” group of the data tab click the “Connections” button

    clip_image006[1]
  4. Highlight the connection in your workbook, and then click the “Properties” button

    clip_image007[1]
  5. Place a checkmark in the box labeled “Refresh data when opening the file”

    clip_image011
  6. Click OK
  7. Close the “Workbook Connections” window
  8. Save your workbook
  9. Re-upload your workbook to SharePoint

Now every time someone opens the workbook it will refresh (although the caching described in section 3 still applies). Note that having your workbook manually refresh every time it’s opened isn’t always a great idea, particularly for workbooks with long refresh times.  

By default Excel services will prompt the user with a warning any time it’s about to run a query.  If you’ve set up your workbook to refresh on open, that warning will be the first thing you see every time you open the workbook, and it can get mighty old dismissing it over and over again.  If you’d like to fashion a pair of cement shoes for that warning here’s how you might go about it:

  1. Open Central Administration
  2. Click on “Manage service applications” under the “Application Management” heading
  3. Locate the Excel service application and click on it (not the proxy)
  4. Click on “Trusted File Locations”
  5. You should have one entry with an address of “http://”. Click on it
  6. Scroll down to the bottom until you get to the “External Data” group
  7. Look for the heading “Warn on Refresh” and uncheck “Refresh warning enabled” as shown below

    clip_image012
  8. Click “OK” at the bottom of the screen

Laugh maniacally as that warning sleeps with the fishes.

5 - Make your workbook refresh right before your eyes while it’s open

Occasionally  I have someone who wants to create a dashboard in Excel, and they want to put it up on a big screen TV somewhere and have it live update in “real time”. Well, I dunno about “real time” but when most people say “real time” what they REALLY mean is “near real time”…and that we can do. But before we do, we should probably have a little discussion about what is and isn’t practical. The shortest refresh period you can set for an Excel workbook out of the box is 1 minute. So once every minute Excel is going to go out and re-query data (the caching from section 3 still applies here…go read that if you skipped it). Now this can create quite a workload for SharePoint, and whatever data source it’s pulling from. Some workbooks might not even finish refreshing in a minute. So….don’t set all your workbooks to refresh every minute. Find out how long the refresh even takes by doing some manual refreshes, and remember the amount of data is probably going to grow over time, so refreshing might start taking longer in the future. Then find out how “real time” the dashboard REALLY needs to be from a practical perspective. The less real time, the less workload it is on all the systems involved. That said, let’s make some magic happen:

  1. Open your workbook in Excel
  2. In the ribbon at the top, click on the “Data” tab
  3. In the “Connections” group of the data tab click the “Connections” button

    clip_image006[2]
  4. Highlight the connection in your workbook, and then click the “Properties” button

    clip_image007[2]
  5. Place a checkmark in the box labeled “Refresh every”
  6. Set the number of minutes Excel will wait between each refresh

    clip_image013
  7. Click OK
  8. Close the “Workbook Connections” window
  9. Save your workbook
  10. Re-upload your workbook to SharePoint

That should do it, you’re dashboarding like a pro now! You should be able to have your Excel dashboard open, then open a new tab and change/add/delete a value or two in the SharePoint list it’s pulling from, then watch in awe as your dashboard updates whenever the next refresh hits (remember cache settings still apply, see section 3 for details)

6 - Make Excel web parts refresh right before your eyes

Welcome to final section on refreshing in Excel. You’re probably just about ready for this walkthrough to be over so I’ll make this fast. I covered how to display your Excel workbook items in web parts in my last blog post. There’s really not much involved in getting any of the refreshing techniques we’ve covered so far to work inside of the Excel web part other than making sure a few checkboxes are selected (and I believe they are by default). I’m not going to go into details about how to edit your web part etc, you can check my last blog post for those instructions, but here’s what you’ll want to look for if refreshing is not working in your web part for some reason:

  1. Edit your web part so that the Excel Web Access menu is open
  2. If you want manual refresh to be available, locate the “Toolbar Menu Commands” heading and make sure the “Refresh Selected Connection, Refresh All Connections” option is checked. As a side note, even if the “Type of Toolbar” is set to none, users will still be able to right click the web part and then select refresh
  3. If you want to make the automatic timed refresh work, located the “Navigation and Interactivity” heading and make sure the “Periodically Refresh if Enabled in Workbook” option is checked

BOOM! That’s it! Hopefully at this point you’re feeling confident enough to start practicing the dark art of the Excel Services workbook data refresh on your own. My fingers are starting to feel pretty tired from all of this typing (pretty sure this is my longest post yet). Let me know how things work out for you in the comments and thanks for reading.