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

A Brief History of nVision and Queries

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

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

More about Query Support

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

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

Tabular Layouts

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

Labels: nVision, Query

This is another of those posts that I had intended to get completed a while ago, but ended up getting distracted.

One of our loyal blog readers is at an organization with 42 setids and they are trying to do consolidated reporting across them. This is an issue that I'm very familiar with. Many years ago (12 to be exact), I was doing consulting work at Norrell Services, a staffing company based in Atlanta. They also had a large number of business units and setids and needed to perform reporting across them.

How did you accomplish this?

This was done through a technique I call the Super-Setid. This is where you create a completely new setid that contains the union of all values across the different setids. You can then build trees and do other things against that new setid. When doing nVision reporting, you can create a Super Business Unit that is intended to map to the super-setids of your chartfields (and pull in your trees as well). Because the report request in nVision can have a business unit different than the data you're reporting against, this is possible (which also means that your business unit will be used for setid mapping only). For those who kept track of features in Financials at PeopleSoft, they had tried to accompplish this with a feature called Partial Tableset Sharing, which was targeted to Financials 7.5, but was pulled at the last minute and never released because they were never able to get it to work properly across all the different use cases customers needed.

What's involved in implementing the Super Setid?

Good question, as always. To implement this, you will want to create a surrogate table for the valid values of each field you want to use a super-set for. This table will be used as the foundation for your trees, for filtering data in your reports, and for getting additional attributes.

To create the surrogate table, there are two main options:

• Create a view against your valid value table
• Create a new table and populate it manually
• Use PeopleSoft's summary ledger and summary tree functionality

View

Let's start by looking at the view. In order for the view to work, your any chartfield or other table you're creating the superset for must have values that are either unique or consistent across the different setids. In other words, you cannot have department 1000 mean Finance in one set id and have department 1000 mean Manufacturing in another setid. This is usually the case, but in the scenario where an organization does a quick migration of an acquisition into PeopleSoft and they don't convert chartfield or other values during the migration, this may not be the case.

Let's use the DEPT_TBL as an example for creating the view. The first step is to open up the record definition for DEPT_TBL, save it under a new name, and remove the fields you don't plan to use for reporting. Then, you change it to be a view and enter the select criteria for the view (hard-coding the setid to be your super-setid). Here is an example of this select statement:

SELECT 'CONS'
, A.DEPTID
, A.EFFDT
, A.EFF_STATUS
, A.DESCR
, A.DESCRSHORT
, A.COMPANY
, A.SETID_LOCATION
, A.LOCATION
, A.TAX_LOCATION_CD
, A.MANAGER_ID
, A.MANAGER_POSN
, A.BUDGET_YR_END_DT
, A.BUDGET_LVL
, A.GL_EXPENSE
, A.SYNCID
, A.SYNCDTTM
FROM PS_DEPT_TBL A
WHERE A.SETID = (
SELECT MAX(B.SETID)
FROM PS_DEPT_TBL B
WHERE B.DEPTID = A.DEPTID)

Populating a new table

Let's move on to discuss how to populate a separate table with data. This gives you a bit more control over the process of doing this than a view would provide (such as rules for picking values that may be duplicated across setids). There are two ways of accomplishing this: the first is to write an application engine program you run periodically to move the data (and the SQL statement above could be the starting point for doing %InsertSelect). Another option is to add SavePostChg Peoplecode to the record you're doing this for.

Let's focus on DEPT_TBL as an example for this again. I've created a new record definition called DEPT_CONS_TBL, which has a subset of the fields in the DEPT table I want to use for reporting. I, then added SavePostChange peoplecode to the SETID field on the DEPT_TBL record. This means that every time data is saved into the table, my PeopleCode will be invoked to update my new table.

/* Grey Sparling Solutions - Create SuperSetid value */
/* */

rem Check to see if current value currently exists in Super Setid Record;
Local Record &SuperSetidRec;
Local Record &CurrentRec;
Local string &SuperSetidVal = "CONS";

&CurrentRec = GetRecord(Record.DEPT_TBL);

&SuperSetidRec = CreateRecord(Record.DEPT_CONS_TBL);
&SuperSetidRec.GetField(Field.SETID).Value = &SuperSetidVal;
&SuperSetidRec.GetField(Field.DEPTID).Value = DEPT_TBL.DEPTID;

