Chapter 17. PerformancePoint Services Issues

With every development tool, you can expect development roadblocks from time to time. These might require you to change tool settings or perhaps to consider a different angle to create a workaround and successful resolution. This chapter covers how to get around some of these roadblocks when developing in PerformancePoint Services.

PerformancePoint Services Is Not Configured Correctly

When creating a data source for Analysis Services, you must set the server name, select the SSAS database, pick a role, and select the SQL Server Analysis Services (SSAS) cube from the available list. However, when you click the Cube drop-down, a message appears stating that the data source cannot be used because PerformancePoint Services is not configured correctly, as shown in Figure 17.1.

Figure 17.1. Improper configuration of PerformancePoint Services.

In addition to the error message, a Windows Application Log message states the following:

The PerformancePoint Services Unattended Service Account is not set. The setting is located in “Manage service applications” in SharePoint Central Administration under the PerformancePoint Services management page.

The resolution is to navigate to Central Admin, Applications Management, Manage Service Applications, PerformancePoint Service Application, PerformancePoint Service Application Settings to add the service account. However, when adding the service account to the PerformancePoint Services Application Settings page, a message might appear stating that the Unattended Service Account cannot be set for the service application. The message is shown in Figure 17.2

Figure 17.2. Secure Store Service not set for PerformancePoint Services.

The resolution is to set up the Secure Store Service first before entering the unattended service account information on the PerformancePoint Service Application Settings page.

See Chapter 6, “PerformancePoint Services Configuration,” for the steps to set up the Secure Store Service and set the unattended service account on the PerformancePoint Services Application Settings page.

Dashboard Connection Formula

In most instances, the filter on a dashboard is pretty straightforward. The standard steps are to create a Member Selection filter, select values to be used in the Selection criteria, and then drag the filter onto a dashboard. After adding the filter to the dashboard, a report or scorecard then creates a connection to pull in the member unique name to a value on the reporting object.

However, in some cases, a manipulation needs to occur between the filtered value and how the reporting object will interpret the value, which is where the connection formula is used to translate the filter value. To understand what to code within the Connection Formula screen, though, you must understand what is being sent to the SSAS server for the translation. For any code being placed into the text, PerformancePoint Services will wrap the code with the following syntax:

SELECT { StrToSet("DISTINCT({USER CODE})") } DIMENSION PROPERTIES MEMBER_TYPE on 0
FROM [Cube Name]

User code is the code that is placed within the connection formula, which provides insight on how to code within the text box. Also, the connection formula will translate the <<UniqueName>> code as the unique name of the member selected from the filter.

Figure 17.3 illustrates a simple connection formula, and Figure 17.4 displays the Profiler trace that shows what was sent to the SSAS server.

Figure 17.3. Connection formula example.

Figure 17.4. SSAS Profiler trace for basic a connection formula.

A simple example is all well and good, but here is an advanced example where a dimension called Date Ref is used as a filter and then translated to a Date Dimension value based on other calculations within the cube:

STRTOMEMBER(case when '<<UniqueName>>' = '[DATE REF].[DATE REF TYPE].&[YESTERDAY]'
  then '[CREATION DATE].[Date].[Date].&' + [measures].[yesterday value]
  when '<<UniqueName>>' = '[DATE REF].[DATE REF TYPE].&[WTD]'
  then '[CREATION DATE].[Week Ending Date].&' + [measures].[WTD value]
when '<<UniqueName>>' = '[DATE REF].[DATE REF TYPE].&[MTD]'
  then '[CREATION DATE].[Fiscal Period].&' + [measures].[MTD value]
when '<<UniqueName>>' = '[DATE REF].[DATE REF TYPE].&[QTD]'
  then '[CREATION DATE].[Fiscal Qtr].&' + [measures].[QTD value]
when '<<UniqueName>>' = '[DATE REF].[DATE REF TYPE].&[YTD]'
  then '[CREATION DATE].[Fiscal Year].&' + [measures].[YTD value]
else
  '[CREATION DATE].[Date].[Date].&' + [measures].[yesterday value]
  end)

