a walk through the kimball etl subsystems with oracle data integration

info@rittmanmead.com www.rittmanmead.com @rittmanmead

Michael Rainey | Oracle OpenWorld 2015

A Walk Through the Kimball ETL Subsystems with Oracle Data Integration Solutions

Introduction

• Michael Rainey- Data Integration Practice Lead - America - Oracle Data Integration expert

• GoldenGate and Oracle Data Integrator • BI Applications 11g

- Blogger, instructor, speaker - Oracle ACE

@mRainey

About Rittman Mead

•World’s leading specialist partner for technical excellence, solutions delivery and innovation in Oracle Data Integration, Business Intelligence, Analytics and Big Data

•Providing our customers targeted expertise; we are a company that doesn’t try to do everything… only what we excel at

•70+ consultants worldwide including 1 Oracle ACE Director and 3 Oracle ACEs

•Founded on the values of collaboration, learning, integrity and getting things done

Optimizing your investment in Oracle Data Integration

•Comprehensive service portfolio designed to support the full lifecycle of any analytics solution

info@rittmanmead.com www.rittmanmead.com @rittmanmead 4

Visual Redesign Business User Training

Ongoing SupportEngagement Toolkit

Average user adoption for BI platforms is below 25%

Rittman Mead’s User Engagement Service can help

What’s Most Important for YOU in Data Integration?

• Big data?

• Big data?• Cloud?

• Financial Reporting on “one version of the truth”?

Let’s take a walk…and talk about ETL

Wait! What are Kimball ETL Subsystems?Do you all know of Ralph Kimball?

www.kimballgroup.com

Wait! What are Kimball ETL Subsystems?Do you all know of Ralph Kimball?

www.kimballgroup.com

Ralph Kimball founded the Kimball Group. Since the mid-1980s, he has been the DW/BI industry’s thought leader on the dimensional approach and trained more than 20,000 students. Prior to working at Metaphor and founding Red Brick Systems, Ralph co-invented the first commercially-available workstation with a graphical user interface at Xerox’s Palo Alto Research Center (PARC). Ralph has his Ph.D. in Electrical Engineering from Stanford University.

The Kimball GroupDo you all know of Ralph Kimball?

The Kimball 34 Subsystems of ETL

• Extracting Data - Data Profiling - Change Data Capture System - Extract System

• Cleaning and Conforming Data - Data Cleansing System - Error Event Schema - Audit Dimension Assembler - Deduplication System - Conforming System

• Delivering Data for Presentation - Slowly Changing Dimension

Manager - Surrogate Key Generator - Hierarchy Manager - Special Dimensions Manager - Fact Table Builders - Surrogate Key Pipeline - Late Arriving Data Handler

- Multi-Valued Dimension Bridge Table Builder

- Dimension Manager System - Fact Provider System - Aggregate Builder - OLAP Cube Builder - Data Propagation Manager

• Managing the ETL Environment - Job Scheduler - Backup System - Recovery and Restart System - Version Control System - Version Migration System - Workflow Monitor - Sorting System

- Lineage & Dependency Analyzer

- Problem Escalation System - Parallelizing / Pipelining System - Security System - Compliance Manager - Metadata Repository Manager

Oracle Data Integration Solutions

Copyright*©*2015,*Oracle*and/or*its*affiliates.*All*rights*reserved.**|* Oracle*Open*World*2015* 1*

NoETL*Engine*100%*NaEve*Data*TransformaEon*

Data$Integrator$

Big$Data$Prepara/on$

GoldenGate$

Data$Quality$

Data$Service$Integrator$

Metadata$Management$

NonIinvasive*CDC,*RealEme*streaming*

data*delivery*

Profile,*Cleanse,*Match,*and*

Remediate*Data*

Prepare,*Secure,*Enrich*and*Publish*Unstructured*Data*

Catalog,*Trace*and*View*Models*across*

the*Enterprise*

Federate*Data*Across*DBs,*Services*and*ApplicaEons*

Now let’s take a walk through the ETL Subsystems

Who’s coming with us?

Data model - where we’re going

Extracting Data

• Data Profiling- Oracle Enterprise Data Quality • Change Data Capture System• Extract System- Oracle Data Integrator - Oracle GoldenGate

Extracting Data

Extracting Data - Data Profiling with EDQ

• Small dataset due to sampling percentage

• _projectid looks like a primary key

• Investigate school_district blanks

Extracting Data - Oracle Data Integrator

• Extract from many different systems? Yes!

- Multiple technologies OOTB - Custom technologies can be added • Data Server - connection to the

data source- Physical Schema - Logical Schema

• Extract from many different systems? Yes!

- Multiple technologies OOTB - Custom technologies can be added • Data Server - connection to the

data source- Physical Schema - Logical Schema

• Models - Based on a single data

source • Datastores- Logically represent a

table, file, XML, etc - Reverse engineer or

build manually

StateZip Code

Extracting Data - Changed Data Only

• Change Data Capture- Extract only the changed data since the last ETL extract • Methods- Audit columns - Timed extract - Full “diff compare” - Database log scraping

Extracting Data - Changed Data Only

• Change Data Capture- Extract only the changed data since the last ETL extract • Methods- Audit columns - Timed extract - Full “diff compare” - Database log scraping

Extracting Data - CDC with Oracle GoldenGate

Extracting Data

Cleaning and Conforming Data

• Data Cleansing System- ODI & EDQ • Error Event Schema- Built on ODI E$ tables • Audit Dimension Assembler• Deduplication System- EDQ • Conforming System

Cleaning and Conforming - Data Cleansing System

• ODI - Check Knowledge Module- Check logical constraints - “Bad” data moves to error table • EDQ- Data cleansing audit processors

