Data Warehouse ArchitectureSakthi Angappamudali

Data Architect, The Standard

@ Oregon State University, Corvallis

16th May, 2005

What is a Data Warehouse?

A Data Warehouse is a

•Subject Oriented

•Integrated

•Non-volatile

•Time variant

collection of detailed and summary data used to support the strategic decision making process for the enterprise

Characteristics of a Data Warehouse

A Data Warehouse has the following characteristics:

Purpose - Decision SupportUsers - Operational personnel, Analysts, StrategistsOrientation - Discovery-orientedIntegration - Maximum integrationData Quality - EnhancedData Enrichment - StrategicVolatility - Non-volatileChronology - Time-variantGranularity - Detailed and summary

The Decision Making Roadmap

TransactionSystems

DecisionSupport Systems

ExecutiveInformation

Systems

Business Planning

RUNRUN MANAGEMANAGE GROWGROW

UsersUsers Knowledge BrokersKnowledge Brokers ManagementManagement

• Operational• Functional• Current• Detailed

• Dimensional• Subject• History• Summary

• Analytical• Subject• History• Detailed

DataData InformationInformation

KnowledgeKnowledge

VisionVisionActionsActions

•Design•Mapping

•Design•Mapping

Source OLTPSystems

• Raw Detail• No/Minimal History

• Integrated• Scrubbed

• History• Summaries

• Targeted• Specialized (OLAP)

Data Characteristics

System MonitoringSystem Monitoring

Meta DataMeta Data

•Extract•Scrub•Transform

•Extract•Scrub•Transform

CentralRepository

•Load•Index•Aggregation

•Load•Index•Aggregation

DataWarehouse

ArchitectedData Mart

•Replication•Data Set Distribution

•Replication•Data Set Distribution

•Access & Analysis•Resource Scheduling & Distribution

•Access & Analysis•Resource Scheduling & Distribution

End UserWorkstations

A Data Warehouse Is A Process

Types of Warehousing Solutions

• Operational Data Store (ODS)– integrated, current, detailed data for operational

activities

• Corporate Information Factory (CIF)– integrated, historic, summary and detailed data

for company-wide data analysis

• Data Mart (DM)– independent, historic, summary data for a small

group of business users analyzing a specific business process

OperationalSource

Systems Extraction

Systems

OperationalData Store

IndependentData Mart

DataWarehouse

ArchitectedData Mart

UserWorkstations

There Are Many Options

Solution Choices - ODS

• Run your business (Operational Data Store) – Tactical

– Perform functions not supported in transaction systems– Perform operational reporting (without impacting “real” system)– Data is currently valued (could be real-time as well)– Detail data analysis capabilities– Subject oriented along the lines of the major entities of the

