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’.
|
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.
|
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;
|
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.
Posts
No comments:
Post a Comment