PT 8.62

Randall Groncki

PeopleTools uses the PSQuery tool to extract data from your PeopleSoft environment to send it to OpenSearch and Insights.

However your data is created, for example a transaction table Vs a batch process, it’s PSQuery that is going to move that data.

Query needs four things to move data from PeopleSoft to Insights:

1. Data
2. Drilling URL
3. Date/Time field
4. Prompt on the Date/Time field

Data

Anything you can gather with a PSQuery can be sent to OpenSearch and Insights. 

But there are considerations

Values you want to see

Any value you want to see on your visualizations must be sent using PSQuery.   If it’s not in your Query results, you can’t see it on a visualization.   Insights and OpenSearch do not have an ODBC connection to pull in detail information at runtime. 

A primary example is XLAT (Translate) controlled fields.  If this is a status field, does your users want to see an “A” as a value?  Or do they want to see the value “Active”.  

A simpler example is a Yes/No field.   Do you want to see the value “No” or is just seeing the value “N” provide enough meaning in the visualization?

Do you need to display the Company Name instead of the company code in a Pie Visualization?  If so, then we need to pull that data into our Query results.

There is no one answer that is always correct.   This is something to discuss with the users when gathering requirements.   Some field values may be so obvious we don’t need larger descriptions.

As a rule, I generally try to bring in the XLAT short names and descriptions from related fields into my Queries.   Don’t assume your users know the code values behind the meanings.

Derived Data

PSQuery can’t access PeopleCode or component logic functions that display calculated fields on a page.  You can use PSQuery expressions.   If there is complex, derived data needed in your visualization, you may have to create a worktable with your data set prior to using PSQuery.  This method uses PeopleCode and SQL to calculate the values once before sending it over to Insights, rather than recalculating it every time a user views a dashboard.

Precalculate Common Data

This is all about Dashboard Performance.

Insights is a great data visualization tool that’s lightning fast.   But you can slow it down without too much effort.  Creating a scripted field that calculates a value and “If-Then” logic is one way to do it.   If you have a million rows of data in your dataset, Insights will have to calculate that value one million times if you use that field in a Visualization or a filter.    

For every user

Every time

The calculation might only take a split-second for each document (row of data), but a million split-seconds add up fast.

Do as much as the calculations and logic before sending it to Insights as possible.  Use PSQuery’s Expressions for the calculations or create a worktable.  Add extra columns to your dataset with the calculated results so you just point to the calculated column and use it.

Drilling URL

PeopleTools uses a PSQuery Drilling URL as the document key for the flattened data being sent over to OpenSearch.   It doesn’t matter if you are planning to use this Drilling URL in a visualization or not.  It must be there as the document key.

The Drilling URL MUST BE UNIQUE for each document in your dataset.  If it is not unique, any subsequent row of data with the same Drilling URL string will overwrite any previous data row with that same Drilling URL String.

This is a common error when creating datasets.  Always check the number of rows sent to OpenSearch Vs the number of documents OpenSearch has in that dataset. See my blog post: Are you missing data in your Insights Dashboards?

PeopleSoft has a cool trick to help with this unique URL.   We can add additional key values to the Drilling URL that are not part of any component search key structure.   When consuming a Drilling URL, the PeopleSoft App Server will just ignore any value that is not part of that component search record.

Date/Time Field

Each dataset must have at least one Date/Time field to use as the Last Updated Date Time value for that data row. 

The most common field used is LASTUPDDTTM.

This date/time field is used for incremental updates from PeopleSoft to OpenSearch.  More on that in a later blog post. 

If your data does not naturally have a Date/Time field, you can create one in PSQuery using an expression.   Just note that this “generated” Last Updated Time field probably excludes this data set from Incremental updates.

Prompt on the Date/Time Field

Our PSQuery must have at least one prompt on a Date/Time field.  

This takes the format of

LASTUPDDTTM is not less than [prompt datetime]

This is required to support incremental updates to the dataset.   Even If you are not planning on using Incremental Updates to your dataset, you must still include this prompt in your PSQuery.

Summary

Use your PSQuery to gather all the data you need for your Visualizations and Dashboards.   Testing that query before using it for a search definition, you should see:

• All the values you want to see your visualizations.  If you don’t see it here, you won’t see it in Insights
• As many values precalculated as possible in additional columns
• Every row has a unique drilling URL
• A populated Date/Time field for each row
• A prompt on a Date/Time field.