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

So, does one size fit all with retaining the reports you're running and sending to the report repository? Based on the discussions we've had with customers over the past several years, the answer is definitely not. Unfortunately, that's exactly how report manager handles it. Every report, regardless of whether it's a drill, ad-hoc query, or financial statement has the same retention period. Obviously, this is not ideal, because not all reports are created equally.

Options Available

Believe it or not, the options for setting the retention period are very similar to several other blog postings we've made. For example, our posting on XMPP integration with PeopleSoft uses one of the hooks, the PSRF_REPORT_CREATE application message. Another option is to use application engine to periodically set the retention period for you.

This blog entry will cover both, and you will probably want to do both as well. This is because using the application message is an ideal way of accomplishing this because it keeps track of what you haven't processed for you and your code is invoked a little bit over time (as reports are run).

Because it's difficult to debug subscription PeopleCode, I generally do both together: write my code to do the processing I want, and test it first using application engine (with warning messages causing data to show up in a log file). When I've got it done corretly, I take my logic and move it to my subscription peoplecode, hooking the values up to the contents of the message (and remembering to comment out my warning messages).

Things to Consider

One of the first things you need to consider is how you want to set your retention periods. For the purposes of this blog entry, I've put in the following rules:

• If it's an nVision drill, set the expiration date to be 1 day from the date the report was run.
• It it's an nVision report, set the expiration date of the current report to be 1 year from the date it was run, and then set the expiration date of all reports using the same set of parameters to yesterday.
• If it's any other report, keep the default expiration date for the current report, and set the expiration date for all reports using the same set of parameters to yesterday.

You can obviously use other rules and lookups and go as crazy as you want. In fact, this is why we have plans to build a report retention product, because we see a lot of options that people won't necessarily want to develop for themselves.

Things to know

The first thing to know is the PS_CDM_LIST table. This is the primary table used by report manager, and will be the one you'll be updating with the new expiration date. It also contains most of the information you'll be using to attach retention rules (such as process type, process name, and report description).

The second thing to know is the PSPRCSPARMS table. This is the table the process scheduler uses to store the runtime parameters used by any process run by it. It's also important to know that the process instance is often embedded in the parameters, which means that if you want to find all reports with a common set of parameters, you need to exclude this one from your comparison.

Subscription PeopleCode

Although I have attached a project to this entry with both the app engine program and the subscription peoplecode, I also wanted to include the following in-line:

Here is the App Engine PeopleCode

This will need to be beefed up to do more than testing, because it only processes a single process instance (either the max one or one that you set manually). Here's it's code:

Code and Limitations

Click here to download the project that can be imported into application designer. Keep in mind, though, that there are several limitations to this code:

• It only supports a subset of the types of retention periods you would want to set
• The SQL for updating an item isn't constrained by date
• For app engine-only setting, you will want to use set logic to select and update in one fell swoop. Because I'm leveraging application messaging, it automatically keeps track of reports I've already processed.

Labels: Process_Scheduler, Report_Manager, Reporting

One of our customers is in the process of rolling out our Excel Add-in, and wanted to find the easiest and most effective means for training the end-users on how to use it. For those who are not familiar with our product selling model, we record demos of each of our products and post them on the website, so that prospective customers can get comfortable with how it works prior to making the commitment to contact us for a potential trial of the product.

