If you need to denormalize or flatten PeopleSoft summer trees (trees with leaves) in your data warehouse, here is an ETL process for you to follow.
Typical PeopleSoft summer tree structure
SQL to flatten the PeopleSoft summer tree
Substitute TREE_NAME with the name(s) of your summer tree(s) on line 54 below.
SELECT SQ4.SETID, SQ4.TREE_NAME, SQ4.EFFDT, SQ4.RANGE_FROM, SQ4.RANGE_TO, SQ4.LEVEL1, SQ4.LEVEL2, SQ4.LEVEL3, DECODE(SQ4.LVL4,'',SQ4.LEVEL3,SQ4.LVL4) AS LEVEL4 FROM (SELECT SQ3.SETID, SQ3.TREE_NAME, SQ3.EFFDT, SQ3.RANGE_FROM, SQ3.RANGE_TO, SQ3.LEVEL1, SQ3.LEVEL2, DECODE(SQ3.LVL3,'',SQ3.LEVEL2,SQ3.LVL3) AS LEVEL3, SQ3.LVL4 FROM (SELECT SQ2.SETID, SQ2.TREE_NAME, SQ2.EFFDT, SQ2.RANGE_FROM, SQ2.RANGE_TO, SQ2.LEVEL1, DECODE(SQ2.LVL2,'',SQ2.LEVEL1,SQ2.LVL2) AS LEVEL2, SQ2.LVL3, SQ2.LVL4 FROM (SELECT SQ1.SETID, SQ1.TREE_NAME, SQ1.EFFDT, SQ1.RANGE_FROM, SQ1.RANGE_TO, MAX(SQ1.L1) AS LEVEL1, MAX(SQ1.L2) AS LVL2, MAX(SQ1.L3) AS LVL3, MAX(SQ1.L4) AS LVL4 FROM (SELECT A.SETID, A.TREE_NAME, A.EFFDT, C.RANGE_FROM, C.RANGE_TO, DECODE(A.TREE_LEVEL_NUM,1, D.DESCR,'') AS L1, DECODE(A.TREE_LEVEL_NUM,2, D.DESCR,'') AS L2, DECODE(A.TREE_LEVEL_NUM,3, D.DESCR,'') AS L3, DECODE(A.TREE_LEVEL_NUM,4, D.DESCR,'') AS L4 FROM PSTREENODE A, PSTREENODE B, PSTREELEAF C, PS_TREE_NODE_TBL D WHERE A.TREE_NAME IN ('TREE_NAME') AND A.SETID = B.SETID AND A.TREE_NAME = B.TREE_NAME AND B.EFFDT = A.EFFDT AND B.TREE_NODE_NUM BETWEEN A.TREE_NODE_NUM AND A.TREE_NODE_NUM_END AND B.SETID = C.SETID AND B.TREE_NAME = C.TREE_NAME AND B.TREE_NODE_NUM = C.TREE_NODE_NUM AND C.EFFDT = A.EFFDT AND A.SETID = D.SETID AND A.TREE_NODE = D.TREE_NODE AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_TREE_NODE_TBL D_ED WHERE D.SETID = D_ED.SETID AND D.TREE_NODE = D_ED.TREE_NODE AND D_ED.EFFDT <= SYSDATE)) SQ1 GROUP BY SQ1.SETID, SQ1.TREE_NAME, SQ1.EFFDT, SQ1.RANGE_FROM, SQ1.RANGE_TO) SQ2) SQ3) SQ4 ORDER BY SQ4.RANGE_FROM;
Result of the SQL
|30000||39999||All||Fund Balance||Fund Balance||Fund Balance|
|40000||47999||All||Revenue||Operating Revenues||Operating Revenues|
|48000||48999||All||Revenue||Nonoperating Revenues||Nonoperating Revenues|
|49000||49999||All||Revenue||Operating Revenues||Operating Revenues|
|50000||58999||All||Expense||Personal Service||Salary and Wage|
|60000||89999||All||Expense||NonPersonal Service||NonPersonal Service|
If you don’t know SQL or need to learn more about SQL, I would recommend that you buy Learning SQL by Alan Beaulieu. It will help you better understand the SQL provided above and give you the tools you need to change it for your needs.
6 thoughts on “Flattening PeopleSoft Summer Tree”
Hello. Thank you for this excellent code, but I’m having a little bit of trouble getting it to work. But I think once I do, it will help me out greatly.
The first problem I had was line 70 which I fixed by replacing it with D.EFFDT = D_ED.EFFDT.
The next problem I’m having that I can’t seem to fix is with line 75 and the closing parentheses. It looks like there should be an additional one including SQ1 but that doesn’t seem to work for me. Any thoughts? Thanks so much!
Thanks for the question. There was a small error in the code on line 70. I have updated it to be correct: “AND D_ED.EFFDT <= SYSDATE)) SQ1".
John, thanks so much for the fast reply. That did indeed fix the syntax errors, but I have another question. (BTW: I have an external view into a legacy PS database and am not very familiar with how they are structured. I am hoping to use your code to get a better understanding of how I can de-normalize the data).
With that said, do you have any suggested steps that I could take if the PS_TREE_NODE_TBL is empty? Thank you so much for your help. I’ve also been looking around your blog and it has a lot of great information that I will be using!
The PS_TREE_NODE_TBL is used to store the description for the node in the tree. So if this is not populated then the tree may not be a summer tree, but another type of tree which can be more difficult to denormalize. I may need to write another post on this topic.
Oh interesting. I will have to look more into the different types of trees. Thanks again for your help! I’ll be back often.
Hi! First of all, thank you so much for sharing this code. It worked perfectly for me. I have very limited knowledge of SQL and I would really appreciate it if you can post another version of this code for a 5-, 6-, and 7-level tree. Thanks in advance!