A Little Something About PPS Filters (part 2)

Here I wanted to go through what happens when you click apply on a PerformancePoint filter. Well first lets look at the simple OLAP based filter that I put together for the last post.

On my blank dashboard page with only a single filter I click apply and then I’ll have my selection displayed so the filter looks like it’s registered the filter change.

The problem is that when I refresh the page PPS doesn’t remember my last selection and will return the All member. This isn’t what PPS filters are supposed to do but the reason is simple, what’s the point of having a filter if there’s no report connected to it?

After putting a simple reporting services report on the page and trying the filters again no problems. I can close the browser, log out, reboot my machine no matter what you do the parameter selection is remembered. So lets ask the question, how?

When a user accesses a report linked parameter their selections are stored in the PPS monitoring database in a table called ParameterValues. This table consists of a filter id, which is a GUID as a string, the users login credentials, the last update time and finally a column called SerializedXml.

SerializedXml is an xml typed field that contains a users actual selections for that particular filter. Select * from the table in management studio and click on one of the values in the SerializedXml column and you can see the xml displayed. Forget the schema tags and looking lower down in the xml document to the elements called ‘MyDataTable’.

<MyDataTable>   <Key>c431349de280b5a3fc3f921d5ebffd67</Key>   <DisplayValue /> </MyDataTable> <MyDataTable>   <Key>18eb638b9e0c1c9ab64aaacdcb21d4bd</Key>   <DisplayValue /> </MyDataTable>

This is where one particular difference between OLAP and tabular filter appears. Look at a the SerializedXml value for an OLAP based filter and you’ll see a GUID appear in the key element. This would seem to be the dimension property MEMBER_GUID but I haven’t been able to confirm this on Analysis Services 2008 but it seems logical enough.

When looking at the SerializedXml for a filter based on tabular data the differences are quite clear.

<MyDataTable>   <LocationID>England</LocationID>   <LocationDesc /> </MyDataTable> <MyDataTable>   <LocationID>Scotland</LocationID>   <LocationDesc /> </MyDataTable>

With tabular filters the key assigned within the parent / child relationship is the value stored in the xml and its possible to query it as well using SQL Servers xml query features. The example above can be queried using the following syntax;

SELECT d.Members.query('LocationID')   AS MemberKey FROM ParameterValues CROSS APPLY SerializedXml.nodes   ('/NewDataSet/MyDataTable')     AS d(Members) WHERE [Login] = @Login AND ParameterUniqueName = @FilterID

By providing the login and filter id parameter we can get the selection for a single users filter. The result set is again xml based returning a row for each filter selection.

MemberKey

<LocationID>England</LocationID>

<LocationID>Scotland</LocationID>

The element names and tags can be cleared away easily enough with some string manipulation or if anyone know how the SQL xml query could be tweaked to use return just the values themselves I would be interested to hear.

In the next post I’m going to show how this can all be put together to demonstrate how these ideas can be applied to real world scenarios.

This Week I Have Been Mostly Listening To…

Kitsuné Maison Compilation 7 – Various Artists

Spotify Playlist

A Little Something About PPS Filters (part 1)

If you have a lot of members, say in the thousands, that you want to display in a PPS filter then you’re going have problems. The current version of PPS doesn’t handle them to well and I surmise that it’s one of the reasons that there’s a configurable setting in the web.config allowing to set the maximum number of members returned in a filter.

Things are compounded when you have hierarchical filters as the number of members is increased by parent members. The obvious way to get around this off the top of your head would be cascading filters but this has its own issues one in particular is that I want the user to be able to use all of the hierarchy in the reports from the all level to the leaf level members.

Hopefully the architecture of PPS v2 will have corrected this little foible but in the meantime we have to work around this. I’m going to talk in the next few posts about the filters and demonstrate a potential workaround but wanted to start off with a bit of an overview on two types of filters available in PPS and that’s OLAP based and tabular based.

The majority of people will have put together an OLAP data based filter. It’s very simple and you can choose to either select the members directly from the dimension, use a named set or a MDX query. I would hazard to guess that most developers will use either named sets or MDX queries.

I’ll use a simple MDX query to generate a set from a dimension I’ve got called “Towns”. This dimension contains a single user hierarchy that looks like this;

• Country
  • Region
    • County
      • Town Prefix
        • Town

As I only want to display data to the county level in my filter I can set this up with this MDX;

DESCENDANTS(    [Town].[Town].[All] // The top level member   ,[Town].[Town].[County] // down to the County level   ,SELF_AND_BEFORE) // including all members

When the filter is built and deployed you’ get something that looks like this;

To achieve the same with a tabular filter you need to put together a parent child structured data set. As my source table has individual fields for each of the hierarchy levels this will need to be created manually using a series of union statements. For example;

SELECT   'All' AS Location  ,'All' AS ParentLocation UNION ALL SELECT   Country AS Location ,'All' AS ParentLocation FROM   dbo.DimTown UNION ALL SELECT   Region AS Location ,Country AS ParentLocation FROM   dbo.DimTown

Now none of what has been written above is new, Earth shattering or of any particular benefit to anyone apart from those who have never touched PPS before but I wanted to mention the distinction between OLAP and tabular data sourced filters as it will become quite important in the next post around how PPS remembers what your last selection was.