# Presentation: 396282

## Data Warehouse Bus Architecture 

- PMO Forum
- February 25, 2004

## Briefing Overview

- Background
- Enterprise Repository Option
- Business Area Warehouse Option
- Web Services Option
- Data Warehouse Bus Architecture
- Overview
- Components
- Next Steps / Wrap-up

## Enterprise Repository Option

**ETL Process**

**Prog. Domain**

**Admin Domain**

**P**
**O**
**R**
**T**
**A**
**L**

**Exch Data**

**CDX**

**Admin User**

**Program User**

**Admin User**

- Enterprise Repository
- w/ Common Data Registries

**ODS**

**Ent. **
**Meta-**
**data**

**ODS**

**EAI**

**ODS**

**ODS**

**ODS**

**Local Data**
** ****Modeling Tool**

- Ent
- ODS

**Custom Home Page**

**Enterprise Applications Registry**

**                     ****Single Sign-On**

**                ****Identity Management**

**                ****Access Management**

**Prog User**

**Exch User**

**Ent User**

**Exch Data**

**Custom Home Page**

**Cust Home Page**

**Cust Home Page**

**Cust Home Page**

**Cust Home Page**

**Custom Home Page**

**Appl**

**Appl**

**Appl**

**Appl**

**Tools**

**Tools**

**Tools**

**LM**

_**Legend**_

**Data**

**Query**

**Metadata**

**Local Data**
** ****Modeling Tool**

**Local Data**
** ****Modeling Tool**

**LM**

**LM**

**LM**

**Local Appl. Registry**

**Local Appl.**
**Registry**

**Appl**

**Appl**

**Notes:**
Everything stored in one data base

Enterprise data model
Data standards/common data representation

## Business Area Warehouse Option

**Enterprise Domain**

**ETL Process**

**Prog. Domain**

**Admin Domain**

**P**
**O**
**R**
**T**
**A**
**L**

**Exch Data**

**CDX**

**Admin User**

**Program User**

**Admin User**

**ODS**

**Ent. **
**Metadata**

**ODS**

**EAI**

**ODS**

**ODS**

**ODS**

**Local Data**
** ****Modeling Tool**

- Ent
- ODS

**Custom Home Page**

**Enterprise Applications Registry**

**                     ****Single Sign-On**

**                ****Identity Management**

**                ****Access Management**

**Prog User**

**Exch User**

**Ent User**

**Exch Data**

**Custom Home Page**

**Cust Home Page**

**Cust Home Page**

**Cust Home Page**

**Cust Home Page**

**Custom Home Page**

**Appl**

**Appl**

**Appl**

**Appl**

**Tools**

**Tools**

**Tools**

**LM**

_**Legend**_

**Data**

**Query**

**Metadata**

**Local Data**
** ****Modeling Tool**

**Local Data**
** ****Modeling Tool**

**LM**

**LM**

**LM**

**Local Appl. Registry**

**Local Appl.**
**Registry**

**Appl**

**Appl**

- Enterprise Warehouse
- w/ Common Data Reg.

- Bus Area
- Warehouse

- Bus Area
- Warehouse

- Admin
- Warehouse

**ETL Process**

**ETL Process**

**ETL Process**

**Notes:**
Data stored in individual business area warehouses

Data standards/common data representation
Enterprise data model across multiple data bases
Data integrated “VIRTUALLY” not physically

## Web Services Option

**Enterprise Domain**

**ETL Process**

**Prog. Domain**

**Admin Domain**

**P**
**O**
**R**
**T**
**A**
**L**

**Exch Data**

**CDX**

**Admin User**

**Program User**

**Admin User**

**ODS**

**Ent. **
**Metadata**

**ODS**

**EAI**

**ODS**

**ODS**

**ODS**

**Local Data**
** ****Modeling Tool**

- Ent
- ODS

**Custom Home Page**

**Enterprise Applications Registry**

**                     ****Single Sign-On**

**                ****Identity Management**

**                ****Access Management**

**Prog User**

**Exch User**

**Ent User**

**Exch Data**

**Custom Home Page**

**Cust Home Page**

**Cust Home Page**

**Cust Home Page**

**Cust Home Page**

**Custom Home Page**

**Appl**

**Appl**

**Appl**

**Appl**

**Tools**

**Tools**

**Tools**

**LM**

_**Legend**_

**Data**

**Query**

**Metadata**

**Local Data**
** ****Modeling Tool**

**Local Data**
** ****Modeling Tool**

**LM**

**LM**

**LM**

**Local Appl. Registry**

