A General Caching Overview for Performance Point Services for SharePoint 2010

The purpose of this article is to briefly describe the caching structure of Microsoft Office Performance Point Server (PPS) and to help give a better understanding the caching structure and its function. We’ll discuss what information gets cached, where caching takes place and runtime considerations that effect operations.

PPS main functions are working with BI Dashboards, collecting and assembling data from various sources during creation and servicing of said Dashboards. In many real word scenarios, Dashboards are created and updated infrequently, but viewed often. This leads itself to caching being useful and an important part of PPS. Let’s explore.

So what information gets cached?

Here is a quick list.

  • Query results:    Recently executed queries to the data sources.
  • Rendered views:    HTML pages and graphics.
  • Filter data:    Choices available on dashboard filters.
  • Query metadata:    The information about dimensions, levels, hierarchies, and members, which is required to support navigation and rendering.
  • Application objects:    The definitions of views, dashboards, data sources, and other application data.

In general, it’s what one might expect. Basically information on the Dashboard, Dashboard objects, and the data collected that is needed to populate the Dashboard.

PPS Caching Structure

PPS (Performance Point Services for SharePoint) are web parts, web services, and a SharePoint service application. It is commonly a multiple-server configuration with various servers assigned to varying tasks. As such, this complicates the caching model and often times make it difficult to get a good understanding for the whole PPS caching model. Let’s look at a diagram.

Here is a basic caching structure overview of a two server configuration.

(1 Application Server/1 Windows Front End (WFE))

image

This diagram has the caching broken down into three areas. There is nothing fundamental about this grouping but it does help us here with conversation points. Here is some more detail.

Data: In general, this is the data gathered and queried that is needed to populate a Dashboard. Data caching can take place on the raw query results themselves but can often be further processed data sets. A good example of this would be a scorecard grid data. Caching takes place on many different data sources not just Analysis Services data. Because all data collection is done on the Application Server, this is where this caching takes place. We can adjust the duration of this caching using the Cache Lifetime and Refresh Interval settings PPS data sources.

Application Objects: This caching area contains PPS objects such as, KPIs, Scorecards, Data Sources, Indicators, etc., which are stored in SharePoint Lists and Documents Libraries and which are needed to create Dashboards. Caching for these objects exist on both the Application Server and WFE but only has one setting for both. Cache settings can be adjusted through Power Shell cmdlets but is normally not needed. The Application Server and the WFE manage their own cache separately and the cache gets set during first object retrieval often referred to as lazy loaded.

Rendered Views: This cache consists of the rendered HTML, JAVA SCRIPT and graphic images generated by the PPS. This is the information that the web browser uses to create the Dashboard. One point I’d like to make here, is not to get this confused with any caching on the IIS or browser. This rendered view cache is completely within PPS and thus will be in addition to any IIS and browser static HTML caching. All of these caches affect the system as a whole, but for the purpose of the article, we’ll concentrate on the PPS side of things.

For all but the rendered view cache, the cache is set on first use. The rendered view cache is different in that it is set after a certain number of times requested. The idea is that PPS doesn’t want to cache every single event as this would be very space intensive, but rather cache HTML requests that are requested often. The default setting is 2 requests to set the rendered cache. This means that the HTML for the view is cached on the second request and is used on the third request.

The render view cache on either the Application Server or the WFE is always checked for validity through the Application Server. Because of this behavior, the cached values (renders) on the WFE should always have the exact same cached entry on an Application Server as well. The only exception to this is if the rendered View cache is disabled for the Application Server but this is not common.

Setting of this cache is normally not needed but can be set through Power Shell cmdlets. This includes an option for disabling the cache for the WFE, Application Server, or both. More on setting cache later.

Other notes/thoughts:

  • Another thing to note is that caches are not shared across servers as each server contains and services its own cache. This can have an effect on multiple server deployments of PPS. Specifically, changes to Excel Services, SharePoint List and SQL data sources do not invalidate cache entries on data source changes and thus cached data may vary between Application Servers. The refresh interval expiration of the data source will have to expire to resynchronize everything. Remember, this is only true for these data sources. Changes to Analysis Services data sources will be detected by the changed timestamp and the current cache entries will be invalidated. i.e. Reprocessing an AS cube.
  • Excel data source data is stored internally in Performance Point Services and is not cached.

Checking Cache Validity

Deciding when a cache if valid or missed, is done internally through use of cache keys. They are set up to incorporate all needed data to determine the uniqueness of the cache entries for the certain request or use.

