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
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
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 |
Oracle Disable Constraints and Make Indexes Unusable
If you are attempting to insert millions of rows into an oracle database, you may want to temporarily disable constraints and make indexes unusable 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
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. Make Indexes Unusable
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
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
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;
/
Blackjack Risk of Ruin
Risk of Ruin is:
The probability that you will lose your entire bankroll
In the earlier post on Blackjack Bankroll Requirements you learned how to calculate win per hand when including comps.
- Reminder: net win per hand in units of 0.0029 was calculated in the earlier post
- If your total bankroll is $1,000 and you bet $5 per hand then your bankroll size is 200 units ($1,000 / $5)
Looking at the chart below your risk of ruin for this scenario is approximately 39.8%.
Here is another example of calculating risk of ruin based on receiving Free Rooms in Vegas.
- Betting unit size is $25 / hand
- Net Win per Hand is ( $85 in comps / 4 hours of play / 70 hands per hour / $25 betting unit size ) – 0.0060 loss from playing blackjack = 0.0061
If your bankroll is $5000 (200 units) then your risk of ruin is approximately 15.9%
If your bankroll is $7500 (300 units) then your risk of ruin is approximately 6.3%
It is critical to understand bankroll requirements when playing blackjack so that you understand the risk of losing your entire bankroll and you can size your bankroll appropriately.
One last reminder, you need to follow my 4 Rules of Blackjack Money Management for this strategy to work.
Blackjack Bankroll Requirements
You have learned how to play blackjack with basic strategy and how to receive casino comps.
How big of a bankroll do you need to take advantage of these strategies?
The term for this is Risk of Ruin. Risk of Ruin is the probability that you will lose your entire bankroll.
To calculate your needed bankroll size you need the following information:
- Net Win per Hand
- Standard Deviation per Hand
- Risk of Ruin desired (percent)
Net Win per Hand is the sum of:
- Loss from playing blackjack
- Amount received from comps = (comps received per hour) / hands per hour / betting unit size
Example:
- 2D H17 DAS game loss = -0.0046 units (decimal form of expected value %)
- Comps received = $2 / 53 hands per hour / $5 = .0075 units
- Net win per hand = -.0046 + .0075 = .0029
You need to follow my second rule of blackjack money management for these bankroll requirements to work.
Every time you receive a comp you need to add to your bankroll the cash value of the comp to replenish your bankroll. Remember you would have spent this money if you didn’t receive comps.
Standard Deviation per Hand is approximately 1.1418:
- In the post on bankroll probability fluctuation you learned that standard deviation per hand is approximately 1.1418.
My next post will finish the calculation for risk of ruin and give you an idea of the blackjack bankroll size you will need to use these comp strategies.
2011 Blog Year in Review
| 2011 Statistics | |
|---|---|
| New Posts | 90 |
| Total Posts | 112 |
| Page Views | 10,584 |
| Google Webmaster Tools (9 months) | |
| Search Clicks (web) | 2,037 |
| Search Impressions (web) | 57,688 |
| Open Site Explorer SEOmoz | |
| Domain Authority | 20 |
| Home Page Authority | 27 |
| Linking Root Domains | 14 |
Top Read Posts of 2011
- WordPress nginx proxy cache
- Las Vegas Free Play Coupons
- Blackjack Strategy Card
- Blackjack Hands per Hour
- Blackjack House Advantage – Expected Value
- Optimize WordPress for Page Speed YSlow and Zoompf
- Free Las Vegas Buffet
- Free Rooms in Vegas
- Blackjack Money Management
- Blackjack Basic Strategy – Splitting Pairs
