1 / 27

DW-1: Introduction to Data Warehousing

DW-1: Introduction to Data Warehousing. Overview. What is Database What Is Data Warehousing Data Marts and Data Warehouses The Data Warehousing Process Data in a Data Warehouse. What Is Database. Before Program = Algorithm + Data Structure Now

Download Presentation

DW-1: Introduction to Data Warehousing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. DW-1: Introduction to Data Warehousing

  2. Overview • What is Database • What Is Data Warehousing • Data Marts and Data Warehouses • The Data Warehousing Process • Data in a Data Warehouse

  3. What Is Database • Before • Program = Algorithm + Data Structure • Now • Application (Weblication) = Visual I/F + SQL Query + Database • Database is Integrated Data • from multiple file system data for OLTP • Data Base (From Air Base?), DB, 데이타베이스, 자료기지(북한)

  4. Database and Data Model • Computer Representation of Data for efficient understanding and processing • Data Model based on Relationship modeling • Relationship between record • one-to-one(1:1), one-to-many(1:N), many-to-many(N:M) • Hierarhical Model: Hierarchical Relationship, 1:N • Network model: Network like relationship, N:M • Relational Model: Use relation (table) for Relationship • Object-Oriented data model: Complex object modeling • SET type, Reference, List

  5. What Is Data Warehousing • Defining Data Warehousing • Operational Systems: A Transactional Solution • Analytical Systems: A Data Warehousing Solution • Comparing Transactional and Data Warehousing Solutions

  6. Defining Data Warehousing • Business Intelligence • Database Marketing: Personalized Product • Especially S/W, Cocoon business etc. • Electronic Commerce • Data Warehouse: 자료 창고 • for OLAP, Data Mining, DSS • Knowledge Management • Data Warehousing: Process to build Data Warehouse

  7. Defining Data Warehousing • A Data Warehouse Is a Database That Contains: • Enterprise data • Integrated sets of historical data • Subject-oriented, consolidated, consistent data • Data structured for distribution and querying • A Data Warehousing Solution Is a Process That: • Retrieves and transforms data • Manages the database • Uses tools for building and managing the data warehouse

  8. Operational Systems: A Transactional Solution • Track Individual Events • Used for Real-time Data Entry and Editing • Examples: • Order-tracking applications • Customer service applications • Point-of-sale applications • Service-based sales applications • Banking functions

  9. Analytical Systems: A Data Warehousing Solution • Assist with Strategic Decision Support • Provide Different Levels of Analysis • Allow Users to Navigate to Different Levels of Data • Allow System Searches to Find New Relationships • Examples: • Spreadsheet-based applications • Sales forecasting applications

  10. Transactional solutions Data warehousing solutions Update frequency Real-time Periodically Structured for Data integrity Ease in querying Optimized for Transaction performance Query performance Comparing Transactional and Data Warehousing Solutions

  11. Data Marts and Data Warehouses • What Is a Data Mart • Moving Data from a Data Warehouse to Data Marts • Moving Data from Data Marts to a Data Warehouse

  12. What Is a Data Mart • What Is a Data Mart • A subset of a data warehouse • Used in an enterprise • Specific to a particular subject or business activity • Why Build Data Marts • Faster queries and fewer users • Faster deployment time • Integrated Data Marts • Ensure consistent data • Require advance planning

  13. Sales Mart Source 1 Data Warehouse Financial Mart Source 2 Source 3 Customer Service Mart Moving Data From a Data Warehouse to Data Marts • Advantages • Shared fields • Common source • Distributed processing • Disadvantages • Longer time to develop

  14. Sales Mart Source 1 Data Warehouse Financial Mart Source 2 Customer Service Mart Source 3 Moving Data from Data Marts to a Data Warehouse • Advantages • Simpler and faster to implement • Department-specific data • Smaller hardware requirements • Disadvantages • Data duplication • Incompatible data marts

  15. The Data Warehousing Process • Basic Elements of the Process • Tools to Manage the Process

  16. Source OLTP Systems Data Marts Data Warehouse Clients Retrieve Data Populate Populate Query Transform Data Data Warehouse Data Marts the Data Basic Elements of the Process 1 3 4 5 2

  17. Tools to Manage the Process • SQL Server • Data Transformation Services • SQL Server OLAP Services • Microsoft Repository • Microsoft English Query • PivotTable Service

  18. ETL process • Extraction, Transformation, Loading • Extraction: 추출 • Data retrieval from existing data source such as File, Table etc. • Transformation: 변환 • Data modification, sorting, calculation etc • Loading: 적재 • Bulk, incremental loading from operational DB • Time consuming process: may use special H/W

  19. Data in a Data Warehouse • Data Characteristics • Example of Organizing Data

  20. Data characteristic Description Consolidated Enterprise-wide Consistent Within the data warehouse Subject-oriented Organized to user perspective Historical Snapshots over time Read-only Cannot update Summarized To appropriate level of detail Data Characteristics

  21. Example of Organizing Data Monthly Southeast Regional Sales Report - May 1999 Units Sold 2,500 2,750 5,250 3,200 1,725 4,925 1,900 1,900 12,075 State FL FL FL Totals GA GA GA Totals SC SC Totals City Miami Tampa Atlanta Savannah Columbia Sales $ $12,850 $14,135 $26,985 $16,800 $ 9,143 $25,943 $ 9,595 $ 9,595 $62,473 Southeast Region Total

  22. Data Warehouse Schema Example: Star schema

  23. A Example of Cube Browsing • 1 Fact with 4 Dimension Table -- Sales_Fact, Product, Store, Time, Customer

  24. Drilling Down • Drilling Down to products

  25. Drilling Down • Drilling Down to the lowest level of Customer Dimension

  26. Rolling up • Rolling up

  27. Review • What Is Data Warehousing • Data Marts and Data Warehouses • The Data Warehousing Process • Data in a Data Warehouse • Data Warehouse will be more popular than DB?

More Related