Database – Part 3

Dr. V.T. Raja Oregon State University External References/Sources: Data Warehousing – Mr. Sakthi Angappamudali

Database – Part 3 - Outline

 Some database trends (past and recent)  Why learn about databases?

Some Database Trends

 Centralized and Distributed databases  Object Oriented and Hypermedia databases  Online Transaction/Analytical Processing (OLTP/OLAP)  Data Warehouse and Data Marts  Data Mining, Business Intelligence (BI) and Analytics

Centralized and Distributed databases



Centralized Databases



Distributed Databases

 Replicated Databases  Partially replicated databases  Fully replicated databases  Concurrency Control  Partitioned Databases   Data spread across two or more smaller databases Connected via communication devices 

Advantages/Disadvantages

Other Trends

 Object Oriented Databases  Hypermedia Databases  Linking Web Applications to Organizational Databases  OLTP, OLAP, DW, DM, BI and Analytics

The Decision Making Roadmap

Transaction Systems Actions Data Business Planning Knowledge Decision Support Systems Vision Information Executive Information Systems RUN

• • • •

Operational Functional Current Detailed

Users MANAGE

• • •

Analyze What If Scenarios History Detailed

Knowledge Brokers GROW

• • •

Multi Dimensional History Summary

Management

On-line Transaction Processing (OLTP) and On-line Analytical Processing (OLAP)



OLTP

: Immediate (On-line) processing of multiple concurrent transactions from customers/users  Example: 

OLAP

: Capability for manipulating and analyzing large volumes of data from multiple perspectives (multidimensional analysis)  Example:

Data Warehouse

 Large repository of detailed and summary data used to support the strategic decision making process for the enterprise  Stores current and historical data (internal and external)  Integrates data from organization’s disparate information systems used by functional units  Involve gigabytes - petabytes of data  Run on very powerful computers  Expensive

• OLTP - Raw Detail No/Minimal History OLTP Systems

Data Warehousing Process OLTP, DW and DM - Data Characteristics

•DW-Integ.

•Scrubbed •History •Summaries • Targeted • Specialized (OLAP)

Data Mart Data Warehouse

Functional IS External Data Central Repository

End User Workstations

•Design •Mapping •Extract •Scrub •Transform •Load •Index •Aggregation •Replication •Data Set Distribution

Data Mart



Data Mart

 A small data warehouse containing only a portion of the organization’s data for a specified function or population of users. It is a subset of a data warehouse (e.g., marketing/sales data mart)

An Incremental Approach

Sales Marketing Distribution Product Glossary Customer Common Business Metrics Accounts Common Business Rules Common Business Dimensions Finance Operations Common Logical Subject Area ERD Vendors Individual Architected Data Marts

The Eventual Result

Distribution Sales Product Finance

Architected

Marketing Customer

Enterprise Foundation

and Inventory Accounts Vendors Enterprise Data Warehouse

Data Mining

 Provides a means of extracting previously unknown, predictive information from the data warehouse  Uses sophisticated, automated algorithms to discover hidden patterns, relationship among data  Some Benefits:  Market Segmentation  Fraud Detection  Market Basket Analysis  Trend Analysis

Business Intelligence

 BI/Analytics software (suite):  Used to collect, store, analyze and present  sufficient and accurate information in a timely manner and in a usable form  Includes OLAP, data mining, statistical analysis  Has a positive impact on business strategy, and operations  Addresses analysis paralysis?

Why learn about databases

?

 Minimize disadvantages of traditional file environment  Improve productivity on personal/professional fronts  Budget vs. Cost (DB could be expensive in the long run)  Maintaining qualified DBA staff  Creating Data Warehouse  Investing in BI Software  SOX Compliance

Why learn about databases

?

 Communicate effectively with DBA and his/her staff     Data model should reflect key business processes and decision-making requirements Information Policy Which current trends in database are important for your unit/firm? Smooth transition for newly hired DBA staff 

Information Resource Management

 Without support and understanding of management at different levels, database efforts fail