Flattening PeopleSoft Summer Tree
Posted on February 20, 2012 6 Comments
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
| RANGE_FROM | RANGE_TO | LEVEL1 | LEVEL2 | LEVEL3 | LEVEL4 |
|---|---|---|---|---|---|
| 10000 | 19999 | All | Assets | Assets | Assets |
| 20000 | 29999 | All | Liabilities | Liabilities | Liabilities |
| 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 |
| 59000 | 59999 | All | Expense | Personal Service | Benefit |
| 60000 | 89999 | All | Expense | NonPersonal Service | NonPersonal Service |
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!