One of the great things about presenting at conferences, is the great questions that come up afterward. I got an email today that prompted this posting (and quite frankly, I’m surprised I haven’t written an entry on this previously).
The issue that this customer is having is that they are getting inconsistent results in their nVision reporting. Whenever I hear about this, the first thing that comes to mind is problems with the tree selector tables. For a dissertation on how tree selector tables fit into how trees work, feel free to read the following post.
Problems? What do you mean?
Good question. For those who weren’t able to read through the posting above, let me summarize the default behavior when running an nVision report with trees.
When nVision runs a report, it identifies all the pieces of tree criteria and determines how to put it in a staging table (the ever-famous tree selector tables). In order to minimize the impact to the system, it keeps track of the PeopleTools version number of the criteria in the tables and compares it to the version in the tree. If it determines that the selectors are out of date compared to the tree, it refreshes the value at report run time. This behavior is called “static selectors”, when looking at the tree performance options (which is the default).
This is where the train can come off the tracks. Because there isn’t centralized management of the trees and tree selectors (each process does its own work), it is possible for processes to accidentally cause undesired behavior (such as refreshing a tree selector table that another report is still using). There have also been bugs related to updating version numbers of trees that have also caused issues with nVision reporting.
When I was managing the reporting team, I remember having my developers spend a lot of time working on addressing these issues (which we had successfully accomplished). However, one of the hard realities of software is that regressions can often occur.
So, tell me about the Workaround, already!
The workaround is actually an artifact of the tree preformance options we put into PeopleTools 6 (actually Tools 7, backporting it to release 6). This performance option is the “Dynamic Selector”. The dynamic selector gives each report its own set of values in the tree selector staging table. This means that regardless of whether a report has already used the current version of a tree node, nVision will pull the data from the tree at the beginning of the process. This takes out any of the versioning issues or the potential issues with multiple reports changing dependent values in the tree selector tables.
Therefore, if you’re having problems with inconsistent results, switch the performance options to “Dynamic Selectors” and see if that fixes them (this recommendation helped many PeopleSoft customer meet their closing needs while we frantically worked on fixing issues).
I don’t get it… Why put in Dynamic Selectors as a performance option??
Ah… Now the true geek in you has come out. This option was created for performance tuning for two purposes:
Let’s start by talking about the equality joins. For the most part, equality joins are critical for a good performing join (and range joins, such as ones where you use a between clause, generally cause poorer performance). In order to do an equality join, every specific detail value must be put in a selector table and matched to a node. This means that all department values that fall under a given node need to be staged (which is retrieved from the “user” table… i.e. not a PeopleTools table). Because the list of detail values is an application data table, it is not versioned. That means that the version number of the tree does not know whether there are new values in the tree detail table. Therefore, the only way to ensure that you have the right values is to re-load the selector table each time it needs to be used.
Now, let’s look at its implications related to statistics. Statistics tells the database the make-up of a table, and it is the data used by the database to pick how to access tables and join them together. With static selectors, the tree selector tables are generally full of data. With dynamic selectors, the tree selectors are generally empty of data, because the processes clean up after themselves. Although it seems that static selectors will give better statistics, your DBA can tweak the system for certain reports by seeding the tree selector table with data and using dynamic selectors to ensure the seeded data drives the optimizer.