Yesterday, I created a posting that discussed this for PS/nVision. Today, we’ll talk about PS/Query.
Well, Mr. Plant, thanks for asking. It all comes down to how PS/Query is architected.
In its initial incarnation, PS/Query was a C program. When Windows went from 16 to 32 bit, I was responsible for taking the old query rewrite the user interface (also using standard MFC controls). What came out of that effort was the windows-based PS/Query that you saw in release 7 and still see today if your use PSQED.EXE. That version had a lot of the things you would expect in a query design tool: a pane where the objects to build the query from exists, a pane that was the workspace for designing the query, and drag and drop and popup menus for building the query.
When we went from the windows client to the Internet Architecture, my initial plan was to take a similar approach as in the past. Develop the user interface in C/C++. Although some people were telling me to develop the user interface as a standard application page, I had concerns that PIA was not up to challenge of a query tool. PIA did not have (and still doesn’t have) drag and drop or popup menus. However, I also didn’t have enough people to rebuild the query design environment in C/C++.
Therefore, when release 8 came out, there was no query designer in the browser. The next release after that consumed me (and my team) with EPM-related requirements. Thus, the rewrite of PS/Query was handed to somebody else.
One of the first decisions the team made was to build the user interface in PeopleSoft application designer. They would build APIs around all the Query function calls and expose them in PeopleCode. This means that the complex rules for applying security, finding records, joining tables, etc. are all encapsulated in these APIs. This is exactly the promise that a service oriented architecture provides.
However, this team didn’t have enough time to complete the tasks in a single release, either. They feverishly built APIs and then UI to show it as quickly as possible, and PS/Query in PeopleTools 8.13 was the result (which I’m still apologizing for). After that release, I took PS/Query back and had developers flush out the APIs and the UI over subsequent releases, so that eventually it was functionally equivalent to the windows version.
So, at this point, PeopleSoft customers have something that is both more powerful (and less powerful) than the previous version. It’s more powerful, because you can harness the Query APIs to do anything you want to (except to break the security rules invoked in PS/Query). It’s less powerful, because it does not have drag and drop, popup menus, or a multi-pane design UI.
Again, keep in mind that the PS/Query UI is merely a set of PIA pages and PeopleCode, just like any other page (although a little more complex). Just as you can customize the vendor page, you can customize PS/Query. You can either modify PS/Query directly, or clone it. You can also create brand new programs, such as a wizard (this is what was done by the HRMS development group with their Query Builder, which simplifies query creation).
Modifying PS/Query UI code
The most common use of this is to make a certain change to a mass set of queries. For example, let’s say that you decide to create a table that denormalizes two tables that have a parent/child relationship. You’re going to want to point existing queries to this new table. You can write an app engine program to accomplish this, using the query API. There are lots of other use cases for this, even on the reading side, where you write a program that merely runs an existing query (scheduled query is a good example of this), and then take the results and work with it. Because the query encapsulates the complexity of the SQL and also provides a layer of abstraction between the database tables and the progrem, you get a lot of benefits by using the query instead of writing the SQL by hand.
The most simple means of accomplishing this is to embed the URL to run a query into your applciation. This was done by the PeopleTools security team to eliminate the need to build inquiry pages to allow users to inquire on what users/roles/permission lists/pages are related to each other. They built a query for each inquiry, and then built a page that had a hyperlink for each query.
This may seem like a pretty big thing to do. In many circumstances, however, you will find that it’s actually very easy. This is because although it’s difficult to build a generic wizard that will work in all circumstances, it’s actually easy to build a wizard to meet a specific goal. In other words, if you wanted to built a wizard that created a query that allowed a call center analyst to search for cases different ways, that wizard should be relatively easy: the tables are pretty constant, and you also know how you organize your products, employees, etc. You can embed these standards into the wizard easily and make it simpler to build these queries.
Future postings will have more detail on some of the solutions listed above. I will also take any questions on specific approaches to problems you’re trying to solve (but beware, those approaches may show up in future postings to this blog). Feel free to email me at mailto:firstname.lastname@example.org.