We've been having an ongoing conversation with one of our loyal blog readers with respect to nVision, Business Units, and Queries. One of the things she found was that Queries didn't quite behave as expected with settings on the report request and was interested in learning more about it.

A Brief History of nVision and Queries

Let's start this discussion by going back to the beginning of nVision, where it was initially developed as part of the GL product and there was no such thing as PS/Query yet. It was built to perform reporting against the ledger and that was pretty much it. In PeopleTools 3, there was a bunch of new things that came in the reporting area: nVision was moved from being part of the GL development team to becoming a PeopleTool, Query and Crystal were introduced as additional reporting capabilities, and nVision received several new features out of the mix, allowing it to perform analysis against content other than Ledgers (as well as drilling into supporting transactional detail through queries).

On the nVision side, this was accomplished by extending the product to support Query as a data source in addition to ledgers as well as support of tabular layouts (in addition to Matrix layouts).

More about Query Support

Adding support for queries as a data source was a very elegant way of identifying data to be used while still leveraging much of the metadata in the PeopleSoft system. This allowed a business user to identify fields of interest, specify how to aggregate data to be analyzed, as well as leveraging effective dating logic.

nVision has special logic inside it to handle the additional criteria specified in a report layout, or to apply scope criteria to the report (it actually modifies the query on the fly to add the additional value or tree criteria and process it). However, a Query simply does not have the same level of metadata as a ledger does, which means that there are limitations. For example, there is no calendar mapping to queries as there is for ledger (which is the primary key to identify what a timespan means). Therefore, although the scope and criteria extensions of queries in nVision work seamlessly, much of the other extensions that are part of the report request are not applied (in the circumstance in question, the business unit specification in the report request is not used to filter the data, although it is used for setid indirection.

Tabular Layouts

One other thing that has intracies (or rather inconsistencies) with how it works is tabular layouts. For example, it would be nice to be able to take a query and apply worksheet criteria to it in a tabular layout. Unfortunately, worksheet criteria does not work and if you want to apply additional criteria to a tabular report without modifying the query, you will need to use a scope.

Labels: nVision, Query

Chris and I finally had some downtime today to catch up on a few things.

• Catch up with Peter Gassner and the Verticals On Demand Crew
• Finally record a flash demo for Client/Server single signon

When we recorded the flash demo of the single signon, we remembered that we had one customer who had created shortcuts to run queries in windows, which single signon made work really well for them. Of course, we had to include this in the demo.

Cool! How did you do it

Well, we actually leveraged a hook that I had been using up until release 8.0 in the windows client (you could actually pass parameters in the windows client to open up a panel with a specific item the same way you can in the web).

The actual command line used in the shortcut is as follows

E:\pt846\bin\client\winx86\psqed.exe PUBLIC.QUERY.GS_TEST_JRNL_DTL

As long as you follow the syntax of PUBLIC.QUERY and then the query name (or PRIVATE.QUERY if the query is private), this will work. Unfortunately, there isn't a way to tell it to run a query and pass in the parameters (but Chris tells me he's already working on it).

Labels: Drilling, Query

So, you know that there's opportunities for improving your end-users' ability to get meaningful information out of PeopleSoft, but it seems very daunting. We've had several discussions with organizations in the past 2 weeks, where they see the opportunity, but don't know where to get started.

This is a bit of a different spin on the following blog entry.

Baby Step 1 - Ad-hoc Queries
The first step is to start leveraging PS/Query as a means for getting data out. Many organizations have already been taking advantage of PS/Query (I know of several situations where organizations have tens of thousands of queries).

If you're not using Query, then I suggest your first step to be to centrally develop a small number of queries to provide answers to common questions and then secure them with object security to be read-only. PeopleTools 8.44 added the ability to run a read-only query, but not save it (which means your end-users can use them without being able to change or break them).

When you create these queries, you will want to try to make them as useful as possible to a wide range of users. This generally means:

