1 / 38

Chapter 2: DATA WAREHOUSING

Chapter 2: DATA WAREHOUSING. FUNDAMENTALS of DATABASE SYSTEMS , Fifth Edition. Who are my customers and what products are they buying?. Which customers are most likely to go to the competition ? . Introduction. What product promotions have the biggest impact on revenue?.

calder
Download Presentation

Chapter 2: 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. Chapter 2:DATA WAREHOUSING FUNDAMENTALS of DATABASE SYSTEMS, Fifth Edition

  2. Who are my customers and what products are they buying? Which customers are most likely to go to the competition ? Introduction What product promotions have the biggest impact on revenue? What impact will new products/services have on revenue and margins? Fundamentals of Database Systems, Fifth Edition

  3. Introduction (cont.) • There is a great need for tools that provide decision makers with information to make decisions quickly and reliably based on historical data. • The above functionality is achieved by • data warehousing • it characterized by subject-oriented, integrated, nonvolatile, time-variant collection of data in support of management's decisions.

  4. Introduction (cont.) • online analytical processing (OLAP) • A term used to describe the analysis of complex data from the data warehouse. • and data mining. • The process of knowledge discovery Fundamentals of Database Systems, Fifth Edition

  5. Characteristics of Data Warehouses- Subject oriented • Organized around major subjects, such as product, sales. • Focusing on the modelling and analysis of data for decision makers, not on daily operations or transaction processing. • Provide a simple and concise view around particular subject issues by excluding data that are not useful in the decision process.

  6. Characteristics of Data Warehouses- integrated • Constructed by integrating multiple, heterogeneous data sources. • Data cleaning and data integration techniques are applied.

  7. Characteristics of Data Warehouses- Time Variant • Data warehouse data : provide information from a historical perspective (e.g., past 5-10 years) • Every data in the data warehouse contains an element of time.

  8. Characteristics of Data Warehouses- Non Volatile • Operational update of data doesn’t occur in the data warehouse environment. • Doesn't require transaction processing, recovery, and concurrency control mechanism. • Require only two operations in data accessing • Initial loading of data and quering.

  9. Data Warehouse vs. operational databases 9

  10. OLTP vs. OLAP

  11. What is a Data Warehouse?A Practitioners Viewpoint • “A data warehouse is simply a single, complete, and consistent store of data obtained from a variety of sources and made available to end users in a way they can understand and use it in a business context.” Barry Devlin, IBM Consultant

  12. What is a Data Warehouse? Clean Integrate Transform Load Refresh client Data warehouse Query and analysis tools Data source in Chicago Data source in New York Data source in Taranto client

  13. 3-D data cube

  14. Example of Querying a Cube 1000 Q1 Q2 Time Dimension Q3 Grapes Cherries Q4 Melons Apples ProductsDimension Sales Units Ave Units Sales Dollars Net Price Measures Dimension

  15. From table and spreadsheet to data cubes • A data warehouse is based on a multidimensional data model which views data in the form of data cube. • A data cube, such as sales, allows data to be modeled and viewed in multiple dimensions. • Dimension tables contains descriptions about the subject of the business. • such as item (item_name, brand, type) or time (day, week, month, quarter, year)

  16. From table and spreadsheet to data cubes (cont.) • Fact table contain a factual or quantitative data • Fact table also contains measures (such as dollars_sold) and keys to each of the related dimension tables.

  17. 4-D Data cube

  18. Cube: a lattice of cuboids 0-D (apex) cuboids 1-D cuboids 2-D cuboids 3-D cuboids 4-D (base) cuboids

  19. Conceptual Modeling of Data Warehouses • Modeling data warehouses: dimensions & measures • Star schema: a fact table in the middle connected to a set of dimension tables. • Snowflake schema: a refinement of star schema where some dimensional hierarchy is normalized into a set of smaller dimension table, forming a shape similar to snowflake.

  20. Conceptual Modeling of Data Warehouses (cont.) • Fact constellations: multiple fact tables share dimension tables, viewed as a collection of stars, therefore called galaxy schema or fact constellation

  21. Example of Star Schema Measures

  22. Example of Snowflake Schema Measures

  23. Example of Fact Constellation Measures

  24. Cube definition syntax in DMQL

  25. Defining star schema in DMQL

  26. Defining snowflake in DMQL

  27. Defining fact constellation in DMQL

  28. Measure of Data Cube: three categories • Distributive: if the result derived by applying the function to n aggregated values is the same as that derived by applying the function on all the data without portioning. • E.g., count(), min()

  29. Measure of Data Cube: three categories (cont.) • Algebraic: if it can be computed by an algebraic function with M arguments ( where M is abounded integer), each of which is obtained by applying a distributive aggregated function • E.g., avg() • Holistic: if there is no constant bound on the storage size needed to describe a sub aggregate • Mode(), rank()

  30. Typical OLAP operations • Roll up ( drill-up) summarize data. • By climbing up hierarchy • Drill down ( roll down): reverse of roll-up • From higher level summary to lower level summary or detailed data. • Slice and dice: project and select

  31. Typical OLAP operations (cont.) • Pivot ( rotate) • Reorient the cub, visualization, 3D to series of 2D planes • Other operations: • Drill across: involving ( across) more than one fact table • Drill through: through the bottom level of the cube to its back-end relational tables (using sql)

  32. Design of Data Warehouse: A Business Analysis Framework • Four views regarding the design of data warehouse • Top down view: allow selection of the relevant information necessary for the data warehouse • Data source view: exposes the information being captured, stored, and managed by operational systems • Data warehouse view: consists of the fact table and dimension table

  33. Design of Data Warehouse: A Business Analysis Framework (cont.) • Business query view: see perspectives of data in the warehouse from the view of end-user

  34. Data Warehouse Design Process • Top-down, bottom-up approaches or combination of both • Top-down: starts with overall design and planning • Bottom-up: starts with experiments and prototypes • From software engineering point of view • Waterfall: structure and systematic analysis at each step before proceeding to next.

  35. Data Warehouse Design Process (cont.) • Spiral : rapid generation of increasingly function systems, quick turn around.

  36. Data Warehouse Design Process (cont.) • Typical data warehouse design process: • Choose a business process to model. E.g., orders, invoice, etc • Choose the grain (atomic level of data) of the business process • Choose the dimension that will apply to each fact table record • Choose measure that will populate each fact table record

  37. Three Data Warehouse Models • Enterprise warehouse • Collect all of the organization about subjects spanning the entire organization • Data Mart: • A subset of corporate- wide data that is of value to specific group of users. • Virtual warehouse • Set of views over operational databases

  38. Data Warehouse Back-End Tools and Utilities • Data extraction • Data cleaning • Data transformation • Load • refresh

More Related