I was having some beers at Pleasaton's (in)famous Hopyard last night and the topic of IBM and PeopleSoft came up. Specifically in the context of the announcement at the last PeopleSoft Connect conference about how we (PeopleSoft) were going to start heavily using the IBM WebSphere technology stack. Since I also had a regular blog reader come up and harass me at the table about a lack of blog entries this year, I figured that this would be a good one to write-up. Besides, it's been awhile since we've shared any good behind-the-scenes dirt :-)

Labels: 2010, History, IBM, Oracle, PeopleSoft

Paco Aubrejuan, General Manager of PeopleSoft within Oracle, gave a session this afternoon introducing the new PeopleSoft 9.1 applications.

Before getting into the content, I have to congratulate Paco on doing multiple live demos during his session. I see way too many people these days relying on Flash demos instead of showing things. Yes, that guarantees that your demo won't crash, but it also makes it much more likely that the audience will be asleep before your presentation is over. Leave the Flash demos for your website - the audience wants to see it live! Having someone at the senior management level do this is a refreshing change.

The session started out on a good note - I ended up sitting in the front row next to Steve Tennant (founder of the PeopleSoft Alumni Network), who was smart enough to find the power outlets embedded in the floor, and nice enough to share it with me.

As Paco got into the content, he shared some interesting statistics with everyone. In support of the statement that interest in PeopleSoft is still strong, he shared that there were over 250 new PeopleSoft customers last year and that customer retention rates are at high levels. Over 50% of the PeopleSoft customer base are now live on post-acquisition releases. Even folks that haven't been engaged for years are coming back alive (he told story of a government customer that was still on PeopleSoft 6 and was now upgrading to 9.1 based on some of the new functionality; PeopleSoft 6 shipped in 1996, so it's been 13 years)

He also talked about the fact that out of the 21 new solutions in PeopleSoft 9.1, only 3 are new products (meaning that there are additional license fees) - the rest are included, so it's part of what your maintenance money goes for. Across the board 9.1 has 1350 new features and 150+ customers were directly involved in the product planning, beta testing of 9.1. As a result of the underlying enhancements in PeopleTools 8.50 (which is the minimum PeopleTools release for the 9.1 apps), 28,000 pages now have Web 2.0 functionality.

In addition to having a number of demonstrations during his session, he also talked about the need for attendees to be able to easily articulate business value for why upgrading would make sense. In several cases he followed up the feature/function side with some hard numbers about financial impact (Steve Tennant was smarter than me by taking pictures of the slides while the session was going so hopefully he captured a few of these).

The high level breakdown of the business value was in 3 areas.

1. End users.
2. Line of business
3. Lower Cost of Ownership

End users were described as having been short-changed a bit in the past because of focus on specific feature/functions, but not as much on simplicity. One example that Paco demoed was getting a Treasury alert (via the new RSS feed functionality in PeopleTools 8.50) and then drilling in to a workbench that allowed some modeling of different financial options ("if I save X by paying some vendors early and taking a discount, can I put the money to better use?"), then collaborating via related discussion threads and then having some collaboration on different payment proposals over the internet. Nothing that couldn't have been accomplished before, but now it's simple enough that it can be demoed live in a keynote session (this was also the demo where Murphy showed up ; one of the pages didn't load properly on the first click so Paco had to logout and log back in and then was back in business.)

For the Line of Business category, Paco went through different things that have direct appeal to CFOs, CIOs, VP of HR. An example was announcing the new Oracle iReceipts for the iPhone. Bi-directional syncing with PeopleSoft Expenses is supported, including the ability to take a picture of a receipt and have that submitted directly for reimbursement. I would have killed for this when I was still at PeopleSoft and had to deal with the expense system.

Another example was showing some of the HCM functionality around Talent Management. Paco pulled up the Succession 360 view for succession planning. A very nice looking page showing current succession planning for a given person was shown with some drag and drop for re-arranging the view, etc. The supporting data popped up inline via the PeopleTools 8.50 Ajax support and showed off some slick comparison tools for looking at different potential candidates for a position (the demo user that he looked at was named Rosanna Channing, which kept making me think of the old Roseanne Roseannadanna character).

He also had an interesting technique for showing some of the Financials close process enhancements. The business process map showing the high level flow along with subordinate bullet points was augmented with customer logos for the customers that had worked with PeopleSoft development in that area for the 9.1 release (e.g. Sprint helping with the reconciliation process).

There was also discussion around the cost of ownership. One big example for improving this was surveying customers for the most common customizations that people implement and incorporating the main ones into the product.

There were some other interesting tidbits sprinkled in the presentation such as PeopleBooks is now available on the Kindle. Also, updated training and documentation were both available at PeopleTools 8.50 GA for the first time ever.

Labels: 2009, OpenWorld, PeopleSoft

A little while back we had a request for help from Genentech to solve an issue that was causing them fits. They have several Macs used for ESS and MSS that run Safari 3.x, the latest browser from Apple. The problem is that when Apple released Safari 3.x, they changed a tiny behavior of the DIV tag. The height attribute is now absolute instead of the minimum height. The result is that certain pages now display overlapping text.

For example:


So, the question was how to solve this problem without rewritting every page that has this issue.

The solution is extremely simple; add a style that defaults all DIV tags to set the display style to inline. In order to accomplish this on all pages, you need to add a small set of JavaScript to one of the PeopleSoft delivered scripts, for example PT_SAVEWARNINGSCRIPT. Simply open App Designer and the HTML object PT_SAVEWARNINGSCRIPT. At the head of the object add the following lines:


if ( navigator.appVersion.indexOf( "Version/3" ) > 0 &&
navigator.appVersion.indexOf( "WebKit" ) > 0 ) {
document.write('<style> div { display:inline; } </style>');
}



The code checks the version string for two things, the start of the version number (Version/3) and the browser rendering engine (WebKit). This allow us to only modify the HTML when the browser is Version 3 of the WebKit engine. That means both Safari and Google Chrome will get the extra style tag.

One caveat is that if you upgrade PeopleSoft, you will need to add this script back to the delivered HTML object.

That's it.

Labels: DIV, Mac, PeopleSoft, Safari

We at Grey Sparling just learned that Ben Rugg passed away on November 22, 2006 from a heart attack. If you use PeopleSoft Accounts Payable, his name is a name you should really know.

Over the years PeopleSoft had several people take on the role of AP Product Manager. Ben, however, knew more about Accounts Payable than anybody else on the planet (but you would never hear him tell you that). For many years, Ben was the top AP developer: always the guy behind the scenes tackling the hard work and making sure that customers' issues were addressed. He eventually (and reluctantly) accepted the Product Management when his expertise was needed most. Ben was also passionate about doing the right thing, even when it wasn't popular.

Ben, we salute you and honor your memory. You were a true PeopleSoft expert in every sense of the word.

Labels: 2006, Financials, PeopleSoft

We've had a couple of situations recently where a customer didn't have easy access to the machine where their process scheduler server was running. The process scheduler was installed as a Windows Service and running under an account that the PeopleSoft team did not have access to.

So when something is not working properly, and an environment issued is suspected, how do you figure out what the environment even looks like? You can a) open a trouble ticket with whoever manages the technical environment and wait for a response or b) run a program under the process scheduler that will show you the environment information. We'll focus on option b for the rest of this blog entry :-)

In order to figure out this info, we created a simple App Engine program that does 3 things.

1. List the environment variables for processes started via the process scheduler. This is accomplished by grabbing the output of the set command that the Windows command shell (cmd.exe) supports. There is a PeopleCode function to check environment variables, but it does not support listing all known variables. Besides, we want to see what Windows says the environment variables are, since that is what our spawned processes end up with.
   
2. List all .cfg files that are found in the appserv directory and it's children. For psappsrv.cfg and psprcs.cfg files, we put the contents of those into our output. These files are what actually hold the values that you typed in when you were setting up process schedulers and application servers with psadmin.
   
3. Get the Windows security information for the files. This is mainly useful for troubleshooting things like a process not being able to save files when running.
   