This customer asked us to remove the product configuration topic from the demo (as end-users don't do configuration), and provide them a version they could use internally to accomplish this. This way, the end-users can quickly navigate between the different topics and see how they would use it (the fact that this demo shows GL and the initial rollout is for GL users was a benefit as well).

I decided to use this opportunity to finally upgrade the version of Camtasia I used, because it has the option of including the table of contents directly in the flash file, instead of generating HTML with it (which makes it much easier for packaging, embedding, and sharing).

Here is what I came up with:

Doing it this way has a lot of benefits

• You can put the training on your corporate intranet, so that it's easy to get to
• You can show navigation and what they should expect instead of describing it
• The file size isn't that much larger than the documentation (with all the screenshots we put in, our doc is around 3-4MB in size, whereas this flash is about 8MB in size which really suprised me)
• Users are much more likely to watch a 10 minute flash demo than to read through a manual or go to a training class.

Labels: Reporting, User

Recently I presented "More Advanced Reporting Tips and Techniques" at the Northern California RUG training day and showed instant messaging integration with the process scheduler and reporting tools. This generated quite a bit of interest.

This is a follow-on to this post, but has been updated and enhanced.

Why should I care about integrating instant messaging and PeopleSoft?
Good question, especially since PeopleSoft has delivered notification of reports through the Real-Time Event Notification service (or REN Server).

Let's start by covering limitations of the REN server:

• It only works when a person runs processes for himself (and only when he requests the output to be run to window).
• It doesn't work for distributing to multiple people at the same time.
• If the browser window receiving the results is closed, the automatic notification is lost, even if another window is started.
• It doesn't work for process scheduler jobs.
• It doesn't work for scoped nVision reports.
• You must have the REN Server configured for it to work.
• You must be on PeopleTools 8.42 or greater.

With these limitations, the instant messaging integration we are discussing here does the following:

• You are notified when any process is completed, not just the ones you run to window
• Users are notified any time output for them is ready, regardless of whether they ran it or how many people are on the distribution list.
• Output from process scheduler jobs is notified.
• This works regardless of whether the REN Server is available or set up.

Click here to watch it in action.

Update on Installing and Configuring the IM example
Although the rough steps are included in the original posting, we ended up updating the code to make it easier to get up and running. For example, we included an application engine program that can be modified with your own IM accounts to test to make sure that the connection to the IM client is working independently of running processes through the process scheduler.

The hook we are using is subscription PeopleCode on the PSRF_REPORT_CREATE message (which gets published each time a process completes that has output, such as a report, log file, or trace).

Update on SMACK libraries for IM connectivity
The connection to the instant messaging service is done with an open source java library (based on the XMPP standard), called SMACK. One thing we found was that the version on the website supports a newer version of java than what our environments support (8.47). Therefore, you'll want to go here to get the libraries for version 2.21 (and put them in the class folder in the PS_HOME of your app servers)

New Project
Click on the following link to download the Application Designer project with the new code. Keep in mind that you'll want to replace gsdemo.user (and password) and the service names with the ones you will use in both the app engine program and the subscription peoplecode.

Labels: Email, Process_Scheduler, Reporting

First, Oracle acquires Hyperion. Next, SAP announces an acquisition of Business Objects. Today, IBM announced it will acquire Cognos. It looks like the only major independent BI player is MicroStrategy.

This acquisition provides a lot of food for thought. With the combination of Ascential and Cognos, IBM will have a complete technology solution from data acquisition, cleansing, all the way through data access. Additionally, these technologies are best-of-breed technologies brought together in one umbrella.

Technology Play
Although this is a good technology play, I'm still of the belief that the days of building your own BI solution from soup to nuts is not the way the market is going. This is a good strategy under yesterday's model. It's my belief that Oracle and SAP have the right long-term strategy, which is to provide analytic applications using their own technologies. Companies don't want to build expensive data warehouses and their own analytic applications if they can buy one that somebody else has developed. Also, by bringing together analytics with their business processes, organizations can achieve benefits that can't be achieved with a BI framework that is silo-ed off from their transaction systems.

Why not Analytic Applications?
Back in 2002, IBM had the opportunity to get in the applications business. We at PeopleSoft were hoping that IBM would be our white knight and save us from Oracle. There was quite a bit of effort going into discussions in this area, but in the end IBM didn't want to get into the applications space. They wanted to stay a technology/consuulting organization. Now, that opportunity is pretty much gone unless some sort of mega-merger occurs between IBM and SAP or Oracle (which I can't see happening). So, it will be very interesting seeing which strategy works better. My money is on Oracle/SAP with Cognos becoming less relevant, but this assumes that Oracle and SAP are successful in integrating their new BI acquisitions (Oracle seems to be doing a pretty good job, at least from a PeopleSoft perspective).

Labels: 2007, Business, Cognos, Reporting

Yesterday's news was very interesting. I had intended to comment on this much earlier, but ended up spending a lot of time on the phone last night talking with people who wanted my perspective on what this means.

The rumors about a big acquisition in the BI space is nothing new. In fact, a big piece of PeopleTools 8.46 and PeopleTools 8.48 was to protect Oracle from SAP acquiring Business Objects (now that this has occurred and I've been out of the company for over 2 years, I'm more or less free to talk about it).

So, Why would this be inevitable?

Good question. It's a combination of needs and market forces.

Looking at it from the BI Side

On one side, you've got BI vendors who need to get data to report and visualize. Because a good portion of this data is locked up in ERP and CRM systems, their solutions are dependent on getting data out of these big, packaged applications. Anybody who's had to built ETL maps or figure out what table to use (and what the values mean) can tell you that this is a big, hairy, painful effort.

In addition, the BI vendors, by definition, are limited in what they can provide. You see, the ERP vendors own the key business processes that people perform. Sure, you can put together an executive dashboard for management without integrating in the business process flow that occurs, but you can only sell so many dashboards. What customers really want to do is to help employees do things with the information presented in BI. This is not something that a standalone BI vendor can easily provide.

Looking at it from the ERP Perspective

On the ERP side, you've got products that are optimized for capturing data, but not getting it out. The focus on SOA as the next big thing is a perfect example of how ERP vendors look at things. SOA is primarily focused on how you can cause transactions to occur across systems (placing an order is one of the most common SOA use cases quoted). Because of this, ERP vendors have, for the most part, either outsourced any BI they provide (ala Business Objects, Cognos, or MicroStrategy), or they have defined reporting and BI in very limited terms, so that functionality gaps can be glossed over.

