[searchandfilter taxonomies="search"]

Understanding Row Level Security

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.

What is Row Level Security in PeopleSoft

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.

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

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Hierarchical Reporting in nVision

There was another set of requirements that were important to the customer in my conference call yesterday. That was hierarchical reporting in nVision. The customer’s initial question was, “Can you do it, and how?” Obviously, the answer to the first part was “yes” (otherwise, I wouldn’t be writing this posting). However, there are actually 3 different answers to the second part, depending on what you mean by it.

So, let’s frame the discussion by listing out the 3 different things one could be trying to do with hierarchical reporting.

Generate reports at different levels of detail. The example that comes up most often is generating a profit and loss for each level of an organization.
Generate a report with a hierarchy used within as the structure. The example that comes up most often here is using the account tree to generate the details and subtotals in a profit and loss statement.
Look at a report and drill using a hierarchy into a number in that report. An example of this is looking at the payroll number in a profit and loss statement and drill into the department tree to see how that is broken down (and repetitively drill to get additional levels of detail).

So, let’s look at each one to see how you would accomplish it with nVision.

Generating reports at different levels of detal. This is a standard feature of nVision, using the “scope” object. When running a report, you can tell nVision to generate instances using a scope. The scope allows for instances to be created based on values in the system or nodes on a tree. One little known feature of the scope is that you can pick multiple levels of the tree in the same scope definition, and it will process all of them. This works very well when a customer runs the nVision reports on a pre-defined schedule and wants to get a “package” of reports pre-generated at all levels of granularity.

If you want to do this in a more ad-hoc manner (in other words, allow the user to run a report for “his” part of the hierarchy), this is a little more cumbersome. One approach is to use row level security to eliminate the data, but this is done at the database level and the reporting tool actually doesn’t know that data was eliminated (so there is no way to show in the report what was included or pulled out other than looking at the detailed data).

Our PeopleSoft Solution Extender for nVision provides an ad-hoc solution that does address these needs. We’ve created a new report request page for nVision that allows the user to specify what nodes to use to filter the report without requiring the scope to be involved. This will allow the user to pick which part of the hierarchy to use for the data in the report at the point they want to run the report.

Generate a report with the hierarchy used within as the structure This is another standard feature of nVision that we added in PeopleTools 8 (called tree nPlosion). When designing the report, you can nPlode on a tree, and pick to nPlode through the tree. Because nPlosion will be inserting rows for the different levels of the tree, formatting is important, so we also added a robust style and formatting set of features that allows control over the font, color, indentation, etc. for each level of the hierarchy.

This works well for most situations. However, there are a couple of limitations in the implementation. The most striking of which is that every amount field in the results must use the same style (which is an issue in excel, where the number formatting is part of the style). This means that Dollars, Percentages, Headcount, and Square Feet all have the same number format applied (not desirable). A simple workaround is to use an instance hook to apply the appropriate formatting. However, we’ve also addressed this in the PeopleSoft Solutions Extender for nVision.

Looking at a report and drill using a hierarchy into a number in that report. Again, this is a standard feature in nVision (and meeting this requirement was considered as part of the design for the tree nPlosion functionality added in release 8). The way to accomplish this is to build a drilldown layout that has the nPlosion options set to drill to the next level of a tree.

One common mistake that people make when developing drilldown layouts is to qualify them too much (that’s why in your drilldown layout, you should pick the root node of the tree for filtering and then pick “next level” as your nPlosion option… If you’re already drilling from a lower node of the tree, it will override the criteria in the drilldown layout).

To use the layout, you merely pick the number you want to drill from, and then pick the layout to go to the next level (if it’s the first time, then you will probably be starting from the root of the tree). If you want to see the next level from your drilldown results, you drill from the number you want to see more detail from, and then re-pick the “to next level” drilldown layout. Here is an example layout for the department tree.

Another option is to create one layout that nPlodes through the whole tree. This will show in one step, all levels in the tree and the associated breakouts of supporting detail from the starting value. This is an important technique when you want to see organizational responsibility for a balance or expense item. For example, if you want to see the sales contribution from your profit and loss through your sales organization, you can develop a drilldown layout that nPlodes to all levels of the organization tree and use it to drill from the sales number on the profit and loss statement. It will then show you all levels of the organization tree, the contribution to that sales number (which is important from a Sarbanes Oxley perspective).

