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

Being the "nVision" guy at PeopleSoft, I've presented it each of the last 8 user conferences. There is one part of my presentation that always brings down the house: taking a simple report and embedding an instance hook into it (usually to create a pivot table). I give that part of the presentation and people start throwing undergarments at me... it's so wierd!

Seriously, though, I'm frequently asked to describe what I did again to people who want to embed macros into nVision reports. Thus, here is my best description of how to do it (without all the pomp and circumstance of a presentation).

Why is this cool?
The reason this is so cool, is that you can work around any limitation in nVision and essentially allow it to do anything you want. For example, there is a limitation in the autmatic formatting in nVision where there can only be a single format for any amount fields (which can be a problem when you are showing dollars, headcount, and a percentage in the report). The macro to change the number formatting is extremely simple to add.

Here are some other examples of things you can do with a macro.

1. Restructure the report, so that the results are put in multiple files (or worksheets in the same workbook).
2. Highlight values that need attention.
3. Send emails based on the data.
4. Create auto filters, charts, pivot tables, and subtotals.
5. Hide data or insert new data
6. Change an instance to a layout and run it (this is a trick used for multiple field nPlosion that will be part of another posting).

First, a little background
For those who are somewhat familiar with nVision, but aren't completely familiar with how it works, a little background information.

nVision is a reporting tool that lives in Excel. We developed what is essentially an add-in (but technically, isn't an excel add-in for those excel experts out there). The add-in provides a user interface for prompting against objects in the PeopleSoft application (such as ledgers, calendars, timespans, trees, and valid value tables). This allows the report to be built much more easily. There's also a set of features for running the report and saving off a report instance that has the data put into it. Finally, there's a set of functionality to allow iterative drilling into the results to allow different slices to be seen and to allow more detail to be shown.

On the other side is all the functionality that Excel brings to the table. Because nVision uses Excel, in addition to generating output in a format that all accountants know and love, we didn't have to develop the following features (that a tool like Crystal Reports did have to develop).

1. Number formatting
2. Font formatting and background colors
3. Formula syntax
4. Extended widgets, such as charts, OLAP controls (pivot tables), and auto filters.
5. A programming language for more complex rules

Instead, nVision has hooks into excel to leverage all of these features that are inherent to it. This posting is inteded to talk about the programming language hooks, called InstanceHooks (which is a hook to call an Excel VBA program).

How to do it...

Okay, now that the background information is done, let's talk a little bit about how to embed macros in a report.

There are really two things that you really have to pay attention to when doing this:

1. Managing the difference between an nVision layout and a report instance.
2. Utilizing the macro recorder in Excel to write your macro for you.

nVision layout

So, let's start with a discussion as to how to use layouts and instances in this process. When you develop an nVision report, you are creating a layout (which in other tools is called a template). The layout contains all the rules for accessing data, all the formatting rules, and all the excel functions. It also contains all the macros. This is because in the process of running a report, the layout is first saved as an instance, and then nVision populates the data into it. Then, the InstanceHook gets called to run any macros you want to have automatically run (you can also embed macros that users can invoke in the standard excel way through a shortcut key, or adding a control or menu item linked to the macro... no instance hook needed when the running isn't done automatically for the user.).

Therefore, in order for something to make it into the final report, it must be present in the layout.

Building the macro

Now that we've covered that, let's move on to the next piece: building the macro. Because most embedded macros work with the data that is extracted (such as an instance hook or auto filter), it is difficult, if not impossible to build the macro directly in the layout. You really need the data there. That is why I recommend that you run the report first and record the macro against a report instance.

So, up until this point, you've created your layout to get the data you want, and have run it to see what data it gets. This is where the macro recorder comes in. The excel macro recorder is pretty good at creating the code behind actions that you perform. So, if you are creating a macro to build a pivot table, I definitely recommend using it. You turn on the macro recorder, select the data range you want to build it on (this is an important step, because in nVision the number of rows or columns that contain data can be dynamic... this means that you need to make sure that your macro will select the data set regardless of the number of rows that come back). When you've finished the last step in creating the pivot table, you stop the macro recorder.