Here is a quick list of some of the information used to determining whether PPS cache is valid.

  • User identity and privileges
  • Data Source timestamp. (AS data source only)
  • PPS Data Source Cache Lifetime or Refresh Interval expiration
  • Application Object version
  • Dashboard Filter change
  • User locale setting

Some important points:

  • As stated before, data and schema update will create cache misses. For example, an Analysis Services cube gets reprocessed. This is a nice behavior, as it allows us not to have to use the data source timeout as a way of monitoring for changing data.
  • Even though render view cache is stored on the WFE, it is still affected by data source changes on the Application server. More on this magic later.
  • Analysis Services data sources can utilize user roles from the Analysis Services database for determining privilege based effects on cache validity. This is important as it may allow different users with the same Analytic Services database role to share cache entries. Thus, the database role settings can have a big effect on caching performance if user based security is used. If the security model uses the Unattended Service Account, the user and credentials accessing the database are always the same so this isn’t as important.

Note on Caching Effectiveness

A very important thing to keep in mind is the user privileges and credentials affect caching a great deal. The choice of data source authentication can have the much bigger effect on PPS caching performance and then adjusting the cache settings. In general, creating PPS data sources configured to use the Unattended Service Account will allow for much more and effective caching. This is because data source authentication will always be from the same user. When data sources are configured as per-user, the credentials of the user requesting the dashboard may be used. Each user may receive different results from the data sources and thus the cache system and may not be able to share results between users, and is much less effective. More on this in a later article but it is such an important point, it needed to be made here.

Configuring cache

As a general rule of thumb, all PPS caching parameters default to useful values and should rarely, if ever, need to be changed. As such there are only two places which cache timeouts can be set and the options are rather limited.

Note: Once again, please keep in mind the above sections statement. Cache timeout settings likely will not affect PPS performance as much as other factors including system configuration and user environment.

Data cache settings:

The data cache timeout setting is the one setting that the Dashboard author might interact with. This is configured when creating/editing a PPS Data Source in Dashboard Designer. This setting affects most areas of data caching including queried results, filter data, data source metadata, and rendered views. In practice these are separate caches but are all set from this value. This value defaults to 10 minutes and is generally considered a good value. Setting this time to 0 will have the effect of disabling data level caching but it is not the recommended setting.

As of the time of this writing, changes to values in SPList, Excel Services, and SQL tabular data sources do not invalidate the data cache. Values will only be picked up by Performance Point after the data cache expires and thus the name Refresh Interval is used. This is an important thing to remember if one using one of these data sources.

As mentioned earlier, data caching invalidates for Analysis Services data source changes. Because of this it might be more useful to set this Cache Lifetime to a greater value for Analysis Services data sources then the other data sources. The trade off to this is greater memory usage on the Application Server. This value defaults to 10 minutes, but an administrator should feel free to change this value for performance reasons. Just remember to track memory usage.

Here is a screen shot of PPS Dashboard Designer where the data caching is set.

image

Rendered View cache setting:

The Application Object and HTML rendered view caches are set through power shell cmdlets. The rendered view cache value takes the form of number of hits before caching begins. Currently this defaults to two which is considered a good value. Through this mechanism, the cache can also be completely disabled. A user or Administrator should rarely, if ever, change either of these settings as the effect on performance will be large. For instructions on setting, please contact Microsoft customer service for more information and guidelines.

Application Object cache setting:

The Application Object is also set through power shell cmdlets, this in the form of a timeout and defaults to 15 seconds. As with the rendered view cache this value should rarely if ever changed due to its large effect on performance and perceived system reliability. For instructions on setting, please contact Microsoft customer service for more information and guidelines.

Next Article

This completes the very brief whirlwind tour of PPS caching. We have briefly talked about a few runtime considerations that affect caching. In the next article we will delve further into user security and other factors that contribute to the caching performance. We will also take a look at how we might monitor caching runtime performance and take a closer look at how rendered view cache is implemented.

Effectiveness

  • How Security affects cache. User Vs. Application
  • User persona, Observer Vs. Explorer
  • Effectiveness in authoring Dashboards.
  • SQL roles, how they play a part

Monitoring Cache performance

  • Windows performance counters.

V1 vs. V2

  • Usage of SQL roles
  • User security per data source vs. Application security.

Further Description of Render View Cache ?

  • How it’s implemented. (Always checked through the App. Server etc.)

Frank Geczi II
SDET, SharePoint BI