Chris and myself have been at the Quest Midwest RUG this week, talking to lots of PeopleSoft customers. Some of my query drilling techniques were a real hit with the audience (as was the giveaway we had for attendees of our sessions).

One item that came up in the Q&A section of the nVision session by Finish Line was performance issues for Super Users for Report Manager. This is actually something that we saw when we did volume testing of our Report Explorer Product.

Tell me more about the Performance Issues
Performance issues with Report Manager is nothing completely new. The specific issues we're looking at are related to the tables supporting the administration tab of Report Manager (much of the previous work performed by PeopleSoft has been focused on tuning the Explorer and List pages).

There are two scenarios that can cause the performance issues I'm discussing:

• There number of reports in the report repository is extremely large
• The number of reports certain users have is extremely large

We'll look at each one independently.

Large numbers of reports in Report Repository

As I mentioned previously, we hit performance issues in our initial volume testing of Report Explorer, which caused us to focus our attention in this area. The reason we were encountering these issues is that Report Explorer uses the same tables as Report Manager to determine which users have access to which reports (we then extend that information with information we pull from the reports and from elsewhere in the application). We were finding that in large volumes, the two tables that contain this information (PS_CDM_LIST and PS_CDM_AUTH) were indexed very poorly for the type of selection done in report manager.

Our solution was to add 3 new indexes to the system (1 on PS_CDM_AUTH, and 1 on PS_CDM_LIST). This improved the performance of Report Manager (and Report Explorer) by 2 orders of magnitude.

Here are the new indexes we created:

PS_CDM_AUTH index:

DISTID
DISTIDTYPE
CONTENTID

First PS_CDM_LIST index:

DISTSTATUS
EXPIRATION_DATE
DISTNODENAME
CONTENTID

Second PS_CDM_LIST index:

PRCSINSTANCE
CONTENTID
EXPIRATION_DATE
FILENAME
DISTSTATUS
LOGFILEONLY_FLAG

The reason these indexes work is that Report Manager (and Report Explorer) include these fields in the where clauses of the SQL generated (either by joining between tables or filtering results).

Users who have large numbers of Reports
Quite often, these users are called Super Users (however, in PeopleSoft, the ReportingSuperUser is a reserved word that means somebody who has access to ALL the reports). For the purposes of this discussion, let's just say that Super Users are people who have access to more than 1,000 reports in Report Manager.

So, obviously, the indexing discussed in the previous section will help some, but the main bottleneck for this type of user is getting all the data from the database and into the page for Report Manager. When opening up Report Manager and clicking on the Administration tab, Report Manager will do a selection automatically, filtering based on the saved filter values. In this circumstance, the best way to address performance issues due to loading too many reports into the page is to segment the reports and save a filter that brings up a subset.

There are two main ways I would recommend you segment reports:

1. By UserID
2. By Folder

Let's start by discussing the Folder option. Starting with PeopleTools 8.4, we introduced folders into reports. They were intended to mimic some of the functionality you get by windows directories when running a report to file. Every time you run a report, you have the option of giving the report a folder (and this gets saved at the run control level). If you don't select one, it will be defaulted for you. If you run reports and pick folders to assign them to, you can filter your reports in the administration tab using a folder. If you pick the folder and click save in Report Manager, it will only select reports with that folder when you open up Report Manager.

The second option is User ID. I've actually already posted this solution in the following blog entry. Again, you can filter on one of these user ids and save the filter criteria so the next time you enter Report Manager, it will select the subset.

What if my users can't even get Report Manager to Open??

Good question. The table in which the filtering criteria is stored is PS_CDM_FILTER. It is keyed by OPRID and has a field for each criteria value for that user. If you need to populate the defualt criteria, you can use a SQL tool or write an App Engine program to put a row in (or update the row if it exists) for each user, containing either the PSRF_FOLDER_NAME or WS_OPRID to use.

So, why would I be interested in Report Explorer?
Well, if you've got large volumes of reports, performance is only one of the issues you're dealing with. When users have to sift through lots of reports in report manager, there are two potential reprecussions to your organization:

1. You're spending a lot of time trying to get the foldering right (which means touching the processing rules for every single report you're running). Even then, the folders may have too many reports in them to be useful.
2. Your users are spending a lot of time trying to find the reports they want to view.

Report Explorer solves these problems by utilizing the data in the reports to organize them. It also has functionality to identify the current version of a report, defining different categorization paths, search based on the content, and setting of favorites. If you haven't seen it already and yiou made it to this point in the blog entry, you should look at the following demo. Here's the product page.

Labels: Performance, Report_Manager

With the interest we've gotten with the PSIDE Helper, we decided to put together another product that solves a niche need. This is nVision Context Helper.

What is it?
This tool shows you the context of any number in an nVision report (report instance or drilldown result). We've packaged it as an excel add-in that adds a menu item into Excel that can be used in place of the DrillToPIA.XLA.

Because it looks at the nVision results in Excel to identify the context, this tool has no server code that needs to be installed and configured. Register the add-in to excel, and you're up and running.

From a user's perspective, they merely select a cell in their nVision report and use the menu to get more information about that cell (GSDrill --> About this Cell).



Benefits
In other words, if your users or auditors ever wonder what went into a number in a report, this tool will tell them. It understands and displays criteria in the report, filters applied by scopes, report requests, and the act of drilling. It provides transparency to end-users that isn't possible with other tools.

More Information
We've put together a product page and flash demo, if you want to see it in action. We've also added this as a standard feature to the nVision drillling Snap-on.

Labels: nVision, Products