Labels: nVision, Tree_Manager

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Understanding Setids and Business Units

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:

It has a recording label.
It has a series of retail stores.
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:

There are probably different suppliers with different rules of operation.
There are definitely different products that are delivered.
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).

There needs to be a common chart of accounts for reporting financial information.
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:

Entities that would be dimensions in an OLAP cube. These are things like customers, vendors, employees, products, and departments.
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:

When you prompt on the journal source, the setid for sources may be mapped to “share”.
When you prompt on the account, the setid for account tables may be mapped to “share”.
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:

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).
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

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Training PS/Query to do new tricks…

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.

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

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

NorCalPRUG Meeting Notes — PS/nVision vs. e.Spreadsheet

Actuate’s Managed Spreadsheet Solution

I was interested in this presentation specifically because when I worked at PeopleSoft, I worked on PS/nVision and part of this presentation seemed to be replacing the functionality of nVision. I was hoping to understand what part of nVision was problematic enough to replace with an entire product. Hopefully, I would be able to gain insight into issues with nVision and be able to help customers with those problems so they would not need to replace their exising nVision infrastructure and knowledge-base with a completely new technology.

The presentation pointed out some of the difficulties with nVision specifically that nVision requires a lot of database resources and the reporting itself is limited to PeeopleSoft data sources. Also, bursting, running the same report with different parameters and deploying the results to multiple users, is not accomplishable with nVision.

In general the results of the Actuate e.Spreadsheet product are wonderful and they can produce wonderful reports and deploy them to many users. However, there was one question came up after the presentation that might be of concern to customers thinking of buying the product. The question of how the actual e.Spreadsheet definitions are created. The presenter indicated that the typical scenario is that Actuate will come in and create the reports and teach the users how at the same time. It sounded like the Actuate product was difficult to use in a design sense. This was bolstered by the statement that nVision was best used as an ad-hoc mechanism for creating one-off reports.

My take:

While the product demos well and the savings of database resource may well justify a switch, it really comes down to whether the additional cost of the Actuate product and its resulting consultation time are offset by the better performance and any downtime required to make the switch from nVision-based reporting to e.Spreadsheet.

Also, since we at Grey Sparling worked on nVision, we are in a unique position to help customers retain their existing nVision knowledge and reports while augmenting the environment to add the bursting capability mentioned earlier in this blog entry and to mitigate some of the performance issues that they may be having.

Labels: Events

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

NorCalPRUG Meeting Notes — Technical Roundtable Notes

The last large session of the day was the Technical Roundtable. Chris Heller and Larry Grey of Grey Sparling were asked to lead the discussion because of their extensive understanding of PeopleSoft and the underlying architecture. I filled in for Larry and mainly took notes since its been a while since I worked on the PeopleSoft products. The issues brought to the roundtable break down in to the following categories: Security, version upgrade via service pack/bundle/maintenance packs, performance issues, problems with Crystal Reports formatting during upgrades, release testing and quality.

Security

No one attending was using LDAP security but there was one person who was planning on testing it to see if using it would help solve some security issues. The first tip was to setup security at each level that requires security with the same user and password combination. This is fine for initial setup but maintenance of passwords and disabling accounts can get tricky. Don’t delete PeopleSoft user profiles. Doing so ‘changes history’ by removing the who on the data in the system and there was confusion if PeopleSoft even lets you do that. Since the security in PeopleSoft is exposed as part of the API, you can write scripts against the security in PeopleSoft to enable you to do bulk changes to security attributes. One example of a use for this is in the termination process of HR. Since large companies can have many employee terminations on the same day, it makes sense to create a script that can take the list of employees to be terminated and update the security to prevent the user access to the system. It takes the onus off of the HR employee who is probably manually doing that now. However, don’t forget to audit the changes!

Performance

Several issues of performance came up in the session. WebLogic 8.1 bug affecting performance Apparently, there is a bug that affects performance in the WebLogic application server layer that was introduced in WebLogic version 8.1 which is corrected in a later service pack (sp8 or sp9). This bug can generate enormous error logs on the application server. So large that they can consume the entire disk on the application server (thereby causing even more errors).

General User Education – “Please be patient…”

Another performance problem can be caused by users. If users become impatient with the application and begin to hit buttons multiple times (ex: the browser back button, run report, etc.), each button will generate a request for service to the application server which will try its best to service all of the requests, not just the last one.

