1 / 7

Operational Data Store

Operational Data Store. Concepts and Components. 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). Row-wise comparisons.

nyssa-tillman
Télécharger la présentation

Operational Data Store

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related