corporation– Integrated (physical unification and cohesiveness of the data– Volatile - can be updated as a normal part of processing– Detailed - contains detailed data only

Solution Choices - DW• Manage your business (Enterprise Warehouse, Data Marts) –

Strategic

– No business functions are performed (read only)– Aggregations are maintained– Measures and dimensions are defined for slice and dice

capabilities– History is maintained for trend analysis– On-line Analytical Processing (OLAP) model

A typical E-R Model

INT_COUNTRY_FK03

INT_SECURITY_FK24

INT_COUNTRY_FK74INT_COUNTRY_FK73

INT_CURRENCY_FK64INT_CURRENCY_FK63

INT_COUNTRY_FK38

INT_COUNTRY_FK37

INT_INDICES_FK19

INT_INDEX_TYPE_FK01

INT_COUNTRY_FK29

INT_INDUSTRY_HIER_LVL_FK44

INT_REGION_COUNTRY_DETCOUNTRY_ID: VARCHAR2(25): String NOT NULL (FK)REGION_ID: VARCHAR2(25): id_type NOT NULL (FK)EFFECTIVE_START_DT: DATE: EFFECTIVE_START_DT NOT NULL

EFFECTIVE_END_DT: DATE: Datetime NULLDW_PUBLISH_IN: VARCHAR2(1): String NULLCREATE_UI: VARCHAR2(25): CREATE_UI NOT NULLCREATE_TS: DATE: <default> NULLUPDATE_UI: VARCHAR2(25): UPDATE_UI NULLUPDATE_TS: DATE: Datetime NULLCURRENT_REC_IN: VARCHAR2(1): String NULLSTATUS_IN: VARCHAR2(1): STATUS_IN NULL

INT_SECURITYSECURITY_ID: VARCHAR2(25): String NOT NULL

SECURITY_NM: VARCHAR2(100): String NULLSEDOL_ID: VARCHAR2(25): String NULLCUSIP_ID: VARCHAR2(25): String NULLISIN_ID: VARCHAR2(25): String NULLORIGIN_COUNTRY_ID: VARCHAR2(25): String NULL (FK)TRADE_COUNTRY_ID: VARCHAR2(25): String NOT NULL (FK)REGISTRATION_COUNTRY_ID: VARCHAR2(25): String NOT NULL (FK)INCORPORATION_COUNTRY_ID: VARCHAR2(25): String NULL (FK)COUNTRY_CURRENCY_ID: VARCHAR2(25): String NULL (FK)TRADE_CURRENCY_ID: VARCHAR2(25): String NULL (FK)TICKER_SYMBOL_ID: VARCHAR2(25): String NULLSECURITY_TYPE_ID: VARCHAR2(25): String NULLISSUER_ID: VARCHAR2(25): id_type NULLCHASE_ID: VARCHAR2(25): id_type NULLEFFECTIVE_START_DT: DATE: Datetime NULLEFFECTIVE_END_DT: DATE: Datetime NULLDUMMY_IN: VARCHAR2(1): indicator_type NULLFUND_SECURITY_IN: VARCHAR2(1): indicator_type NULLDW_PUBLISH_IN: VARCHAR2(1): String NULLCREATE_UI: VARCHAR2(25): CREATE_UI NOT NULLCREATE_TS: DATE: Datetime NULLUPDATE_UI: VARCHAR2(25): UPDATE_UI NULLUPDATE_TS: DATE: Datetime NULLOVERRIDE_UI: VARCHAR2(25): String NULLOVERRIDE_RSN_ID: VARCHAR2(25): String NULLCURRENT_REC_IN: VARCHAR2(1): String NULLSTATUS_IN: VARCHAR2(1): STATUS_IN NULL

INT_INDICESINDEX_ID: VARCHAR2(25): String NOT NULLVENDOR_ID: VARCHAR2(25): String NOT NULL (FK)VENDOR_TYPE_ID: VARCHAR2(25): String NOT NULL (FK)

SOURCE_INDEX_ID: VARCHAR2(25): id_type NOT NULLINDEX_TYPE_ID: VARCHAR2(25): id_type NOT NULL (FK)REGION_ID: VARCHAR2(25): id_type NULL (FK)COUNTRY_ID: VARCHAR2(25): String NULL (FK)INDEX_CATEGORY_ID: VARCHAR2(25): id_type NULL (FK)INSTRUMENT_TYPE_HIER_SCHEME_ID: VARCHAR2(25): id_type NULL (FK)INSTRUMENT_HIER_LEVEL_ID: VARCHAR2(25): String NULL (FK)INDEX_SHORT_NM: VARCHAR2(100): String NULLINDEX_LONG_NM: VARCHAR2(100): HIER_LEVEL_NM NULLCASH_RETURN_IN: VARCHAR2(1): indicator_type NULLDAILY_FEED_IN: VARCHAR2(1): indicator_type NULLRISK_FREE_IN: VARCHAR2(1): indicator_type NULLCUSTOM_INDEX_IN: VARCHAR2(1): indicator_type NULLREGION_IN: VARCHAR2(1): indicator_type NULLCOUNTRY_IN: VARCHAR2(1): indicator_type NULLGENERAL_IN: VARCHAR2(1): indicator_type NULLREINSTATE_RETURN_IN: VARCHAR2(1): indicator_type NULLINDEX_OPEN_DT: DATE: Datetime NULLINDEX_CLOSE_DT: DATE: Datetime NULLEFFECTIVE_START_DT: DATE: EFFECTIVE_START_DT NOT NULLEFFECTIVE_END_DT: DATE: EFFECTIVE_END_DT NULLDW_PUBLISH_IN: VARCHAR2(1): String NULLCREATE_UI: VARCHAR2(25): CREATE_UI NOT NULLCREATE_TS: DATE: CREATE_TS NOT NULLUPDATE_UI: VARCHAR2(25): UPDATE_UI NULLUPDATE_TS: DATE: Datetime NULLOVERRIDE_UI: VARCHAR2(25): String NULLOVERRIDE_REASON_ID: VARCHAR2(25): String NULLCURRENT_REC_IN: VARCHAR2(1): String NULLSTATUS_IN: VARCHAR2(1): STATUS_IN NULL

INT_COUNTRYCOUNTRY_ID: VARCHAR2(25): String NOT NULL

COUNTRY_NM: VARCHAR2(100): String NULLEFFECTIVE_START_DT: DATE: Datetime NULLEFFECTIVE_END_DT: DATE: Datetime NULLDW_PUBLISH_IN: VARCHAR2(1): String NULLCREATE_UI: VARCHAR2(25): CREATE_UI NULLCREATE_TS: DATE: Datetime NULLUPDATE_UI: VARCHAR2(25): UPDATE_UI NULLUPDATE_TS: DATE: Datetime NULLCURRENT_REC_IN: VARCHAR2(1): String NULLSTATUS_IN: VARCHAR2(1): STATUS_IN NULL

INT_CURRENCYCURRENCY_ID: VARCHAR2(25): String NOT NULL

CURRENCY_NM: VARCHAR2(100): String NULLREPORTING_CURRENCY_IN: VARCHAR2(1): indicator_type NULLCURRENCY_SYMBOL_CD: VARCHAR2(25): id_type NULLEFFECTIVE_START_DT: DATE: Datetime NULLEFFECTIVE_END_DT: DATE: Datetime NULLDW_PUBLISH_IN: VARCHAR2(1): String NULLCREATE_UI: VARCHAR2(25): CREATE_UI NULLCREATE_TS: DATE: Datetime NULLUPDATE_UI: VARCHAR2(25): UPDATE_UI NULLUPDATE_TS: DATE: Datetime NULLCURRENT_REC_IN: VARCHAR2(1): String NULLSTATUS_IN: VARCHAR2(1): STATUS_IN NULL

INT_INDUSTRY_HIER_LVLINDUSTRY_HIER_SCHEME_ID: VARCHAR2(25): id_type NOT NULL (FK)INDUSTRY_HIER_LEVEL_ID: VARCHAR2(25): String NOT NULL

INDUSTRY_HIERARCHY_LEVEL1_ID: VARCHAR2(25): id_type NULLINDUSTRY_HIERARCHY_LEVEL1_DE: VARCHAR2(100): description_type NULLINDUSTRY_HIERARCHY_LEVEL2_ID: VARCHAR2(25): id_type NULLINDUSTRY_HIERARCHY_LEVEL2_DE: VARCHAR2(100): description_type NULLINDUSTRY_HIERARCHY_LEVEL3_ID: VARCHAR2(25): id_type NULLINDUSTRY_HIERARCHY_LEVEL3_DE: VARCHAR2(100): description_type NULLINDUSTRY_HIERARCHY_LEVEL4_ID: VARCHAR2(25): id_type NULLINDUSTRY_HIERARCHY_LEVEL4_DE: VARCHAR2(100): description_type NULLINDUSTRY_HIERARCHY_LEVEL5_ID: VARCHAR2(25): id_type NULLINDUSTRY_HIERARCHY_LEVEL_NO: NUMBER: Number NULLINDUSTRY_HIERARCHY_LEVEL_DE: VARCHAR2(100): description_type NULLINDUSTRY_HIERARCHY_LEVEL5_DE: VARCHAR2(100): description_type NULLINDUSTRY_HIERARCHY_LEVEL6_ID: VARCHAR2(25): id_type NULLINDUSTRY_HIERARCHY_LEVEL6_DE: VARCHAR2(100): description_type NULLDW_PUBLISH_IN: VARCHAR2(1): String NULLEFFECTIVE_START_DT: DATE: EFFECTIVE_START_DT NOT NULLEFFECTIVE_END_DT: DATE: EFFECTIVE_END_DT NULLCREATE_TS: DATE: CREATE_TS NOT NULLCREATE_UI: VARCHAR2(25): CREATE_UI NOT NULLUPDATE_TS: DATE: UPDATE_TS NULLUPDATE_UI: VARCHAR2(25): UPDATE_UI NULLCURRENT_REC_IN: VARCHAR2(1): String NULLSTATUS_IN: VARCHAR2(1): STATUS_IN NULL

INT_INDEX_TYPEINDEX_TYPE_ID: VARCHAR2(25): id_type NOT NULL

INDEX_TYPE_NM: VARCHAR2(100): HIER_LEVEL_NM NULLEFFECTIVE_START_DT: DATE: EFFECTIVE_START_DT NOT NULLEFFECTIVE_END_DT: DATE: EFFECTIVE_END_DT NULLDW_PUBLISH_IN: VARCHAR2(1): DW_PUBLISH_IN NULLCREATE_UI: VARCHAR2(25): CREATE_UI NOT NULLCREATE_TS: DATE: CREATE_TS NOT NULLUPDATE_UI: VARCHAR2(25): UPDATE_UI NULLUPDATE_TS: DATE: UPDATE_TS NULLCURRENT_REC_IN: VARCHAR2(1): CURRENT_REC_IN NULLSTATUS_IN: VARCHAR2(1): STATUS_IN NULL

INT_INDEX_SECURITY_DETINDEX_ID: VARCHAR2(25): String NOT NULL (FK)VENDOR_ID: VARCHAR2(25): String NOT NULL (FK)VENDOR_TYPE_ID: VARCHAR2(25): String NOT NULL (FK)SECURITY_ID: VARCHAR2(25): String NOT NULL (FK)EFFECTIVE_START_DT: DATE: EFFECTIVE_START_DT NOT NULL

INSTRUMENT_TYPE_HIER_SCHEME_ID: VARCHAR2(25): id_type NOT NULL (FK)INSTRUMENT_HIER_LEVEL_ID: VARCHAR2(25): String NOT NULL (FK)INDUSTRY_HIER_SCHEME_ID: VARCHAR2(25): id_type NOT NULL (FK)INDUSTRY_HIER_LEVEL_ID: VARCHAR2(25): String NOT NULL (FK)EFFECTIVE_END_DT: DATE: EFFECTIVE_END_DT NULLDW_PUBLISH_IN: VARCHAR2(1): DW_PUBLISH_IN NULLCREATE_UI: VARCHAR2(25): CREATE_UI NOT NULLCREATE_TS: DATE: CREATE_TS NOT NULLUPDATE_UI: VARCHAR2(25): UPDATE_UI NULLUPDATE_TS: DATE: UPDATE_TS NULLOVERRIDE_UI: VARCHAR2(25): OVERRIDE_UI NULLOVERRIDE_REASON_ID: VARCHAR2(25): OVERRIDE_REASON_ID NULLCURRENT_REC_IN: VARCHAR2(1): CURRENT_REC_IN NULLSTATUS_IN: VARCHAR2(1): STATUS_IN NULL

A typical Star Schema

ACCT_CUST_ASST_REP_ASSOCBUS_UT_KY: NUMBER (FK)ACCOUNT_KY: NUMBER (FK)ASSOC_TYPE_KY: NUMBER (FK)CUSTOM_ASSET_REP_SCHEME_KY: NUMBER (FK)ASSOC_START_DATE_KY: NUMBER (FK)ASSOC_END_DATE_KY: NUMBER (FK)

CREATE_UI: VARCHAR2(25)CREATE_TS: DATEUPDATE_UI: VARCHAR2(25)UPDATE_TS: DATEOVERRIDE_UI: VARCHAR2(25)OVERRIDE_REASON_ID: VARCHAR2(25)CURRENT_REC_IN: VARCHAR2(1)STATUS_IN: VARCHAR2(1)

CALENDAR_DATEDATE_KY: NUMBER

CALENDAR_DT: DATEDAY_OF_DT: VARCHAR2(100)DAY_NUMBER_OVERALL_NO: NUMBERWEEK_NUMBER_IN_YEAR_NO: NUMBERDAY_OF_WEEK_NO: NUMBERWEEK_NUMBER_OVERALL_NO: NUMBERMONTH_NO: NUMBERMONTH_NM: VARCHAR2(100)DAY_NUMBER_IN_MONTH_NO: NUMBERMONTH_NUMBER_OVERALL_NO: NUMBERLAST_DAY_IN_MONTH_IN: VARCHAR2(1)LAST_DAY_OF_MONTH_DT: DATEQUARTER_NM: VARCHAR2(100)QUARTER_NO: NUMBERYEAR_OF_DT: NUMBERDATE_FORMAT_TX: VARCHAR2(25)DAY_OF_WEEK_NM: VARCHAR2(100)CREATE_UI: VARCHAR2(25)CREATE_TS: DATEUPDATE_UI: VARCHAR2(25)UPDATE_TS: DATEOVERRIDE_UI: VARCHAR2(25)OVERRIDE_REASON_ID: VARCHAR2(25)

BUSINESS_UNITBUS_UT_KY: NUMBER

BUS_UT_ID: VARCHAR2(25) (AK1.1)BUS_UT_NM: VARCHAR2(100)BUS_UT_LONG_NM: VARCHAR2(100)PM_SYS_REGION_ID: VARCHAR2(25)PM_SYS_REGION_NM: VARCHAR2(100)BUS_UT_MTHLY_PERF_STR_DAY_NO: NUMBERBUS_UT_MTHLY_PERF_END_DAY_NO: NUMBERBUS_UT_COUNTRY_ID: VARCHAR2(25)BUS_UT_COUNTRY_NM: VARCHAR2(100)BUS_UT_CONTACT_FIRST_NM: VARCHAR2(100)BUS_UT_CONTACT_LAST_NM: VARCHAR2(100)BUS_UT_CONTACT_TTL_TX: VARCHAR2(100)BUS_UT_CONTACT_LINE1_AD: VARCHAR2(100)BUS_UT_CONTACT_LINE2_AD: VARCHAR2(100)BUS_UT_CONTACT_LINE3_AD: VARCHAR2(100)BUS_UT_CONTACT_CITY_NM: VARCHAR2(100)BUS_UT_CONTACT_ZIP_CD: VARCHAR2(25)BUS_UT_CONTACT_STATE_NM: VARCHAR2(100)BUS_UT_CONTACT_COUNTRY_NM: VARCHAR2(100)BUS_UT_CONTACT_EMAIL_AD: VARCHAR2(100)BUS_UT_CONTACT_WORK_PHONE_NO: VARCHAR2(100)BUS_UT_CONTACT_EXTN_NO: VARCHAR2(100)BUS_UT_CONTACT_CELL_NO: VARCHAR2(100)BUS_UT_CONTACT_FAX_NO: VARCHAR2(100)INVEST_MGR_BUS_UT_IN: VARCHAR2(1)FISCAL_REPRESENT_IN: VARCHAR2(1)INTERMEDIATE_BUS_UT_IN: VARCHAR2(1)BUS_UT_STATUS_IN: VARCHAR2(1)ROR_DECIMAL_PRECISION_CT: NUMBERCASH_RETURN_REQD_IN: VARCHAR2(1)MKT_VALUE_DECIMAL_PRECISION_CT: NUMBERAFTER_TAX_RETURNS_IN: VARCHAR2(1)DEFINED_IN_ACCOUNTING_IN: VARCHAR2(1)AIMR_COMPLIANCE_IN: VARCHAR2(1)COUNTRY_LEVEL_REPORT_IN: VARCHAR2(1)PERFORMANCE_ONLY_IN: VARCHAR2(1)FEED_TYPE_ID: VARCHAR2(25)FEED_TYPE_NM: VARCHAR2(100)EFFECTIVE_START_DT: DATE (AK1.2)EFFECTIVE_END_DT: DATECREATE_UI: VARCHAR2(25)CREATE_TS: DATEUPDATE_UI: VARCHAR2(25)UPDATE_TS: DATEOVERRIDE_UI: VARCHAR2(25)OVERRIDE_REASON_ID: VARCHAR2(25)CURRENT_REC_IN: VARCHAR2(1)STATUS_IN: VARCHAR2(1)

ACCOUNTACCOUNT_KY: NUMBER

ACCOUNT_ID: VARCHAR2(25) (AK1.2)BUS_UT_ID: VARCHAR2(25) (AK1.1)ACCOUNT_SHORT_NM: VARCHAR2(100)ACCOUNT_LONG_NM: VARCHAR2(100)ACCOUNT_SYS_ID: VARCHAR2(25)ACCOUNT_SYS_NM: VARCHAR2(100)ACCOUNT_SYS_REGION_ID: VARCHAR2(25)ACCOUNT_SYS_REGION_NM: VARCHAR2(100)ACCOUNT_SYS_RELATIONSHIP_ID: VARCHAR2(25)ACCOUNT_SYSTEM_RELATIONSHIP_NM: VARCHAR2(100)ACCOUNT_SRC_SYS_ID: VARCHAR2(25)ACCOUNT_SRC_SYS_NM: VARCHAR2(100)PERFORMANCE_VENDOR_ID: VARCHAR2(25)PERFORMANCE_VENDOR_NM: VARCHAR2(100)PERFORMANCE_VENDOR_TYPE_ID: VARCHAR2(25)ACCOUNT_OPEN_DT: DATEACCOUNT_CLOSE_DT: DATEFUTURES_DOMINATED_IN: VARCHAR2(1)ACCOUNT_STATUS_IN: VARCHAR2(1)EFFECTIVE_START_DT: DATE (AK1.3)EFFECTIVE_END_DT: DATEPERFORMANCE_START_DT: DATEHIST_IRR_INCEPTION_DT: DATEIVS_MGT_FIRM_ID: VARCHAR2(25)IVS_MGT_FIRM_NM: VARCHAR2(100)REPORTED_IN: VARCHAR2(1)BALANCED_ACCOUNT_IN: VARCHAR2(1)AFTER_TAX_RETURNS_IN: VARCHAR2(1)AIMR_COMPLIANCE_IN: VARCHAR2(1)DISCRETIONARY_ACCT_IN: VARCHAR2(1)COUNTRY_LEVEL_REPORT_IN: VARCHAR2(1)MANAGER_FIRE_DT: DATECREATE_UI: VARCHAR2(25)CREATE_TS: DATEUPDATE_UI: VARCHAR2(25)UPDATE_TS: DATEOVERRIDE_UI: VARCHAR2(25)OVERRIDE_REASON_ID: VARCHAR2(25)CURRENT_REC_IN: VARCHAR2(1)

CODE_TYPECODE_TYPE_KY: NUMBER

CODE_TYPE_ID: VARCHAR2(25)CODE_TYPE_NM: VARCHAR2(100)CODE_TYPE_CAT_DE: VARCHAR2(100)EFFECTIVE_START_DT: DATEEFFECTIVE_END_DT: DATECREATE_UI: VARCHAR2(25)CREATE_TS: DATEUPDATE_UI: VARCHAR2(25)UPDATE_TS: DATEOVERRIDE_UI: VARCHAR2(25)OVERRIDE_REASON_ID: VARCHAR2(25)CURRENT_REC_IN: VARCHAR2(1)

CUSTOM_ASSET_REP_SCHEMECUSTOM_ASSET_REP_SCHEME_KY: NUMBER

CUSTOM_ASSET_REP_SCHEME_NM: VARCHAR2(100)HIGHEST_LEVEL_NO: NUMBERCREATE_UI: VARCHAR2(25)CREATE_TS: DATEUPDATE_UI: VARCHAR2(25)UPDATE_TS: DATECURRENT_REC_IN: VARCHAR2(1)STATUS_IN: VARCHAR2(1)

DataWarehouse

Systems

OperationalTransaction

Systems

OperationalData StoreSystems

So, When Is Each System Type Used

Detailed DataSummary Information + Appropriate Detail

Detailed Data + Appropriate Summary

Single FunctionSummary

Current Point-in-TimeNearly Current Point-in-Time

Continually PeriodicallyFrequently Periodically

Tuned for Update Tuned for QueryTuned for ProductionEnvironment

Tuning Not UsuallyAn Issue

OperationalSource SystemsProperties

OperationalData Store

DataWarehouse Data Mart

Contents

Timeliness

Updated

PerformanceNeeds

Volatility ofContents

Very Volatile Non-VolatileVolatile Non-Volatile

What are the differences?

May Be Very HighControlled for Performance

ModerateLowAmount of DataAccessed

Job Schedulers

RDBMS Utilities

Replication/Distribution Tools

Extract//Transform/Load

CASE

DB Design

Repositories

Design/Transform/Extract/Aggregate/Monitor/Manage Suites / Environments

Database & System Monitors

MOLAP/ROLAP

Data Mining

EIS

Data Visualization

Metadata Browsers

•Design•Mapping

•Design•Mapping

•Extract•Scrub•Transform

•Extract•Scrub•Transform

•Load•Index•Aggregation

•Load•Index•Aggregation

•Replication•Data Set Distribution

•Replication•Data Set Distribution

•Access & Analysis•Resource Scheduling & Distribution

•Access & Analysis•Resource Scheduling & Distribution

Meta DataMeta Data

System MonitoringSystem Monitoring

Data Warehouse Tools

 

 

 

  

 

 

 

 

 

 

  

   

Data Warehouse Development Methodology

WAREHOUSE

PLANNING

S

TAGE

WA

REHOUSE

DEVELOPMENT

STAGE

Met

hodol

ogy

and

Process

Technology Knowledge Team

Business Knowledge Team

Business Sponsor

Maintain

Architected

Data M

art

DeployArchitectedData Mart

Develop

Architected

Data Mart

Desi

gn

Arc

hit

ect

ed

Data

Mart

Define

Arc

hit

ect

ed

Data

Mart

Focu

s

Define Architecture

Survey

User

Needs

Initia

tePro

ject

Individual Architected Data Marts

Common Logical Subject Area ERD

Common Business Dimensions

Common Business Rules

Common Business Metrics

Glossary

SalesDistribution

Product

Marketing Customer Accounts

Finance Inventory Vendors

An Incremental Approach

ArchitectedArchitectedEnterpriseEnterpriseFoundationFoundation

SalesDistribution

Product

Marketing Customer Accounts

Finance Inventory Vendors

Enterprise Data Warehouse

The Eventual Result