Data Warehousing

Data Warehousing

Presented by: Ankur Agarwal

Agenda


What is a Data Warehouse?




Why we need Data Warehouse?




Benefits & characteristics of a data warehouse




Data warehousing




Types of data warehouses




Data Warehouse Architecture




Design methodologies




Business Intelligence




Components of BI




ERP Data Warehousing Strategies

What is a Data Warehouse? Data collected from one or many systems that exist within and outside the organization. The Data is structured in such a way as to reduce the amount of time that it takes to produce reliable information.

Why we need Data Warehouse?


To Provide a Consistent Common Source for Corporate Information




To Store Large Volumes of Historical Detail Data from Mission Critical Applications




Improve the Ability to Access, Report Against, and Analyze Information




To Solve or Improve Business Processes

Benefits of a data warehouse • The ability to reach data quickly, since they are located in one place • The ability to reach data easily and frequently by end users with Web browsers.

Characteristics of data warehousing • Organization • Consistency • Time variant • Nonvolatile • Relational • Client/server • Web-based

Data Warehousing Functional Data Warehouse

Sales System

System Generated Reports

Sales Analysis is extrapolated from the System Reports.

Data Warehousing Functional Data Warehouse

Sales System

Functional Data Warehouse of Sales Information Sales Information is available to a wider audience of decision makers.

Data Warehousing Division A

Cross Organizational Functional Data Warehouse

Division B

Sales System

Sales System Division C

Centralized Data Warehouse of Sales Data from across the Organization Sales System

Analysis performed and Decisions drawn from the Cross Organizational Sales Data

Data Warehousing Cross Functional Data Warehouse

Marketing System

Sales System Corporate Performance Analysis is extrapolated from the System Reports.

Production Systems

System Generated Reports

Data Warehousing Cross Functional Data Warehouse

Marketing System

Sales System

Cross Functional Data Warehouse of Information Corporate Performance Analysis is available to a wider audience.

Production Systems

Data Warehousing Cross Organizational & Cross Functional

Division C

Division B

Division A

Data Warehouse

Centralized Cross Functional Data Warehouse of Information

Analysis is performed and Decisions made from the Cross Functional Organizational Performance Data

Types of data warehouses Enterprise Data Warehouse

1.

◦

Provides a central database for decision support throughout the enterprise

2. ODS(Operational Data Store) ◦ Has broad enterprise wide scope ◦ Data is refreshed in near real time and used for routine business activity

3. Data Mart ◦ Subset of data warehouse ◦ Supports a particular region, business unit or business function.

Data Warehouse Architecture

Data Warehouse Architecture

External Data

Data Staging Area

Extract,Transformation and Load (ETL)

Division C

Division B

Division A

Source Systems

Data Warehouse Repository

Data Warehouse Architecture A system should give response to almost any question regarding company performance measure.

Transformed Relational Architecture


Star schema




Snowflake schema




Fact constellation schema

Star schema




Simplest data warehouse schema Center of the star consists of fact table [3NF] Points of the star are the dimension tables [de-normalized]

Example: Fact tables store data about sales while dimension tables data about geographic region(markets, cities), clients, products, times, channels.

Snowflake schema



More complex variation of the star schema Dimension table are normalized

Fact constellation schema



Splitting the original star schema into more star schemas Contains multiple fact tables that share many dimension tables

Design methodologies


Bottom-up design




Top-down design




Hybrid design

Example: User

What are our five most-profitable products?

X

Financial Analysis Activity-Based Costing

Corporate Data Warehouse

OLTP Systems

ERP

CRM

BI Applications

Business Intelligence • Definition • A set of tools that allow users to access enterprise data via reports, Online Analytical Processing (OLAP) cubes, graphs/charts, ad-hoc queries and dashboards

• Purpose • Allow users to view the data from all levels of the enterprise • Provide users with information necessary to make timely, well-informed business decisions

Components of BI •

Reports

•

Cubes

•

Charts & Graphs

•

Dashboards

ERP Data Warehousing Strategies One DW

Separate DWs

Data Marts and BI Applications

Custom or ERP DW Sources

Data Marts and ERP BI Applications

Other Sources

Custom DW Other Sources

ERP DW Leading

Custom DW

Data Marts and BI Applications

ERP DW ERP Sources

ERP DW ERP Sources

Custom DW Leading ERP BI Apps. ERP DW ERP Sources

Data Marts

Custom DW Other Sources

References



• • • •

http://www.tdan.com/view-articles/4994/ http://etltools.info/en/bi/datawarehouse_architecture.htm www.cdd.go.th/it/file/DataWarehousing_and_DataMinin g.pdf http://principlepartners.com/presentations/DataWarehous eConceptsAndArchitecture.pdf http://www.pdfcoke.com/doc/2922402/Data-WarehouseConcepts http://www.dnsarrow.co.uk/sun06/white_papers/Busines s%20Intelligence%20and%20Data%20Warehousing%20 %28BIDW%29.pdf

Thank You