DATA

WAREHOUSEBy: Ritu Jain

DEFINITIONData Warehouse A collection of corporate information, derived directly from operational systems and some external data sources. Its specific purpose is to support business decisions, not business operations.

APPROACHES OF DATA WAREHOUSE

Subject oriented- data warehouse is prepared through key subjects and it is subject specific.

Integrated- The data installed in a data warehouse is gathered from various formats, conventional from various legacy systems and also from some external sources.

Time variant- Data in a data warehouse is time constrained and can be changed according to changing trends.

No updatable- The information in the data warehouse is not changeable by end users but can only be altered by operational systems.

THE PURPOSE OF DATA WAREHOUSING

Realize the value of data Data / information is an asset Methods to realize the value (Reporting,

Analysis, etc.)

Make better decisions Turn data into information Create competitive advantage Methods to support the decision making

process

Data Warehouse Components

• Staging Area• A preparatory repository where

transaction data can be transformed for use in the data warehouse

• Data Mart • It is limited in scope. Here data is obtained

by selecting and summarizing data from a data warehouse through ETL process from source data system

• Operational Data Store (ODS)• It is an integrated subject oriented

database that help uses for decision support processing.

DATA WAREHOUSE FUNCTIONALITY

Data Warehouse Engine

Optimized LoaderExtractionCleansing

AnalyzeQuery

Metadata Repository

RelationalDatabases

LegacyData

Purchased Data

ERPSystems

EVOLUTION ARCHITECTURE OF DATA WAREHOUSE

Top-Down Architecture

Bottom-Up Architecture

Enterprise Data Mart Architecture

Data Stage/Data Mart Architecture

GO TO DIAGRAM

GO TO DIAGRAM

GO TO DIAGRAM

GO TO DIAGRAM

BACK TO ARCHITECTURE

Top-Down Architecture

BACK TO ARCHITECTURE

Bottom-Up Architecture

Enterprise Data Mart Architecture

BACK TO ARCHITECTURE

Data Stage/Data Mart Architecture

BACK TO ARCHITECTURE

COMPLEXITIES OF CREATING A DATA WAREHOUSE

Incomplete errors Missing FieldsRecords or Fields That, by Design, are

not Being Recorded

Incorrect errorsWrong Calculations, AggregationsDuplicate RecordsWrong Information Entered into Source

System

SUCCESS & FUTURE OF DATA WAREHOUSE

The Data Warehouse has successfully supported the

increased needs of the State over the past eight

years.

The need for growth continues however, as the

desire for more integrated data increases.

The Data Warehouse has software and tools in place

to provide the functionality needed to support new

enterprise Data Warehouse projects.

The future capabilities of the Data Warehouse can be

expanded to include other programs and agencies.

BENEFITS OF DATA WAREHOUSING

Maintain data history, even if the source

transaction systems do not.

Improve data quality, by providing consistent

codes and descriptions, flagging or even fixing

bad data.

Present the organization's information

consistently

BENEFITS OF DATA WAREHOUSING

Provide a single common data model for all data of interest regardless of the data's source.

Restructure the data so that it makes sense to the business users.

Restructure the data so that it delivers excellent query performance, even for complex analytic queries, without impacting the operational systems.

Integrate data from multiple source systems, enabling a central view across the enterprise.

Thank You