One of the biggest challenges of end-users is to figure out where the data they want to query against resides. This is yet another tip from our “Advanced Query Tips and Techniques” webinar. By combining the PeopleCode Query Classes with knowledge of the PeopleTools tables underlying the component definition, you can provide a page that allows queries to be easily created.
Getting the Records to put in the Query
Probably the most important part of this is to figure out what records are in a component (and then subsequently weeding out the extraneous records from the list). This information is stored in the PSPNLFIELD and PSPNLGROUP records (the PSPNLGROUP record tells you the list of pages you need to look at, and the PSPNLFIELD record tells you what records and fields are on those pages.
Here is sample peoplecode that will log the list of records from a component.
Now, let’s get to the good stuff
Instead of extending our code to get the records to include in the query, we will start with some code to create a simple query definition, and then extend it accordingly. Here is the code to create a “Hello World” query against the GL_ACCOUNT_TBL record.
Add multiple fields to Query
The next step is to extend our code to add multiple fields to the query definition. For the purposes of this step, we are hard-coding the record alias to be “A”, and passing in the field name for both the query fielname and query field heading.
Here are the modifications to accomplish this.
Determine what fields to add the query
The next step is to add the logic to determine which fields we should add to the query from the current record. As the goal is to identify the records for a starting point, we are taking the approach of adding the key fields of the current record to the query.
This information is stored in the PSRECFIELD table, in the USEEDIT field. This field is a bitmap integer where each base-2 position is a switch for a different attribute.
Here are the modifications to accomplish this.
Dynamically add records to Query
Now that we’ve got a single record and its key fields being added to our query, the next step is to add more than one record by selecting against the PSPNLFIELD and PSPNLGROUP records.
In addition to putting in the appropriate selection and looping logic, it is important to keep track of the alias by which the record will be referred (this ensures that logic for adding the selected fields puts the fields in the proper place). To accomplish this, we are using the Char peopletools function and adding 64 to the index value (65 is the ascii value of “A”).
Here are the modifications to accomplish this.
Expanding to handle more records
Because most components contain a lot of records and fields that do not match the key structure of the component, we limited our logic in the prior step to only bring in the GL_ACCOUNT_TBL. At this point, we will expand it to determine what keys are required from the search record, and then only include records that match at least one field from the search record.
To accomplish this, we will be looking at the PSPNLGRPDEFN record, in the SEARCHRECNAME field. The code creates an array of fields, and then a separate function will compare this array to an array that contains the list of fields of the current record to be added to the query.
Here are the modifications to accomplish this.
Expand to support more complex components
The last step is to address some complexities that come from more complex component definitions. These issues include the following:
- Components with search records that don’t have any keys (e.g. INSTALLATION table)
- Related Displays
The related display information is also stored in the FIELDUSE field on the PSPNLFIELD record, but in a different position in the bitmap. For the search records that don’t have any keys, we took the shortcut of taking the first record in the component and using its keys to drive what gets included in the query.
Here are the modifications to accomplish this.
Last Step – Create a Page
The last step is to create a page to allow users to create the queries by picking a component name.
You can go to the project home page to pull down the whole project for your own use. When doing this, you will want to open up the component definition in application designer and use the wizard to add it to the portal registry and add it to one of your permission lists for testing.
The code discussed here does have a number of places where it should be enhanced as part of deploying it widely. Those can be viewed here.
Labels: PeopleCode, 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:
- 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.
- 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:
- 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.
- 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.
- 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:
- 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.
- 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.
- 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 Dat a Warehousing group), you may be forced to go in this direction.
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
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
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
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 fo r 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.
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
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:
- Creating a wizard to simplify the user interface.
- Extending the delivered security.
- Change the behavior of the query UI.
- Adding new restrictions to the way that queries are built.
- 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:
- By modifying the PS/Query UI code.
- By building batch programs to modify queries.
- By embedding queries into your application.
- 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.
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:email@example.com.