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 Business Planning Actions Vision Knowledge Transaction Systems Decision Support Systems Executive Information Systems Data Information RUN MANAGE GROW • Operational • Functional • Current • Detailed • Analyze What If Scenarios • History • Detailed • Multi-Dimensional • History • Summary Management Users Knowledge Brokers
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
Data Warehousing Process OLTP, DW and DM - Data Characteristics • OLTP - Raw Detail • No/Minimal History • DW-Integ. • Scrubbed • History • Summaries • Targeted • Specialized (OLAP) Data Warehouse OLTP Systems Functional IS Central Repository External Data • Design • Mapping • Extract • Scrub • Transform • Load • Index • Aggregation Data Mart End User Workstations • 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)
Distribution Sales Product Customer Accounts Marketing Operations and Inventory Finance Vendors An Incremental Approach Glossary Common Business Metrics Common Business Rules Common Business Dimensions Common Logical Subject Area ERD Individual Architected Data Marts
Distribution Sales Product Customer Accounts Marketing Operations and Inventory Finance Vendors Enterprise Data Warehouse The Eventual Result Architected Enterprise Foundation
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