The Multidimensional Model & OLAP (I)

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

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

3. 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.

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

5. Introduction • Underlying architecture: multidimensional model & view

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

7. 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

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

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

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

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

13. 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)

14. 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,…)

15. 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

16. 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

17. 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)

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

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

20. 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

21. 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

22. 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”

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

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