For those folks wondering whether SAP's acquisition of Business Objects is a good idea, they should look at the reporting functionality provided with an SAP application. Game... Set... Match!

If done properly, the things that BI firms do well can dramatically improve the core functionality of an ERP application as well as provide new ways of providing intelligence within the context of business processes. Here are some things that BI can do to improve core ERP processing if adopted properly:

1. Navigation within the ERP product can be dramatically improved. Suppose that you had a high-end query tool as the means of finding items you wish to process ("I want to manage the inventory of every product that meet some set of complex criteria"). This means that your employees can more efficiently do the most important things to your organization more productively (one might say "intelligently").
2. Conditional processing, such as approvals can leverage BI functionality. In other words, you can use common definitions for evaluating trends of key risk metrics and automatically routing the approvals to the appropriate person (or better yet, to cause the evaluation of metrics to drive business processing). A report would then be a visualization of common calculations that drive your processes (ooooh.... aaaah...)
3. Reporting can be integrated with business process, so that it's easier for users to determine what data to use, and how to get the information they need. If you can close the loop automatically, by allowing the user to navigate back into the business process, now you're talking!
4. Providing better ways of navigating and organizing things. BI firms have already had to spend a lot of time figuring out how to organize and present data, logical representations of tables, and relationships to business people (versus developers). ERP vendors could learn a lot about how to facilitate navigation, organize content, and help business people do more within their products.

Therefore, I see lots of synergies, assuming that organizational issues don't get in the way of cross-pollination of assets or ideas. In fact, I've believed for a long time that BI truly seamlessly embedded into business applications will be the next big thing for ERP (sorry guys, not SOA).

What this means to the Market

SAP acquiring Business Objects has a pretty big impact on several players in the market. Therefore, let's look at its impact to the players in both the ERP and the BI marketspace.

• Cognos - Cognos and Business Objects are the two primary competitors in the BI space. In the short-term, Cognos will benefit, because it won't be distracted from its current plan. However, it is my belief that this will play out as being very bad for Cognos. This is because Cognos's major focus in the ERP area has been going after SAP customers. With SAP owning business objects, they have a better story on integration (which is the biggest cost of doing BI). Although Cognos could move down-market to capture additional market-share, their long-term survival will be tied to their ability to integrate with their customer's data and focus on beefing up markets other than SAP (such as Siebel, Oracle or PeopleSoft).
• Oracle/PeopleSoft - I'll start by looking at just the PeopleSoft applications aspect of this. PeopleSoft has embedded Crystal since 1994, and has packaged a majority of its production reports in it (well over 1,000 of them), and every PeopleSoft customer uses Crystal in one manner or other. Back in 2004, PeopleSoft's contract with Business Objects was up for renewal, we spent a lot of time looking at what could happen, knowing that we were highly dependent on Crystal. One of the first things we recognized was that we needed to lock in a long-term contract with them. One of the second was that our integration with Crystal was originally written using windows APIs that were antiquated and needed updating. Therefore, we made the decision to embed Crystal Enterprise and utilize Crystal Enterprise APIs from the process scheduler to ensure that we would be protected in the event of a change of ownership in Business Objects. Therefore, PeopleSoft customers are relatively protected in their Crystal investment.
• Oracle Corporate - From a corporate perspective, this can't be a good thing for Oracle. One of SAP's weaknesses is the difficulty of reporting. This gives SAP the ability to address that weakness. It also puts more focus on addressing some of the limitations that exist in XML Publisher, Concurrent Manager, and Collaboration Suite that Crystal Enterprise addresses (yes, I'm sorry to report that although XML publisher has a lot of great features with respect to PDF and Excel, it has some limitations). Don't get me wrong, I still give Oracle the lead in BI integration with their applications (however, I also believe that SAP will have fewer distractions in closing the gap because it has one BI tool to integrate with one ERP suite, whereas Oracle has 4 different BI suites that can be integrated with 4 different ERP suites).
• Microsoft - This is one of the more interesting organizations affected by this acquisition. Microsoft, for a long time has embedded Crystal in its development tools. SAP has worked extremely closely with Microsoft on Microsoft Analysis Services. Now, Microsoft has built its own reporting and BI tooling and is doing a lot more development in the ERP space. I believe that Microsoft would much prefer to have a relationship with SAP that would have had the potential to make SAP more dependent on them from a BI perspective (and would much prefer to have worked with Business Objects in a way that marginalized them more).

Conclusion

In case it wasn't clear in the previous dissertation, I am in the camp with folks who believe that SAP was smart to buy Business Objects. What will be telling, however, is whether SAP will be willing to leave some tools money on the table in order to deliver the next wave of innovation in ERP. Whoever does a better job of bringing Business Intelligence into their ERP product suite will be the 800-lb gorilla (that's a prediction).

Labels: Business, Business Objects, Microsoft, Reporting

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