University:
California State University, Northridge
Course:
ME 595A-Z | Experimental Topics Courses in Mechanical Engineering
Academic year:

2011
Views:

480

Pages:

29
Author:

Alonzo Riddle

Data Warehouse and OLAP II Week 6 1 T i l OLAP Operations Typical O ti • • • • • • Roll up (drill Roll‐up (drill‐up) up) Drill‐down (roll down) Slice and dice Pivot (rotate) Drill‐across Drill through Drill‐through R ll Roll-up • Perform aggregation on a data cube by – Climbing up a concept hierarchy for a dimension – Dimension reduction Roll up Roll-up 5 Drill down Drill-down • Drill‐down is the reverse of roll‐up • Navigates from less detailed data to more detailed data by – Stepping down a concept hierarchy for a dimension – Introducing additional dimensions Drill down Drill-down 7 Slice and Dice • The slice operation performs a selection on one dimension of the given cube, resulting in a sub‐cube • The dice operation defines a sub‐cube by performing a selection on two or more dimensions 8 Slice 9 Dice 10 Pivot (Rotate) • Visualization operation that rotate the data axes in view in order to provide an alternative presentation of the data 11 Pivot 12 Drill across Drill-across • An additional drilling operation • Executes queries involving (i.e., (i e across) more than one fact table 13 Drill through Drill-through • An additional drilling operation • Uses relational SQL facilities to drill through the bottom level of a data cube down to its back‐end relational tables 14 Figure 3.10. Examples E o Typical OLAP of O operrations on mu ultidimensio onal data cube, com mmonly used for data wareho ousing 15 Motivation for Building Data Warehouse • Building and using a data warehouse is a complex, difficult, and long‐term task • The construction of a large and complex information system can be viewed as the construction of large and complex building D t W Data Warehouse h P Project j t Process P (1) • Top‐down, bottom‐up approaches or a combination of both – Top‐down: Starts with overall design and planning (mature) – Bottom‐up: Starts with experiments and prototypes (rapid) Data Warehouse Project Process (2) • Typical data warehouse design process – Choose a business process to model, e.g., orders, invoices, etc. etc – Choose the grain (atomic level of data) of the business process – Choose the dimensions that will apply to each fact table record – Choose the measure that will populate each fact table record Th Three Data D t Warehouse W h M Models d l • Enterprise warehouse – Collects all of the information about subjects spanning the entire organization • Data mart – A subset of corporate‐wide data that is of value to a specific groups of users Its scope is confined to specific, users. specific selected groups, groups such as marketing data mart • Independent vs. dependent (directly from warehouse) data mart • Virtual warehouse – A set of views over operational databases – Only some of the possible summary views may be materialized Data Warehouse Development: A Recommended R d d Approach A h Figure 3.13 A recommended approach for data warehouse development. Figure 3.12 A three-tier data warehousing architecture. OLAP SServer A Architectures hit t • Relational OLAP (ROLAP) • Multidimensional OLAP (MOLAP) • Hybrid OLAP (HOLAP) ROLAP • Advantages – Can C h handle dl large l amounts off d data – Can leverage functionalities inherent in the relational database • Disadvantages – Performance can be slow – Limited by SQL functionalities 23 MOLAP • Advantages – Excellent performance – Can perform complex calculations • Disadvantages – Limited in the amount of data it can handle – Requires additional investment HOLAP • HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. 25 Open Source Data Warehousing Tools • MySQL‐based data warehouse • Open data warehouse D t W Data Warehouse h U Usage (1) • Information processing – supports querying, querying basic statistical analysis analysis, reporting using cross‐tabs, tables, charts and graphs • Analytical processing – multidimensional analysis of data warehouse data – supports basic OLAP operations, operations slice‐dice, slice dice drilling, drilling pivoting D t W Data Warehouse h U Usage (2) • Data mining – knowledge discovery from hidden patterns – Supports associations, associations constructing analytical models, models performing classification and prediction, and presenting the mining results using visualization tools From OLAP t OLAM to • On‐Line Analytical y Miningg – High quality of data in data warehouses – Available information processing infrastructure surrounding data warehouses – OLAP‐based exploratory data analysis – On‐line O li selection l ti off data d t mining i i functions f ti Fig gure 3.18 An integrrated OLA AM and OLAP arc chitecture e.

Data Warehouse and OLAP II

of 29

Description

Free up your schedule!

Take 5 seconds to unlock