If &SuperSetidRec.SelectByKeyEffDt(DEPT_TBL.EFFDT) Then


rem Update existing value;
&CurrentRec.CopyChangedFieldsTo(&SuperSetidRec);
&SuperSetidRec.Update();

Else

rem insert new value;
&SuperSetidRec = CreateRecord(Record.DEPT_CONS_TBL);
&CurrentRec.CopyFieldsTo(&SuperSetidRec);
&SuperSetidRec.GetField(Field.SETID).Value = &SuperSetidVal;
&SuperSetidRec.GetField(Field.DEPTID).Value = DEPT_TBL.DEPTID;
&SaveRtn = &SuperSetidRec.Save();

End-If;

Summary Ledger and Summary Tree

The last option we'll discuss is using summary ledgers and summary trees for accomplishing this. This option requires using a little bit of abstract thinking. Imagine that you are capturing data at a very granular level (i.e. low-level accounts), but you don't need to perform consolidated reporting against that level. In other words, you can show your values aggregated a bit when doing reporting. If this occurs, then you don't need to have a single set of valid values at the detail level that works across setids. Instead, you can get a consistent picture by ensuring that your trees have a consistent node structure across your different setids and use those nodes for reporting. You will, in essence, set up your trees to have a common hierarchy, but the sets of values linked in at the leaf level will be different for each setid. The tree will actually be doing your mapping for you.

You will then run PeopleSoft's summary ledger build process for each business unit, where you aggregate your chartfields to a specific level in these trees (thus storing the values with a consistent set of chartfield values across setids). Viola!

Putting this to use

Okay. Now you've handled the data manipulation to get a consistent set of values across your setids. Now, you need to use this in your reporting. How do you do it?

The first thing to do is to build a tree that you can use. If you're creating the super-setid with a view or table, then you will create a tree structure that uses your new table for the leafs, and build a tree with that structure using your super-setid. You will also want to use that table for valid values in reporting (for value criteria in nVision or for query). If you use the summary ledger option, you will build your report against the summary ledger and report across business units. You would also build a summmary tree as referenced in PeopleBooks.

Code

Here is a project with the code referenced above.

Labels: nVision, Reporting

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

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

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

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

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

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

Baby Step 2 - Formatting the Queries

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

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

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

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

Baby Step 3 - Linking Queries

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

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

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

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

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

Baby Step 4 - Aggregating information

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

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

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

Baby Step 5 - Aggregating and Comparing across different items

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

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

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

Summary

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

Labels: excel, nVision, Query, Reporting

For those who are familiar with our demo and posting that discusses how to drill from a report into a page (blog posting here), you may or may not notice a limitation in what was presented. The example showed drilling from a financial report to the journal entry where the number came from. Unfortunately, drilling to the journal is just not granular enough to tell you exactly where the number came from (journals can have hundreds of lines, and a number in a financial report is governed by the chartfield values that are used).

This means that what you really want to do is to drill to items that are at scroll level 1 or greater in the page. Because the standard URLs to PeopleSoft pages are driven by the search records for those pages, you need to be able to (1) pass parameters to identify what values you want to navigate to, and (2) write code to do the navigation.

Sounds interesting, so how do you do it?

Well, the first part was answered in this posting on how to add parameters to your PeopleSoft pages.

The second part can be acoomplished in multiple ways (depending on the following):

• Whether the page or scroll items are read only.
• Whether the data to be navigated to is chunked by application code or by PeopleTools.

Using the SetCursorPos PeopleCode Function

The first approach we will discuss is using the SetCursorPos function. This works by iterating through the data in the component buffer until you find the row you want to be on, and setting the focus (or cursor position) to a field on that row. Because you can't navigate to fields that are grayed out (or are read only), this only works when that occurs. Also, since you are navigating through what's already in the component buffer, if the only loads a subset of the data at a time into the component buffer, then you may be navigating through a small part of the data you want to search. Navigating to a posted journal entry in PeopleTools is a perfect example of where both of these conditions would prevent this from occurring.

Here is an example of code you would use for a page with updatable data where the component buffer contains the full data set you want to search

Local Rowset &rsJrnlLines = GetLevel0().GetRow(1).GetRowset(Scroll.JRNL_LN);

