This came as a comment from the following posting, and I felt that it deserved its own blog entry.

The exact question is as follows:

What would be the best approach to share reports between two PeopleSoft environments, considering we use drill down extensively?

Background

This is an extremely good question, and it brings back memories of some of the discussions we had when updating the report repository in PeopleTools 8.40. Therefore, it makes sense to talk a bit about the history of the report repository as a starting point.

Prior to PeopleTools 8.0

Prior to PeopleTools 8.0, the report repository did not exist. Customers would run reports either locally using the client/server tools (PSQED.EXE, PSNVS.EXE, PSSQR.EXE, CRW32.EXE), or on the process scheduler server. The reports would either be viewed with online viewers (when using the client/server tools), or saved in the file system accessible to the process scheduler server. Users would then access the files using network shares (and secured using network security).

PeopleTools 8.13

PeopleTools 8.13 represents the first release where we had a report repository and report manager. At the time, it was architected to work with only one system, and would not actually authenticate the user accessing a report (it would create an RBAN that was impossible to guess as the identifier of the report --- for those geeks out there, RBAN stands for Really Big Alpha-Numeric). Other limitations include a lack of using folders to categorize reports in the user interface.

PeopleTools 8.40

One of the main projects in PeopleTools 8.40 was to extend report manager to address some of its limitations. One of the primary use cases was to make it easier for users of nVision to utilize it. Here is a snopsis of some of the features that went into that release

• Folders, so that reports could have some level of categorization.
• Support for cross-system report lists
• Improved security over accessing reports (thus eliminating the imfamous RBAN).

The designs for this release pulled together all the requirements into one solution. Because foldering and cross-system access required additional data and infrastructure, it was decided to leverage integration broker for both (and the PSRF application messages were born). The report manager pages were reorganized, including two additional pages added to report manager that utilize the information published and subscribed by these new messages.

• The List tab is a standard search page against cross-system data and the first level of foldering.
• The Explorer tab is a tree view over the foldering metadata attached to the reports.
• Finally, the old report manager page was renamed to "Administration", so that you could access reports on the local system, even if integration broker wasn't working in your environment.

About Deployment Strategies

Okay, now that we have some of the background covered, let's go into a little more detail about how the cross-system report access was intended to work (because we leverage the same concepts in our Report Explorer product).

Notification versus Ownership

The key to understanding deployment options is to understand that conceptually we are separating report notification from report ownership. In other words, the system that generated a report is always owned by that report. Ownership means that the system continues to secure and grant access to the report, and any actions taken when viewing that report are specific to the system that generated the report (more on this later). This also means that any means of aggregating the list of reports and/ore notifying end-users of those reports (with links back) is just that: a list of reports with links to access them in place.