**Local Appl.**
**Registry**

**Appl**

**Appl**

- Enterprise Warehouse
- w/ Common Data Reg.

- Bus Area
- Warehouse

- Bus Area
- Warehouse

- Admin
- Warehouse

**ETL Process**

**WS**

**WS**

**WS**

**WS**

**ETL Process**

**ETL Process**

**Notes:**
Individual warehouses expose their data via Web Services

Data standards/common data representation
Enterprise data model across multiple data bases
Integration performed by the query or analytical application – not in the database

## Data Warehouse Bus Option

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Archive**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
The architecture is a Data Warehouse Bus Architecture:

Program Offices utilizing their program system for their day-to-day-work, and utilizing:

(*****) a common Data Integration Platform (ETL tool) to create
(*****) Data Marts tailored to meet specific analytical needs and
(*****) Business Intelligence tools to enhance the analytical capabilities of the Data Marts.

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Archive**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
The Data Integration Platform does the following:

Source Analysis and Extraction
Data Staging (if necessary)
Transformation, Cleansing, and Loading

Based on a Central Metadata Registry with common data modeling tools

## WH Bus Architecture

- Target Applications Architecture Components

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
Everything goes through the Portal:

Single sign-on
Identity Management
Access Management
Customizable Home Pages/Portlets

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
Data comes in through CDX

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
And, of course, we know it is a two-way flow.

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
And we know there are always other internal data that do not come in through CDX.

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
Business Areas utilize the ETL tool to extract and load desired data into the Data Mart(s)  (*****)

And may also use data from other source systems (*****)

Including data from the Common Data Registries. (*****)

The ETL tools is serving as the data bus to build the data marts.

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**BI Tool**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Archive**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
(*****) The Business Intelligence tool is used to access the data marts and return the desired results to the business user – THROUGH THE PORTAL.

(*****) The Business Intelligence Tool might also drill back through the ETL tool into the source systems for more detailed data, if necessary.

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Appl Registry**

**Appl**

**Appl**

**Notes:**
Programs/business areas might also use custom analytical applications, queries, and reports to access the data marts.

(*****) and will access the source systems for day-to-day operations.

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
Business Areas might use the ETL tool to access external Web Services to get desired data into the Data Mart (*****)

Or may access external Web Services as part of an analytical application (*****)

FOLLOWING appropriate Identity and Access Management protocols.

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**Notes:**
Programs that do not have their own operational systems, or rely predominantly on program systems from other business areas (e.g., indicators, Regions, etc.) can use the ETL tool to create Data Marts to support their business needs (*****)

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
Envirofacts with continue to extract data in the normal way, then utilize the ETL tool to create a Public Access Data Mart (*****)

As each of the Envirofacts component systems come online with the ETL tool (*****)  Envirofacts will be eliminated and the Public Access Data Mart will be populated directly from the source systems. (NEW SLIDE)

## E&HP Domain

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

## EF

**Exchange Domain**

**ETL Tool**

**Exch**
** ****Node**

**CDX**
**Proc**

**Data Integration Platform**

**BI Tool**

**E**
**x**
**t**
**r**
**a**
**c**
**t**
**i**
**o**
**n**

**WH**
**Admin**
**Tools**

**S**
**t**
**a**
**g**
**i**
**n**
**g**

**Trans-**
**form**
**Cleanse**
**Load**

**Cent**
**Meta-**
**data**

**Appl Registry**

**CDX **
**Node**

**Appl**

**Appl**

**LM**

**P**
**O**
**R**
**T**
**A**
**L**

**Custom Home Page**

**In**

**Out**

**Cust HP**

**Single Sign-On**

**Identity Management**

**Access Management**

**Access**

**Data Model Tool**

**Data Cleans Tool**

**Archive**

**TRIS**

**LM**

**AQS**

**LM**

**Transitional**

**Trans-**
**form**
**Cleanse**
**Load**

**EF**

**LM**

**Common Data**

**FRS**

**SRS**

**LM**

**DM**

**LM**

**Cust HP**

**Cust HP**

**Cust HP**

**In**

**Out**

**In**

**Out**

**In**

**Out**

**WS Data**

**EF Data**

**Notes:**
The Data Integration Platform REQUIRES a Central Metadata Registry (*****) ACTIVELY linked to each of the local Metadata Registries (*****) and utilizing a common Data Modeling Tool. (*****)

## Data Warehouse Bus Option

## Next Steps/Wrap-up

## Contact Information:Bill Grabsch – grabsch.bill@epa.gov