Local number &j;
For &j = 1 To &rsJrnlLines.ActiveRowCount


Local Row &rowTest = &rsJrnlLines.GetRow(&j);

If &rowTest.GetRecord(Record.JRNL_LN).GetField(Field.ACCOUNT).Value = &sAcctNum Then

&rowTest.GetRecord(Record.JRNL_LN).GetField(Field.ACCOUNT).SetCursorPos(%Page);

Break;
End-If;

End-For;


Adding a navigation element to the grid

If all the items in the scroll are read-only (or grayed out), then another option is to put a push button or other element in the grid that isn't grayed out to set focus to. It's actually as simple as that. You add the item, and then set the cursor position to it. Of course, this gets into customizing the page itself, which can be an issue at upgrade time.

Leverage selection code written into the page

This approach can be used very effectively in inquiry pages or even pages where there search logic is used written by application developers to populate the scroll. The journal line page is a great example of this. There's a link in the Financials 8.9 journal entry page that allows you to enter search criteria for your journal lines. This page actually displays fields in the JRNL_PANELS_WRK record, which is in the componenet buffer for the page. By merrely setting the values of chartifelds in this work record and calling the adjust_line_scroll function, you can use parameters to restrict the set of journal lines displayed in the page (ultimately drilling to those values).

Here is the code to do that.

Declare Function adjust_line_scroll PeopleCode FUNCLIB_GL.JOURNAL_LINE FieldFormula;

/* Code to drill to row with account number passed in as a parameter */

Local string &sAcctNum = %Request.GetParameter("ACCOUNT");
If All(&sAcctNum) Then

If JRNL_HEADER.JRNL_HDR_STATUS = "D" Or
JRNL_HEADER.JRNL_HDR_STATUS = "P" Or
JRNL_HEADER.JRNL_HDR_STATUS = "U" Then

/* Journal is read only */
JRNL_PANELS_WRK.ACCOUNT = &sAcctNum;
adjust_line_scroll();

End-if;

End-if;

One last item of note: if there is already Page Activate PeopleCode, you will probably want to put yours at the end for the navigation (this ensures that all other logic has already been executed). The JOURNAL_ENTRY2_IE page is an example of this.

Labels: Drilling, nVision, PeopleCode

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

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

What do you mean?

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

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

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

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

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

Want to learn more?

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

Labels: Drilling, nVision, Products, Query, User