Cleaning and Conforming - ODI Constraints

Cleaning and Conforming - EDQ Data Cleansing

Cleaning and Conforming - Error Event Schema

Image From: Ralph Kimball. “The Data Warehouse Lifecycle Toolkit.” iBooks. https://itun.es/us/lon6z.l

SNP_LP_RUN

SNP_CONDSNP_KEYSNP_JOIN

SNP_CHECK_TAB

SNP_LP_RUN

SNP_CONDSNP_KEYSNP_JOIN

SNP_CHECK_TAB

E$ Tables

Cleaning and Conforming - Deduplication System

Cleaning and Conforming Data

• Data Cleansing System- ODI & EDQ • Error Event Schema- Built on ODI E$ tables • Audit Dimension Assembler• Deduplication System- EDQ • Conforming System

Delivering Data

• Slowly Changing Dimension Manager

• Surrogate Key Generator• Hierarchy Manager• Special Dimensions Manager• Fact Table Builders• Surrogate Key Pipeline• Late Arriving Data Handler

•Multi-Valued Dimension Bridge Table Builder•Dimension Manager System•Fact Provider System•Aggregate Builder•OLAP Cube Builder•Data Propagation Manager

Delivering Data

• Slowly Changing Dimension Manager- ODI Integration Knowledge Module - Set SCD behavior type for each

target column • Surrogate Key Generator- Database Sequence objects and ODI Sequences • Fact Table Builder- Lookups in ODI

Delivering Data - Slowly Changing Dimension in ODI

Delivering Data - SCD in ODI - Surrogate Keys

Additional audit columns

Delivering Data - Fact Table Builder

Delivering Data

• Slowly Changing Dimension Manager- ODI Integration Knowledge Module - Set SCD behavior type for each

target column • Surrogate Key Generator- Database Sequence objects and ODI Sequences • Fact Table Builder- Lookups in ODI

Managing the ETL Environment

• Job Scheduler• Backup System• Recovery and Restart System• Version Control System• Version Migration System• Workflow Monitor• Sorting System

• Lineage & Dependency Analyzer

• Problem Escalation System• Parallelizing / Pipelining

System• Security System• Compliance Manager• Metadata Repository Manager

Managing the ETL Environment - Job Scheduler

• Create ODI schedule on execution object

- Tied to an agent and context

• Limited flexibility- Custom Fiscal Month end,

for example

Managing the ETL Environment - Job Scheduler

• Alternative to ODI scheduler - external scheduling tool- ODI Scenarios and Load Plans can be executed via command

line script or web service

./startloadplan.sh LOAD_EDW GLOBAL 6 -AGENT_URL=http://localhost:20910/oraclediagent

12.2.1

Managing the ETL Environment - Version Control/Migration

• ODI 12.2.1 Lifecycle Management- Integrated with Subversion - Deployment Archives for code

migration between environments

Managing the ETL Environment - Workflow Monitor

Drilldown from ODI Session to SQL detailed activity report

Obtain real-time and historical agent statistics

Managing the ETL Environment

• Job Scheduler• Backup System• Recovery and Restart System• Version Control System• Version Migration System• Workflow Monitor• Sorting System

• Lineage & Dependency Analyzer

• Problem Escalation System• Parallelizing / Pipelining

System• Security System• Compliance Manager• Metadata Repository Manager

Where did we end up?

• The Kimball ETL Subsystems will guide your data warehouse program

• Oracle Data Integration can help you fully implement the ETL Subsystems

- Extract, Load, Transform with ODI and GoldenGate

- Profile and cleanse data with Enterprise Data Quality

Where did we end up? One version of the truth…

Questions?

• Websites:- www.kimballgroup.com - www.rittmanmead.com/blog • Contact:- info@rittmanmead.com - michael.rainey@rittmanmead.com • Twitter- @rittmanmead - @mRainey

Rittman Mead Sessions

No Big Data Hacking—Time for a Complete ETL Solution with Oracle Data Integrator 12c [UGF5827] Jérôme Françoisse | Sunday, Oct 25, 8:00am | Moscone South 301

Empowering Users: Oracle Business Intelligence Enterprise Edition 12c Visual Analyzer [UGF5481] Edelweiss Kammermann | Sunday, Oct 25, 10:00am | Moscone West 3011

A Walk Through the Kimball ETL Subsystems with Oracle Data Integration Solutions [UGF6311] Michael Rainey | Sunday, Oct 25, 12:00pm | Moscone South 301

Oracle Business Intelligence Cloud Service—Moving Your Complete BI Platform to the Cloud [UGF4906]

Mark Rittman | Sunday, Oct 25, 2:30pm | Moscone South 301

Oracle Data Integration Product Family: a Cornerstone for Big Data [CON9609]

Mark Rittman | Wednesday, Oct 28, 12:15pm | Moscone West 2022

Developer Best Practices for Oracle Data Integrator Lifecycle Management [CON9611]

Jérôme Françoisse | Thursday, Oct 29, 2:30 pm | Moscone West 2022

a walk through the kimball etl subsystems with oracle data integration

Technology

subsystems and incremental testing. subsystems testing and...

satellites subsystems

joelle - kimball

mis – subsystems

kimball thesis

kimball class2 location

data warehouse: the choice of inmon versus kimball · data...

etl process etl: overview

etl testing training | etl testing course | best etl testing...

kimball dimensional modeling techniques - kimball group

kimball class1

aiesec subsystems

kimball international

kimball 1956

etl - sozialinformatik.de · etl makes or breaks the data...

the kimball graphic. (kimball, brule county, dakota [s.d

edward l. kimball - spencer w. kimball and the revelation on...

visions for kimball creek - carbon leadership...

mpublishing, shana kimball

kimball art center