Figure 17.5 contains the trace example of how this calculation formula is then wrapped and sent to the SSAS server.

Figure 17.5. SSAS Profiler trace for the advanced connection formula.

Knowing that PerformancePoint Services is going to wrap the query with a StrToSet function allows for creative solutions for piecing together the string to be interpreted.

Other examples on using multidimensional expressions (MDX) to extend the connection formula are found in Table 17.1.

Table 17.1. Connection Formula MDX Examples

Creating PerformancePoint Content Items in a Nontrusted Location

When PerformancePoint Services is initially configured, the default for trusted locations is to trust all locations. Although this is great for the developer, the administrator might not want to grant such wide open permissions on the SharePoint site. If you are trying to create a PerformancePoint content item in a nontrusted location, a message will appear stating that the query could not complete because the report is in a nontrusted location, as shown in Figure 17.6.

Figure 17.6. Nontrusted content location.

In addition to the message in the Dashboard Designer, events are written to the Application log. The following message is an example of an event in the Application log where PerformancePoint content is being saved in a list called PerformancePoint Content but the list itself is a Nontrusted Location:

The shared service administrator must configure the following content locations as “trusted”:

/Lists/PerformancePoint Content

To set the list that the Dashboard Designer is going to use for storage of the PerformancePoint content items, refer to steps detailed in Chapter 6 in the “PerformancePoint Services Configuration in the Trusted Content Locations” section.

Insufficient Security for Dashboard Deployment

A developer is able to create PerformancePoint Services content, including a dashboard, and save the items to the SharePoint site. However, when attempting to deploy the dashboard, a message appears, as shown in Figure 17.7.

Figure 17.7. Unable to deploy dashboard.

To solve the problem, ensure that the developer has the proper permission level in the SharePoint site. To develop content, the developer needs the Contributor permission level, but the actual deployment of the dashboard requires the Designer permission level. Adding the Designer permission level to the SharePoint user or group the developer belongs to will allow for the deployment of the dashboard.

Cube Action Not Available on a PerformancePoint Services Report

In the SSAS cube development, a custom action is created to allow for displaying details about the data within Excel or some other front-end application. The action is displayed in the menu when the user right-clicks a cell to drill into and selects additional account. In Figure 17.8, two actions are available for the Operation cube: Row_Action and Sales Details.

Figure 17.8. Available Excel cube actions.

When using the PerformancePoint Services Dashboard, as shown in Figure 17.9, right-clicking an Analytic Grid cell and selecting Additional Actions displays just one available action: Sales Details.

Figure 17.9. Available PerformancePoint Services cube actions.

You might be wondering why the Row_Action is not displayed. The answer is in the type of action that is developed. For PerformancePoint Services, only a drillthrough action will display in an Analytic content item. For this example, the Row_Action was created via a new action in SSAS with a target type of Cells and an MDX statement as the action expression. Although this specific action is essentially creating a drillthrough action in nature, because the type is not a drillthrough action the action will not display within the PerformancePoint ServicesAdditional Actions menu.

Summary

PerformancePoint Services has many different parts that need to be set up correctly from within SharePoint or the data sources themselves for functionality to be exposed as expected. This chapter showed the different behaviors and messages that are displayed to the developer or end user if the underlying settings have not been properly configured. In addition to what PerformancePoint sends back to the user, this chapter covered the steps necessary to resolve configuration issues.

Best Practices

The following are best practices from this chapter:

• Ensure that PerformancePoint Services is set up in conjunction with the Secure Store Service before starting development.

• To deploy a dashboard, the user must have the Designer permission level assigned for the SharePoint web application.

• To use SSAS cube actions within a PerformancePoint Services dashboard, ensure that the cube action was created as a drillthrough action.