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:
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:
Baby Step 3 – Linking Queries
As your users start getting used to running queries and formatting them, they’ll start wanting to include more and more information in the query results. This is a natural part of the process, because once they learn one thing, users will want to see information related to it.
When your organization reaches this milestone, you will want to be very careful. Many organizations start extending the queries by joining related data into their queries. This can lead to “Kitchen Sink” queries and can cause two issues:
The 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:
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.
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