by PT 8.62
Randall Groncki
Data Tables
A PeopleSoft Insights Data Table is an interactive visualization that presents indexed PeopleSoft data in rows and columns. It gives users a detailed view of business records and supports sorting, filtering, dashboard interaction, and drill-through to the related PeopleSoft transaction.
Data Tables are not Excel spreadsheets. They are analytics like bar and pie charts. The Data Table will always have at least one column showing the sum, count or other aggregate for that row.
Create a Data Table
On the Insights home page, click on the Visualizations menu and click the Create Visualization button at the top of the page.
Choose Data Table from the bottom of the list.
Choose your Index Pattern to supply the data. I am using the Training data for our Demonstration.
The Data Table visualization appears on the page. One column showing the count of all documents in the Index Pattern shows in the main window.
The left window shows the current state of your Data Table. The right window contains configuration controls. The search bar at the top allows us to filter data for the entire visualization.
Filter the Entire Data Table
Many times, we want our visualization to work with a subset of the whole data to show a specific business idea. In our case, we only want to see the classes of current, active employees in our Data Table.
Click on the Add Filter link at the top of the page
| Field | Payroll Status |
| Operator | Is |
| Value | Active |
Click Save.
Now only the data for the current active employees is in our dataset. We removed 18 documents from inactive employees.
Configuration Panel
Data Tab – Metrics Box
The Metrics section defines the calculations displayed in the Data Table. The Metric becomes a numeric column, such as a record count, total amount, average value, minimum, or maximum. While charts plot metrics on an axis, a Data Table displays them directly in table columns.
I’m using “Count” for most Data Tables.
Data Tab – Buckets Box
Buckets are variables or memory locations. The number of Spit Rows directly correlates to the number of columns you want in your Data Table. The number of buckets represents the number of rows for each item of the split row.
If you want five columns, then you need five Split Rows.
For our primary data series, I want to show the most popular course the employees are taking.
Create First Data Series
Click the “+ Add” link in the Buckets box
| Type | Choose “Split rows” for our first data series |
| Aggregation | Terms. This means we are going to use a field from the Index Pattern |
| Field | Course Title |
| Order | Descending |
| Bucket Size | 5 |
| Custom Label | Set to “Courses” |
Click Update
Our chart shows five Courses
Show the “Other” Bar
For clarity, Insights allows us to group all the remaining documents into an additional row labeled “Other”. This not only provides a truer perspective of the data but also provides a convenient filter to drill down into the smaller buckets. If there are no additional values not already appearing in the Data Table, the “Other” row will not display.
Click the “Group other values in separate bucket” checkbox and update.
Create Second Data Series
I want to show the breakdown by company in each of these Courses. In other words, which companies are taking these classes?
Click the “+ Add” link below the current Split row.
| Type | Choose “Split row” for our second data series |
| Aggregation | Terms |
| Field | Company |
| Order | Descending |
| Bucket Size | 5 |
| Group other values in separate bucket | Select |
| Custom Label | Set to “Company” |
Click Update
For each of the top five courses, we see the top five companies whose employees have taken those courses.
Options Tab
Max Rows per page
This controls how many rows per page are displayed in the data grid. The more rows per page configured here usually means a taller visualization. This does not limit the number of documents displayed in the visualization, but how many per page. The smaller the number, the more pages.
Show metrics for every bucket/level
This adds an additional column for every bucket of the Data Table showing the breakdown for that bucket.
Show Total
Show Total adds an additional row at the bottom of your Data Table showing the aggregate, such as count or sum, for all the data in the current visualization.
Percentage Column
This adds an additional column to show the percentage by count or sum of each row to the current selected data.
Larger Data Tables
Add more split rows of data to add additional columns to your Data Table. In this example, I’ve added the Department, Employee ID and Employee Name to our table.
These columns are added the same way we added the Company column earlier.
Add Links
We can add the Drilling URLs to our Data Table. This allows the user to click through to the PeopleSoft transaction relevant to each row of the Data Table.
As a refresher:
- Our extract PSQuery requires a unique drilling URL as the data key for each row of data
- We defined that URL as a type Link in our Index Pattern.
All we have to do now is to add that link as an additional column of the Data Table.
Click the “+ Add” link below the current Split row.
| Type | Choose “Split row” for our second data series |
| Aggregation | Terms |
| Field | URL |
| Order | Descending |
| Bucket Size | 5 |
| Group other values in separate bucket | No |
| Custom Label | Change if needed |
URL Constraints
The URL link will contain the text you defined for that field on the Index Pattern. As of PeopleTools 8.62, we can’t dynamically change the label of each row’s URL to reflect that row’s data. We can only change the column heading in the configuration and the link text in the Index Pattern.
URL Action
Clicking on the link opens a new browser tab to the PeopleSoft transaction as defined when we created our data set.
Buckets…Be Careful
A note about buckets in Data Tables.
A Data Table is not Excel spreadsheet with practically unlimited rows and columns. Each “cell” in the Data Table is its own bucket.
And there is a limited number of buckets… usually just a few thousand.
A few thousand may seem large until we understand bucket math. Each column has a defined number of buckets. The total buckets is each column multiplied by the others.
Total Buckets = Col 1 Buckets x Col 2 Buckets x Col 3 Buckets x Col X Buckets …
For example, if you have a data grid where:
- column 1 (Employee ID) buckets defined as 500
- column 2 (Company) buckets defined a 5
- column 3 (Department) buckets defined a 5
- column 4 (Classes) buckets defined a 10
This has the potential to allocate 125,000 buckets for the visualization, which may be more than your Insights server is configured to use.
When you run out of buckets, Insights will start displaying errors and incomplete Data Tables to your users.
More Information from other sources
| PeopleBooks | Search Technology: Creating a Visualization for Application Data |
| OpenSearch.Org | Building Data Visualizations – Data Tables |
