Hello Technical and Reporting community!
Last month, we shared a Did You Know?! about tree flattener. As promised, here is a detailed article around this topic. Consider this the first part of a two-part series. Enjoy!
Do you know Tree Flattener can improve query performance when you need to use the tree information to join with the transactional data? Do you know how the tree related information is stored in the PeopleSoft (PS) system?
If you understand the true characteristic of the tree, you will understand the reason why PS uses trees to store configuration data such as GL Chartfield Element. We use tree structure to store the GL Chartfield elements in hierarchy levels for representing the parent-child family tree relationship among the data defined in the system. The data stored in tree nodes or tree leaves represents a relationship or a true value of the elements in either a single value or a range. The tree related data are decoupled and stored in, but not limited to, the four metadata tables including PSTREEDEFN, PSTREENODE, PSTREELEVEL, PSTREELEAF.
TREE Field Tables Page
What is the GL Chartfield element? Why use a tree?
The GL Chartfield element includes Account, Source, Fund, Department, etc. The tree maintains the parent-child relationship between tree nodes in hierarchy levels. Each tree node can have one or many leaves and each leaf represents one single value or one pair of range values. The future configuration data will become effective when the time comes based on the effective date in the tree definition. The original configuration data associated with the old effective date becomes historical data, and the new configuration data becomes the current effective value, which should be used in transaction when joining. The old historical configuration value is not deleted, but is forever kept in the system. This is so that it can be available for reference in reporting associated with the historical transactional data to reflect the true configuration in the past.
Build Chartfield EDR Request Page
What is the effective date? How do you query effective date related tables? What is Effective Date Range (EDR)? What is the performance impact of the tree with EDR involved? Do you foresee the complexity of the coding involved in order to find out the transactional data associated with the right configuration data of the right EDR with which it is associated?
In order to find out how the transactional data is related to the range information defined in the tree leaf, we need to join all those tree related tables together with all the parent-child key relationships and EDR in consideration. Since the tree is growing day by day with new effective date creation, the performance of the query will become an issue due to the complexity among tables and numbers of rows in tables. In the worst case, it can take more than an hour or two to get the information back based on the complexity of the combination of all scenario described above. Each EDR represents multiple effective dates and each effective date means one subquery is needed in order to find the correct configuration data for a specific date. If the configuration data is stored in tree, that means that we need to join the PSTREEDEFN, PSTREENODE, PSTREELEVEL and PSTREELEAF together, so that we can find the configuration data from the tree leaves. All these tree related tables are effective date sensitive tables, so there are four more subqueries that need to be used in order to find the correct configuration data based on the time of the transactional data was created.
Performance Issue on Tree
What is the root cause of the performance issue? How complicated do the multiple versions of effective date code look like? Do you know how to write the generic code for retrieving the true configuration in the past?
The performance regarding retrieving multiple versions of effective date is not the only issue. The codes also become extremely complicated and hard to maintain or extend for future enhancement. There is another issue on the tree level, which is stored in the metadata table in top down fashion, and we want them to be joined to the transactional data with the tree levels in left to right columns. This is impossible to be constructed in PS query directly without introducing a view to flip the data in 90 degrees, so that the tree level information can be displayed in columns from left to right for the underneath configuration value.
Do you know how we solve the performance issue? Our solution is flattening the tree in flatten Chartfield specific EDR tables, such as IC_TFLT_DEPTID, R_TFLT_DEPTID and etc. The flattened tables will need to be refreshed whenever we have the Chartfield element table data updated. The Chartfield element data is the configuration data applied to the system with the EDR concept and needs to be maintained in the system, so we can have the correct configuration applied to the transactional data based on the date range that needs to be applied. The end result of running a same query based on the old/new configuration can be totally different. The EDR tables option is the best candidate for tree related metadata tables, such as PSTREEDEFN, PSTREENODE, PSTREELEVEL, PSTREELEAF and etc.
What is one institution’s solution to solving this issue in detail? Read part 2 of this blog series, coming soon!
Reference Documents
https://docs.oracle.com/cd/E41507_01/epm91pbr3/eng/epm/penw/task_SettingUpandFlatteningTreeMetadata-991202.html
https://docs.oracle.com/cd/F28475_01/fscm92pbr35/eng/fscm/fspf/task_UsingTreeFlattener.html?pli=ul_d32e201_fspf