The attached project has the App Engine code and a process definition for it. The process definition gives access to the ALLPAGES and TLSALL process security groups, but you can adjust that as needed. Since there is no online page for configuring the values fed into the process, the process definition just puts it into the System Process Requests page (PeopleTools -> Process Scheduler -> System Process Requests). The name of the process that will show up on that page is Environment Information.

I mentioned that there is no online page for configuring this, but there are a few minor tweaks that you can make in the code itself. At the top of the PeopleCode, there are 3 boolean variables that control whether or not to run each of the 3 steps listed above. These default to True, but if you didn't want to run the list of the file system security, you could just change that variable to False.

The other configurable part of the code is for picking which directory to use as a base. By default it will use the PS_HOME environment variable as a starting point (which will pick up everything underneath that), but if you need to look at a different directory you can change that to something else.

As for the output, there will be 3 files that you can view from Process Monitor. One is called gs_environment_info.txt. That contains the list of environment variables and should be self-explanatory.

The next file is called gs_appservprocsched_cfg.txt. At the top is the list of all .cfg files that it finds (the same as what you would see if you typed dir *.cfg /s at a command prompt from PS_HOME). After that it lists the name and then the contents of each psappsrv.cfg and psprcs.cfg file that it finds. If you only have one domain setup, then there would likely be one of each, but that will depend on your environment.

The last one should have been called gs_cacls.txt, but it turns out the cacls command (which ships with Windows and will list file permissions) does not like to have it's output re-directed. So we just let the process scheduler pick up the output and dump it into the Message Log file. Note that the Message Log file is different from the "regular" Message Log. The Message Log file is visible from the View Log/Trace page in the Process Detail page in Process Monitor. The "regular" Message Log is the list of messages that the process wrote to the database. The quick way to tell the difference is that the Message Log file is just a plain text file, while the other Message Log page is a regular PeopleSoft page with a grid on it.

In order to make sense of the file permissions that are listed in output, you'll want to read the Remarks section of this document from Microsoft that explains it (or take a look at this writeup that has what I think is a more clear explanation). Neither one makes it super easy to make sense of the output, but it's better than nothing. A future enhancement to this program might be to parse the output and display it in a more human readable fashion.

Aside from fixing up the display of the permissions and offering a page to launch this from that let you pick which options that you want, a few other nice enhancements to this would be :