For a product like nVision, where you need to drill, the information needed to do the drilling is embedded directly in the report from the system running the nVision report. There's actually PeopleCode that embeds this information as a parameter on the command line. This means that regardless of how you open the nVision report, it will go back to the system that ran the report to perform the drill (which is exactly what you would want it to do). This works the same way if you use one of the drilling techniques discussed in the following blog entry. Because the local system is running and managing the report, you don't have to worry about it if you use the PSRF integration broker messages as a means of pulling it together. Even if you physically move the reports to a new location, the metadata needed to drill contains the URL to access the system it was generated from (but if you delete the report from the report repository after you move it, you will prevent the drilling from occurring because the process scheduler won't be able to find it).

So, the simple answer is that because the system that ran the report also puts in the additional drilling metadata (including the URLs to access the system), drilling will continue to work even if you copy or aggregate the links to access the reports elsewhere.

Labels: Drilling, Report_Manager

So, does one size fit all with retaining the reports you're running and sending to the report repository? Based on the discussions we've had with customers over the past several years, the answer is definitely not. Unfortunately, that's exactly how report manager handles it. Every report, regardless of whether it's a drill, ad-hoc query, or financial statement has the same retention period. Obviously, this is not ideal, because not all reports are created equally.

Options Available

Believe it or not, the options for setting the retention period are very similar to several other blog postings we've made. For example, our posting on XMPP integration with PeopleSoft uses one of the hooks, the PSRF_REPORT_CREATE application message. Another option is to use application engine to periodically set the retention period for you.

This blog entry will cover both, and you will probably want to do both as well. This is because using the application message is an ideal way of accomplishing this because it keeps track of what you haven't processed for you and your code is invoked a little bit over time (as reports are run).

Because it's difficult to debug subscription PeopleCode, I generally do both together: write my code to do the processing I want, and test it first using application engine (with warning messages causing data to show up in a log file). When I've got it done corretly, I take my logic and move it to my subscription peoplecode, hooking the values up to the contents of the message (and remembering to comment out my warning messages).

Things to Consider

One of the first things you need to consider is how you want to set your retention periods. For the purposes of this blog entry, I've put in the following rules:

• If it's an nVision drill, set the expiration date to be 1 day from the date the report was run.
• It it's an nVision report, set the expiration date of the current report to be 1 year from the date it was run, and then set the expiration date of all reports using the same set of parameters to yesterday.
• If it's any other report, keep the default expiration date for the current report, and set the expiration date for all reports using the same set of parameters to yesterday.

You can obviously use other rules and lookups and go as crazy as you want. In fact, this is why we have plans to build a report retention product, because we see a lot of options that people won't necessarily want to develop for themselves.

Things to know

The first thing to know is the PS_CDM_LIST table. This is the primary table used by report manager, and will be the one you'll be updating with the new expiration date. It also contains most of the information you'll be using to attach retention rules (such as process type, process name, and report description).

The second thing to know is the PSPRCSPARMS table. This is the table the process scheduler uses to store the runtime parameters used by any process run by it. It's also important to know that the process instance is often embedded in the parameters, which means that if you want to find all reports with a common set of parameters, you need to exclude this one from your comparison.

Subscription PeopleCode

Although I have attached a project to this entry with both the app engine program and the subscription peoplecode, I also wanted to include the following in-line:

Here is the App Engine PeopleCode

This will need to be beefed up to do more than testing, because it only processes a single process instance (either the max one or one that you set manually). Here's it's code:

Code and Limitations

Click here to download the project that can be imported into application designer. Keep in mind, though, that there are several limitations to this code:

• It only supports a subset of the types of retention periods you would want to set
• The SQL for updating an item isn't constrained by date
• For app engine-only setting, you will want to use set logic to select and update in one fell swoop. Because I'm leveraging application messaging, it automatically keeps track of reports I've already processed.

Labels: Process_Scheduler, Report_Manager, Reporting

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

For those who know about Report Manager, it's the PeopleSoft-delivered means of finding and accessing reports. For those who support people who use Report Manager, there are quite a few significant usability issues with it.

Issues? What issues?
The main issue with Report Manager is that it doesn't know very much about the reports that it manages, which means that it's limited from the start. It also has limited features to allow configuration of the behavior of this product. Because it's the primary means by which users access reports, these limitations have significant ramifications with the user satisfaction, adoption and productivity in reporting.

Unfortunately, most BI tools aren't a whole lot better in terms of managing and accessing reports.

So, how do you fix it?
Grey Sparling fixed it by doing three main things:

1. We capture the information that describes the content of a report.
2. We allow you to use that information in combination with information in your PeopleSoft application to provide a rich user interface for organizing, finding, and describing the reports.
3. We allow you to configure the behavior of organizing, accessing, and using reports.

The end-result is a product named Report Explorer, which is revolutionary in how it solves these problems.

What do you mean, revolutionary???

The reason its revolutionary is that it is the only product that uses artifacts in your business application to help describe and organize reports in business terms. It's also revolutionary in the amount of control you have over the behavior of the user interface.

So, how do I learn more?

I'm glad you asked. We've recorded a demo that shows the product in action here. We've also put out a marketing brief that describes it here. Finally, if you want to talk with us about it in person, our contact information can be found here.

Labels: Products, Report_Manager

One of my good friends, Tom Pitra, of Pitra Consulting came up with a very ingenious technique for working around limitations in report manager in PeopleTools 8.1x which doesn't require modification of the application. Although he presented the solution it at Connect in 2002, I feel that it deserves being reposted in this forum (mainly because there are probably a lot of PeopleSoft customers on that tools release who aren't going to get the money to do an application upgrade to a version that has folders in report manager)

Folders? What are folders and what do they do?
Well, folders is a way of organizing all the reports that are generated. If you think of a report as being analogous to a file on your computer, imagine what it would be like if you didn't have folders to organize these files. It would very hard for you to wade through the long lists of files to find one you wanted to run or edit. Therefore, one can think of folders as something similar to directories.

The ironic thing is that the reports in report managers actually ARE files in a file system (which is the report repository).

Prior to PeopleSoft 8 and the internet, customers would run nVision reports to the file system with directories to organize the reports, using a feature called the directory template. This allowed a company to organize all reports for period 3, 2002, department 1001 into a folder structure like Financial reports --> 2002 --> Period 3 --> Department 1001, where all financial reports for that combination of year, period, and department would be placed.

