250 likes | 270 Views
The Multidimensional Model & OLAP(II). Developed by: Dr Eddie Ip Modified by: Dr Arif Ansari. Outline. OLAP issues Two implementations of OLAP MOLAP & ROLAP Multi-tier architecture Managed query environment Lab session : Transformer. OLAP: issues. Calculation of the number of cells
E N D
The Multidimensional Model & OLAP(II) Developed by: Dr Eddie Ip Modified by: Dr Arif Ansari
Outline • OLAP issues • Two implementations of OLAP • MOLAP & ROLAP • Multi-tier architecture • Managed query environment • Lab session : Transformer
OLAP: issues • Calculation of the number of cells • # products = 100, • # markets =30, • #time points = 20 • # of cells =? • problem: cube sparse
OLAP • Sparse matrices • e.g. 400 products, 1,000 customers, 200 time points • Solutions • Desktop product: Limit the number of dimensions • e.g. 7 dimensions at most
OLAP • Solutions • ROLAP and MOLAP • E.g. ROLAP does not store all combinations
OLAP • No single product optimum for every application! • Note OLAP tools provide the views, storage methods may vary
MOLAP • Multidimensional OLAP • Two main types of implementations • DB is MDDBMS • DB is RDBMS
MOLAP • First implementation: use MDDBMS • MOLAP • MDDBMS: Direct access to array data structures • Sparsely populated matrices & rigid structure • Vendor: Essbase, Oracle (Express Server)
MOLAP • Pre-calculated aggregate data at load time • Enormous overhead for pre-consolidation • Storage of aggregated data & index • Loading time • E.g., 200M ->>> 5G • Faces sparse matrix & scalability problem • New advances : e.g. T3 (0.5 T cube)
MDDB • Different storage technology from RDBMS • Often used to store information for a specialized group • Sometimes called data mart (big data cube) • Typically <50G
ROLAP • Second implementation: use RDBMS • Relational DB <--> MOLAP Server • Use star schema to store source data • Compute results on-the-fly • Could be slow (SLOW LAP??)
ROLAP • Relational OLAP • RDBMS to store and manage DW • OLAP as “middleware” • No multidimensional structure in between client and RDBMS(p. 254 B&S) • Vendor: Microstrategy, Informix (MetaCube)
ROLAP • ROLAP uses star schema for storing data cubes need SQL + intermediate server engine or s/w to perform multidimensional manipulation • Non-valid combinations not created • Typically 0.5% or 1% of the cells contain value!
ROLAP • E.g. Microstrategy (ROLAP vendor) • Ohio Department of Education • Interactive Local Report Card (iLRC) application pulls together school district performance data and presents online • http://www.ode.state.oh.us/
ROLAP vs MOLAP • MOLAP pluses • More intuitive • Storage structure aligned with user’s view • Within size limit, faster than ROLAP • MOLAP minuses • New & expensive technology • No access standard like ODBC • Not highly scalable like ROLAP
HOLAP • Hybrid OLAP • Data storage: Dense part stored in MDB • Rest in RDB • Vendor: SQL Server OLAP Services
Multi-tier architecture • MOLAP & ROLAP invariably built on 3- or multi-tier architecture • MOLAP/ ROLAP Engine between user and central DBMS
Two-tier DW architecture DW Server Clients
Three tier DW architecture OLAP Engine DW Server Clients
Managed Query Environment (MQE) • Query DBMS (or DW) > download data to desktop • Data cube created on desktop • Slice and dice locally with OLAP desktop products • Tradeoff between flexibility, performance, and data consistency
MQE • MQE = MOLAP but without MDDB • Vendors: Hyperion, Cognos, Hummingbird, Business Objects
Summary • OLAP’s underlying architecture = Multidimensional modeling • = cubist view • OLAP provides a flexible method (e.g. roll-up, drill-down) to manipulate data
Summary • Sparse data distribution in cube • MDDB = new DBMS based on “cube” • ROLAP = star implementation of cube • MOLAP, ROLAP & MQE - different approaches in implementing OLAP
Glossary • OLAP: Online analytical Processing • MOLAP: Multidimensional OLAP • ROLAP: Relational OLAP • HOLAP: Hybrid OLAP • MDDB: Multidimensional Data Bases • MQE: Managed Query Environment