• Make it work properly in Unix/Linux environments (I've just been building out an Oracle Linux environment for PeopleSoft. Fun!)
• Make these into callable functions and deliver an iScript that calls them so that they can run in locations where there isn't a process scheduler installed.
  
• Support using cacls to change permissions (might require the ability to run the command as a different user)
• Support listing out the contents of other commonly used PeopleSoft configuration files (e.g. pssqr.ini)

Of course, what would really be nice would be to feed the process instance of a process that died for some reason and have it go figure out why. That is (as they say) left as an exercise for the reader.

Labels: 2007, AppEngine, PeopleSoft, Windows

Over the past 24 hours, I've had lots of folks wanting to learn more about the HCM reporting examples in yesterday's post. I decided to record a flash demo that shows how one would use the queries as well as the nVision reports (and drills).

In order to simplify the navigation in the demo, I did use the nVision Drilling Snap-on (which is separately licensable, but is not required to use the queries and nVision objects in the project). However, it does make it much easier to find and use them together.

Click here to watch the HCM Reporting in action...

Labels: Drilling, HCM, nVision, PeopleSoft, Query, Tree_Manager

This posting goes in the category of "why the $#%$^&$% didn't I do this earlier?!?!".

Yes, I finally spent some time getting myself up to speed on PeopleSoft HCM. One of our loyal blog readers was looking at using nVision to do some analysis without paying the significant costs of purchasing a BI tool and asked for recommendations for reporting against PeopleSoft HR. My only excuse as to why I didn't dig into this earlier was that I had a very strong Financials background (Cullinet Purchasing, followed by Millenium Financials, and finally working with version 1 of PeopleSoft financials and the beta version of AR and AP... I was one of the first 4 Financials consultants at PeopleSoft). Unfortunately, I never got to it.

Yeah... You made your point... You've been busy... But why do it now???

Good (but very cynical) question. Coming off of the success of our Open World presentations, we're gearing up to present at several other conferences this year. In order to make the presentations interesting to both mystelf and those who may have already been to one of our sessions, I decided to switch the subject matter to be HCM. When I got started, I was really excited about this. Then I with the person who put together the reporting demos that the Oracle/PeopleSoft salespeople use. Apparently, she did this years ago.

Oh well, I guess the difference is that I did the work myself, and that I'm making it available to all of you (so you may thank me now... or curse me now for not doing it earlier).

Queries

Let's start with Queries (because although I created some very cool nVision examples, I'm afraid I'll lose many of you HR junkies without hooking you first). I started putting together queries that focused on getting employee lists based on different attributes, such as JobCode, Deptid, and Supervisor.

Here are the queries that are included in the project (again, most of which are built against the JOB and PERSONAL_DATA records.

One of the things that France Lampron, founder of NuvoSoft taught me when we brought her in for use case development for nVision 9 was the importance of analyzing the HR actions in HCM reporting (this tells you whether you need to be worried about a manager that has a large number of termination actions for his employees). In honor of her, I created a query that lists the actions for a given employee. Here is what the results look like prompting against a given employee:

Attached in the Zip file below is a project that includes all these queries for your personal use. Keep in mind that I hadn't focused on whether you want to provide security for the data in them (query security will handle that in general. However, if there are users who should have access to some of the data in these records, you should create views that only show the pertinent fields, grant access to them by adding them to the tree, and build the appropriate queries, using these as a guideline).

nVision Reporting against HCM

For me, this is probably the most important part of this posting. I am going to prove, once and for all, that nVision does work for doing analysis against HCM, and that there are a lot of good reasons to use this tool. As with many other things in this blog, had I spent the time on this earlier, PeopleSoft customer probably would have had much better reporting delivered out of the box (and I'm truly sorry for that).

When looking at nVision reporting in HCM, I focused on three main areas:

• Generating a report that shows headcount and salary information at the organization level. This was based on a delivered nVision report (that hadn't been tested in many PeopleSoft releases).
• Generating a report that shows headcount and salary information by salary grade. Again, this was based on a report delivered as part of HCM, but was also broken.
• Generating a report that shows a benefits summary. This required less work to fix up.

The data in the standard HCM database has good data in the US006 business unit, that's what I used:

Here's what the departmental headcount and salary report looks like:

Drilldown Layouts

One of the key features to nVision is drilling. It allows you to take slices or list out details that support the data shown in a summary report (sometimes called analysis). The most important drills you can include in HCM are ones that list out employee information (either personal data or job information depending what you're looking for). There are also drills that allow you to see how FTE or Salary information is broken out by different atrributes (such as the department tree, jobcode, or location). Here is the list of drills I developed as part of this effort:

When drilling from the Department Salary report to the Employee Salary details (from the Job record), this is what you would see:

Other Information that might be Useful

One thing that is important to note is that all these examples perform analysis as of the date that the report or drill is run. This means that there is no time series analysis (i.e. tell me who's enrolled in benefits right now). For time series analysis, you will need to create a view that segregates the effective dates of the data into categories (such as month and year) for doing that type of analysis. That may come in a future posting.

Excellent! How can I get a copy???

I'm glad you asked. I've put together a zip file that contains the nVision definitions as well as the query definitions discussed above. The nVision reports require the query definitions, because the delivered ones by HCM are broken.

Download the zip file from here.

Copy the .xnv files from the nVision directory to your nVision directory, and copy the projects directory to your projects directory (and use app designer to import the project).

Taking it to the Next Level

One of the other things that I did as part of this effort was to pull all these objects together with our nVision Drilling Snapon. This allows you to organize your nVision drills better as well as enable drilling from your nVision reports to Queries and pages to either get additional information or take action on the contents of your reports.

Here is an example of what the drill menu could look like:

I plan to put together a flash demo that shows how you can drill from a departmental report to attributes, drill to employee detail, query on attributes of the employee, and maintain employee information, all from one place. As my daughter would say, "FANTABULOUS!!".

Labels: Drilling, HCM, nVision, PeopleSoft, Query, Reporting

Out with the Old
We watched Dick Clark, drank Champagne, and sang Auld Lang Syne. We also began work on our year-end close, just like so many of the PeopleSoft customers who read our blog. This must mean that 2006 is over and 2007 is beginning. It was such a great year for us that we felt we would be remiss not to blog the year.

In with the New
2006 marked the first full year of operations for Grey Sparling Solutions. It was a great year to be part of the Oracle/PeopleSoft world. 2005 was a year of uncertainty for PeopleSoft customers (what is Oracle going to do? what is Fusion all about? who planned the PeopleSoft content at the 2005 OpenWorld?), but 2006 was a huge improvement.

Oracle announced Applications Unlimited at the Collaborate 2006 conference, and that has been hugely popular. Oracle also announced that the PeopleSoft products would have their own General Manager structure, with longtime PeopleSofter Doris Wong heading things up. Another interesting metric is that all of the system integrator companies that we work with seem to have more business with PeopleSoft projects than ever before

The Grey Sparling Effect
We saw a significant increase in the number of customers using Grey Sparling products. That wasn't too hard since we shipped our first product in November 2005, but we have more customers than employees now, which I always remember as being a key statistic for enterprise software.

It also helped that we have several new products that we shipped this year:

• nVision Drilling Snap-On
• Report Explorer
• PSIDE Helper Snap-On
• Desktop Single Signon
• nVision Context Helper Snap-On

All of that helped us turn a profit for 2006. Woo hoo! Well, it wasn't a very large profit, but it still feels pretty good to hit profitability in our first 18 months of operation. We also became an official Oracle partner this year.

PeopleSoft Experts on the Road
Aside from folks that became Grey Sparling customers this year, we talked with a large number of people at various conferences. We were extremely flattered to be asked as subject matter experts by conference organizers to give Oracle (versus vendor) presentations. This allowed us to connect with over 2,000 PeopleSoft customers in person in 2006.

• Financial Services Industry User Group
• Alliance Conference (Higher Education User Group, State and Local User Group, Federal User Network)
• Northern California PeopleSoft User Group
• Mid-Atlantic User Group
• Quest Mid-West
• Oracle OpenWorld
• UK Oracle User Group
• Santa's Helpers User Group (just seeing if you're still reading!)

PeopleSoft Experts Online
In addition to physical meetings with folks, we also met lots of people via our weblog. I'm always surprised that there aren't more PeopleSoft bloggers out there - we certainly know plenty of people with lots to say about PeopleSoft!

We were also proud to be credited with inspiring some new bloggers out there.

• Marion Crkon
• Dan Iverson
• Jim Marion

2006 Blog Entry awards.
We figured that if Time Magazine can have its set of top 10's, we should too.:

• Most Unusual blog entry. Who would have thought about integrating instant messaging with PeopleTools? Chris, of course!
• Most Humorous blog entry. Before it was posted on YouTube, the top link to see SAP's take on Larry Ellison was from our site.
• Most Historical blog entry. Think scoping a PeopleSoft release took lots of analysis and effort? It did, but if certain features made it in, the world may actually have come to an end.
• Best Fusion Preparation series (Java and PeopleCode 1, Java and PeopleCode 2, Java and PeopleCode 3). J2EE and Web 2.0 your PeopleSoft application, because that's a big part of Fusion!
• Most popular blog entry that almost didn't get written. Is anybody really thinking about Setids and Business Units any more? Apparently Yes!

Top Grey Sparling Conference Stories of 2006
While we're putting together our top blog entries, we might as well list out our top conference stories of the year.

• Best Sales story. At Oracle Open World, we literally had a PeopleSoft customer come up to our booth on the first day and ask us how quickly we could generate an invoice for our Desktop Single Signon product. We did a remote install using the Open World wireless, and they were using the product live within 2 weeks for their 2007 Open Enrollment. Initial contact to Production in less than 2 weeks!
• Runner-Up Sales story. At the FSIUG in New York after demonstrating the nVision Drilling Snap-on, we did a remote installation of a trial version at a customer. This customer was able to use WebEx to show it to end-users who were not at the conference, and decide to purchase it right then and there.
• Best Cheapskate story. This had to be at the Alliance Conference, where we discovered how expensive it would be to rent an additional table for our booth for the 3 days the exhibition hall was open. Bert Laws of BearingPoint (a local of Nashville) was kind enough to make a run to Costco and buy a table for us (yes, it was significantly less expensive to buy a table than to rent it). We ended up giving the table to the folks at the Dell booth next to ours at the end of the conference (it would have been too difficult to check with our baggage).
• Best Clueless travel story. As good interenet users, we used Google Maps to print driving directions for all the places we were going in the UK while we were there for the UKOUG (knowing that Chris's mobile account for his Blackberry wouldn't work there). I don't remember the last time I had to look at a map to get where I needed to go in the states. This didn't work too well in the UK, and we ended up getting hopelessly lost many times on the trip. We ended up buying a map to help us make it through the trip.

2007 - The Year Ahead
The coming year looks to be an exciting one as well. With the success of the conferences we attended last year, we're signed up for lots of conferences this year as well. Although the sessions haven't been formally approved for these conference, expect to see us at:

• 2007 Northern California Training Day
• 2007 Alliance Conference
• 2007 EMEA Conference
• 2007 Collaborate
• 2007 Open World

From an engineering perspective, we've got two new products that we're partnering with customers to build in the first half of the year:

• nVision Bolt-on. This will address most of the outstanding nVision issues that we had hoped to address while we were still at PeopleSoft.
• Workflow Notifications. This is a product requested by a long-time Higher Education customer who saw our email notification functionality in our report distribution product, and are currently grappling with the best way to deploy workflow with eProcurement and HCM applications.

We also have plans to add some new features to our existing products, so if you've already licensed something from us, you'll probably hear from us soon.

Labels: 2006, Events, nVision, PeopleCode, PeopleSoft, Reporting, Security, User

I've known that Jesper Andersen has had a blog for awhile, but I just saw that Jim McGlothlin is blogging now. Getting sales executives blogging is pretty impressive. Jim's a good guy though. I can vouch for him :-)

You can find a complete list of Oracle blogs here. That includes executives and 3rd party blogs related to Oracle.

Although I don't see any submission form to get our blog listed. Hmm, maybe I'll have to ping Brian Duff (who started the whole Oracle blogging effort) about that.

Add a comment here to vote if we should be added to the 3rd party list of Oracle related weblogs (or if you think we shouldn't!).

Labels: PeopleSoft

We've been spending a lot of time on drilling features in our products. The nVision Drilling Snap-on is all about drilling, and we're working on a new product that will add drilling all over the online PeopleSoft pages. So we're constantly needing to dig up a complete URL to a PeopleSoft page (including the keys).



Like a lot of early web applications PeopleSoft 8 didn't do a very good job in letting you know what URL you needed to supply to get a user directly to a specific page. In between frames and a heavy reliance on using POST (which is a topic for some other day) it was pretty tricky to figure out.



So when PeopleSoft 8.4 came out in 2002 one of the things that was added automatically to the top right corner of each page was a link that would copy the URL for the current page to the clipboard.





You can see the same sort of thing done now on sites like Google Maps where they provide a "link to this page" link in top right corner of each page. Sof if you drag the map around or zoom in/out, you can get the direct URL to where you are I'm sure that there are other examples as well.



One minor problem with the way that the PeopleSoft link works is that it pulls in not just the keys needed to identify a single row in the component search, but it also pulls in alternate search keys as well.



For example, if you pull up VP1 on the User Profiles component and click on that link, then paste the URL from the clipboard somewhere where you can look at it, you'll see that the generated URL includes the DESCR field in it, along with OPRID. If the description of this user account ever changes (like someone getting married and changing their name), then the URL doesn't work anymore. Which is a real headache if you've mailed that URL to a bunch of people.



That's easy enough to work around by changing the generated URL before you do something like emailing it (pain that is), but if you're using Firefox and just tried to click that link and then paste the URL somewhere, you probably noticed that it didn't work.



The reason is that Internet Explorer allows JavaScript to access the clipboard by default and Firefox does not.



To turn on access temporarily in Firefox enter the URL about:config and hit Enter. This will bring up a huge list of settings. The one we want is called signed.applets.codebase_principal_support. Thankfully the settings dialog does type ahead searching so you only have to type a few characters to get to it. Once you find it, double click the entry to change it from it's default setting of False to True.



Now when you go back to the PeopleSoft page and try it again you'll find it works. Actually it prompts you about the current site trying to access the clipboard and ask you to grant access, which you'll want to allow. Security is always a good thing, even if it means an extra step in the process.



If you want to make this setting last between restarts of Firefox, then you'll need to update the Firefox prefs.js file, which stores your Firefox settings. This file lives in your Firefox profile directory.



An alternative to getting the full URL is to enable Notification support for the component. I'll write more about what Notifications (aka Ad-hoc Workflow) are in a future post. For now, the easiest way for you to test this out is to go to PeopleTools -> Workflow -> Notifications -> Generic Templates and pull up the system default template. You'll notice a Notify button down near the Save button. If you click this and take a look at the generated email, you'll see that the direct link to the component (with the proper key structure) is correctly generated.

The big drawback to this approach is that Notifications were not defaulted on in the 8.4 upgrade, so you have to update each component definition where you want this turned on (Component properties, Internet tab).

Labels: PeopleSoft

Wow. I knew that there were plans to adopt some of the PeopleSoft user interface for Oracle 12 (in advance of Fusion), but these screenshots that Steven Chan has posted are quite impressive.

I was almost expecting to see VP1 as the login ID :-)

For current PeopleSoft customers worried about teaching their users a new user interface in Fusion, this is a great indicator that the transition won't be too difficult.

Hopefully keyboard navigation is handled properly as well.

Labels: Fusion, PeopleSoft

Application Engine can be fairly handy in a PeopleSoft developer's toolchest. Aside from all of the useful batch processing things that it can do, it can also be useful for providing ways of running PeopleCode against a system directly from within Application Designer. This can be used for things like testing some PeopleCode or providing some developer productivity utilities. David Bain and I used to do some presentations on developer productivity for PeopleSoft developers, and taking advantage of Application Engine was one of the tips that we used to always mention.

An example utility is the version control work that we've been doing for our products at Grey Sparling. When you have a project that needs to be checked into the source code control system, the project needs to be split up from one large file into a multitude of separate files (the exact number of objects that you have in your project). We have an App Engine program that does this for us, but we needed a way to specify the exact project name to the program. In regular App Engine programs running on a server, you'd have some page for entering run control parameters and the program would look at these. But when you run the App Engine program directly from within Application Designer, you don't have those facilities available to you.

So what we end up doing is a couple of things. First, we take advantage of the COM integration in PeopleCode and use that to have Internet Explorer provide a prompt with the list of projects in the database.

When you have an Application Engine program open in Application Designer, you can press the traffic light icon or select Edit->Run Program from the menu. You'll get a prompt like this.



I always turn on the save to log checkbox, and then press Enter or click OK. Once the program starts, Internet Explorer pops up the list of projects in the database and lets you select one. The project name that you select is then used by the rest of the Application Engine program to do it's work.



Here's what the code looks like:



The IEPrompt function takes a title and a label and an array of choices and returns back the selected choice. We use a hidden form field as a flag for when the user has made their choice since we can't actually catch COM events from within PeopleCode. The HTML that we generate from PeopleCode is not super fancy, but it gets the job done.

The other functions in the code are to assemble the list of projects from the database into a PeopleCode array and then to put the entire thing together.

How about if you wanted to supply the parameter yourself without getting prompted? Maybe you, as the developer, want to run this AE program as part of a bigger script. The answer is to just pass the parameter on the command line and use a little PeopleCode to parse out the values.

In order to do that, I ported over this C# based command line parser from The Code Project. It was easiest to port by utilizing Java from PeopleCode.



The C# regular expression classes are fairly similar to what is available in Java. The only minor headache was that PeopleTools was having problems looking up one of the Java methods used (which we've seen before in previous blog entries), but that was fairly straightforward to get around. The workaround is to repeatedly compile one of the regular expressions instead of just once, but in this particular usage scenario, the overhead of that is so negligible that we don't care.

As a side note : native regular expression support was added the 1.4 version Java Runtime Environment, so if you're on an older version then you'd need to look at some extra libraries for adding regular expression support, or this code won't work.

Labels: PeopleSoft

End users don't always like the usability of PeopleSoft, but what about the people that have to administer PeopleSoft? In a previous entry I wrote about some ways of fixing up the usability of existing PeopleSoft pages for end users, but this time around we'll look at a way to make life easier for the system administrators.

Someone that I met at the recent Financial Services Industry User Group meeting brought up an issue that they were having with the PeopleSoft portal registry. Someone had run the menu import process (which populates the portal registry for PeopleSoft components that are not already in the registry) by accident. This caused them to end up with some extra portal references that they didn't want.

Since there isn't an "undo" feature for the menu import (and unfortunately it doesn't even log what actions it takes), the quick thing to do is when you see a link online in the portal that you don't want, go to where that link is registered and either change the security on it, or change the dates that the link is active, or flag it as hidden.

The normal steps for doing this is to select the PeopleTools folder from the portal navigation, and the Portal subfolder, and then the Structure and Content link (this requires that you have the Portal Administrator role). This gets you into a page that shows the entire structure of the portal registry and allows you to manage links and folders in the portal. Then you get to click through the folder structure to find the link or folder that you're looking to edit.

Sounds like a lot of clicks, eh? It is, which is why the portal registration wizard was added to Application Designer to make it easier get a new component registered into the portal. When the first builds of PeopleTools 8.4 were rolling internally, the PeopleSoft application developers were screaming about how much work it was to work with the portal registry. But the portal registration wizard doesn't help us in the scenario that we're working with.

It would be nice if we could drill from any online page directly into the portal registry administration page for that page. In order to make that happen, we'll first setup an iScript that can take the current URL and use that to figure out what URL to use for the portal registry administration. Then we'll setup a bookmarklet in our browser that will grab the URL of current page and send it into the iScript.

Let's start by taking a look at the iScript.



We start with a couple of helper functions. One for looking up the portal content reference by passing in the name of the menu/component/market that is being looked for. The other function takes a URL string and uses that to look up the portal content reference.

Then we get into the actual iScript itself, the function iScript_DrillToCref. It looks for a "crefurl" parameter (this is what our bookmarklet will pass in). If it finds that, then it looks to see if the current URL is a portal folder or an actual content page. Then it looks up the content reference in the portal registry (to make sure that it exists, etc.). If so, then it redirects to the appropriate page for maintaining the current URL in the portal registry.

In order to get the current URL into the iScript, we'll use a bookmarklet to generate the appropriate reference. For those that don't know about bookmarklets, they are like regular bookmarks, except that they use javascript instead of a plain URL. The javascript can do things like look at the current URL in the browser (which we need) as well as setting the URL to navigate to. Bookmarklets can do a lot more than that though. To learn more about bookmarklets and find some really cool pre-developed bookmarklets, check out bookmarklets.com or Jesse Ruderman's bookmarklet pages.

The one drawback to bookmarklets is that the entire javascript function has to be on one line. Our javascript logic looks something like

var g = 'WEBLIB_GS_CREF.VIEW_CREF.FieldFormula.iScript_DrillToCref';
var u = '../s/' + g + '?crefurl=' + escape(location);
location = u;

Fairly straightforward, but the actual bookmarklet has all of that on one line, plus some extra code that is needed to function as a bookmarklet. It's easiest for getting going with the bookmarklet to just drag the following link to your Bookmarks/Favorites bar.


You may get prompted about whether you really want to do this (some browsers give this security warning), but just go ahead and click OK. Note that clicking the link here won't work it only does anything useful when are logged into a PeopleSoft portal page and you select that link from within your browser favorites.

Of course, the bookmarklet won't do you any good if you don't have the iScript installed. I've uploaded a PeopleTools project with the iScript in it. You just need to register the iScript function itself using the portal registration wizard in Application Designer. You should grant access to the Portal Administrator role (permission list PTPT1300) for this iScript. That's the same role as has access to administer the portal registry.

There's some more functionality to this, but it's late and I'm tired so you'll have to look at the code :-)

What I've explained so far will save you anywhere from 5 to 10 clicks each time that you want to look at the portal registry information for the current PeopleSoft page that you're on. That adds up over time.

Happy administrating!

Labels: PeopleSoft

This entry is about how to enhance the usability of your PeopleSoft applications.

PeopleSoft always did well against competitors when comparing user interfaces, but there is still plenty of room for improvement. I don't know of any PeopleSoft users that would disagree with that. Let's take a look at one example of how we can improve the user interface in existing PeopleSoft applications.

It's common in PeopleSoft applications to see lots of grid controls on the pages. One issue that grid controls have is that they don't provide great keyboard navigation for power users. There are hotkeys that you can use to insert and delete rows from a grid (alt-7 and alt-8) as well as more advanced things like searching the grid, toggling between showing all rows of data or just a subset. You can see a list of the system hotkeys by pressing Control-K on any page in PeopleTools 8.4x applications.

If you'd like to do something like move through a grid like you do in a spreadsheet (up key moves up a row, down key moves down a row), then you have to do some work. So, that's what we're going to do. Along the way we'll also show how to highlight the field with the cursor in it. I did that to help make the demo video (see below) a little more obvious, but it's a useful tip in and of itself.

Step one is to capture the up and down keystrokes in the grid. PeopleTools has a delivered keystroke handler that gets inserted each page at runtime. Unfortunately there's no support for you changing how that works. You do have access to the code for the generic keyhandler, but I'd recommend against changing it. It's a little bit complicated to understand, and there's an easier way to insert our logic.

For the purposes of keeping the demo fairly simple, we'll insert our logic into to the page by placing an HTML area on the page definition. There are better ways of getting your logic into PeopleSoft pages without touching the delivered PeopleSoft definitions (which is the only way that we at Grey Sparling build our products), but those are beyond our scope today.

Note that in this demonstration I attached this to the USER_ROLES page, but it will work on any page with a grid control in PeopleSoft 8.4 and above. I've attached the code that goes in the HTML area as a separate file. Here's what the code inside the HTML area looks like:



We start off with a little CSS that defines how the field with the current focus looks. Here we're just setting the background to yellow, but you can do anything that you want. This gets turned on and off in the onfocus and onblur events for the fields.

Next we define a couple of helper functions (getEvent and getEventSource) to smooth over some browser differences.

Then comes our main function for handling the up and down keys, gridKeyHandler. We want this to get called everytime a key is pressed. The code looks at which key got pressed (38 is the keycode for up and 40 is the keycode for down). If it's not something we're interested in, then we don't do anything.

If it was an up or down key, then we use a regular expression to see if we're in a grid field. Grid fields have IDs of the form RECORD_FIELD$row_number. On the user roles page, the grid control is for the record PSROLEUSER_VW, so we see fields like PSROLEUSER_VW_ROLENAME$1, PSROLEUSER_VW_ROLENAME$2, etc. in the generated HTML. Our regular expression looks for some text followed by a dollar sign, followed by some digits. If we find that, then we add or subtract one from the row number (depending on whether up or down was pressed), and then try to move to the appropriate field.

The next two functions (oninputfocus and oninputblur) exist as event listeners for when focus moves between the fields. This technique comes directly from the HTMLDog folks Suckerfish series. Go check out some of their demo pages. Great stuff!

So how do we get these functions to execute at the proper times? The addEvent function from Scott Andrew LePera. PeopleTools attaches it's key event handler by directly assigning the PeopleTools JavaScript key handler function to the onkeydown and onkeyup properties of the document object. That works, but only for mapping one function to the event. addEvent allows us to "attach" additional functions to these browser DOM events independent of the PeopleTools function. For those are interested in using this technique more extensively, be sure to read these follow up articles that explain why this technique isn't the end-all, be-all method for doing this. It suits our purposes well enough though, so we'll use it here.

The last little bits make use of the addEvent function to wire things up. We can hook up our grid key handler right away, but we need to delay the attaching of our field level handlers until the page finishes loading (otherwise we might not have all of the fields even defined in the DOM yet) so we put that logic in a separate function and use addEvent to call that at page load. Alternatively, you could just make sure that this extra logic that we're adding always gets put into the HTML page after all of the fields that are having handlers attached to them.

I put together a short Flash demo of this code in action. This was my first time using the Wink tutorial/presentation software. It lets you record what you're doing and then lets you go back and do things like add text boxes, etc. highlighting what is happening in the demo. The lack of artistic ability in the demo is definitely due to me and not Wink!

There's lots of things that I would do to beef this up to make it production ready.

• Don't use an HTML area to get this code into the page.
  
• Move the CSS for the current field focus out of this code and use a regular PeopleTools style sheet definition.
  
• Add some unit tests for it with Selenium or JSUnit.
• Add some code to handle "wrapping around". If someone is on row 1 and presses the up arrow, then this should wrap around to the last row in the grid. If they are on the last row in the grid and they press down, then they should move to row 1.
  
• Beef up the regular expression test to handle the add row/delete row buttons. If you do a view source on a PeopleSoft page, you'll see that these buttons don't follow the same naming standard as the rest of the fields in a grid.
  
• Do some more testing on pages with grids at level 2 and 3.
  

As you can see, there are ways of enhancing the usability of existing PeopleSoft applications without too much effort. The grid navigation enhancement is just an example of what is possible.

Let me know if you end up implementing this in your PeopleSoft apps and how your users like it.

P.S. That picture of me on our company "About" page?  That was Larry's way of letting me know that I should have been in attendance when we were getting pictures taken :-)  I'm tempted to leave that picture up though.

P.P.S. I still haven't put up a new picture, but I delete the reference at the top to some conferences that have come and gone.

Labels: PeopleSoft, User

Rich Manalang has a good post up about adding Live Search into the PeopleSoft Enterprise Portal. He's even got a little Flash movie of how it works and the code required to make it work. Very nice!

We probably need to do some Flash movies of some of the stuff that we write about here.

Labels: PeopleSoft

An article on Slashdot points to this news.com.au article in which Bill Gates is quoted as saying that the IRS has special computers for his tax returns because of the large numbers involved.

This reminded me of a story that I heard from the folks who worked in Microsoft's payroll department. Many moons ago Microsoft used PeopleSoft Payroll . They had opened a case with PeopleSoft saying that there was a bug in the tax handling logic for generating W-2 tax forms.

At the time (I don't know if this has changed now), the IRS only accepted up to 8 figures for reported income. So if you made $100 million in one year, you had to file more than one W-2 form with the IRS. PeopleSoft Payroll had no provision for generating multiple W-2 forms for one employee, so Microsoft had to "hire" Bill Gates into the system multiple times (Bill Gates, Bill Gates 2, Bill Gates 3, etc.) In their opinion, this was a bug in PeopleSoft Payroll and should be fixed. PeopleSoft (of course) viewed this as an enhancement request.

It's a moot point now since Microsoft tossed PeopleSoft out when they standardized on SAP for everything ERP. I wonder when they're going to get their ERP acquisitions to the point where they can eat their own dogfood? Probably still a little way off :-)

It's interesting to see the Slashdot comments though. A good chunk of them focused on how much money can be represented in a single 32bit number (my favorite comment "a geek knows that he's made it when he has a 64 bit fortune"). The technical precision is certainly a factor, although in this case it had more to do with business issues than technology.

Labels: PeopleSoft

We've been spending quite a bit time lately working on our Grey Sparling PeopleSoft Solution Extenders. As you can tell from reading the weblog and our main website, reporting and analytics are a key area of focus for us.

One of the interesting challenges in this area is notifying users of what they're interested in. As part of the Grey Sparling Report Security and Distribution PSE, we have built a way of delivering highly formatted custom emails to each user as well as a new Report Explorer, which greatly expands on the capabilities of the PeopleSoft Report Manager. (Don't worry - there's more meat to this post than just telling you about stuff that you can buy from us :-)

As a follow on to that work, I wanted to write up a blog post about tying in instant messaging to the report notification. PeopleSoft added some support for instant messaging back in 2003 (I actually got sucked into doing interviews when we added support for AIM and the marketing folks were out on some offsite meeting :-), but it only handled presence detection and providing "click-to-chat" links on a page.

The example here shows deeper integration. A user runs a report (or it is run for them).







When the report is done, we start a chat with any of the users that are authorized for that report (if they're online) and send them a direct link to the report.





Now the user is directly in the PeopleSoft Report Manager. Or if they are a Grey Sparling customer, they get a much nicer interface with our Report Explorer.





In order to do the deeper integration, we're actually leveraging the Smack library from Jive Software. This is an open source Java library that understands the XMPP protocol that we'll call from within PeopleCode.

For those that aren't familiar with XMPP, it is an open standard for IM (messaging and presence). Aside from Jive (who have commercial IM servers based on XMPP) and some other open source XMPP based IM servers, there are a few other big names that support XMPP. From the title, you probably already guessed that GoogleTalk, Google's IM service, is based on XMPP.

You might not know that Oracle's Collaboration Suite IM product is also based on XMPP. I don't know why they don't do a better job of marketing that - with all of the news about Google getting into IM and being standards-based, you'd think that the Oracle marketing folks would have been talking up the fact that they have a corporate solution that works with the same standard. But, I digress :-)

At a high level what we're doing is adding some new subscription PeopleCode to the message that gets sent when reports are published. The PeopleCode picks out the URL for the published report and who the report is being distributed to, and then looks to see if any of those users are online. If so, it starts a chat with them and provides the direct link to the reports.

Before I explain how you can try this out yourself, I want to point out several things about this that make it just a proof of concept and not production ready, so if you find this interesting, please don't just stick this as is into your production PeopleSoft environment!

• It connects to the Google server each time a report is published. This is OK for a little experimenting because it simplifies the code a bit, but definitely not the way to do things for real. Especially since we have to wait a few seconds to receive the presence packets from the server.
  
• The username and password to connect with are hard-coded in the PeopleCode. A production version would store this info in the DB somewhere for easier maintenance.
  
• The text that it sends out is hard-coded to one language. Works for a demo, but not the right way to do things.
  
• The error checking is less than robust. Any problems and we punt.
  
• The demo code does not listen for the user to type anything back. It's not really instant messaging if it's a one way conversation! If you look at the Smack APIs, you'll see that they support receiving text back from the user, so beefing up this code to support that wouldn't be too hard.
  
• The PeopleCode is attached directly to the PSRF_REPORT_CREATE message. We never touch delivered PeopleSoft objects in our Grey Sparling PeopleSoft Solution Extenders because we don't want to cause any upgrade or support issues, so we'd implement this differently if it became shipping product. The other day, a little birdie told me that PeopleTools 8.48 (shipping mid-year 2006) supports having subscription PeopleCode detached from the message definition itself though. Then this particular problem goes away.
• It's not multi-threaded. If you have a single report that is being distributed to a group of people, then this code sends them messages one at a time. Not very scalable.
  
• Only tested on PeopleTools 8.4x.
  

I'm sure that there's other areas for improvement here - feel free to ping us if you come up with some.

To try this yourself, you need to:

1. Have an account on an XMPP based server (such as GoogleTalk).
   
2. Download the Smack libraries and place the smack.jar and smackx.jar in your PSHOME\class directory.
   
3. Open up the PSRF_REPORT_CREATE message in Application Designer and insert a new subscription PeopleCode program (right click on Subscriptions to insert a new one). I called mine GoogleTalkNotify, but you can use whatever you want.
   
4. Add this PeopleCode, editing it for your username and password.
   

Here's what the code itself looks like. Remember that this is just proof of concept stuff and not supported in any way. You are free to use this however you want.

Labels: PeopleSoft

I recently did a 1-week consulting engagement in NYC (as a software product company, not something in our standard business model, but a blog reader wanted me to help them implement the techniques in the following entry, and besides, money is money ;-)

As we followed the steps of creating a materialized view and registering it as a summary ledger, we discovered that not all the chartfields in the detail ledger were not showing up in the dialogs in GL for mapping to the summary ledger. I asked them to open up the record definition of the ledger table, and, sure enough, the field in question was not in the proper place in the record definition to be recognized as a chartfield (in fact, it was the last field in the record).

There's a little known tip (that was much more important in the early days when every customer had to customize the chart of accounts by adding new fields). As long as you put the field in the proper location in the record definition of the journal line and ledger table, all processing in GL will automatically adjust.

For the both the LEDGER and JRNL_LN tables, the field needs to be between the LEDGER field and the CURRENCY_CD field.

The formal recommendation from PeopleSoft is that if you cannot relabel the expansion fields delivered with the product, to use the CF subrecords to add these fields. Because the CF subrecords are placed between these fields, the existing logic works. This customer preferred to add the fields directly to the ledger and jrnl_ln tables, so moving the field to the proper place solved the problem.

Labels: Financials, nVision, PeopleSoft

Yesterday we attended Oracle Open World wearing our old Team PeopleSoft garb. We were trying to get noticed so that PeopleSoft customers attending Open World could find an expert in the crowd. We are after all, PeopleSoft Experts. Interestingly there was only one person I ran into that asked such a question of me personally. As it turns out I was unable to answer the question on the spot, but after half a night's sleep, I think I have an answer that solves the issue using a tool that I built for PeopleSoft several years ago called Cube Manager.

Cube Manager is a tool that was slightly ahead of its time. The goal of the tool was to allow customers to create Data Marts using the available PeopleSoft objects; specifically Records, Queries, and Trees. The part that makes it ahead of its time, is the fact that one cube definition can be targeted to several different targets, one of which is a star schema. The star schema output is what I'll focus on for this posting, but the general idea should work for the other supported targets as well.

The issue posed was this; using the PeopleSoft HR software there is a bit of a problem when using Trees to track departmental or organizational changes in conjunction with the Job Tracking functionality. The underlying issue is that the Tree doesn't necessarily have enough information to correlate to a specfic Job Transfer transaction so that when you try to do any Business Intelligence using both pieces of information (the effective dated tree and the transactions) there is no direct link between the two pieces of data.

After sleeping on it for a while last night, it dawned on me that Cube Manager was built specifically to help with this type of problem. In essence, the customer needs to build a dimension and a fact that can use a common set of linking information. Now a part of this issue is the fact that there isn't a clear set of linking information, but with Cube Manager and a star schema it may be possible to create a set of attributes that can be easily linked in a star schema. Now it is true that I haven't used Cube Manager in several years, but the concepts behind Cube Manager have driven much of the development I've done over the past several years.

The solution is this, since the organizational or department tree is basically a dimension and the set of job transactions are facts, it is possible to create a simple star schema with two dimensions and one fact in the PeopleSoft database that can link the needed data. The two dimensions are time and the org structure. The fact is defined by a job transaction query. As long as the individual's name or department is represented in both the tree and the job transaction the linking should be easy to perform. The result of the cube definition should be a star schema that is defined as a set of records in PeopleSoft. The records can then be used in Queries and thus reports. Also, since the cube definition is reuseable, updating the data in the records can be done each time a set of job transfers or organizational changes are made.

Labels: PeopleSoft

For something as fundamental as Setid indirection, it’s surprising how few people really understand how it works. Hopefully, this will shed some light on the subject.

Why, when, and how?
Setid indirection is the key to why PeopleSoft has been able to compete at the same level as Oracle and SAP. It allows complex organizations to use a single instance of the application, versus requiring multiple installs, each set up and configured differently. The Structure definition in Oracle Flexfields is intended to provide similar functionality (but less consistently).

Setid indirection was something introduced in PeopleTools 2, and to this day, amazes me that PeopleSoft was able to change something so fundamental in its products after the products were initially released. It’s also interesting that the approach for delivering it was to introduce it as a core component of the toolset, versus creating a standard around it.

So, we’ve talked about the when and why. Let’s talk about the how. The best way to explain the purpose of Setid indirection is to look at a complex company as an example. One I like to use is Virgin. This is because it’s pretty clear that the company has multiple disparate lines of business (and helps explain how it works.

So, let’s look at the high level structure of Virgin:

1. It has a recording label.


2. It has a series of retail stores.


3. It has an airline.

We could also complicate matters by looking at the different countries it operates in, and some of the requirements there as well, but let’s get to that later.

So, there are 3 different lines of business. In this example, there are definitely certain things that need to be different across the different lines of business:

1. There are probably different suppliers with different rules of operation.


2. There are definitely different products that are delivered.


3. There are probably different jobs, jobcodes, and unions.

However, in any organization, there are certain things that should be the same (to provide consistency where needed).

1. There needs to be a common chart of accounts for reporting financial information.


2. There usually needs to be a common fiscal calendar (again to allow consistent reporting of financial information).

Therefore, there are aspects of the business that need to be kept separate between lines of business, and aspects that need to be shared across lines of business.

Now, let’s add back in the complication of operating in multiple countries. Because each country has its own regulations and currency, some of those business rules also need to be segregated between countries, but also need to be shared across countries.

Enter: the Setid.
The Setid is the means by which sets of values are given an identifier. The Setid is generally the topmost key of any table in a PeopleSoft application that does not hold transactions. Setids are managed at the table level (or, actually, at the group of table level). So, in the example of Virgin, you could see the following:

• One common setid for the set of account numbers used to represent the financials.


• 3 different setids for the different products sold by each line of business.

One way to understand setids is to understand the entities in the application that use it (i.e. the entities where it makes sense to both segment and share definitions across lines of business). These are:

1. Entities that would be dimensions in an OLAP cube. These are things like customers, vendors, employees, products, and departments.


2. Business rules that govern how processing is to occur in the application. These are things like payment terms, pay cycles, allocation rules, calendars, and timespans.

The other main type of table in a PeopleSoft application is the transaction or document. These entities are keyed by a corollary field, called Business Unit. You could think of the business unit as a set of books in financials, or a discrete instance of a set of transactions that stand on their own for other applications. Examples of business unit keyed tables are Journals, Ledgers (which are just an aggregation of journals), vouchers, receivables, orders, returns, and cases). In the OLAP world, tables keyed by business unit are generally facts in the system.

So, how to business units and setids relate to each other?
Good question. Remember, that transaction tables are keyed by business unit. One could also think of a transaction as any business event that happens in an ERP application that needs to be captured or measured. Therefore, the role of setids is to control either the set of values used for things captured in a given transaction, or the rules used for processing that transaction.

This means that there is a relationship between a business unit, and the sets of values that are used for valid data, or for controlling processing (which are stored in tables). This is done using the tableset controls (which is accessed in the PeopleTools menu, under the administration sub-menu). The mapping of setids to business units occur with the following levels:

• There’s a level that groups similar tables together into a functional group (called record groups). This means that if there are 5 tables for storing or processing journals, you don’t have to map those tables individually, they are assigned a group and the mapping is at the group level.


• There’s a level called record group controls, that actually performs the mapping. When setting this up, you first provide a business unit value, and then assign setids for each record group that would be used for that business unit. The sharing and segregation occurs when you pick different setids for business units (or the same across business units). This means the following:


• If you use a single “share” setid across all business units for your account tables, then all business units will use that common set of values.


• If you use a setid for “recording”, one for “retail”, and one for “airline” four your product tables, then whichever business units you assign the “airline” setid will share a common set of products (you may be in a sharing situation if you set up a different set of books, or business unit, per country the airline operates in).

So, what happens when I use the application?
Another good question. Let’s build on the previous examples by looking at what happens when you enter a journal (i.e. a transaction).

When you go into the journal page, the first thing it does is ask you what business unit the journal is for. When you pick this value, you have identified what set of record group controls will be used for entering that journal. The first place you will se that is in prompting in the page. Pretty much any field, with the exception of the numeric fields, will use the setid indirection (i.e. the record group controls) to identify what values to use. One example may be the following:

1. When you prompt on the journal source, the setid for sources may be mapped to “share”.


2. When you prompt on the account, the setid for account tables may be mapped to “share”.


3. When you prompt on the department, the setid for the department tables may be mapped to “retail”, and so on.

When you run any batch program, the first thing you need to supply in the run control is the business unit for the transactions to be processed. This will identify the business rules to be used for that process (such as balancing rules for posting journals).

Okay, what about reporting?
Another good question, especially for nVision. PeopleSoft reporting tools definitely need to understand about setid indirection, so that the appropriate values are returned when the report joins transaction data to the attributes of the trasaction (i.e. dimensional information).
However, reporting has two extra levels of complexity:

1. It is possible, and quite often desirable, to show data across multiple business unites in a single report (a consolidated balance sheet is a good example).


2. Trees are very important for aggregating data, and need to be shared in a manner similar to the detail values (for example, a tree rolling up department values only makes sense for the setid of those values).

So, from a reporting perspective, here is how setid indirection works:

• Query will automatically perform setid indirection when joining between a transaction table and a valid value (dimension) table, if that table is set as a prompt table in application designer.


• Query will perform setid indirection for cascading prompts, as long as the business unit prompt has a sequence prior to the prompts that depend on it.


• nVision will only do setid indirection for the business unit specified in the report request (even if the report displays multiple business units). This is very important to know, because it is the key to solving setid indirection issues in nVision).

Now, for trees. Trees are mapped separately from tables. This means that you have the ability to map the setid for each tree differently for each business unit. However, each business unit has a default setid, and if the default setid for the business unit is correct for the tree, you don’t have to explicitly map it.

More on nVision: in design mode of nVision, setids can become even more complicated. This is because an nVision report layout can be run for different business units (which are supplied at runtime on the report request). This means that in design mode, you don’t know what business unit to use for setid indirection (and that is why in the “layout options” dialog in nVision, you can pick the setid to use for prompting of values when designing the report. In a scenario with multiple setids, this is important to know, because you may have to change the setid when adding criteria for different fields.

Applying this to the real world
Probably very few of you will be designing the business unit and setid structure of your PeopleSoft application. However, many of you will need to put this to use when writing reports, creating trees, or even maintaining the list of vendors, customers, accounts, and departments.

The first thing to know is that it’s very easy to be tripped up by setid indirection. I’ve handled lots of calls from customers who don’t get any data in their reports and think it’s a problem with the reporting tool. In the end, it’s usually either a setid indirection issue (i.e. the business unit resolves to a setid without any data for a given field), or an effective dating issue (i.e. the as-of-date resolves to a date before the first effective date).

The second is figuring out how to work around limitations in setid indirection. Many organizations will use setid indirection for security purposes (i.e. ensure that only a certain set of departments can be entered for a given business unit). Unfortunately, if you want to build a tree across the departments, the tree can only refer to a single setid (and, yes, because the tree is built on the department, which has its own definition for tableset mapping, the tree is resolved independently of the table it is built on). The only way to build a tree across multiple setids, is to create a single setid with all the values in it (assuming that the department values mean the same thing across setids). I call this “creating a super-setid”. This can be done through PeopleCode (either in an app engine program if batch is acceptable, or in SavePostChg, if it needs to be done immediately). Probably the easiest way to accomplish this is to create a view that gives a common setid for all values. You can then build the tree on the super-setid. In nVision, to get to the super-setid, you can create a super-business unit that has all the appropriate tableset sharing options for the super-setids. Because the business unit filtering in nVision can be different than that of the report request, this is possible.

Labels: Financials, PeopleSoft

I've talked with many PeopleSoft customer who have needed to use PeopleSoft trees outside of nVision, Query, or Cube Manager. One of the services I had pushed for was to add this functionality into PeopleCode (especially app engine).

Doesn't Tree Manager already have an API?
Sort of. Although there is an API available in PeopleCode for trees (called the tree classes), these APIs are focused on describing and maintaining trees (essentially tree manager functions). However, as any good BI person will tell you, it's not the act of maintaining a dimension that's important... it's what you can do with the dimension when you join it to other stuff.

So, what sort of things would you want to do?
The things you would want to do is to filter data with a tree or subtotal data using a tree as the grouping mechanism. The former is available to both nVision and PS/Query as either Filtering criteria or "in tree" criteria. The latter is only available in nVision through nPlosion. However, there are lots of things outside of nVision or Query that you would want to do.

• Aggregate data using the tree. Those familiar with Summary ledgers in GL understand the concept. You can aggregate data using a tree for other purpose, such as improving performance for downstream processes. For example, if you want to display a count of reported problems by product family for all product families, you're doing this type of aggregation at the product family level of the tree.
• Filter data using the tree. This is generalizing the "in tree" criteria in PS/Query. A couple of good examples of this are the batch programs used in GL for selecting open receivables to restate, or for selecting what data to use in Allocations. Another example of where this would be valuable would be in search records, where you could find all cases for eastern region customers, that are reported against any product in the Finanicals product line.

Today, PeopleSoft applications and customers use the technique I'm describing below to accomplish this.

First, a little more on Trees

The first thing to understand is how trees work. A tree is not a standard structure (such as a strict self-referencing table, as described in Ralf Kimball's definitive book on data warehousing), or a standard denormalized structure. It has a unique structure that allows it to do the following things:

Things that are not supported well by denormalized tables.

1. It supports unbalanced (or ragged) hierarchies.
2. It supports dynamic leveling.
3. It supports joining at any level of the hierarchy.

Things that are not supported well by a self-referencing table.

1. It supports finding all children at any level in a single SQL statement regardless of the number of intermediate levels.

So, how does it do it?

The key to it is the data model, and the fields added to it (that are maintained by tree manager). Here are the tables in the model:

• PSTREEDEFN - This identifies the tree, and the attributes of it (such as where to get application attributes of the nodes, leafs, and levels).
• PSTREENODE - This is the heart of the tree. It is a self-referencing table that also has additional attributes to facilitate non-recursive access to children of a node.
• PSTREELEAF - This maps a node to the sets of leafs or detail values that can be used with it. This can be thought of as the "leaf" level in the BI world, but that the leafs can be attached to any level of the tree, even to nodes that have children nodes.
• User tables - These are tables that store additional attributes to the node, leaf, or level, such as description, size, manager, etc. This allows a tree to be defined against any data element in the application and also opens up a whole host of opportunities for using trees in a given customer enviornment.

It's also important to understand that the tree definition is the starting place for everything, and that all the tools owned tables (i.e. not the user tables) strictly follow the key structure of the tree definition. Because trees are effective dated and utilize setid indirection, this is an important thing, because effective dating and setid indirection is only evaluated for the PSTREEDEFN table and the user tables. This logic is not used when joining between PSTREEDEFN and PSTREENODE. This means that from the perspective of effective dating, any nodes that have not changed between to tree instances are duplicated in the PSTREENODE table.

Once you've identified the tree you want to use (setid, tree name, and effdt are the unique keys), you also know the high order keys to use in the PSTREENODE table to get the nodes for that tree. Now, let's look at the structure of the PSTREENODE table:

• Setid
• Tree Name
• Effdt
• TREE_NODE
• TREE_LEVEL_NUM
• TREE_NODE_NUM
• TREE_NODE_NUM_END
• PARENT_NODE_NUM

As you can see, this table is self-referencing (the PARENT_NODE_NUM tells you the NODE_NUM of a tree node's parent). However, tree manager also maintains the TREE_NODE_NUM_END field, which is what prevents the need to do a recursive set of selects to find all the descendants of a node. Tree nodes are strictly numbered. A tree nodes' descendents will always have a node number that is between the value of its TREE_NODE_NUM and TREE_NODE_NUM_END.

Therefore, if I want to select all the descendents of the node "Assets", here is the SQL I would issue:

Select B.TREE_NODE, B.TREE_LEVEL_NUM
from PSTREENODE A, PSTREENODE B
where A.SETID = B.SETID and A.TREE_NAME = B.TREE_NAME and A.EFFDT = B.EFFDT
and A.TREE_NODE = "Assets"
and B.TREE_NODE_NUM between A.TREE_NODE_NUM and A.TREE_NODE_NUM_END

You could also filter by level number to get the descendents at a given level.

Now, to add in the leafs. For winter trees, this step is not necessary (winter trees are trees where the nodes themselves represent data points of interest, such as positions, whereas with summer trees, the nodes are the hierarchy that categorizes something else, such as most account trees or customer trees).

Because leafs are essentially mapping tables, you either join them directly to another table (either the user table to get attributes, or a fact table to get rows). However, for performance purposes, most PeopleSoft processes will stage the data values before joining it to a fact table.

Getting back to the subject at hand, here's the table structure of the PSTREELEAF table:

• Setid
• Tree Name
• EFFDT
• TREE_NODE_NUM
• RANGE_FROM
• RANGE_TO
• DYNAMIC_DTL_FLG

As you can see, the high level keys are again the same. Also, you can see that the mapping to the tree node is by node ID, and the mapping to the target table is by either individual key values or by range.

So, let's say we want to find out all account numbers and account types for all accounts under the "Asset" node of the tree. Now, there are three things that need to be looked up in order to determine what to put in the SQL:

1. The table and field where the Accounts are stored. This is stored in a tree table I haven't discussed, which is the PSTREESTRUCT table (which has a reference in the tree defninition).
2. The setid to use for the accounts. As mentioned, setid indirection occurs when going after the user tables. Depending on where your getting your business unit or setid, you will have to do the lookup separately (which is a more complex topic, but is often hard-coded at a customer anyway).
3. The effective date to use for the accounts. Again, this occurs when going after the user table. This means that you have to perform the standard correlated subquery for effective date logic. I won't go into detail on this in this posting, but you can see the SQL by building a query against the table in PS/Query.

So, your SQL will look like the following when after the list of accounts under a tree node:

Select C.ACCOUNT, C.ACCOUNT_TYPE
from PSTREENODE A, PSTREELEAF B, GL_ACCOUNT_TBL
where A.SETID = B.SETID and A.TREE_NAME = B.TREE_NAME and A.EFFDT = B.EFFDT
and A.TREE_NODE = "Assets"
and B.TREE_NODE_NUM between A.TREE_NODE_NUM and A.TREE_NODE_NUM_END
and C.ACCOUNT between B.RANGE_FROM and B.RANGE_TO

As you can see, this SQL statement didn't have to look at any children nodes at all! Because the leafs are attached using node numbers, and the range on the tree node record identifies all the node numbers of all its children, you could join the leaf directly to the node.

Now, it's important to note that this SQL will not perform in DB2, because indexes are not used when joining two fields of different size (which is why nVision has 30 different tables for staging the join between the tree leaf and the data table).

Staging the join

As mentioned previously, when joining to a fact or data table, one generally stages the join into an intermediate table. nVision and all other PeopleSoft delivered processes use selector tables (in nVision it's PSTREESELECT##, where the ## represents the field size of the field joined to. In nVision, there's a whole subsystem dedicated to managing what goes into the PSTREESELECT tables. For the purposes of this discussion, you can merely create your own selector tables that identify what you are putting in there, and the set of values that it represents. Let's assume, for example, you have a table called "PS_ACCOUNT_TREE_FLAT" to represent the flattened account tree. The SQL to load the data related to the "Assets" node might look as follows:

Insert into PS_ACCOUNT_TREE_FLAT as
Select "Assets", B.RANGE_FROM, B.RANGE_TO
from PSTREENODE A, PSTREELEAF B
where A.SETID = B.SETID and A.TREE_NAME = B.TREE_NAME and A.EFFDT = B.EFFDT
and A.TREE_NODE = "Assets"
and B.TREE_NODE_NUM between A.TREE_NODE_NUM and A.TREE_NODE_NUM_END

You could then join to the ledger table as follows:

Select Sum(B.POSTED_TOTAL_AMT)
from PS_ACCOUNT_TREE_FLAT A, PS_LEDGER B
where A.FILTERED_NODE = "Assets"
and B.ACCOUNT between A.RANGE_FROM and A.RANGE_TO

Hopefully, this gives a starting point to determining how to approach this. I suggest playing around with a SQL editor and PS/Query to make sure you understand (I did the same thing with nVision when I was first learning). Keep in mind, however, that the SQL that gets displayed in either an nVision trace or the SQL tab in Query does not include the SQL for populating that tree select table. However, it does show the filtering conditions used for joining the select table to the data table. You can select directly against the tree select table to see what values were put in it, and then look at the PSTREE% tables to compare the two.

Follow-on Info

For those who use tree manager extensively, we recently added a product that makes it easier to maintain multiple trees together. Additional information can be found here. A demo of this product can be found here.

Labels: PeopleSoft, Tree_Manager