Posts

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

If you are playing at a blackjack table where late surrender is allowed, you should take advantage of the late surrender rule by slightly changing the blackjack basic strategy rules for when to stand on hard hands. Late surrender allows you to surrender your hand and lose half your bet only after the dealer checks for blackjack. Ask the blackjack dealer at your table if late surrender is allowed. Here are the updated rules for when to surrender. ...

If you are playing at a blackjack table where the dealer hits soft 17, you need to slightly change the blackjack basic strategy rules for when to double down on hard and double down on soft hands. Here are the updated rules for when to double down. 3 blackjack basic strategy rules for doubling down on hard hands when dealer hits soft 17: Always DOUBLE on 11 DOUBLE 10 vs. dealer 2 thru 9 DOUBLE 9 vs. dealer 3 thru 6 ...

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