Operational Data Store

1. Operational Data Store Concepts and Components

2. What is an ODS? • Transactional Data (OLTP) • Normalized Structure • Simplify and reduce data • Join data from multiple sources • Platform for building applications • Used to source data warehouse (OLAP)

3. Row-wise comparisons • Compare many values at once • (expression [, expression…]) =,<>,IN, NOT IN (subquery) • Watch for NULLs!SELECT * FOM etl_usr_people epWHERE (id, first_name, last_name, user_name, birthdate, gender) <>( SELECT id, first_name, last_name, user_name, birthdate, gender FROM usr_people up WHERE ep.pidm = up.pidm)

4. Merge • Inserts and updates in one operation MERGE INTO ods.hr_ppms_leave_balances lb --target table USING etl_merge_leave_balances -- view using row-wise comparisons sub ON ( lb.pidm = sub.pidm AND lb.leave_type = sub.leave_type AND lb.period_begin = sub.period_begin ) WHEN MATCHED THEN UPDATE SET lb.accrued = sub.accrued, lb.taken = sub.taken, lb.adjustments = sub.adjustments, lb.ending_balance = sub.ending_balance WHEN NOT MATCHED THEN INSERT (pidm, leave_type, period_begin, period_end, accrued, taken, adjustments, ending_balance) VALUES (sub.pidm, sub.leave_type, sub.period_begin, sub.period_end, sub.accrued, sub.taken, sub.adjustments, sub.ending_balance);

5. Connect by • Joins hierarchical data w/o self-joins --Walking down SELECT u.work_unit_id, LPAD(' ', 10 * (Level-1)) || description AS descr, level FROM ods.hr_org_units u START WITH work_unit_id = 3 CONNECT BY parent_id = PRIOR work_unit_id --walking up SELECT u.work_unit_id, LPAD(' ', 10 * (Level-1)) || description AS descr, level FROM ods.hr_org_units u START WITH u.org_code = 343 CONNECT BY PRIOR parent_id =  work_unit_id

6. Connect By Results DESCRIPTION ORG_CODE LEVEL --------------------------------------------------- ----------- -------- Finance and Administration 3 1 VP for Finance and Administration 30 2 VP Finance and Administration 301 3 Commute Trip Reduction 302 3 Food Service 303 3 Campus Master Plan 304 3 Misc Activities Cash Machine 309 3 SEM II Project 397 3 Library Surge 398 3 FA Division Reserves 399 3 Director of Facilities 31 2 Director of Facilities 311 3 Director of Facilities 3111 4 Campus Safety 3112 4 Scheduling 3113 4 Tacoma Campus Facilities 3114 4 Energy Initiative 3115 4 Facilities Labor Pool 3119 4

7. ODS Naming Conventions • Schemas • ODS (ods.usr_employees, ods.hr_leave_requests) • ETL – (Extract/Transform/Load) Staging area to move data into ODS • Packages • zp_* • Views • baninst1.ods_usr_employees