PT 8.61

Randall Groncki

Introduction

Insights, AKA Kibana, uses PeopleSoft transactional data to generate analytic dashboards Illuminating trends ,statuses and providing situational insight to the user.

… but it only works if ALL the data you think you are sending to Insights has actually arrived …and has been indexed.  Just because your extract process sent “X” rows of data to OpenSearch (AKA Elasticsearch), doesn’t mean that “X” rows of data arrived and is available to your visualizations and search indexes.

If data is missing, the accuracy of your analytics is left up to luck and coincidence.

But why would our data go missing?

Drilling URLs, not keys

When we create a PSQuery to send data to insights, we need to include a Drilling URL.  As per PeopleBooks: “The drilling URL defines the URL, the target, for the search result.”

Our missing data issue occurs when the data we are visualizing is not at Buffer Level 0, but rather buffer level 1 or deeper in the page.

Here’s a simple page example of a page showing an employee’s training data

We want to use this employee’s four training courses in our visualization.   The proper Drilling URL FOR THE COMPONENT would just have the EMPLID field as a key.

However, when we extract data to Insights and see the detailed information for this employee

We can see from the PeopleSoft page above that Employee KU0010 has four classes, not one.  Additionally, only the LAST class in the list appears in the Insights analytics.

OpenSearch and Insights uses the Drilling URL to index a unique row of data, not the keys from the source PeopleSoft tables and views. 

If I send four rows of data with the same Drilling URL to OpenSearch to be indexed, only the LAST row will appear.   Each subsequent row overwrites any previous data rows with the same key.

Same example only bigger

Our example uses the TRAINING table from the HCM Demo Database.  This is a breakdown of the different types of Unique indexes available and the result number of rows.

Index Type	Count
Total table Row Count	245
Unique Employee ID count	142
Unique Employee ID + Course ID count	235
Unique Employee ID + Course ID + Session# count	245

AS you can see from our chart above, the only way to get all our data into OpenSearch is to create a Drilling URL that contains the Employee ID, Course and Session Number.  Anything less than that will result in missing data in our Dashboard.

How do you know you’re missing data?

A quick way to determine if you’re overwriting your own data due to the drilling URL not being unique is to compare the PSQuery results Vs the PTSF_GENFEED log from a “Full Index” process.

This is a shot of the PSQuery Results. 

• The query returns 245 total rows. 
• We can see the four classes for employee KU0010
• Each of the employee KU0010’s classes have an identical Drilling URL. 

This is a screenshot of the PTSF_GENFEED log sending this data to OpenSearch.

Total Documents Indexed is 142.

We lost 103 rows of class data during our indexing into OpenSearch.  That’s 43% of our data lost due duplicate Drilling URLs.

Fixing our Drilling URLs

For OpenSearch to index all our data, we need to provide a unique Drilling URL for each row of data we send.

But do you need for the drilling URL to actually link back to a PeopleSoft page?  

In many cases, you don’t.  The business requirement is just to show rich analytics to a user on a dashboard.  That user will never directly drill back to a page from a data item on that dashboard.  This case is the easiest because we just need to create a unique drilling URL without the concern if it points to a working component with the proper keys.

Some cases you do need a functional Drilling URL because the user wants to link back to a PeopleSoft transaction page from an Insights Dashboard. 

Here is where PeopleTools has a secret:  As long as you have the correct component keys up front in the drilling URL, the PIA will ignore additional key items and take the user to the correct page using the keys it needs.

Let’s go back to our Training Data example. The Drilling URL to the component must have EMPLID and the correct Employee ID value.  But we can then add Course and Session Number to the Drilling URL making the combination unique for every row in the Training Table.

This is the PSQuery Expression Text from these keys.  There is character limit of 512 Bytes of data for a drilling URL.  That should be plenty of space to create a unique combination of fields in drilling URL to  avoid duplicates.

Running or PSQuery, here is that changed Drilling URL.   There are still 245 rows of data returned. Looking closely, we can see that each of Employee KU0010’s classes have a Drilling URL with Emplid, Course and Session Number.

Next, after running our search index (PTSF_GENFEED), we see that 245 rows of data have been indexed by OpenSearch.

Returning to our Insights Dashboard, we again see that 245 rows are available in our Index Pattern.

Filtering the data down to Employee KU0010, we can see all four classes in the data grid.

Conclusion

PeopleSoft Insights Dashboards are powerful analytics tools for our users.  However, missing data will at best skew your results and at worse totally misinform your users. Any accuracy is due to luck and coincidence.

Ensure that you have all the data in your visualizations by ensuring a unique Drilling URL for each row of indexed data.