Database server on same machine as application server?

The answer to that question is a resounding NO. This is a general issue for hosted web applications. The resource requirements for a database server are very large as are those for an application server. If both are on the same physical machine, they will starve each other of needed resources.

Be aware of demo certificates

The default installation will contain demo certificates for the application server which need to be replaced after installation. Otherwise, the application server will continually add to the error log with messages about the demo certificate. Note this may be related to the WebLogic bug mentioned above.

Use a performance monitor

Since no system no matter how well tuned and adjusted will have perfect performance, it helps to have a monitoring regiment in place to ensure that it is working well on a day-to-day basis. Some recommended applications were:

One other tip was not to use PS trace in the App Engine. Also PS-PerfMonitor is very time intensive to use. SQL Server can sometimes get deadlocks (esp. when doing batch processing at peak times). In those instances, the Enterprise Manager is the solution. It identifies the deadlocks and can free them in real-time. Crystal Reports There were a set of problems and clarifications around Crystal Reports.

Odd font formatting

There were a couple of instances of problems, neither of which found a good way to fix the problem other than to manually re-format the reports. The first occurred after an upgrade and affected the behavior of the micr fonts. In essence the micr font used on a set of checks enlarged which shifted the information on the check. At issue may have been the fact that the font size was customized to a fractional point in order for the check to print correctly. The second occurred after a PeopleSoft upgrade (7.5 -> 8.1). The formatting in the reports simply didn’t stay put. This may have been the result of a change in the Crystal Reports version shipped with PeopleTools. In both cases the solution was to reformat the affected reports manually.

Poor linespacing in export to Word

Another problem centered around the export to Word function in an older version of Crystal. When the report was exported, the resulting Word document had incorrect line spacing making the result unusable. No solution for this one.

Use two versions of PeopleTools side-by-side for better reporting

PeopleTools depends on the name of database objects to do its magic. So if you install the 8.4x PeopleTools instances on a different database than the currently running 8.1x version, you can use database linking to link the 8.4x metadata to the 8.1x data and thereby create an environment where the 8.4x PeopleTools reporting tools can access the current 8.1x data.

What is included in the Crystal Enterprise license that comes with PeopleTools 8.46 & E1 (8.94/5)?

The Crystal Enterprise version shipped with PeopleTools is a limited 5 user license. These 5 users are concurrent runtime licenses not just development licenses (as mentioned by Chris in the discussion). In order to use a more expansive license for the Crystal Enterprise product you will need to contact your Sales Representative from Oracle or Business Objects.

Upgrades

Upgrading is always an interesting topic and this session was no different. Everyone seemed to have something to ask about upgrading PeopleSoft applications and Tools. Aside from the issues involved with upgrading Crystal Reports (as mentioned above), there were questions ranging from upgrading major PeopleSoft releases (7.5->8.x) and when should you upgrade via bundles or maintenance packages.

If you have the Payroll module, think about doing maintenance upgrades when tax tables change.

Since both state and federal payroll taxes can change during the year, if you are going update the payroll tax tables anyway, it makes sense to bring in and of the upgrade bundles that you are missing. Remember that auditing the changes is always a good idea.

When is a bundle upgrade apppropriate versus a maintenance upgrade?

This one really doesn’t have an answer. The best you can do is decide if the upgrade has enough merit to out-weigh the cost of testing the upgrade. This really depends on your immediate need. If you take individual bundles, you may start losing support from Oracle by virtue of the fact that you are venturing into un-supportable configuations. There will come a time when the ChangeAssistant starts giving you so many possible updates that Oracle loses confidence in the stability of the system (at least from an Oracle support standpoint!). When this becomes an issue you will (eventually) either need to upgrade to all of the supported bundles, or live with the fact that Oracle can no longer support your system. (Don’t despair, read a little further to the next section or click here)

Oh, and when do I get support if I only use bundles?

Yuck. The big problem with bundles (its also a feature) is that you are able to select which (potentially system damaging) updates to apply. If you do this often enough and you call Oracle support you will get the dreaded question, “Are you up-todate?”. The answer is usually: “No, I’ve only been applying the bundles that apply to my situation and am afraid to do a complete maintenance pack.” In that case support will probably cut you loose saying that you are “in an unsupportable configuration…” or some other nonsense. This leaves you in the position of saying, “… well, I’m not sure that we are up-to-date, but I think so.” or trying to fend for yourself and upgrading the system to the current set of maintenance packs.

