Another question from ITTOOLBOX.COM

The answer to this question is contained in a previous post to this weblog.

However, database links may not be an available option to you (either due to policy considerations or due to dabase platform limitations).

Believe it or not, this issue sparked much debate in the PeopleTools team when scoping PeopleTools 9 (not the Peopletools 8.48 version that will be coming out under Oracle but the Tools 9/Tools X release that PeopleSoft was working on prior to the Oracle acquisition). There were several use cases that our applications wanted to solve that required remote access to another PeopleSoft database (such as using a KPI from EPM to control processing in a transaction system, looking up a credit rating from AR in the CRM order entry application, etc.).

The PeopleSoft application development teams wanted a feature called "remote data objects", which essentially allowed a remote table to be accessed as if it were a local table without having to get a DBA involved. As the person responsible for the Data Objects feature in release 9, I was part of these discussions.

In the end, we realized that although it would be convenient, remote data objects was not a good feature to deliver. This was because of the following:

1. It introduced a dependency between two different systems that was too difficult to manage. Without a standard interface between the two systems, a simple upgrade or change to one system could bring the other down. This was even more risky because the administrator of the system depended on would have no idea that the dependency existed.
2. It also created a potential performance issue. This is because database links can be poor performing, because data must be moved between database servers that wouldn't otherwise have to be moved if the data were local (and the current methodology in Peoplesoft applications is generally to replicate the data to a local table and then use it).

The resulting decision was that SOA would be a much better way of accomplishing this level of integration. By having well-defined services, a program can request through a well-defined interface, the information it needed. Upon upgrade, as long as the source system maintained the same interface, the consuming system would continue to work. Thus the interest in services oriented architectures.

Unfortunately, SOA has a long way to go to be as effective as a SQL statement. This is because the services are not designed for the type of access that a SQL statement supports. This means that SOA does not support reporting or batch programs in its current incarnation (we kicked around defining a standard for this type of service, but never got off the ground).

Labels: Database

So, ever wonder where all that "stuff" goes when you use the dialogs to lay out an nVision report? I'm sure many readers don't wonder because they already know. However, even those who do know don't always know the potential for using that knowledge.

All the "stuff" goes into content cells. For row, column, and sheet specifications, it's stored in row 1 and column A (I talked with the folks at Actuate responsible for e.Spreadsheet, and they use a similar construct, but instead of the top and left, they store their report definition information to the right and bottom of the report).

So, why do I care?
Good question. The reason you care is that you can do all sorts of stuff with the contents cells to make nVision do new and interesting things. I've worked with customers who've built VB modules that either create or update the content cells to either leverage corporate standards, or simplify the report design process. A good example is replacing a detailed ledger with a summary ledger automatically (this is something that I wish I had addressed as the product manager for nVision, because it's pretty cumbersome for a business user to understand when to use different summary ledgers that may be set up for them).

Here are some examples of things that I've seen customers do with knowledge of content cells.

1. Summary ledger substitution.
2. Streamlined, standardized, report design using a VBA application instsead of nVision dialogs
3. Mulitple field nPlosion on a single axis (this technique was developed by my friend, Tom Pitra of Pitra consulting).
4. Making calculated columns (or rows) drillable.
5. Extension of security (by having the contents cell have a blank value depending on the user running the report).
6. Faster report design (this is the most common usage... copy a content cell down and then modify part of it using the formula bar in Excel).

Okay, so what's in these cells?

Another good question. All content cells in nVision begin with a % sign. They are also made up of prefixes that identify what type of specification is in that cell. For example, if you have taken column C and designated that that column contain the local ledger, then the contents cell (C1) would have the following:

%,LLOCAL

If, instead of using a ledger, the same column used the column B.MONETARY_AMOUNT of query NVS_JOURNALS, then the contents cell (C1)would have the following:

%,QNVS_JOURNALS,CB.MONETARY_AMOUNT

As a 3rd example, let's say that row 6 had criteria to filter on the field ACCOUNT using the tree ACCTROLLUP, tree node INCSTMT, and nPlosion options to nPlode to all levels, including details. The contents cell (A6) would have the following:

%,FACCOUNT,TACCTROLLUP,XBYNNNY01,NINCSTMT

Now, that previous one may be a little complex because of the XBYNNNY01 string (which is actually the nPlosion options that were set). My suggestion is to use the UI to make the settings you want in that circumstance to learn what the different flags mean (each Y and N represents a check box either selected or not selected).

I created the following table to explain the different prefixes and what they mean (I don't remember what R is, so I apologize up front).

C=Column
F=Field
L=Ledger
N=Node
Q=Query
T=Tree
U=Metric
V=Value
Y=Period

The reason I had this table handy was because I used it to decompose inherited criteria in a drilldown, so that the user can see the context easily. The logic using the % and the prefixes is exactly the same. After the drill is run, all inherited criteria is stored in a defined name called NvsInstSpec. I used a combination of the Mid function and the Find function to split the InstSpec into multiple cells (and then did a VLOOKUP to the table to tell the type). The resulting inherited criteria from an example drill looks something like this:

Query CASE_FACTS
Column B.CASE_OPEN_COUNT
Field CUST_ID
Tree CUSTOMER
Node AMERICAS

The NvsInstSpec has the following value:

%,QCASE_FACTS,CB.CASE_OPEN_COUNT,FCUST_ID,TCUSTOMER,NAMERICAS

As you can see, much easier to understand.

Labels: nVision

This came up in ITTOOLBOX and is a follow-up on the previous posting I put together on this topic. The person asking it wanted to know how to add field level security to PS/Query, even though it was never part of the product.

Here is my response.

Probably the most elegant solution would be to modify PS/Query to do what you want it to do. Before Oracle acquired PeopleSoft, this would not have been something you would have wanted to do (because development could make a bunch of changes in Query in a future release and hose you). However, it's probably pretty unlikely that (a) you'll be upgrading any time soon, or (b) Oracle will make dramatic changes to PS/Query.

If you want to have a general-purpose way of doing field level security, you can create a table for maintaining the fields and the users who should not see those fields. In PeopleCode, you can check to see if that field is on the query and whether the user has access to the field and delete it from the Query if they don't.

Obviously, you will have to figure out how you want to handle public queries and design access. But, if all the person is doing is running a query, you can delete the field and not save it (assuming you're running on PeopleTools 8.4 or greater).

Labels: Database, Query, Security

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