• You will want to use prompts in the queries to allow end-users to use the same query to answer multiple questions. One neat trick on the prompt side is to put wild-card criteria in it (in other words, make it so that the criteria does something like this: WHERE ..... AND ( A.DEPTID = :1 OR '*' = :1)
• You will want the query to include fields to answer as many questions as possible related to the prompts and data source as possible.

Baby Step 2 - Formatting the Queries

Now that you're using PS/Query, the first thing your users will want to do is to change them, either by re-sorting the data, by applying additional filters, or merely by making the output look better.

Many of your more savvy users will run the Query to Excel and do this themselves. The first thing they will do is put an auto-filter on the data, so that they can use the query results as a data source for doing additional querying (using the auto-filter). They may also resize columns and apply other formatting to the data.

Unfortunately, this can be very cumbersome to do manually every time your end-users run a query. Therefore, they will very quickly ask whether there is a way to automate this. There are two options available to you:

1. A tabular nVision report with an InstanceHook macro in it to format the data. This option was discussed in the following posting. The nice thing about a tabular nVision report is that it's essentially a Query that is specifically designed and formatted, so it's relatively simple to do.
2. Our Excel Add-in product, which will take the results of any query and automatically format and add auto-filters to it. The nice thing about this approach is that any query your end-users run will automatically be usable in this way (whereas somebody has to build an nVision report and write the macro code to apply auto-filters in nVision).

Baby Step 3 - Linking Queries

As your users start getting used to running queries and formatting them, they'll start wanting to include more and more information in the query results. This is a natural part of the process, because once they learn one thing, users will want to see information related to it.

When your organization reaches this milestone, you will want to be very careful. Many organizations start extending the queries by joining related data into their queries. This can lead to "Kitchen Sink" queries and can cause two issues:

1. The query results start getting too large to understand. Now, the users have to start wading through the columns to find the data they're interested in.
2. The queries become difficult to develop and maintain. This is because as you join in disparate data sources, the SQL gets more complex, and you often start introducing cartesian products in your results that you have to find and troubleshoot.
3. Performance issues start to crop up. As the SQL gets more complex, the database has to do more work.

The best approach for this is to find ways to link queries together. Often, this is called drilling. There are 3 main ways of linking your queries together:

1. Modify the query to have hyperlinks in the result set. This is covered in the powerpoint and code attached following blog entry. This means you will have to pre-think and code this into the query.
2. Believe it or not, our Excel Add-in product will allow you to drill from any Query you're viewing in Excel, allowing you to link one query to another without having to write code or dirty up the output with hard-coded links to other queries.
3. Utilize drilling in nVision. I will discuss this further in the next topic, because the core functionality of drilling in nVision is not exposed for query-style output (so we haven't yet taken that baby-step in this blog entry).

Baby Step 4 - Aggregating information

Okay, now your end-users are getting information and drilling into related information. However, now they want to do some comparisons. How are they doing related to their budget? Has the average customer satisfaction been going up or down over time? Instead of seeing lists of information, now they want to start aggregating it.

The first and easiest step of this is to simply put subtotals into their queries (which can either be done manually by you or automatically with our Excel Add-in product). This will allow you to see counts and sums of your results broken out for each field you sort on.

The second step is to leverage PivotTables in Excel, which will allow you to do the analysis in a cross-tab format. Again, your users can do this using the Excel menus or you can automate this in a tabular nVision report and and InstanceHook macro (again the example is covered for pivot tables here).

Baby Step 5 - Aggregating and Comparing across different items

Although Subtotals and Pivot Tables on top of queries provides a lot of value to your users, you will quickly reach the point where your users will want more. As we discussed in Baby Step 3, your queries will have a targeted set of data (which means that the data available for your pivot tables is limited). What your users will want to do is to do comparisons between different items (such as comparing customer opportunities with customer satisfaction ratings).

The common thread for doing this type of anlaysis is that there's a set of "attributes" (often called dimensions) that you want to compare "data" (often called metrics, KPIs, or facts) against. There are a couple of options available to you here.

• Matrix reports in nVision. nVision allows you to take different queries (or Ledgers) and organize the data by common attributes, such as trees, chartfields, or timespans. Because you already have experience with Queries (and because nVision is included with PeopleSoft), this is often a natural step to take. The following blog entry has examples of nVision matrix reports for different subject areas.
• A BI tool, such as OBIEE, Hyperion Essbase, Cognos PowerPlay, or Microstrategy. These tools require additional development effort to model the relationships and often to extract the data from PeopleSoft. However, if your organization has already standardized on one of these tools (and especially if you have a Data Warehousing group), you may be forced to go in this direction.

Summary

Although there are other aspects we could look at, I believe that this is the best place to start (especially since many other aspects are more difficult to accomplish in baby steps). Another benefit of this approach is that your learnings can be applied to alternative technologies (i.e. even if you end up building a data warehouse, building a set of nVision reports helps you identify the data you want, how to aggregate them, and what facts your users want prior to building your first ETL map).

Labels: excel, nVision, Query, Reporting

Well, it's official. We've expanded our add-in to provide lots of cool new features for excel output in PeopleSoft.

It's always interesting to see how one of your products gets used at a customer site and the value of it. When we added query features to the product, we knew they were cool (otherwise, why would you spend the time, right?). What we didn't realize how much it could change the way people use PeopleSoft.

What do you mean?

Well, we recently did some travelling to spend some time looking at how customers were using the new features of the product, and here's what we found.

• Requests for new Reports went to near zero.
• End-users were able to cut the time the spent looking for information in half.

Although we realized there would be some impact here, the numbers were surprising. However, this is merely an aspect of scale and is limited to the aspect of reporting. What really surprised us is how the feature of drilling to pages from queries changed the way end-users utilized PeopleSoft.

• End-users used queries in place of PeopleSoft search pages in the application, because the queries gave them a more efficient list of items to work.
• End-users also used our Excel drill menu in place of the PeopleSoft menus wherever possible because it allowed them to get to pages more quickly than using the PeopleSoft-delivered navigation (with the added bonus of passing the context from where they were)

And, finally the metrics: Some end-users at one customer were almost 50% more productive with these features because so much of the time spent using PeopleSoft for them was navigating to pages and finding the list of items that they needed to work. Bringing it all together and streamlining the navigation made a dramatic impact for them (and gave them the additional time to tackle other projects that had been languishing).

Want to learn more?

You can learn more about the product here. In addition, we've put together a flash demo that shows it in action.

Labels: Drilling, nVision, Products, Query, User

The question behind this posting came from several different customers in the UK earlier in the month. The use case behind this is that customers want some additional control over what comes up in excel when running a query to excel without requiring the user to perform manual steps to accomplish this.

Options

So, there are several options for accomplishing this, each with its own set of pros and cons. Here they are.

• Creating a tabular report in nVision and using the instance hook in nVision to do this.
• Scheduling a query, sending it to a known folder in the process scheduler, and then having a VBA program open each file, format it, and save it.
• Performing the formatting on the client when the excel file is opened.

Using nVision

Pretty much all you need to know is covered in this blog posting.

Although this works relatively well to solve the problem, it has the following issues.

• An nVision tabular report must be created for each query. This requires a developer to get involved (but does provide granular control over the formatting
• nVision runs only on NT, which means that the reports must be scheduled on an NT box dedicated to it.
• The instancehooks must also be added to each report (versus having a global one for all reports

Scheduling the Query and using an App Engine Program

This approach has quite a few moving parts, and will not work if you want to run the queries to window. Here is my recommendation for doing this:

1. Create a VBA program that looks into a directory for an excel file, opens it, performs the processing, and closes it.
2. Create a process definition for it in process scheduler, so that it can be invoked as part of a job (clone the settings for the scheduled query app engine program, so that it can be kicked off from the same page)
3. Create a job with both process definitions in it. This job can be scheduled from the schedule query run control page.

Add the macros to the client instead of the server.

This approach, I believe, is the easiest to put in place and has the most stability. The key to it working is that whenever excel opens up a spreadsheet, it will fire the auto-open macro in any add-ins that are part of the excel environment. Therefore, by creating an excel add-in with an auto-open macro in it, you can cause the macro to run when the query results are opened.

This sounds easy in practice. However, to be successfull, you need to test to see if the excel file came from query (where you do want to process it), or if it didn't (which means you don't want to touch it).

This is where the tricky part comes in. You see, because drilling was always a part of nVision, we stuffed additional metadata into the resulting file that allows us to do all sorts of cool things with it. However, with query, we never thought to do that (and one concern we had was about keeping the file as small as possible for sizing reasons). Now that I'm no longer running the group, I'm thinking that it would have been really nice to have put that feature in (if I had a time machine, I could probably go back and fix that... Does anybody have one I can borrow?)

Okay. So now we all have to workaround an oversight I had when I owned query. What's the best solution? Well, unfortunately, scheduled query and the online query (which is a servlet on the web server) have different formats of files. Here, let me illustrate.

Here's what it looks like when runnning query online (using the servlet). See if you can find the query name somewhere in the picture.

Now, here's what it looks like when running a query through scheduled query. Again, see if you can find the query name somewhere in the picture.

As you can see, the query name is not in the query results, but is in the title bar of the browser (if hosting in excel). This is a property that is not easily accessible in VBA (although we spent a lot of time and effort figuring that out for our product... you'll have to buy it to get that code from us, though ;-).

Our recommendation to you is to have a standard set of text that you include in your query result, such as a prefix in the query description (because the query description is always put in call A1 regardless of whether its run through scheduled query or online). If you want to put somebody else on the hook for making sure it works right all the time (and also get a lot of other cool features that would take a lot of work, such as drilling, subtotalling, and number formatting), we think our add-in would be a great fit.

Plug for Grey Sparling Excel Add-in

Since we wrote this post (and due to requests from our customers), we ended up building this into our nVision add-in and changing the name of the product to be the "Grey Sparling Excel Add-in". The post processing we do is to apply formatting, such as number formatting, sizing of columns, freezing the coumn headings, applying auto-filters, and applying subtotals. This product also allows users to drill from their queries to pages or other pages. Here's the product page for it. Also, here's a flash demo of the product's features.

Labels: excel, Query

Although I put together a previous posting on Drilling and nVision, I only included a single nVision drill layout to illustrate this. I already put together comprehensive packages to show:

• Reporting in HCM (Orig Posting, Demo, and Code)
• Reporing in Student Admin (Orig Posting, Demo, and Code)

Code for Financials Drilling to Pages and Queries (among other things).

I decided to put together a package for Reporting in Financials (Orig Posting and Code). As with the other packages, the code includes a working example with our nVision drilling product.

Labels: Drilling, nVision, Query

In preparation for the Alliance Conference this week, I had taken some information kindly shared by our latest customer, Catholic University of America, and put together a set of queries and nVision reports against the Student Admin system.

The approach was similar to what was shown in the following blog entry.

Demo
Here is where you can watch it in Action.

nVision Content
On the nVision side, it shows a Statement on Student Financials, breaking out the different cost and revenue items into categories across different terms. It also has drills into classes, institutions and grades.

Query Content
On the PS/Query side, it contains a prompted query that shows the detailed transactions in student records.

Drilling Content
It also includes a sample query and sample nVision report with hyperlinks in them to drill to pages (they will need to be modified to have your own server information in the URL to make work in your environment.

Streamlined Navigation
It also shows how one of our products, the nVision Drilling Add-in will streamline the navigation from reports into other content without having to modify the reports. The package below includes a trial version of it that expires at the end of June 2007 for those who would like to try it out.

Download of Objects
Here is where you can download the queries, trees, and report definitions.

Labels: nVision, Query, Student

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

This is a common question from our loyal blog readers, which finally deserves its own posting.

Query with Prompts... What's the issue?
Good question. The issue is that in PeopleTools 7.5, customers could use queries with runtime parameters in them in nVision reports. Because nVision ran on the client (and not the server), the client code would have the ability to display a dialog box prompting for the values the query needed to run. When we moved to the web, this dialog could not be displayed at runtime, and therefore, there was no way to accomplish this.

Why would somebody need to do use a query with prompts?
Another good question. It gives you the ability to dynamically change the filtering of the report to be run. You see, in nVision unless you modify the report, modify the query, or apply the scope you don't have much flexibility in changing the results.

A nice workaround to the fact that timespans are only supported in ledger-based reports would be to allow the user to specify a date range in query prompts that cause rows to be returned within that date range. Basically, any time you would want to pass parameters into the report specified when you run it or pick at runtime the set of data to use in the report could be solved with this feature.

Sovling the problem...
The solution to the problem has two major parts to it:

1. Figuring out how to pass parameters when running an nVision report. Unfortunatly, nVision only knows how to handle the parameters it knows about in the report request (as of date, requesting business unit, report title, etc). If you can't even pass in a parameter, you won't be able to do anything with it.
2. Figuring out how to use the parameter inside the nVision report. Depending on what you want to accomplish, there are a couple of approaches you can take.

Passing in Parameters

Probably the easiest way to pass parameters is to create your own table intended to capture those parameters and use it in conjunction with a query in the report. The query could be used in one of the following ways:

1. It could join the parameters to the data table and filter the date.
2. It could be put into its own tabular layout in the report and control excel logic (i.e. be used in an excel function, etc.)

I've seen examples of both.

Okay, so now that we've agreed that we can create a table, let's go the next step and design the table and page intended to be used. So, the next question is: how would nVision know which set of paramters to use, especially since there can be multiple people running reports at the same time. The easiest answer I was able to come up with is to leverage the row level security hook in PeopleTools.

How would Row Level security cause nVision to use paramters????

It takes a little thinking out of the box, but if you understand that row level security automatically filters on oprid when it is a key in a table, you can use that to pick the right row with the right parameters. In other words, if your parameter table is keyed by OPRID and contains columns for the parameters you want to pass, then nVision will automatically filter on OPRID for the user running the report. This means the user can go to the parameters page, update the values, save it, and run their nVision report.

One other thing to note is that you have the ability to use PeopleCode in the page to help pass parameters. For example, you could prompt the user on a timespan, but actually save the from date and the to date resolved from the timespan. This gives you a lot of options that you may not already be considering.

Pretty cool, right?

Using the Parameters

So, now that you know how to give your users the option of setting parameter values for running a report, the next step is using them. As briefly mentioned above, you can do the following:

1. Join the parameter table to the data table and use the parameter values to filter the results in the data table. This is probably the most common use of it. This means using common SQL techniques.
2. Putting the parameters into Excel for use. Although you can include those parameters when joining to the data table for use, you can also embed a tabular worksheet in your nVision report that returns the parameters. Once in Excel, you can use those parameters for the following:
   
   
   
   • To control formatting in the report
   
   
   • To pass parameters into macro code in the report (see instancehooks).
   
   
   • To be used in calculations or functions in the report (such as passing a rate to use, or changing the results of an =if() function)

A couple of other options I looked at for solving this is to run a report to get the parameters from the table and then run it again to use them. This is problematic, because it adds complexity in scripting the sequence and modifying an instance to be used as a report. However, there's another simple way (but much more restrictive way) of accomplishing this....

Using a Scope
A scope is a dynamic filter that can be applied to a report. One option is to create a scope definition that has your filtering rules in it and pick it in your report request. You can use the scopefield variables to embed attributes of the scope value used into the report.

Although this is cool, it has several limitations:

1. It doesn't work on date fields.
2. It is limited to selecting values or tree nodes (no ranges, not exists, etc).
3. Scopes can be cumbersome when there are a lot of them (the key is 8 characters, and there is no security on them... this means that it's hard to find the one you want to use).

Other options:

One final note is that we're in the process of building this feature as part of a larger nVision bolt-on. You can think of the bolt-on as a product that solves all the limitations we've identified in nVision. One thing our solutions will provide is a means for doing this without requiring you to do a lot of work. Our nVision drilling enhancements is an example of another product where we took a something we showed you how to build yourself in the blog and developed a more powerful, simpler, more intuitive, and lower-maintenance means of solving it.

Labels: nVision, Query

One of the things that is often misunderstood about PeopleSoft applications is how row level security works.

Part of the reason for this is that a lot of the application groups within PeopleSoft provided their own row level security setup - HR with the security based on the department tree being a great example. The Financials group went beyond just one type of row level security "out of the box" - I forget how many choices they offered - there were several different common chart of account fields (business unit, department, account, etc.). With the application teams providing row level security in the vanilla install, many customers didn't realize that it is possible to handle row level security differently.

One thing that I always found helpful in customer meetings when this topic came up was to go through exactly how the PeopleTools component processor used search records to handle security. Once you understand that, then you can best decide if changing the row level security that the application groups provide makes sense from a cost/benefit perspective. And there definitely is a cost in the current PeopleTools - fixing row level security so that it was brain dead simple for customers to pick and choose how they wanted to implement it was one of the big ticket items planned for PeopleTools 9.

First, a quick side journey into the component processor (catch me at a conference or user group meeting sometime and I'll tell you the inside story on where the "component" name came from - it'll make you laugh). The component processor is truly the guts of PeopleTools - it is what handles the business logic, database commits, etc. for just about every page that you see in a PeopleSoft application. PeopleSoft components are parent/child hierarchies of database records that automatically get pulled together as one unit of work from the application developer's perspective.

There is one row of data at what is called Level 0. There can be up to 3 levels beyond that. Each level is required to have the same unique keys as the level above it, plus at least one additional unique key field to identify what makes the rows in that level unique.

For example, if a customer master record is at level 0 and has CUSTOMER_ID as it's unique key, you might have customer contacts at level 1 keyed by CUSTOMER_ID and CONTACT_ID. Customer contact phone numbers might be at level 2, keyed by CUSTOMER_ID, CONTACT_ID and PHONE_ID.

The data at each level comes from the key values above it. If the CUSTOMER_ID at level 0 is 1234, then the component processor would automatically use 1234 for CUSTOMER_ID for selecting data into level 1, and so on, down to level 3 (there are ways to override this, but that's how the default behaviour works).

The important thing to keep in mind here from the perspective of row level security is that populating the key fields in the level 0 record drives everything else. This is what the seach dialogs are responsible for.

When you first enter a component via the browser you get a page asking you for whatever the key fields are. In this case it would just be CUSTOMER_ID, but if the level 0 record has multiple key fields, then you'd get prompted for those as well. If you don't know the values, you click Search and get presented with a list of valid values.

But where does that list come from? Each component has what is known as a search record. The component processor uses the search record to supply the values for the key fields of the level 0 record, which then flow down to the child levels.

Any rows of data that the search record returns to the user gives the user access to that data.

So if you wanted to limit someone to only look at active customers, then you could create a view that had the same key fields as the customer master record, but had a WHERE clause like " WHERE CUSTOMER_STATUS='A' ". The view would only return active customers, which means that no one could get into that component to look at an inactive customer.

The component processor recognizes a couple of "special" fields on search records - OPRID and OPRCLASS. If the field OPRID exists on the search record, it automatically gets filled in with the current logged in user's ID. If the field OPRCLASS exists on the search record, it gets filled in with the Row Level Security Class (Permission List) that is specified on the user's security profile.

And that's essentially it for how the component processor handles things for row level security. There are a couple of PeopleCode events that fire when a search dialog is initialized (SearchInit) and when the user clicks "Search" (SearchSave), but those are not really intended for row level security. I'll write more about that in the future.

So how does the department tree security in HR work then? The HR group created a setup page that lets you pick a security class (permission list) and a tree node from the department security tree. For each one of these combos that you select, you decide whether access is granted or denied. All of this data is stored in a table. The HR team then created a view that links this table, the tree manager tables, and the employee department data together. The component processor automatically plugs in the row level security class for the user when selecting from the view, which limits what the user can see.

Financials works in a similar fashion, although the performance of the trees in the view didn't work well enough for them so they ended up with flatter security structures. The HR team actually delivered a couple of de-normalizing utilities for larger customers to deal with this issue as well.

So, how do you change that delivered security?

You could clone the delivered security views (and the tables/pages that they use to maintain the data). I helped a customer once do this where they wanted to use the position tree instead of the department tree.

You can also create your own security structures that do not mimic the delivered ones. All that matters is that you have a table or view that will return the key values that a user has access to. All that matters is that the search record has the same key fields as the level 0 record in the component that you're securing and that you either have the OPRID or OPRCLASS there to provide security.

There are no restrictions about what data the search record looks at or how that data gets maintained. For example, you might have some batch job that updates the table based on security defined in some totally different system. Or maybe someone has to request access to look at some data and security administrator will update the table that the search record looks at. I know of one customer that wanted to have their call center employees only be able to look at customer data when a call came in. So right before the server would send the screen pop for the customer data down to the agent's desktop, the security tables would be updated for that user to have access. As soon as the call was over, then access was taken away.

So the development of a new search record/view is one cost. This includes not just the technical development, but the auditing to make sure that it works properly as well.

Another cost is that the search record is attached to the component definition. So if you update a whole bunch of components to point to different search records, then you have to keep track of that at upgrade time. This is less costly than doing things like changing delivered code since you just need to add one extra step in your upgrade - update any changed components again. Not really a lot of thinking/analysis involved. But, a lot of customers like to run as close to vanilla as they can, so it is worth keeping in mind.

Of course, you don't want to be so afraid of a small cost at upgrade time if your business requirements need different data level security. I talked with a customer once that had turned off row level security in their Purchasing implementation because the delivered methods didn't work for them. Their auditors were complaining (and this was even before all of the current SarbOx push) and they wanted to know when Purchasing would support what they wanted. Changing the row level security is not so hard/costly that you'd want to run without security.

Also, it's worth mentioning that there is a way to override the delivered row level security without touching the delivered components. Take a look at this posting I wrote on the PeopleTools forum in ITToolbox for the details on that.

Update:
Since this posting was made, we at Grey Sparling Solutions solved row level security for reporting (which as several additional issues with respect to routing results in report manager). Information about this product, including a flash demo of the solution is available here. For customers who are interested, we can also apply this solution to secure online access.

Labels: Query, Security

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

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

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

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

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

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

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

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

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

Labels: Performance, Query

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

Here is my response.

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

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

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

Labels: Database, Query, Security

Yesterday, I created a posting that discussed this for PS/nVision. Today, we'll talk about PS/Query.

Tricks... Tricks... What tricks?
Well, actually, the sky's the limit. Many of the most common tricks are the following:

1. Creating a wizard to simplify the user interface.
2. Extending the delivered security.
3. Change the behavior of the query UI.
4. Adding new restrictions to the way that queries are built.
5. Using queries to select data and use it in new ways (replace inquiry pages, use in batch programs, where users can define SQL).

Wow! How is this possible?

Well, Mr. Plant, thanks for asking. It all comes down to how PS/Query is architected.

In its initial incarnation, PS/Query was a C program. When Windows went from 16 to 32 bit, I was responsible for taking the old query rewrite the user interface (also using standard MFC controls). What came out of that effort was the windows-based PS/Query that you saw in release 7 and still see today if your use PSQED.EXE. That version had a lot of the things you would expect in a query design tool: a pane where the objects to build the query from exists, a pane that was the workspace for designing the query, and drag and drop and popup menus for building the query.

When we went from the windows client to the Internet Architecture, my initial plan was to take a similar approach as in the past. Develop the user interface in C/C++. Although some people were telling me to develop the user interface as a standard application page, I had concerns that PIA was not up to challenge of a query tool. PIA did not have (and still doesn't have) drag and drop or popup menus. However, I also didn't have enough people to rebuild the query design environment in C/C++.

Therefore, when release 8 came out, there was no query designer in the browser. The next release after that consumed me (and my team) with EPM-related requirements. Thus, the rewrite of PS/Query was handed to somebody else.

One of the first decisions the team made was to build the user interface in PeopleSoft application designer. They would build APIs around all the Query function calls and expose them in PeopleCode. This means that the complex rules for applying security, finding records, joining tables, etc. are all encapsulated in these APIs. This is exactly the promise that a service oriented architecture provides.

However, this team didn't have enough time to complete the tasks in a single release, either. They feverishly built APIs and then UI to show it as quickly as possible, and PS/Query in PeopleTools 8.13 was the result (which I'm still apologizing for). After that release, I took PS/Query back and had developers flush out the APIs and the UI over subsequent releases, so that eventually it was functionally equivalent to the windows version.

So, at this point, PeopleSoft customers have something that is both more powerful (and less powerful) than the previous version. It's more powerful, because you can harness the Query APIs to do anything you want to (except to break the security rules invoked in PS/Query). It's less powerful, because it does not have drag and drop, popup menus, or a multi-pane design UI.

So, how do I leverage this power?

Another excellent question! There are three primary ways you can leverage this:

1. By modifying the PS/Query UI code.
2. By building batch programs to modify queries.
3. By embedding queries into your application.
4. By building your own program to generate queries and/or run them

Again, keep in mind that the PS/Query UI is merely a set of PIA pages and PeopleCode, just like any other page (although a little more complex). Just as you can customize the vendor page, you can customize PS/Query. You can either modify PS/Query directly, or clone it. You can also create brand new programs, such as a wizard (this is what was done by the HRMS development group with their Query Builder, which simplifies query creation).

Modifying PS/Query UI code

A few things that I've seen customers do in this area is the following:

• Modifying the search dialogs to extend the delivered security, or to make the list of queries more managable to business users.
• Extend the PS/Query UI to track additional data.
• Remove options that you don't want people to use.
• Streamline the query building process based on standards used by a company (by automating some of the tasks and restricting what can be chosen).
• Automatically extend queries with links to drill to other places.

Building batch programs in app engine

The most common use of this is to make a certain change to a mass set of queries. For example, let's say that you decide to create a table that denormalizes two tables that have a parent/child relationship. You're going to want to point existing queries to this new table. You can write an app engine program to accomplish this, using the query API. There are lots of other use cases for this, even on the reading side, where you write a program that merely runs an existing query (scheduled query is a good example of this), and then take the results and work with it. Because the query encapsulates the complexity of the SQL and also provides a layer of abstraction between the database tables and the progrem, you get a lot of benefits by using the query instead of writing the SQL by hand.

Embedding queries into your application.

The most simple means of accomplishing this is to embed the URL to run a query into your applciation. This was done by the PeopleTools security team to eliminate the need to build inquiry pages to allow users to inquire on what users/roles/permission lists/pages are related to each other. They built a query for each inquiry, and then built a page that had a hyperlink for each query.

Other examples are as follows:

• The batch program example in the previous section.
• Using a query to select data into a page to be used elsewhere. A good example is to use a query to select data into a rowset that is used to generate a chart.
• Allowing a user to pick options in a page that automatically builds a query to be used elsewhere. This is similar to the next section, "wizard", but can be applied in different ways. One example is to get around the limitation in PS/Query that you cannot have a runtime prompt that asks for a tree node. One customer created a crystal report and in the run control page, prompted for the tree node. Upon saving the values, the query was modified to select against the new tree node and the crystal report used that modified query.

Building a wizard

This may seem like a pretty big thing to do. In many circumstances, however, you will find that it's actually very easy. This is because although it's difficult to build a generic wizard that will work in all circumstances, it's actually easy to build a wizard to meet a specific goal. In other words, if you wanted to built a wizard that created a query that allowed a call center analyst to search for cases different ways, that wizard should be relatively easy: the tables are pretty constant, and you also know how you organize your products, employees, etc. You can embed these standards into the wizard easily and make it simpler to build these queries.

Conclusion

Future postings will have more detail on some of the solutions listed above. I will also take any questions on specific approaches to problems you're trying to solve (but beware, those approaches may show up in future postings to this blog). Feel free to email me at mailto:larry.grey@greysparling.com.

Labels: Query