The Multidimensional Model OLAP I Developed by Dr

The Multidimensional Model & OLAP (I) Developed by: Dr Eddie Ip Modified by: Dr Arif Ansari 1

Outline • Introduction • OLAP architecture: Multidimensional modeling • Hierarchical structure • Lab session 2

Introduction • On-Line-Analytical-Processing (OLAP) • Process of creating and managing multidimensional enterprise data for analysis & viewing. • DW & OLAP have similar aims/functions but they emerged independently & are positioned differently. 3

Introduction • DW focus : make data accurate & consistent • OLAP focus: meet end-user’s analytical requirements • OLAP tools are often referred to as BI tools 4

Introduction • Underlying architecture: multidimensional model & view 5

OLAP architecture: Multidimensional Model • Make DB – simple – understandable – in line with business view 6

Multidimensional Model • Think of data as a cube • “We sell products in various markets, and we measure our performance over time. ” • Design DB along dimensions -- e. g. Products, Markets, Time 7

Multidimensional modeling: Examples • Product, market, time = • 3 -dimensional cube • But what’s in a cell? 8

Multdimensional modeling: Examples • Example 1: Car sales data • Dimensions: – make – color – year • What’s in a cell? 9

10

Multidimensional modeling: Examples • Example 2 • Customer spending data • Dimensions: – age group – income level – gender 11

Multidimensional modeling: Examples • Example 3 • Model student final grades in a survey • Dimensions: – gender – time spent – grad/undergrad 12

Multidimensional modeling • Data can be stored as cubes • Multidimensional database (MDDB) • Historical note: OLAP is coined by Edgar Codd at Arbor Software (pioneered MDDB called Essbase) 13

Multidimensional modeling • Each dimension has different categories • E. g. product (s/w for word processing, s/w for spreadsheet, s/w for graphics…) • E. g. age group (old, middle, young, …) 14

Multidimensional modeling • data stored in the “cell” of the cube == instance of a variable – numerical measurement of the business – some vendors use the term measure – additive 15

Hierarchical structure of dimension • e. g. All product > electronics > office appliances > copiers • dimensional hierarchy may be asymmetric (ragged) or symmetric • Asymmetric : All product > electronic / furniture >. . • Symmetric : year > month > quarter 16

Hierarchical structure of dimension • Hierarchy is the backbone of aggregating • e. g. aggregate over time, aggregate over products into broad product types • Hierarchy provides structure for navigating the cube • Hierarchy is also useful for managing security • e. g. department can only see its own budget (low level) 17

Hierarchical structure of dimension • A single dimension may have multiple hierarchies • e. g. All_product > electronics/furniture • All_product > bargain/regular/deluxe 18

Hierarchical structure of dimension • Going down the hierarchy = getting detailed data • = Drill down = getting the marginal tables • Aggregate at appropriate level = roll up 19

OLAP as a Business Intelligence Tool • Slice and dice = general term for viewing data from different angles • matrix = a multidimensional cube • cell = a unit in the matrix • More terms in demo 20

OLAP tool : Functional Requirements • Fast access & calculations • Analytical capabilities (e. g. aggregate at any level & along any dimension) • Flexibility (viewing, analysis, interface with spreadsheet, on-the-fly calculation according to view) • Multiuser support 21

OLAP • Key is the “A” in OLAP • Query can be performed along any dimension & level – e. g. customer , product – “Total # of Messy Paint Kits sold =? ” – “Items Fun Land Toys sold in the last year” 22

OLAP • Flexibility : e. g. Selection = define a subcube • Sales where Product = Messy Paint Kits and date = 1/97 23

Summary • OLAP architecture: Multidimensional modeling • Hierarchical structure • Lab session 24