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

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; /

If double after split (DAS) is available, you should split pairs more often to take advantage of this rule. Here are updated rules for pair splitting when double after split is available. 7 blackjack basic strategy rules for pair splitting (DAS allowed): Always SPLIT A’s and 8’s NEVER split 5’s or T’s SPLIT 2’s and 3’s vs. dealer 2 thru 7 SPLIT 4’s vs. dealer 5 or 6 SPLIT 6’s vs dealer 6 or less SPLIT 7’s vs dealer 7 or less SPLIT 9’s vs dealer 9 or less, except STAND vs dealer 7 Here is the relevant update to the pair splitting section of the basic strategy card for when double after split (DAS) is available. ...

Learning how to play blackjack using a basic strategy card is relatively easy given the willingness to do so. Basic strategy is the optimal set of rules to follow for playing blackjack. Using simple basic strategy you will cut the house advantage for blackjack to about 0.50%. A simple blackjack strategy card is often used for memorization and as a reference at the tables when allowed by the casino. You should use a blackjack strategy card to make decisions on playing each hand in the following order: Insurance Pair Splitting Double Down (look for soft hands first) Standing (look for soft hands first) 2 rules for insurance: NEVER take Insurance NEVER take Even Money when you have a blackjack, it is the same as insurance 6 rules for pair splitting: Always SPLIT A’s and 8’s NEVER split 4’s, 5’s, or T’s SPLIT 2’s and 3’s vs. dealer 4 thru 7 SPLIT 9’s vs dealer 9 or less, except STAND vs dealer 7 SPLIT 7’s vs dealer 7 or less SPLIT 6’s vs dealer 6 or less, except HIT vs dealer 2 The table below each set of rules is the relevant section of the blackjack strategy card. You read these tables by looking at the column for the dealer upcard and the row for your hand total. ...

In 2009 I purchased my first road bike. 2009 was also the first year I specifically trained for a cycling event. My goal was to complete 100 miles in the Apple Cider Century. As you can see, I came a little short of my goal distance and only finished about 71 miles of the event. However, this was the longest I have ever bicycled in one day. ...

The Amishland and Lakes bike tour is a 2 day cycling tour of Amish countryside and Michigan lakes hosted in Howe, Indiana. In 2009 I participated in this event and completed 97 miles of cycling over a 2 day period. This is the longest (in miles and time) that I have spent on a bicycle over a 2 day period. Day 1 - Amish Countryside ...