Flattening PeopleSoft Summer Tree

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.

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.

6 thoughts on “Flattening PeopleSoft Summer Tree

  1. David

    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!

    Like

  2. David

    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!

    Like

    1. 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.

      Like

      1. David

        Oh interesting. I will have to look more into the different types of trees. Thanks again for your help! I’ll be back often.

        Like

  3. Ferdinand Santos

    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!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.