Business Intelligence

Creating a workcenter in peoplesoft is a pretty easy process. In this post I will teach you a step by step process on how to configure a workcenter in peoplesoft. I completed these steps using PeopleSoft Financials 9.2 with PeopleTools 8.55.15. 1. Configure Navigation Collection PeopleTools > Portal > Portal Utilities > Navigation Collections Click Add Collection Enter a name for your navigation collection Add Links to Menu Items and Folders as needed for your navigation collection Click Publish Collection Tab Click Navigation Pagelet Checkbox Select category for pagelet in the Pagelet Category Dropdown (Common WorkCenter Pagelets is what I selected) Click Save 2. Configure WorkCenter Dashboards PeopleTools > Portal > WorkCenter > Manage WorkCenter Dashboards ...

A dimension table is one of the 2 primary building blocks of a dimensional data model. The other primary type of table is the fact table. This post will describe 7 best practices for designing a dimension table. What is the purpose of a Dimension Table A dimension table is the primary way to filter and group results from a fact table. A fact table is almost useless by itself, as you need to give some descriptive attributes to provide context to the data. Imagine if your fact table said that you had $1 million in sales. Is this good or bad? What are the sales by year and region? Only with this descriptive data from your dimension tables does the $1 million in sales give actionable meaning. The attributes in a dimension table provide the descriptive elements that appear on a report or dashboard to provide needed context. ...

There are situations where you need to keep a source and target in sync. One method to do this is to truncate and reload. However this method is not that efficient for a table with millions of rows of data. You really only want to: insert rows from the source that don’t exist in the target update rows that have changed delete rows from the target that no longer exist in the source Can you do this efficiently in a single informatica mapping? ...

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

If you are attempting to insert millions of rows into an oracle database, you may want to temporarily disable indexes and constraints to improve the speed of the load. Here are 5 steps to follow that will disable constraints and make indexes unusable. You need to substitute the OWNER and TABLE on line 5 and 6 respectively in each statement. 1. Disable Constraints 1 2 3 4 5 6 7 8 9 10 11 BEGIN FOR cur IN (SELECT OWNER, CONSTRAINT_NAME , TABLE_NAME FROM all_constraints WHERE OWNER = 'OWNER' AND TABLE_NAME = 'TABLE') LOOP EXECUTE IMMEDIATE 'ALTER TABLE '||cur.OWNER||'.'||cur.TABLE_NAME||' MODIFY CONSTRAINT "'||cur.CONSTRAINT_NAME||'" DISABLE '; END LOOP; END; / 2. Disable Indexes 1 2 3 4 5 6 7 8 9 10 11 BEGIN FOR cur IN (SELECT OWNER,INDEX_NAME FROM all_indexes WHERE TABLE_OWNER = 'OWNER' AND TABLE_NAME = 'TABLE') LOOP EXECUTE IMMEDIATE 'ALTER INDEX '||cur.OWNER||'.'||cur.INDEX_NAME||' UNUSABLE'; END LOOP; END; / 3. Insert the process to extract, transform, load your data here 4. Rebuild Indexes 1 2 3 4 5 6 7 8 9 10 11 BEGIN FOR cur IN (SELECT OWNER,INDEX_NAME FROM all_indexes WHERE TABLE_OWNER = 'OWNER' AND TABLE_NAME = 'TABLE') LOOP EXECUTE IMMEDIATE 'ALTER INDEX '||cur.OWNER||'.'||cur.INDEX_NAME||' REBUILD ONLINE'; END LOOP; END; / 5. Enable Constraints 1 2 3 4 5 6 7 8 9 10 11 BEGIN FOR cur IN (SELECT OWNER, CONSTRAINT_NAME , TABLE_NAME FROM all_constraints WHERE OWNER = 'OWNER' AND TABLE_NAME = 'TABLE') LOOP EXECUTE IMMEDIATE 'ALTER TABLE '||cur.OWNER||'.'||cur.TABLE_NAME||' MODIFY CONSTRAINT "'||cur.CONSTRAINT_NAME||'" ENABLE '; END LOOP; END; /