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

David Kurtz has a good tip on his weblog about how to get rid of an annoying little bug in the index management in Application Designer. David knows what annoys DBAs about PeopleSoft - he wrote the book on it (literally).

The underlying source of the problem has to do with the fact that some of the internal PeopleTools code still thinks that it is supporting some platforms that are actually not supported (Allbase!).

The problem of code cruft in PeopleTools is actually something that I've had in my list of blog topics to write about for awhile, but it'll have to stay in the queue a bit longer. For now, check out David's post for how to make Application Designer behave :-)

Labels: Database, Performance

A common headache for DBAs managing PeopleSoft applications is not knowing which user a particular piece of SQL is being executed for. This happen because the SQL is being executed by the application server under a privileged user account and not the PeopleSoft user (who probably doesn't even have a database login).

The DBA can go ahead and kill the connection at the database, but they can't explain to the user what happened. Of course, the end user just sees it as a PeopleSoft error (PeopleSoft does not handle killed connections very gracefully) and they try it again. Which just annoys the DBA even further. A vicious circle indeed.

At the Mid-Atlantic RUG last week, this topic came up in a few different discussions. Some of the DBAs there weren't familiar with the EnableDBMonitoring option for PeopleSoft application server domains.

When this is turned on (and it's on by default in PeopleTools 8.43 and up), then each time the application server begins doing work on behalf of a different user than it was previously doing work for, it will set a variable in the database session with information on that user.

Platform Support.

• Oracle. You can see the information in the CLIENT_INFO field of the V$SESSION Oracle system view.
  
• SQL Server. The information is available in the context_info field of the sysprocesses system view in the master database. This is a varbinary field, so you'll need to cast it as varchar to view the data. There is also a PeopleSoft version of the sp_who stored procedure (called sp_pswho) that will return this information. Unfortunately this stored procedure does not get installed automatically when you install PeopleSoft. You'll need to do it manually. A script to install this can be found in appendix B of the SQL Server for PeopleSoft Performance Tuning Red Paper.
  
• DB2/zOS. The DISPLAY THREAD command will display the PeopleSoft user ID in addition to it's regular information. Other DB2 platforms (Windows, Unix, Linux) do not support this command.
  
• Sybase. Later versions of Sybase support this feature, but I'm not sure about how to access the information in the database. I would assume that an additional column was added to the sysprocesses view (similar to SQL Server), but I don't have access to a Sybase installation to verify that at the moment.
  
• Informix. Informix does not support this feature.
  

Labels: Database, Performance

While we were at a customer today installing our Report Explorer product, we were asked to help troubleshoot a problem that they were encountering. Apparently, their nVision reports were timing out in their production reporting run, even though performance didn't seem to be an issue.

Processing Time Limits? What are they?
For those customers not as familiar with some of the new process scheduler features in PeopleTools 8.44, processing time limits is one of the cool new features that made it into that release. This is part of a series of features in process scheduler that makes it much more usable:

• Jobsets: the ability to nest jobs within jobs
• Processing time limits: the ability to set time limits for processes at the processs type and process level.
• Thresholds: the ability to set constraints on a process scheduler server that drive wether it will accept new process requests. Significantly improves load balancing.
• Restart: the ability to restart processes, including nVision reports and clean up after them.

For nVision, you can set process time limits at the process type, process, and even report request level. This allows you to minimize the impact of long-running, bad-performing nVision reports to your production environment.

Makes Sense, so where's the issue?

Good question. In this circumstance, the customer was running a high volume set of nVision reports (generating many thousands of instances). Each instance was generated in 1-3 minutes. The time limit for nVision reports was set to 30 minutes.

The problem came up as an artifact of using scopes to generate instances (which in nVision allows application data to drive the generation of report instances... an example would be to generate a Profit and Loss for each department in the department table). Although one would expect the time limit to be re-set for each instance (because you don't want the processing of any individual report instance to go too long or get hung), it isn't. This means that if it takes 1 minute to generate an instance and the scope generates 31 report instances, your process will time out.

Ah... So, what are my options?

Good question. There are two relatively obvious ones:

• Reduce the "scope" of your scopes, which means scheduling more report requests with smaller scopes.
• Beef up the time limit. In nVision, this can be done at the report request level, which can allow you to override a generalized time limit designed for non-scoped report requests in one that does use a scope.

You can also use the new restart functionality in PeopleTools 8.44. Restart gives you two powerful capabilities:

• The ability to pick up a scoped report request where it left off and not re-run instances that were already generated.
• The ability to automatically try to restart the report a certain number of times.

Therefore, the process scheduler can more gracefully handle a scoped report. It will automatically restart the process after the time limit has expired and pick up where it left off. That means that if you set the restart limit to 5 and the process time limit to 30 minuts, you can get 150 minutes of processing for your scoped reports (although it's better to manage it a little more closely).

Happy Processing!!

Labels: Database, nVision, Performance

This week, when I was picking up my kids at school, I ran into one of the nVision developers who used to work for me (who's still working at Oracle). She's working on taking nVision to Fusion, which will use the Siebel Analytics platform.

When I first remembered that Oracle's acquisition of Siebel included Siebel Analytics, I told my co-workers that if Oracle was smart, they'd use that as the replacement for nVision (and metric calculations in EPM).

So, Why is it a good thing?
Believe it or not, we at PeopleSoft evaluated Siebel Analytics (although under a different name) to be the infrastructure for the next generation of nVision. At the time, it was a small company called nQuire. We put together a bunch of things we wanted them to prove they could do within a week against multiple PeopleSoft systems. At the time, Chris Heller and myself had a lot of discussions about the product's claimed capabilities... his quote was "Either they're crazy or they're geniuses". We found out that they were geniuses.

Unfortunately for PeopleSoft, Siebel beat us to the punch and acquired nQuire and re-branded the product. Now that Oracle has acquired both Siebel and PeopleSoft, it looks like that original vision may be realized for PeopleSoft customers.

So, what is Siebel Analytics?
Siebel Analytics is most of the functionality we planned to provide in reporting in PeopleTools 9.

• A data abstraction layer that allows users to work with meaningful objects for building reports
• A browser-based way of building both tabular and crosstab reports by business users
• A server-based quering engine that runs the reports and delivers results

In the published PeopleTools 9 plans, we had different names for the same functionality

• Data Objects (Data Abstraction Layer)
• nVision Studio (Browser-based way of building tabular and crosstab reports)
• nVision Engine (server-based querying and reporting engine)

Mark Rittman has some good images on his weblog (which is a must-read for anybody wanting to understand what's going on with Oracle and BI) that shows screenshots for building and managing the data abstraction layer and for building queries or reports.

Data Abstraction Layer

The data abstraction layer looks exactly as it did when we reviewed the product.

The far right pane contains the list physical objects that are the sources of data and metadata. These can be tables or files (and I'm assuming XML sources now). You can think of these as record definitions in PeopleSoft.

The middle pane allows you to map the physical objects to objects that have a more meaningful business names and structures. They abstract away joins and unions and other physical attributes you don't want to present. (a good example we had them prove in the PeopleSoft evaluation was that you could have a single representation for sales, where under the covers you were getting historical sales from a data warehouse and current sales from the CRM system). These are related to Data Objects in the PeopleTools 9 feature list.

The left pane contains the presentation layer for how you would want to present these objects to business analysts. For example, you might want to have different versions of an order, depending on the vertical you're using (where fields specific to a vertical are displayed and not others, and where the terminology presented to the user is targeted to that user type). In other words, you might have a different presentation object for Universities and Commercial in CRM, where in a university a customer is displayed as a contributor and in commercial, they're displayed as customer. These are related to Data Views in the PeopleTools 9 feature list.

Reporting

Siebel analytics provides a drag-and-drop interface for building reports in the browser using these objects. Although I believe that there is some opportunity for making the user interface more targeted to business analysts, they've done the big, hairy effort to pull together crosstab and tabular reporting into a single reporting tool. Here's another screenshot from Mark Rittman's weblog that shows some of the user interface.

As you can see, you have the objects from the presentation layer in the left pane, and you have the ability to lay them out in a report.

So, What's left to be done?

Good question. Here are the major tasks I see that need to be done to get nVision using this platform:

1. Map ledgers to the Siebel Analytics metadata. This should be relatively straightforward, but it is dependent on the fusion business unit/setid project as well as how chart of accounts configuration gets done in fusion.
2. Teach the Siebel Analytics platform how to read trees. Again, this is dependent on the fusion tree project. Fortunately, there are a lot of designs in place for how an engine would utilize different approaches for modeling trees. Another good thing is that the Oracle database has sql extensions for trees/hierarchies that make this much, much simpler than what is currently being done in nVision.
3. Build a robust excel user interface that leverages the calculation engine in Siebel Analytics. From what I know about WebADI, this infrastructure may do the trick, because it is build to allow web services to be used interactively to embed application functionality into to provide a user experience targeted to a business user.
4. Extend Siebel Analytics with output management functionality (this will probably involve work with concurrent manager, which is being managed by the person who used to own PeopleSoft's process scheduler and report manager).

Conclusion

Hopefully this makes most PeopleSoft customers more comfortable with the future of things. I've already known or suspected much of this information earlier, but was waiting for Oracle to provide enough information publicly for me to safely write this entry.

Labels: Fusion, nVision, Performance

With the recent popularity of our nVision drilling snap-on, we've had the opportunity to talk to many customers about nVision drilling performance, and how to improve it. Based on these discussions, I decided it was time to put a blog posting out that discusses it.

80% Rule
Let's start by discussing the two settings in PeopleSoft that cause 80% of the performance issues. Many DBA's will tell you that there is never a simple fast/slow switch that they can set (otherwise, why would you set it to slow?). We, in our infinite wisdom at PeopleSoft put this switch in place on the process scheduer and application messaging products.

Okay. That's not very fair, because these settings were put in place for something different than nVision drilling, and drilling is merely affected by these settings. However, based on the default values of these settings, nVision drilling can have a latency of 30 seconds or more.

The two settings to look at are polling intervals of the process scheduler and the application messaging dispatcher. For each of them, the default setting is set to 15 seconds, which means that with the default settings, the process scheduler will wait 15 seconds between times it checks to see if you've requested a process to run (i.e. a drilldown request), and the message dispatcher will wait 15 seconds between times it checks to see if there's a message for it to process (such as a report being completed).

Therefore, if it normally takes around 30 seconds to run a drill and you're using the default settings, reducing them will improve your performance (but keep in mind, that the server is doing more work by checking the queues more frequently).

• Process Scheduler Changes: The Server settings page in PIA (PeopleTools --> Servers) is where the Sleep Time is set for a given server (default is 15 seconds)
• Application Server Config Changes: In the application server config file (or using PSADMIN), there is a Scan Interval setting in the PSMSGDSP section, which sets the interval by which the message dispatcher sleeps between picking up requests (default is 15 seconds)

Other areas to look at:
There are several other things that can affect the performance of your drills:

1. The performance of running reports. See the following posting for more information on general nVision performance tuning.
2. Web Server Performance
3. The performance of the PSREPORTS servlet (which moves reports to and from the report repository).
4. The performance of the REN server (the application server process that makes running to window possible).

The Anatomy of running a drill

When you run a drill in nVision (whether you run it through our snap-on, or you use PeopleSoft-delivered functionality), it does the following things:

1. It opens a web page that passes parameters from the report and requests the drill to be run on the process scheduler.
2. The process scheduler picks up the request and invokes nVision and requests it to run the drill.
3. nVision launches and calls the PSREPORTS servlet to move the report you're looking at from the report repository to the process scheduler server.
4. nVision selects the cell you're drilling from, and requests the drill to be run (which opens up the drill layout, generates the SQL and runs the drill in the same way that an nVision report is run).
5. When the report is completed, the process scheduler calls the PSREPORTS servlet to move the drill results into the report repository and publishes a message that indicates that the report is finished (using application messaging).
6. The message dispatcher takes the message and updates the metadata in the report manager tables, so that the report shows up and is available to the right people.
7. The REN server is notified that the report is ready to be viewed, and it notifies the browser of the person running the drill.
8. The browser invokes the URL to view the report, which calls the PSREPORTS servlet to access the drill results and download it to the client.

There! All done! Wasn't that easy and simple? So, you can see how the latency of the polling can affect how long it takes to get drill results. However, the PSREPORTS servlet, web server performance, and the performance of the REN server can also affect the delivery time.

PSREPORTS Servlet

The PSREPORTS servlet is the process that moves content into and out of the report repository. As you may notice, it is called 3 times when drilling. Once to get the report from which you are drilling, once to post the drill results to the report repository, and once to deliver the drill results to the end-user. The PSREPORTS servlet is actually a pretty small and simple program. All it does is authenticate the user, check whether he has access to the report, and copy the file.

Usually, when there are performance issues, it's caused by the file system used by the report repository or by network performance issues between the process scheduler server and the report repository (although network performance between the end-user and the report repository can also be a culprit, you'll see performance issues with anything you access from report manager).

Web Server Performance

The interface between the client requesting the drill and nVision is through the web server (i.e. a PIA page). If there are web server performance issues between the client machine and your PeopleSoft web server, you'll also have performance issues with drilling.

REN Server Performance

The REN Server handles the messaging between PeopleSoft and the browser page. PeopleSoft's internet architecture was designed to allow a stateless connection between the browser and the server. This means that all interactions between the client and the server are request / reply. In other words, you put some data in a page, post it, the server processes it, and replies with a new web page. This means that a page cannot be out there waiting for an event to occur to do something.

In comes the REN server (Realtime Event Notification). What it does is it sends a message to a specially designed page when an event occurs (and the page does processing in it). The run to window page is one of these special pages (but the multichannel pages in CRM are also other special pages).

The REN server generally isn't a performance problem, but can be if the application server machine that it runs on gets overloaded with too much other processing.

In closing...

Now that you know more than you ever wanted to know about process scheduler, REN Server, and drilling, you can impress your co-workers at your next company function (or just annoy them).

Labels: Drilling, nVision, Performance

As I was setting up a meeting with a large PeopleSoft customer (for Open World), we talked about issues they're having with their solution for handling rogue PS/Queries. For customers running PS/Queries using the internet architecture, this can be a problem (but isn't unique to that release). Unfortunately, the solution they were using was also affecting their Crystal and nVision reports that used queries.

What are Rogue Queries?
Rogue Queries are queries that are started, but never seem to complete. Because each process in PIA takes up an app server process (whether PSAPPSRV or PSQRYSRV), this locks up valuable server resources (because PeopleTools isn't threadsafe, the way to scale up is to have many app server processes running). It also takes resources from the database server.

Rogue Queries are most often created by users who kick off a long-running PS/Query and then close the browser window. The query continues to run, even though there's nothing waiting for the results any more.

How do you handle Rogue Queries?
Prior to PeopleTools 8.44, there wasn't a clean way to handle them. Many customers would use the PSQRYSRV (which is a specialized PeopleSoft application server that can be used to handle queries), and then kill the process periodically if it runs too long. Although this seems to address the problem, it can cause other issues:

1. There can still be a thread running on the database server related to the SQL and returning the data, even though there's no app server process to receive it (therefore, the roque query is still there on the database server).
2. If you're running nVision reports or Crystal reports in 3-tier (i.e. using PSQED or Crystal designer on the client connecting through the application server instead of directly to the database server), those reports can get killed inadvertently.

Another approach for preventing rogue queries is to use the process scheduler to run queries versus running them online. With the "Run to Window" functionality in PeopleTools 8.42, you can make it seem like the queries are being run on the app server, even though they are being scheduled to run "right now" on the process scheduler server. Processes are much easier to identify and kill on the process scheduler, and a rogue query running on the process scheduler server will not lock any of the app server processes.

The best solution, however, is to use PeopleTools 8.44. In this release, a lot of thought was put into this issue, and PeopleSoft provided a solution to this problem at two levels:

1. The query permissions list was extended with the ability to restrict the amount of time (or number of rows) a query can run or return.
2. The query monitor was enhanced and renamed to the Query Administrator. It allows an administrator to identify rogue queries currently running, kill them, and disable queries from being run in the future.

The limits in the permission list will cause any rogue queries to automatically be killed, regardless of what is running them (except for 2-tier running of queries, i.e. PSQED or PSNVS). The killing of the queries will kill the thread on the database as well as the app server. For the people with nVision design access connecting through an application server, one can bump up these time limits (or make them unlimited). If you create a rogue query when running nVision reports, you can use the query administrator to find the query (it has your user ID, process name, and amount of running time listed), and kill it from there.

Labels: Performance, Query

Just saw the following question on ITTOOLBOX.COM

It's an interesting problem, and is completely related to how nVision and excel work in conjunction with each other.

Here is background information on the problem:

When nVision runs on the process scheduler, two processes are launched, PSNVS.EXE and EXCEL.EXE. Sometimes, one of the processes crashes or hangs, and the process scheduler doesn't know how to manage both of them (it only knows about the one it directly launched).

The person posting the question already found the way to clean it up: look on the process scheduler server that is having the problems, and then looking at task manager to see if there's the same number of PSNVS.EXE and Excel.EXE processes. Then, killing the one that's out there.Usually, what causes this problem is contention in excel. It's not truly multi-threaded, and so when it is hung by one nVision instance, others have problems launching.

For customers upgrading from release 7.5, nVision's architecture was different (and the scheduler would not run more than 1 nVision report at a time). This may explain why the problem only manifested itself after an upgrade.

We did a lot of testing after numerous customer complaints and found that running too many nVision reports in parallel on the same serveris what often causes the hanging to occur (again, related to multithreading issues in Excel). This is also why in PeopleTools 8.2x, it is not recommended to run more than 3 nVision reports on a process scheduler instance at the same time.

PeopleTools 8.44 has enhancements to process scheduler to resolve this situation automatically. Prior to that release, some customers have written a script that kills old PSNVS or Excel processes.

Some customers will also segment existing hardware using windows terminal server to have multiple virtual machines to run nVision reports. You can then configure a process scheduler server for each virtual machine and really run excel in parallel without contention. This approach should virtually eliminate the problem, if I'm right as to what it is.

Labels: nVision, Performance

It's surprising to me how few people know how to effectively tune nVision. Even from within PeopleSoft, there didn't seem to be many people who could do it. One side benefit of knowing the "secret handshake" was that I got a free trip to Paris to help Carrefour.

Over the years, we added new features to the product to make it easier to tune nVision, and believe it or not, it's easier to tune nVision now than it ever has been in the past. Hopefully this weblog entry will help you understand how to approach it.

Why is it so challenging?
Well, actually, it's challenging to tune almost any product that does what nVision does. Because data is stored at a very granular level, tools like nVision need to aggregate the data to get results. In addition, the flexibility for using trees in nVision can make it more challenging to tune than a star schema in a data warehouse (but the trade-off with a warehouse is that you are more limited in what you can do with it, believe it or not).

In many ways, I believe that the biggest challenge is more cultural than anything else. Because nVision makes it extremely easy to create "analytical" reports and view data in different ways, many people don't expect that they have to manage it as closely as they have to manage a data warehouse (although there are may data warehousing techniques not used by nVision that would dramatically improve performance).

How to approach tuning
The performance of nVision is driven by two different aspects of how data is stored and how it accesses it. These are:

1. How easy it is to find the rows of data it needs to report on.
2. How many rows have to be aggregated to get a number on a report.

Let's talk a little bit about each of these.

Finding rows

The first topic is the first place you should look when analyzing performance.

nVision accesses data very much in the same manner that a ROLAP tool does. There is a centralized fact table (often the ledger), with many different "dimensions" (or chartfields) that are used to group and filter the results. There are two key differences between the ledger tables in PeopleSoft and a well-designed star schema. The first is that in a star shema, the dimension tables are always joined to the fact table to minimize the impact of indexing the fact table. In nVision, dimension tables are only joined when needed for filtering and grouping the data, which means that the focus needs to be on indexing the fact (or ledger) table instead of the dimension tables.

Another difference is that nVision does not use a star or snowflake schema in the dimensions. It uses a proprietary structure called a tree, which provides much more flexibility in how you model hierarchies than a star schema would support. It also allows many different hierarchies to be used interchangably with the fact table (which also is a big bonus). However, this also means that the rules for flattening the tree and generating SQL needs to be managed closely.

Therefore every organization needs to look at two things when managing the performance of nVision: indexing (especially on the fact table), and the tree performance options (which drive how the SQL is generated by nVision).

The most successful organizations are ones who take a methodical approach to both of these (this is how one organization is able to generate around 200,000 nVision instances each month on a 4-way NT box running nVision).

When looking at indexing, it is important to look at which fields are constrained and grouped on the most (and analyzing the cardinality or number of values of each). Quite often, putting the accounting period as the first field in the index is the best place to start, because there are at least 12 values, and they are always used to constrain data. Account is often the second field, because you almost always constrain against the account field (otherwise you would get meaningless financial statements).

When looking at tree performance options, the best approach is to go through all the permutations of options for each tree used (focus on one tree at a time), to determine which option will work best for a given report and data set. This is the approach that the high-volume customer used. They also prioritized their reports by the ones that had the worst performance and focused on them one at a time. They also had a defined performance goal for their reporting window, so that they could claim success when meeting that goal.

Aggregating Rows

When you're confident that you've done a good job of indexing and setting performance options, the next place to look is the number of detail rows you're reporting on. Because nVision is all about summing up details to get summary values (that's what a financial statement is), you will get into performance issues merely because each number on a consolidated financial statement may represent tens of thousands of detail rows in the database (and all of them need to be aggregated to get that number).

A good way to determine the affect of this is to do a a SQL trace of your nVision report and do the following. Any SQL statements that perform a sum of a numeric field (generally on the ledger table), edit the traced SQL to change it to count instead. Run the SQL with the count in it, and each number in the results is a count of the number of rows that needed to be selected to get a number in the report. Now, depending on your performance options you may not have the other tables populated so it's a good bet to make sure that you have the report open in the nVision designer when you run this trace.

If each number on a report requires more than 100 rows, it's a good bet that a summary ledger would be required to improve performance. The trade-off you make by using a summary ledger is that you are summarizing the data in batch versus online. Therefore, if you can have many reports use the same summary ledger, you are making the most of this trade-off.

For populating the summary ledger, you can either use the GL program to do this or use a little trick that I've had many customers use --> a materialized view. In both circumstances, you'll want to populate the summary ledger definition, so the reporting tools know how to access them. However, for the materialized view, you can let the database do the aggregation for you instead of running a program to do it (much better from a performance perspective). The key, however is that a materialized view will only support aggregating by dropping a chartfield in the summary table (wherease the batch program will allow you to summarize to nodes in a tree).

More on SQL tracing in nVision

It's impportant to note that in PeopleTools 8.44, we improved the tracing so that you can easily turn it on and get the SQL when running nVision on the process scheduler server (it's an option on the process scheduler configuration). Because the trace is captured and managed with the process, you don't have to go through a long log of SQL to determine which statements are associated with which reports. Additionally, it eliminates the need to try to trace it in the design environment (which has just enough differences in how it works to make it an ineffective way to capture it and manage performance).

Not sure how understandable this is. I'm more than happy to clarify any area in a future posting that is confusing.

Labels: nVision, Performance, Tree_Manager