At this point, you should open the Visual Basic editor to review the code that was generated. Now, you can tweak it if it had hard-coded logic in it (such as selecting a range). One tip for dynamically selecting ranges is to use the Shift+End, then Down sequence. This will create the following code.

Range(Selection, Selection.End(xlDown)).Select

Using Shift+End, then right will allow you to select the columns in a range, and will create the following code.

Range(Selection, Selection.End(xlToRight)).Select

Now, this will only work if all rows and columns are contiguous.

Here is the code that would be generated for creating a pivot table from data.

Sub CreatePivotTable()

'

' Macro3 Macro

' Macro recorded 7/28/2005 by Larry Grey

'

'

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _


"Sheet1!R2C1:R24C4").CreatePivotTable TableDestination:="", TableName:= _

"PivotTable2", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

ActiveSheet.Cells(3, 1).Select

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Region")


.Orientation = xlRowField

.Position = 1

End With

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Salesperson")


.Orientation = xlRowField

.Position = 2

End With

ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _


"PivotTable2").PivotFields("Count"), "Sum of Count", xlSum

ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _


"PivotTable2").PivotFields("Amount"), "Sum of Amount", xlSum

Range("C3").Select

With ActiveSheet.PivotTables("PivotTable2").DataPivotField


.Orientation = xlColumnField

.Position = 1

End With

End Sub

You will now need to modify the first "Selection" reference to be a cell (this was cell B4 on my report). The second "Selection" reference can remain, because the first line of code selects all rows. I also suggest making the subroutine a "Public" subroutine. This will make it easier when you have to create the instance hook (otherwise you ahve to qualify the macro by the module it was put in, which is "Module1" in this circumstance.

Moving the code to the layout and testing

The next step is to take your macro, which is in the report instance (i.e. the one that is the output of running an nVision report), and move it to the layout, so that it will be part of the report to be run. At this point, I suggest you run it and invoke the macro manually to make sure it works correctly.

If there are any problems, you can tweak the code directly in the VB editor in the new instance that you ran or record the macro all over again. Don't forget to move any changes back to the layout when you're done.

Turning on the InstanceHook

Okay, now you have created your macro and are sure that it does what you want. The next step is to automate the running of this macro after nVision has returned the data. This is done through a defined name NvsInstanceHook. What you do is you create this defined name in your layout, using the menu items Insert-->Name-->Define...

You then type NvsInstanceHook as the name, and then type in the name of your macro. You'll need to qualify it by the module if you don't make it public. Otherwise, you can merely type in the name. If the macro doesn't get invoked when you test it, you may need to qualify it in quotes (and make sure that it's a public routine).

Good luck and happy hunting!

Labels: nVision

It's surprising to me how few people know how to effectively tune nVision. Even from within PeopleSoft, there didn't seem to be many people who could do it. One side benefit of knowing the "secret handshake" was that I got a free trip to Paris to help Carrefour.

Over the years, we added new features to the product to make it easier to tune nVision, and believe it or not, it's easier to tune nVision now than it ever has been in the past. Hopefully this weblog entry will help you understand how to approach it.

Why is it so challenging?
Well, actually, it's challenging to tune almost any product that does what nVision does. Because data is stored at a very granular level, tools like nVision need to aggregate the data to get results. In addition, the flexibility for using trees in nVision can make it more challenging to tune than a star schema in a data warehouse (but the trade-off with a warehouse is that you are more limited in what you can do with it, believe it or not).

In many ways, I believe that the biggest challenge is more cultural than anything else. Because nVision makes it extremely easy to create "analytical" reports and view data in different ways, many people don't expect that they have to manage it as closely as they have to manage a data warehouse (although there are may data warehousing techniques not used by nVision that would dramatically improve performance).

How to approach tuning
The performance of nVision is driven by two different aspects of how data is stored and how it accesses it. These are:

1. How easy it is to find the rows of data it needs to report on.
2. How many rows have to be aggregated to get a number on a report.

Let's talk a little bit about each of these.

Finding rows

