Flattening PeopleSoft Summer Tree

February 20, 2012 · John Levandowski
Oracle PeopleSoft
Table of Contents

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

PeopleSoft Summer Tree

SQL to flatten the PeopleSoft summer tree

Substitute TREE_NAME with the name(s) of your summer tree(s) on line 54 below.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

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

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.

Further Reading