And then you can use ChangeAssistant to help

ChangeAssistant is the link between you and Oracle support. If ChangeAssistant says you’re up-to-date, then you are. The basic idea of ChangeAssistant is to create an environment whereby you can check to find updates and download them in bulk. The benefit is that it removes the tedium of doing the checks manually, but at the same time it can be detrimental to the behavior of the system. The best advice is to use ChangeAssistant to update your PeopleSoft demo database. Since the demo database is not part of the production system, you can use it as a staging point for updates and still tell Oracle that you are “up-to-date”.

Release Quality and Testing of Upgrades

One of the strongest sentiments came as the question, “Why can’t you [Oracle/PeopleSoft] test the simplest functionality that I need before you release?” That is a powerful question in all of software development and one that many engineers lose sleep over. The answer is basically, “We can’t test everything.” A better answer is that software customers should follow the edict “caveat emptor”, let the buyer (you) be aware of what you are accepting from the vendor (Oracle/PeopleSoft). All software companies with which I have been associated (PeopleSoft included!), try to make the customer’s experience with their software positive. It is true that sometimes release dates can intrude on the quality of the software, however, the whole point is to make the customer more productive. The best tip shared for testing was to ensure that you (as the customer) have quality assurance tests that you feel you can trust and be able to run them on the PeopleSoft demo database or a static database that is functionally equivalent to your own either manually (not suggested) or automated (definitly a good idea). Then by downloading the changes via ChangeAssistant to the PeopleSoft demo database you can control the acceptance testing by running your acceptance tests.

What’s up with Fusion, SQR and COBOL?

Since Fusion is a product in development, we can only use the publicly available information as source. It looks like the WebLogic and WebSphere servers will be unsupported in Fusion. As for SQR and COBOL, remember that the SQR and COBOL in PeopleSoft are stable in the sense that both of the code bases for those languages have been included in the product for several years. They are not dependent on the language modifications imposed by the current providers.

Labels: Events

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Request a Demo

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.

Necessary

Always Enabled

Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Functional

Performance

Analytics

Others

Platforms

Features

Register for an Upcoming Webinar

SAP

PeopleSoft

Appsian360

Review

Webinars

Persona

Register for an Upcoming Webinar

Customers

Video Testimonials

Featured Case Studies

Why Appsian

Management Team

Careers

Press Releases

Partners

Contact Us

Customers

Partners

Home / Blog / Understanding Row Level Security

Understanding Row Level Security

What is Row Level Security in PeopleSoft

Update:

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / Hierarchical Reporting in nVision

Hierarchical Reporting in nVision

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / Understanding Setids and Business Units

Understanding Setids and Business Units

Why, when, and how?

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

So, what happens when I use the application?

Okay, what about reporting?

Applying this to the real world

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / Training PS/Query to do new tricks…

Training PS/Query to do new tricks…

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

Wow! How is this possible?

So, how do I leverage this power?

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

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

Building batch programs in app engine

Embedding queries into your application.

Other examples are as follows:

Building a wizard

Conclusion

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / NorCalPRUG Meeting Notes — PS/nVision vs. e.Spreadsheet

NorCalPRUG Meeting Notes — PS/nVision vs. e.Spreadsheet

Actuate’s Managed Spreadsheet Solution

My take:

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / NorCalPRUG Meeting Notes — Technical Roundtable Notes

NorCalPRUG Meeting Notes — Technical Roundtable Notes

Security

Performance

General User Education – “Please be patient…”

Database server on same machine as application server?

Be aware of demo certificates

Use a performance monitor

Odd font formatting

Poor linespacing in export to Word

Use two versions of PeopleTools side-by-side for better reporting

What is included in the Crystal Enterprise license that comes with PeopleTools 8.46 & E1 (8.94/5)?

Upgrades

If you have the Payroll module, think about doing maintenance upgrades when tax tables change.

When is a bundle upgrade apppropriate versus a maintenance upgrade?

Oh, and when do I get support if I only use bundles?

And then you can use ChangeAssistant to help

Release Quality and Testing of Upgrades

What’s up with Fusion, SQR and COBOL?

Related Articles.

Register
for an Upcoming
Webinar

Register
for an Upcoming
Webinar

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