The first topic is the first place you should look when analyzing performance.

nVision accesses data very much in the same manner that a ROLAP tool does. There is a centralized fact table (often the ledger), with many different "dimensions" (or chartfields) that are used to group and filter the results. There are two key differences between the ledger tables in PeopleSoft and a well-designed star schema. The first is that in a star shema, the dimension tables are always joined to the fact table to minimize the impact of indexing the fact table. In nVision, dimension tables are only joined when needed for filtering and grouping the data, which means that the focus needs to be on indexing the fact (or ledger) table instead of the dimension tables.

Another difference is that nVision does not use a star or snowflake schema in the dimensions. It uses a proprietary structure called a tree, which provides much more flexibility in how you model hierarchies than a star schema would support. It also allows many different hierarchies to be used interchangably with the fact table (which also is a big bonus). However, this also means that the rules for flattening the tree and generating SQL needs to be managed closely.

Therefore every organization needs to look at two things when managing the performance of nVision: indexing (especially on the fact table), and the tree performance options (which drive how the SQL is generated by nVision).

The most successful organizations are ones who take a methodical approach to both of these (this is how one organization is able to generate around 200,000 nVision instances each month on a 4-way NT box running nVision).

When looking at indexing, it is important to look at which fields are constrained and grouped on the most (and analyzing the cardinality or number of values of each). Quite often, putting the accounting period as the first field in the index is the best place to start, because there are at least 12 values, and they are always used to constrain data. Account is often the second field, because you almost always constrain against the account field (otherwise you would get meaningless financial statements).

When looking at tree performance options, the best approach is to go through all the permutations of options for each tree used (focus on one tree at a time), to determine which option will work best for a given report and data set. This is the approach that the high-volume customer used. They also prioritized their reports by the ones that had the worst performance and focused on them one at a time. They also had a defined performance goal for their reporting window, so that they could claim success when meeting that goal.

Aggregating Rows

When you're confident that you've done a good job of indexing and setting performance options, the next place to look is the number of detail rows you're reporting on. Because nVision is all about summing up details to get summary values (that's what a financial statement is), you will get into performance issues merely because each number on a consolidated financial statement may represent tens of thousands of detail rows in the database (and all of them need to be aggregated to get that number).

A good way to determine the affect of this is to do a a SQL trace of your nVision report and do the following. Any SQL statements that perform a sum of a numeric field (generally on the ledger table), edit the traced SQL to change it to count instead. Run the SQL with the count in it, and each number in the results is a count of the number of rows that needed to be selected to get a number in the report. Now, depending on your performance options you may not have the other tables populated so it's a good bet to make sure that you have the report open in the nVision designer when you run this trace.

If each number on a report requires more than 100 rows, it's a good bet that a summary ledger would be required to improve performance. The trade-off you make by using a summary ledger is that you are summarizing the data in batch versus online. Therefore, if you can have many reports use the same summary ledger, you are making the most of this trade-off.

For populating the summary ledger, you can either use the GL program to do this or use a little trick that I've had many customers use --> a materialized view. In both circumstances, you'll want to populate the summary ledger definition, so the reporting tools know how to access them. However, for the materialized view, you can let the database do the aggregation for you instead of running a program to do it (much better from a performance perspective). The key, however is that a materialized view will only support aggregating by dropping a chartfield in the summary table (wherease the batch program will allow you to summarize to nodes in a tree).

More on SQL tracing in nVision

It's impportant to note that in PeopleTools 8.44, we improved the tracing so that you can easily turn it on and get the SQL when running nVision on the process scheduler server (it's an option on the process scheduler configuration). Because the trace is captured and managed with the process, you don't have to go through a long log of SQL to determine which statements are associated with which reports. Additionally, it eliminates the need to try to trace it in the design environment (which has just enough differences in how it works to make it an ineffective way to capture it and manage performance).

Not sure how understandable this is. I'm more than happy to clarify any area in a future posting that is confusing.

Labels: nVision, Performance, Tree_Manager

This topic was brought up by Bob Stambaugh in a very thought provoking presentation to the Northern California PeopleSoft RUG called "The new 21st century business environment".