This is another blog entry I started a while back, but never completed. As mentioned in yesterday's entry, there was a lot of work done in PeopleTools 8 that didn't get much visibility (because it didn't makes sense with the new paradigm). This feature fits into that category (but it allows you to do some very cool things).

What you can I do with it?

This interface allows you to control nVision in a manner similar to the design UI. For example, you can programmatically populate an nVision report request and run it from VBA without saving it or using the PeopleSoft-delivered dialogs. You can also use it to define criteria in an nVision report and even invoke nVision dialogs.

Cool! Does this mean I can use this with web nVision to automate things?

Sadly, no. You see, this feature puts a VBA interface on top of the nVision designer features. Again, this was all done back when the primary means of running nVision reports was on the client and not on a server. Because we hadn't had the paradigm shift of "no code on the client" yet, people were still putting code on desktops and our initial focus of this release was to improve the client/side functionality.

• New NVSUSER.XLM, which provided a better launching place for end-users for running and managing nVision reports.
• VBA hooks to allow better control of nVision

Unfortunately, when we eliminated code on the client; we also eliminated the foundation for these features (unless customers continue to deploy the client/server code).

Okay. I see, so why are you even covering it then?

Good question. You see, there are still a lot of situations where this level of automation is good:

• Automating parts of the development of nVision reports
• Performing actions around running reports, such as creating and updating report requests and scopes.

You see, you can create some macros to do things such as swap the data source of a report, etc that are utilities for a developer through the hooks. You can also create a new process definition for running nVision with the designer loaded (the existing process definition causes nVision to start with the designer not loaded for performance and stability purposes). When running this way, you can have simple routines that could ensure that report requests are set up appropriately, etc (which is desirable if you're not in a position to use the new security hooks in the report request page added in PeopleTools 8.44).

Getting Started

Unfortunatey, this is one of the few places where PeopleBooks will not help you. Although there is a section in PeopleBooks for them, the documentation is wrong. Therefore, you will want to use the object browser in VBA to see what is available (and because the DLL you browse has a couple of issues with the object browser, you'll need to pull it in twice).

Here are the steps you go through to do this:

1. Open up Excel and navigate to the Visual Basic Editor
2. Open up the object browser


3. Use the menu Tools --> References menu
4. Then browse to your PeopleTools bin directory and open the PSNVD.DLL. You will then see PS/nVision Type Library in the list of the dialog. If you see more than one, pick the one with a reference to NVDUSER.TLB
5. Click OK to add it. If you get an error, re-browse to PSNVD.DLL and re-select the PS/nVision Type Library
6. You should now see PS/nVision in your list of libraries in the object browser

Now that you've done that, you can start looking at the classes, properties, and methods available to you in VBA

nVision Report Requst

The first class of interest is the report request class. Here's a screenshot of it (click on the thumbnail to see a full-sized version of it).

nVision Criteria

Another class of interest is the criteria class (where you can set and change criteria). Here's a screenshot of it (click on the thumbnail to see a full-sized version of it).

Grey Sparling Plans in this area

In our nVision bolt-on, we are in the process of building web services for all these classes. These services allow client-side logic to call the server-side code with logic and prompting without requiring installation on the client. This will allow you to perform this level of automation regardless of the entry point.

Labels: nVision

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

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

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

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

Labels: Drilling, nVision, Query

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

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

Demo
Here is where you can watch it in Action.

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

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

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

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

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

Labels: nVision, Query, Student

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

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

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

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

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

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

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

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

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

Queries

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

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

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

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

nVision Reporting against HCM

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

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

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

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

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

Drilldown Layouts

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

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

Other Information that might be Useful

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

Excellent! How can I get a copy???

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

Download the zip file from here.

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

Taking it to the Next Level

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Marion Crkon
• Dan Iverson
• Jim Marion

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

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

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

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

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

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

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

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

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

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

Over the past week, I've had the opportunity to talk with several PeopleSoft customers who want to improve how they run and manage processing that occurs on the process scheduler.

This posting focuses on pulling together information from the run control (or report request) and the runtime statistics. The original question came from the following ITTOOLBOX posting.

Restating the problem
The person asking the question wanted to be able to be able to determine for each run of a specific nVision report, statistics, such as how long the report ran and other attributes of the processing. This will allow him to identify long-running reports and focus in on items to tune. Our High Volume Report Security, Distribution, and Management product has similar functionality in a page that allows an administrator to identify and manage all the reports to be run (or run) for a specific user.

Approach
The way to solve this problem is to find a way to join the run control (or report request) that contains all the parameters used to run the process with the process request table (which contains the status and processing information maintained by the process scheduler server.

The table in the process scheduler that contains the process status and other pertinent running statistics is the PSPRCSRQST table. For nVision, the table that stores the request information is the PS_NVS_REPORT. Therefore, we will want to join the PSPRCSRQST table to the PS_NVS_REPORT table to get what we need.

Unfortunately, this isn't as straightforward as it might seem. There are no common fields between these two tables. In fact, the keys to the PS_NVS_REPORT table (Business_Unit and Report_Id) are actually parameters passed to the process and stored in a 3rd table in a string that concatenates all parameters passed to the process (this is PSPRCSPARMS). Here is an example of the parameters stored in this table for running an nVision report (the Report_ID is in red, and the Business_Unit is in blue):

-CTMICROSFT -CDFSCM89 -COVP1 -CPOPRPSWD -R" " -I5570 -OT6 -OF8 -OPDistribution List -NRNPNL -NBUUS005 -NHLhttp://www.gsdemo.com/psp/ps_newwin/EMPLOYEE/ERP/c/REPORT_BOOKS.IC_RUN_DRILLDOWN.GBL?Action=A -NDL0

How to do the join
Okay, so now we know the tables involved, and where the keys are stored. Let's review where we are so far:

1. For nVision, we know we want to join PS_NVS_REPORT to PSPRCSRQST to allow us to determine how a given nVision report is run and the statistics of that run.
2. There are no direct common keys between these tables, but there is a 3rd table that does contain enough information to allow the joining to occur (PSPRCSPARMS).
3. However, the parameters are concatenated into a single string, which complicates the join SQL.

Okay, now let's figure out how do do this 3-way join. Probably the easiest thing to do is to use the like verb (although a purer solution would be to use substring functions in the SQL instead). The Business_Unit is the -NBU parameter, and the Report_ID is the -NRN parameter, so our SQL will look for both of those parameters and see if that parameter concatenated with the appropriate key value in the PS_NVS_REPORT record is found.

Here's the resulting SQL (which uses PeopleSoft meta-SQL to ensure platform independence... if you paste this into a view in application designer, it will work).

SELECT PRMS.PRCSINSTANCE, NVS.BUSINESS_UNIT, NVS.REPORT_ID, RQST.OPRID, RQST.RUNSTATUS, RQST.BEGINDTTM, RQST.ENDDTTM
FROM PS_NVS_REPORT NVS, PSPRCSPARMS PRMS, PSPRCSRQST RQST
WHERE PRMS.CMDLINE LIKE '%PSNVS.EXE' AND PRMS.PARMLIST LIKE '%NRN' %CONCAT NVS.REPORT_ID %CONCAT '%'
AND PRMS.PARMLIST LIKE '%NBU' %CONCAT NVS.BUSINESS_UNIT %CONCAT '%'
AND RQST.PRCSINSTANCE = PRMS.PRCSINSTANCE

What you can do with this...

Now that we have the SQL or view determined, we can put it to use. Again, what we've done is to identify which processes in the process scheduler table are associated with a given run control or report request (and all the pertinent values stored in each). You can now see all the parameters in the run control as well as the process scheduler stauts and runtimes for each time it was run.

Although this example is specific to nVision because it joins the nVision report request table, you can substitute the query run control record (PS_QUERY_RUN_CNTRL) for the PS_NVS_REPORT table (or other run control records) in its place to do the same thing for other process types.

Here are some things you can do with the results:

• See how often a given run control is run, who ran it, and the run times.
• See which processes are the slowest processing to identify which ones to tune.
• See whether a common set of parameters are frequently run by different people multiple times (to identify whether it would be better to pre-run the reports for them).

One final thing that came up at the UKOUG is to use this as the basis for better managing the running of reports in the scenario where a user kicks of the same report while a report with the same parameters is still processing on the server (this can happen when running to window and the report is slow... the user doesn't have patience, or can happen when with other scenarios when a run control is re-used for multiple sets of parameters). I plan to write a follow-on posting to this.

Labels: nVision, Process_Scheduler

For those who were interested in downloading the powerpoint to the presentation I gave, here it is.

It contains the tips I gave in the following areas:

• Drilling from and to PeopleSoft Reports
• Applying Data Security to PeopleSoft Reports
• Setting up reporting jobstreams in process scheduler
• Getting Context from numbers in nVision Reports

Labels: 2006, Drilling, Events, nVision

One of the great things about presenting at conferences, is the great questions that come up afterward. I got an email today that prompted this posting (and quite frankly, I'm surprised I haven't written an entry on this previously).

The issue that this customer is having is that they are getting inconsistent results in their nVision reporting. Whenever I hear about this, the first thing that comes to mind is problems with the tree selector tables. For a dissertation on how tree selector tables fit into how trees work, feel free to read the following post.

Problems? What do you mean?
Good question. For those who weren't able to read through the posting above, let me summarize the default behavior when running an nVision report with trees.

When nVision runs a report, it identifies all the pieces of tree criteria and determines how to put it in a staging table (the ever-famous tree selector tables). In order to minimize the impact to the system, it keeps track of the PeopleTools version number of the criteria in the tables and compares it to the version in the tree. If it determines that the selectors are out of date compared to the tree, it refreshes the value at report run time. This behavior is called "static selectors", when looking at the tree performance options (which is the default).

This is where the train can come off the tracks. Because there isn't centralized management of the trees and tree selectors (each process does its own work), it is possible for processes to accidentally cause undesired behavior (such as refreshing a tree selector table that another report is still using). There have also been bugs related to updating version numbers of trees that have also caused issues with nVision reporting.

When I was managing the reporting team, I remember having my developers spend a lot of time working on addressing these issues (which we had successfully accomplished). However, one of the hard realities of software is that regressions can often occur.

So, tell me about the Workaround, already!
The workaround is actually an artifact of the tree preformance options we put into PeopleTools 6 (actually Tools 7, backporting it to release 6). This performance option is the "Dynamic Selector". The dynamic selector gives each report its own set of values in the tree selector staging table. This means that regardless of whether a report has already used the current version of a tree node, nVision will pull the data from the tree at the beginning of the process. This takes out any of the versioning issues or the potential issues with multiple reports changing dependent values in the tree selector tables.

Therefore, if you're having problems with inconsistent results, switch the performance options to "Dynamic Selectors" and see if that fixes them (this recommendation helped many PeopleSoft customer meet their closing needs while we frantically worked on fixing issues).

I don't get it... Why put in Dynamic Selectors as a performance option??
Ah... Now the true geek in you has come out. This option was created for performance tuning for two purposes:

1. To allow equality joins between tree tables and data tables.
2. To give a DBA more control over the state of a table when they run statistics.

Let's start by talking about the equality joins. For the most part, equality joins are critical for a good performing join (and range joins, such as ones where you use a between clause, generally cause poorer performance). In order to do an equality join, every specific detail value must be put in a selector table and matched to a node. This means that all department values that fall under a given node need to be staged (which is retrieved from the "user" table... i.e. not a PeopleTools table). Because the list of detail values is an application data table, it is not versioned. That means that the version number of the tree does not know whether there are new values in the tree detail table. Therefore, the only way to ensure that you have the right values is to re-load the selector table each time it needs to be used.

Now, let's look at its implications related to statistics. Statistics tells the database the make-up of a table, and it is the data used by the database to pick how to access tables and join them together. With static selectors, the tree selector tables are generally full of data. With dynamic selectors, the tree selectors are generally empty of data, because the processes clean up after themselves. Although it seems that static selectors will give better statistics, your DBA can tweak the system for certain reports by seeding the tree selector table with data and using dynamic selectors to ensure the seeded data drives the optimizer.

Labels: nVision, Tree_Manager

With the interest we've gotten with the PSIDE Helper, we decided to put together another product that solves a niche need. This is nVision Context Helper.

What is it?
This tool shows you the context of any number in an nVision report (report instance or drilldown result). We've packaged it as an excel add-in that adds a menu item into Excel that can be used in place of the DrillToPIA.XLA.

Because it looks at the nVision results in Excel to identify the context, this tool has no server code that needs to be installed and configured. Register the add-in to excel, and you're up and running.

From a user's perspective, they merely select a cell in their nVision report and use the menu to get more information about that cell (GSDrill --> About this Cell).



Benefits
In other words, if your users or auditors ever wonder what went into a number in a report, this tool will tell them. It understands and displays criteria in the report, filters applied by scopes, report requests, and the act of drilling. It provides transparency to end-users that isn't possible with other tools.

More Information
We've put together a product page and flash demo, if you want to see it in action. We've also added this as a standard feature to the nVision drillling Snap-on.

Labels: nVision, Products

Last week, we had a great trip to the Washington, DC / Richmond, VA area. We had lots of meetings with PeopleSoft customers, helped many of them with some of their PeopleSoft issues, and got lots of great ideas for new products.

Presentation
For those who are interested in looking at the PowerPoint presentation I gave, here's a link to download it. The presentation covered the following topics:

• Adding hyperlinks to your reports (one of our favorite BLOG entries)
• Adding Security to your reports (another favorite)
• Creating production job streams for your reports using process scheduler

Report Validation, Analysis, and Balancing

We had several customers express interest in us building a product for Report Validation, Analysis, and Balancing. This product would address significant business and compliance needs by automating:

• The validation and analysis of similar reports in your system (across different formats and reporting tools)
• The balancing of reports in your system (to ensure that you're reporting accurately across the different parts of your business)
• The tying out of numbers across systems, such as validating numbers in EPM against the source systems, or validating your numbers in an upgrade.

Due to the interest from customers, we expect to have the product ready in September. If you are interested in the product and would like to work with us as an early adopter, contact us at ReportBalancingProgram@GreySparling.com.

Labels: Drilling, nVision

While we were at a customer today installing our Report Explorer product, we were asked to help troubleshoot a problem that they were encountering. Apparently, their nVision reports were timing out in their production reporting run, even though performance didn't seem to be an issue.

Processing Time Limits? What are they?
For those customers not as familiar with some of the new process scheduler features in PeopleTools 8.44, processing time limits is one of the cool new features that made it into that release. This is part of a series of features in process scheduler that makes it much more usable:

• Jobsets: the ability to nest jobs within jobs
• Processing time limits: the ability to set time limits for processes at the processs type and process level.
• Thresholds: the ability to set constraints on a process scheduler server that drive wether it will accept new process requests. Significantly improves load balancing.
• Restart: the ability to restart processes, including nVision reports and clean up after them.

For nVision, you can set process time limits at the process type, process, and even report request level. This allows you to minimize the impact of long-running, bad-performing nVision reports to your production environment.

Makes Sense, so where's the issue?

Good question. In this circumstance, the customer was running a high volume set of nVision reports (generating many thousands of instances). Each instance was generated in 1-3 minutes. The time limit for nVision reports was set to 30 minutes.

The problem came up as an artifact of using scopes to generate instances (which in nVision allows application data to drive the generation of report instances... an example would be to generate a Profit and Loss for each department in the department table). Although one would expect the time limit to be re-set for each instance (because you don't want the processing of any individual report instance to go too long or get hung), it isn't. This means that if it takes 1 minute to generate an instance and the scope generates 31 report instances, your process will time out.

Ah... So, what are my options?

Good question. There are two relatively obvious ones:

• Reduce the "scope" of your scopes, which means scheduling more report requests with smaller scopes.
• Beef up the time limit. In nVision, this can be done at the report request level, which can allow you to override a generalized time limit designed for non-scoped report requests in one that does use a scope.

You can also use the new restart functionality in PeopleTools 8.44. Restart gives you two powerful capabilities:

• The ability to pick up a scoped report request where it left off and not re-run instances that were already generated.
• The ability to automatically try to restart the report a certain number of times.

Therefore, the process scheduler can more gracefully handle a scoped report. It will automatically restart the process after the time limit has expired and pick up where it left off. That means that if you set the restart limit to 5 and the process time limit to 30 minuts, you can get 150 minutes of processing for your scoped reports (although it's better to manage it a little more closely).

Happy Processing!!

Labels: Database, nVision, Performance

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

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

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

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

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

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

Passing in Parameters

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

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

I've seen examples of both.

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

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

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

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

Pretty cool, right?

Using the Parameters

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

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

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

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

Although this is cool, it has several limitations:

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

Other options:

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

Labels: nVision, Query

This week, when I was picking up my kids at school, I ran into one of the nVision developers who used to work for me (who's still working at Oracle). She's working on taking nVision to Fusion, which will use the Siebel Analytics platform.

When I first remembered that Oracle's acquisition of Siebel included Siebel Analytics, I told my co-workers that if Oracle was smart, they'd use that as the replacement for nVision (and metric calculations in EPM).

So, Why is it a good thing?
Believe it or not, we at PeopleSoft evaluated Siebel Analytics (although under a different name) to be the infrastructure for the next generation of nVision. At the time, it was a small company called nQuire. We put together a bunch of things we wanted them to prove they could do within a week against multiple PeopleSoft systems. At the time, Chris Heller and myself had a lot of discussions about the product's claimed capabilities... his quote was "Either they're crazy or they're geniuses". We found out that they were geniuses.

Unfortunately for PeopleSoft, Siebel beat us to the punch and acquired nQuire and re-branded the product. Now that Oracle has acquired both Siebel and PeopleSoft, it looks like that original vision may be realized for PeopleSoft customers.

So, what is Siebel Analytics?
Siebel Analytics is most of the functionality we planned to provide in reporting in PeopleTools 9.

• A data abstraction layer that allows users to work with meaningful objects for building reports
• A browser-based way of building both tabular and crosstab reports by business users
• A server-based quering engine that runs the reports and delivers results

In the published PeopleTools 9 plans, we had different names for the same functionality

• Data Objects (Data Abstraction Layer)
• nVision Studio (Browser-based way of building tabular and crosstab reports)
• nVision Engine (server-based querying and reporting engine)

Mark Rittman has some good images on his weblog (which is a must-read for anybody wanting to understand what's going on with Oracle and BI) that shows screenshots for building and managing the data abstraction layer and for building queries or reports.

Data Abstraction Layer

The data abstraction layer looks exactly as it did when we reviewed the product.

The far right pane contains the list physical objects that are the sources of data and metadata. These can be tables or files (and I'm assuming XML sources now). You can think of these as record definitions in PeopleSoft.

The middle pane allows you to map the physical objects to objects that have a more meaningful business names and structures. They abstract away joins and unions and other physical attributes you don't want to present. (a good example we had them prove in the PeopleSoft evaluation was that you could have a single representation for sales, where under the covers you were getting historical sales from a data warehouse and current sales from the CRM system). These are related to Data Objects in the PeopleTools 9 feature list.

The left pane contains the presentation layer for how you would want to present these objects to business analysts. For example, you might want to have different versions of an order, depending on the vertical you're using (where fields specific to a vertical are displayed and not others, and where the terminology presented to the user is targeted to that user type). In other words, you might have a different presentation object for Universities and Commercial in CRM, where in a university a customer is displayed as a contributor and in commercial, they're displayed as customer. These are related to Data Views in the PeopleTools 9 feature list.

Reporting

Siebel analytics provides a drag-and-drop interface for building reports in the browser using these objects. Although I believe that there is some opportunity for making the user interface more targeted to business analysts, they've done the big, hairy effort to pull together crosstab and tabular reporting into a single reporting tool. Here's another screenshot from Mark Rittman's weblog that shows some of the user interface.

As you can see, you have the objects from the presentation layer in the left pane, and you have the ability to lay them out in a report.

So, What's left to be done?

Good question. Here are the major tasks I see that need to be done to get nVision using this platform:

1. Map ledgers to the Siebel Analytics metadata. This should be relatively straightforward, but it is dependent on the fusion business unit/setid project as well as how chart of accounts configuration gets done in fusion.
2. Teach the Siebel Analytics platform how to read trees. Again, this is dependent on the fusion tree project. Fortunately, there are a lot of designs in place for how an engine would utilize different approaches for modeling trees. Another good thing is that the Oracle database has sql extensions for trees/hierarchies that make this much, much simpler than what is currently being done in nVision.
3. Build a robust excel user interface that leverages the calculation engine in Siebel Analytics. From what I know about WebADI, this infrastructure may do the trick, because it is build to allow web services to be used interactively to embed application functionality into to provide a user experience targeted to a business user.
4. Extend Siebel Analytics with output management functionality (this will probably involve work with concurrent manager, which is being managed by the person who used to own PeopleSoft's process scheduler and report manager).

Conclusion

Hopefully this makes most PeopleSoft customers more comfortable with the future of things. I've already known or suspected much of this information earlier, but was waiting for Oracle to provide enough information publicly for me to safely write this entry.

Labels: Fusion, nVision, Performance

With the recent popularity of our nVision drilling snap-on, we've had the opportunity to talk to many customers about nVision drilling performance, and how to improve it. Based on these discussions, I decided it was time to put a blog posting out that discusses it.

80% Rule
Let's start by discussing the two settings in PeopleSoft that cause 80% of the performance issues. Many DBA's will tell you that there is never a simple fast/slow switch that they can set (otherwise, why would you set it to slow?). We, in our infinite wisdom at PeopleSoft put this switch in place on the process scheduer and application messaging products.

Okay. That's not very fair, because these settings were put in place for something different than nVision drilling, and drilling is merely affected by these settings. However, based on the default values of these settings, nVision drilling can have a latency of 30 seconds or more.

The two settings to look at are polling intervals of the process scheduler and the application messaging dispatcher. For each of them, the default setting is set to 15 seconds, which means that with the default settings, the process scheduler will wait 15 seconds between times it checks to see if you've requested a process to run (i.e. a drilldown request), and the message dispatcher will wait 15 seconds between times it checks to see if there's a message for it to process (such as a report being completed).

Therefore, if it normally takes around 30 seconds to run a drill and you're using the default settings, reducing them will improve your performance (but keep in mind, that the server is doing more work by checking the queues more frequently).

• Process Scheduler Changes: The Server settings page in PIA (PeopleTools --> Servers) is where the Sleep Time is set for a given server (default is 15 s