Prior to PeopleTools 8.4, report manager was merely a means of listing out the reports one had, and although one could search on the limited attributes of the process running the report, there was really no way of organizing this output (which is a challenge for people who have lots of reports over time to wade through). Even in PeopleTools 8.4, the features for organizing reports are rudimentary (which is why we've identified some additional features that we are selling as a packaged service)

What attributes are available to me in PeopleTools 8.1x?
Good question, because this is what explains both the limitation as well as provides the foundation for understanding the solution. Here's the list:

1. User ID or Role


2. Process Type


3. Process Name


4. When it was generated (search on the last X days)


5. Process Status


6. Process Instance

As you can see, these attributes aren’t that useful for finding reports.

So, how did Tom do it?
Tom utilized an interesting set of features in PeopleSoft's security infrastructure to make it happen (which still impresses me to this day).

Security in PeopleTools is organized by Users, Roles, and Permission Lists. Permission lists represent a set of privileges that are assigned as a grouping. These permission lists can either be directly assigned to users, or can be assigned as roles (to simplify the aministration of security). Users can then get a set of privileges through the roles he or she are assigned to.

For example, there could be a role called "FS Call Center Analysts". Anybody who is a financials call center analyst could be assigned that role (and others if they play other roles in the system, such as "Manager"). The permission list for that role would provide the appropriate rights for reporting, pages access, etc. All security in PeopleSoft is the union of all privileges across all roles and permission lists. Of course the key thing is that users can be members of any number of different roles.

One other thing is that roles do not have to be used in permission lists. A good example is a super user role (such as RPT_Super_User), which by getting that role, the user has access to all the reporting features in report manager, regardless of other settings.

The solution was to use the fact that you could filter on roles in report manager, that you could distribute reports to roles when scheduling them, and that you could assign people roles with no permission lists. This provided a very concise solution that made distribution simple (to distribute to a user, you merely added him to that role), and searching simple (to find a set of common reports, you filtered on a role in report manager).

More on the steps involved...
The first step was to pick a set of categories to use for the reports. I forget the exact categories Tom used, but they could be anything from a cost center ID to a type of report. These categories would then be created as roles in the security infrastructure.

The second step was to determine whether the roles would be used for securing the reports or merely for navigation. If it is used for security, then users would be assigned the appropriate roles for accessing the reports. If the role is merely for navigation, then users would not be assigned (the users would be manually distributed to in the next step).

The third step is to run the reports and set the distribution settings from the process request page (which is the icon that looks like people). When scheduling the report, you would pick the role to distribute to, so that you can filter on that role in report manager. If the role is also used for security, then you don't have to manually add users to distribute to. However, if you aren't using the role for granting security to report instances, then you need to add the list of users who have access to the results of that report run.

When using report manager with reports that have been run, the user can then search for the list of reports distributed to that role. Bada Boom!

Labels: Report_Manager

This is a topic that comes up quite frequently when looking at reporting from an enterprise perspective. No user wants to go to several different places to do his job. This is the reason why portals have sprung up. Because PeopleSof has both a set of reporting tools and a report distribution/access framework, and because many customers have many different reporting tools in-house the span many different applications, many customers want to figure out how to combine them together.

Wouldn't a Portal solve this?
Well, actually, no. Although PeopleSoft does deliver the report list as a portal pagelet that can be embedded into the PeopleSoft portal or another portal, this is not what customers want. Customers want a single integrated list of their reports, so that they can go to one place, see all the reports generated in the past day, and view them. This means that customers want the lists of reports across their different reporting tools merged into a single list.

Okay, so what are my options?
Well, there are 4 ways to approach this problem:

1. Move all reports into the PeopleSoft report repository.
2. Register all reports to PeopleSoft report manager.
3. Move all reports to a 3rd party tool.
4. Register all reports to a 3rd party tool.

So, the first question is: do you want PeopleSoft to be the place where people go to access their reports or another tool? There are several things to consider when determining whether to use PeopleSoft or another tool:

1. With PeopleSoft, the users, passwords, and roles are the same as the application users, passwords, and roles.
2. PeopleSoft does not charge additional licensing fees to use its report distribution framework, whereas 3rd party tools generally charge on a per-user basis.
3. The UI for report manager is generally more limited than other report tools. However, these areas can be addressed through the following packaged services (warning... shameless marketing at the other end of this hyperlink).
4. You should determine which is the primary environment for your users. If the majority of your users aren't PeopleSoft application users, then it may be better to use the 3rd party tool.

The second question is: do you want to move the reports or register them in place? Here is what you should consider when making this decision:

1. How important is interacting with the output? In other words, if you want to provide drilling or page on demand (features that require viewing functionality generally only available when the report is kept in its original repository), then you will want to keep the output in place and register the URL for accessing the instance.
2. How important is it to have a single repository of all your reports? If you register in place with several different reporting environments, then each environment is responsible for storing, securing, and archiving its own content. This can cause an administrative overhead that you may not want to deal with.

Okay, so let's look at how one would accomplish each.

Move all reports into the PeopleSoft repository.

There are a couple of techniques you can follow to accomplish this (and the one you take is primarily dependent on the tools release you are on). Here is a list:

1. PostReport() PeopleCode function.
2. Utilizing process scheduler distribution agent functionality

The first is the easiest one to use, because it is well documented. The PostReport() function (which is only available on PeopleTools 8.4 and greater) allows you to specify a location of a file, as well as the metadata needed to organize it in the report repository (such as who has access to it, and the folder it should be placed in). You can use PostReport() in app engine or in a PeopleSoft page. When used in app engine, you can set up a daemon process to look in a directory for a file and then move it when it finds it (PeopleSoft delivers a sample daemon process out of the box that is also well documented).

The second method is to use the way process scheduler manages output to your advantage (and is the way you need to do it for tools releases that don't have PostReport(). Let's talk a little bit about how process scheduler works.

When process scheduler starts a new process, it creates a unique temporary working directory for it (it containes the process instance ID, so that all running processes get their own directory). All output of programs get put into that directory by default. When the process has completed (either by ending normally and updating the status to either error or successful, or by having the process scheduler detect that the PID of the process doesn't exist any more), all output in this directory is collected and copied to the report repository (which is what happens when the state of a process is "posting").

Therefore, one can move output to the report repository by merely running a process in the process scheduler and copying a file into the default output directory of that process. The distribution settings of the process would be used and the file would be moved to the report repository. This physical act of copying can be done through any of the following methods:

1. Creating a shell script that is registered as a process definition in process scheduler.
2. Creating an app engine program
3. Creating an SQR.

Each of the different tools has its own substitution variable for the standard output directory, and is documented in the process scheduler documentation (but for example, %SQOT is the output directory for SQR).

Now, it's important to note that the distribution rule of the process that does the copying is the rule that is used for putting output into report manager. This means that the list of users who have access to it as well as the folder it shows up in comes from the calling program and cannot be overridden. Therefore, many organizations will write a separate program in application engine that uses the ScheduleProcess PeopleCode function to set the distribution rules and then run the program that does the copying. This calling program is often implemented as a Daemon, which also has logic to figure out which settings to apply to a given file (which could merely be a second file in the same directory with that information in it).

Register all reports into report manager

This is actually relatively easy to accomplish, if you're willing to use integration broker. The report distribution infrastructure was designed to work in an environment with multiple PeopleSoft environments (such as Enterprise Portal, HR, and Financials). To do this, we implemented messages to publish information about a new report when it is posted. This allows the different PeopleSoft environments to subscribe to those messages to have reports from the other PeopleSoft system registered to it. Because PeopleSoft messages are exposed as XML, you merely need to generate the appropriate set of XML messages to register external content.

The messages you need to look at are the following:

• PSRF_REPORT_CREATE
• PSRF_REPORT_DELETE
• PSRF_FOLDER_CREATE

There's also a message for updating a report, but I forget the name (just look for the PSRF prefix and you'll find it). When generating the XML, the most important things to provide are the URL for accessing the content, and the list of users or roles who have access to it.

When the 3rd party system generates the XML message, it merely needs to send it to the integration gateway URL you set up with integration broker.

Move all reports to a 3rd party tool

If you want to move your PeopleSoft reports to a 3rd party tool, there are also several techniques available.

1. Run the report to a directory. Many customers give access to windows directories and secure the directories at the operating system. Others will copy the reports from the directory into the other system.
2. Print the report using a special print driver. This is a technique used by Vista Plus and Cypress. The process scheduler prints the report to the print driver, and then the print stream is consumed by the 3rd party tool, parsed, organized, and stored.
3. Subscribe to the XML messages generated by PeopleSoft distribution agent, and then call the URL to copy the file. Again, this is standard integration broker functionality.

The approach you take is primarily dependent on the functionality available in the 3rd party system.

Register content in the 3rd party tool

There are two techniques you can use to register the URL to access a report from the report repository using a 3rd party tool. They are as follows:

1. Subscribe to the XML messages generated by PeopleSoft distribution agent and register the URL and users.
2. Write a program that reads the data from the PeopleSoft report manager (which are PS_CDM_LIST for the list of reports, and PS_CDM_AUTH for the list of users).

Labels: Process_Scheduler, Report_Manager