The Multidimensional Model & OLAP(II)

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

The Multidimensional Model & OLAP(II)