At BICG we have been using Performance Point filters and connecting them with custom SharePoint solutions to deliver top notch functionality and dashboards to our customers that are not available out of the box.
But there are many tweaks and settings in PPS which is not fairly documented and need its due credit.
One such feature is Filter Caching, there are hardly any white paper and documentation available which explains how the filter caching is saved, applied or retrieved. The closest documentation is a MSDN blog which is not enough to understand how PPS filter caching will behave in different scenario.
Filter Caching with Per User Identity
The article here says that:
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.
Well, that’s what we wanted. We want to have filters to be shown and cached per user. For That
- 1. Our tabular model was using dynamic security by using row filters.
- 2. We configured per user identity for the data source
- 3. We set Refresh Interval(Cache Lifetime) to minimum
- 4. And Most important, Kerberos Delegation was configured for SharePoint to pass user identity
But still the filter data was not per user. It was the cached version from previous user and completely disregarding user’s identity and access.
What I nightmare! We tweaked each and every possible setting in SharePoint related to Performance Point caching but no luck. The filter values from pervious user(first user) in SharePoint keep on showing to all the user.
We started to loose hope and was convinced that it is a Performance Point bug which no one has either reported or our scenario is unique (Tabular model security). PPS has not been Microsoft focus for quite sometime and we did not have any hope from them.
It made our custom solution and entire PPS unusable from customer’s stand point.
Here comes the solution
After reading and re-reading the above article multiple times, one thing was clear. PPS should be able to understand that connection coming from each user should be different but somehow treating them as same. Though the reports were running under user context, the filters we returning cached data.
Taking this idea forward, we tried to find ways to make user connection unique.
Hence, there was only one way to make user connection unique. Pass the user name to the connection string. But that was out of our control, PPS was suppose to do and therefore we went back to PPS Setting in Central Admin and found this setting “EffectiveUserName in PerformancePoint Services“. As per the documentation this setting was required when Kerberos Delegation was not configured but still we gave it a last (desperate) try.
And Yes it worked!!
Now the PPS filters are really cached per user and each user is able to select only those values they are authorized to access.