One of the things that I've had in my list of things to do for awhile now is to help out someone that I know answer the question of SQR vs Application Engine. They have the challenge of having a staff that doesn't know much about SQR or Application Engine (they are just implementing PeopleSoft now), but they do have consultants available that do know SQR.

So, having set the stage of what we're trying to figure out, we're going to step way, way, way back from current day and look out how PeopleSoft even ended up with SQR and Application Engine. Brace yourself, this isn't going to be a short entry :-)

Going back to the early days of PeopleSoft, there were two main batch tools; COBOL and SQR. Both COBOL and SQR had the benefits of working well across the different platforms that PeopleSoft supported. COBOL also had the benefit of being something that most PeopleSoft developers understood; most of them having come from old mainframe players like M&D and Walker. SQR brought a simple, yet powerful, idea to the table: let people write conditional logic as well as output data as part of a SQL statement. Today that's not so strange, but back in the late 80's/early 90's, having your printing logic so tightly integrated (some might say "tied") to your SQL was powerful stuff.

The big unspoken question of course is why didn't PeopleSoft create some sort of batch/reporting tool as part of PeopleTools? The short answer is that, in the beginning, everyone was too busy just making PeopleTools work for online transactions to even think about some sort of batch/reporting tool.

PeopleSoft did add PS/Query and PS/nVision in the PeopleTools 2-3 timeframe, but they were only reporting tools. Batch was not even considered for a higher level development tool at that point (although PeopleTools 3 also marked the delivery of the PeopleTools Process Scheduler). The only thing that PeopleTools provided was the concept of meta-SQL, which helped smooth over some of the differences between database platforms.

As time went on, it wasn't even the PeopleTools team that came up with a good higher level batch development tool. It was a lone coder on the PeopleSoft AR team, Owen O'Neill, who got sick of writing COBOL batch programs and decided to write a higher level tool for developing batch programs. He called it Application Engine, and used PeopleTools for building the frontend, and (of all things) COBOL for the backend.

Application Engine was different than regular batch programs because it wasn't procedural. It focused on set-based SQL logic and it had a much deeper understanding of the underlying application definitions that it was working worth. Describing set-based logic is beyond the scope of this blog post, but suffice to say that set-based logic is more in line with how SQL is supposed to work, but it's not as familiar to developers that have only worked with procedural logic.

To make an already long story short, Application Engine slowly won converts within PeopleSoft, and by the time that PeopleSoft 8 was being developed, Application Engine was adopted by the PeopleTools team. The frontend was converted to being a regular PeopleTool as part of Application Designer, and the backend was re-written in C++ and given the ability to run PeopleCode as well as SQL logic.

Although it took more than a decade for Application Engine to get developed and finally moved into PeopleTools, SQR and COBOL didn't really change that much during that time. PeopleSoft bought the source code for SQR, but that was really just to make our own lives easier for shipping new PeopleSoft versions - it was never made a full part of PeopleTools.

COBOL was even worse since PeopleSoft never actually controlled the COBOL compilers in use. There were some real big headaches that happened because of that - catch me at a conference sometime and I'll tell you some stories that will make you laugh (or cry) about some of our dealings with the various vendors of COBOL compilers that we had to deal with. As for the COBOL language, well, it's COBOL. Nuf said.

Ultimately though, you could dig up an early PeopleSoft developer and they would still feel right at home with the versions of COBOL and SQR that are still in use with PeopleSoft today. On the other hand, Application Engine kept getting new features, both for development and runtime use, and there were big plans for adding something known as PeopleCode Print to App Engine for PeopleTools 9. That probably would have been the end for formal support for SQR within PeopleSoft.

An interesting thing happened on the way to PeopleTools 9 though. Although, it was scoped on 3 separate occasions, for one reason or another, PeopleTools 9 never saw the light of day (other than a few PeopleTools 9 baseball hats that are collector's items now*). But there are a number of PeopleTools developers that are working on the Fusion project, and I expect that you'll see something very similar to Application Engine in Fusion.

Why would Application Engine show up as part of Oracle Fusion? There's a couple of reasons. One is that Oracle really doesn't have a good tool for batch development. If you look at existing Oracle Applications, you'll notice that batch is done by writing raw PL/SQL or Pro*C code. Not the most straightforward thing for a typical application developer to do. The other thing about Application Engine that fits well within Oracle is that it is a set-based tool. Most of the processing is pushed to the database, and that plays well to Oracle's strengths. Oracle themselves list using Application Engine for batch as number 4 in their top 10 things for PeopleSoft Enterprise customers to do today to get ready for Fusion.

Coming back around to the original question of whether it makes sense to train people in SQR or Application Engine for current PeopleSoft customers, the answer is that both have pros and cons. For starters, there aren't many PeopleSoft applications that don't use both SQR and AppEngine, so you need to have some level of skills in both. I think that CRM is the exception here; I know that CRM has no COBOL, and I think that is true for SQR as well.

SQR also has the advantage of being a little bit more independent of the applications. For example, when you want to write an SQR, all you need is a text editor instead of a proprietary tool like Application Designer. You can also run SQRs in any database, not just a PeopleSoft database. Of course, doing so requires that you go license SQR independently of the license that you receive with your PeopleSoft applications, but you could actually do it.

Of course, those pros are also part of the cons of SQR. SQR has zero understanding of the underlying applications that it's working with. Trees, effective dating, setids, row level security, related language records, etc. all have to be coded by hand when you're writing SQR code. Not impossible to do, but it's certainly more costly and error prone to build and maintain it by hand.

Application Engine comes with some cons as well, especially when looking at it from a reporting perspective. Aside from being a very PeopleSoft specific tool, App Engine is lacking any kind of built-in output capability. This was the target of the (never finished) PeopleCode Print project, but there's nothing there today. There have been various efforts to add output (we've done some of this in our products for example), but generally all you can count on App Engine for is data gathering and not any sort of output management.

Of course, Oracle has a great tool called BI Publisher (previously known as XML Publisher) that handles the data formatting and output side very well, and has recently been integrated with PS/Query and PeopleCode (which makes it accessible from App Engine). It's not a big stretch to see how App Engine could be enhanced to use BI Publisher natively as an output tool and effectively get rid of the need to use SQR at all.

Given that SQR is unlikely to get any further investment from Oracle, and Application Engine is, I would recommend that PeopleSoft customers ensure that they have SQR skills available to them (either from internal resources or from consulting augmentation as necessary), but limit their investment in new SQR development.

Of course, I would combine that with a bit of pushing Oracle to finish connecting the dots with the tools at their disposal for having a good batch/reporting story for Fusion applications :-)

Update 3-Mar-2007: A post by Mark Rittman about the Hyperion acquisition just reminded me that SQR, which was owned by Brio, who got acquired by Hyperion, is now owned by Oracle :-)






*) if you're a really weird collector.

Labels: 2007, AppEngine, History, SQR