Bob talked discussed what decision networks are. They are the informal way you get things done in a company. In other words, who does a person go to when they need help. Bob has done several projects where he had employees fill out questionairres to determine who were the control points of information. Who did people go to when they needed to understand a certain policy? Who did they go to when they needed to get information about an aspect of their business? ect. He posed that this network does a much better job of describing who has power in an organization than an org chart. In fact, he postulated that one could determine who was dead wood in a reduction in force by determining who wasn't part of the network (and one could also determine who was supporting the top producers by looking at it as well).

I found this extremely interesting (being the former product manager of tree manager for 9 years). Obviously, this is a much different way ot looking at things than I was used to (and also much different than the way ERP vendors build their products).

However, I also realize that the informal network may determine how things get done, but they do not represent who is accountable for things. In a day where investors and the government want to make individuals responsible for the controls and performance of a company, there is still a very important role for the org chart (as outlined in the following Blog entry). In fact, accountability is a very important area that is also missing from ERP vendors (which is an opportunity that Doctor Richard Connelly and I have discussed at length for many hours).

So, in the end, both structures need to play an increasingly important role in business applications. They need to better reflect not just how to capture transactions and secure access to the application, but they need to help organizaitons do the following:

• Assign, delegate, and manage responsibility of roles and actions.
• Help people get things done by helping guide users to the best individual in the network to get things done (and imagine using the network as a means managing the careers of individuals by identifying opportunities for training and networking).

Definitely food for thought.

Labels: Tree_Manager

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:

• Advanced HostMonitor
• I³(now from Symantec software)
• Quest

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

Strategic Structure Alignment... pretty important sounding term (that probably doesn't tell you that much). It's something that probably every customer struggles with, but doesn't realize what it is.

Strategic Structure Alignment is all about making sure that the important organizational constructs are aligned across different applications and domains. In 90% of the circumstances, this is looking at how the HR responsibility structure maps to roles that people play in other systems. Throughout my years of experience with PeopleSoft applications, I've had many discussions with PeopleSoft customers about how the department chartfield in financials can be aligned with the HR employee structure (in fact, one of the remaining baby bells has a whole system dedicated to this, and we've put in enhancements to tree manager to help them handle distributed nature of this structure alignment).

There are other areas, however, where this can be applied:

1. Row/Role level security in reporting
2. Sarbanes Oxley compliance delegation
3. Responsibility definition in front-line applications (such as call center and sales force automation

Basically, any place where the official reporting structure is needed to be used to drive use and functionality in an application. This is different from a knowledge network, which was discussed by Bob Stambaugh at the Northern California RUG on friday.

So, how do you align strategic structures? There are several approaches:

1. Create one MASTER structure that gets replicated to other systems. This is the approach that the baby bell took. All information was maintained in the HR org tree, and then the tree was replicated to the other database for use in reporting. They, then made sure that the chartfield used by the tree had the appropriate values for the tree to link to (which could be a challenge). Another limitation is that you must put entries in the HR org tree that don't have any HR function in order to use them in financials.
2. Another option is to replicate the departments in HR to financials, but not the hierarchy itself. This allows a financial organization structure to exist that does not have to exactly match the HR one (but ensures that all departments are represented financially).
3. A third option is to synchronize the maintenance of trees between the two applications using ERI. In other words, as the user is making changes to the org tree in HR, he can drill into the appropriate tree in Financials and determine how that should be reflected there.

Because of the new requirements of Sarbanes Oxley, it is my suggestion that customers do a combination of the three approaches above. This is because the HR responsibility hierarchy will be the key to ensuring Sarbanes Oxley compliance. A good example is managing expense accounts. If you can drill down the HR responsibility hierarchy to see which people are responsibile for how much of that expense, it provides accountability and visibility into the accuracy of the financials. To accomplish this, one merely needs to provide a means for linking the HR org structure to the department chartfield in financials and drilling in nVision into a drilldown layout that uses tree nPlosion to the HR org tree.

Labels: PeopleSoft, Tree_Manager

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