Multidimensional OLAP Analysis in Computer Science, Schemes and Mind Maps of Business Informatics

Download Multidimensional OLAP Analysis in Computer Science and more Schemes and Mind Maps Business Informatics in PDF only on Docsity!

Masters IA Offshoring

Pr. M. Benkhalifa

Fall 2023

Faculté des Sciences de Rabat

Computer Science Department

Multi dimensional Analysis OLAP

• (^) Goal : Get aggregated information based on user needs : Easy and quick access
• (^) OLAP Cube: represents of information in a cube of N dimensions.
  • (^) Facts “live” in a multidimensional cube (dice): Like array
• (^) OLAP (On-Line Analy cal Processing) : supports functionalities for multidimensional Analysis via different operations carried out on cubes.

M.Benkhalifa Cubes

• Facts “live” in a multidimensional cube Think of an array from programming languages
• A “cube” may have many dimensions.
  • More than 3 - the term ”hypercube” is sometimes used
  • Theoretically no limit for the number of dimensions
  • Typical cubes have 4-12 dimensions
• But only 2-3 dimensions can be viewed at a time
• A cube consists of cells
  • A given combination of dimension values
  • A cell can be empty (no data for this combination)
  • A sparse cube has few non-empty cells
  • (^) A dense cube has many non-empty cells
  • Cubes become sparser for many/large dimensions

ROLAP

Multidimensional OLAP (MOLAP)

• (^) Data stored in special multidimensional data structures
• E.g., multidimensional array on hard disk
  • (^) MOLAP data cube
  • (^) Pros
    • (^) Less storage use (“foreign keys” not stored)
    • (^) Faster query response times
  • Cons
    • (^) Less scalability
    • (^) Less flexible, e.g., cube must be re-computed when design changes
    • (^) Does not reuse an existing investment
  • (^) Examples:
    • (^) IBM TM
    • (^) Hyperion (Oracle Essbase)

MOLAP

Hybrid OLAP (HOLAP)

• (^) Detail data stored in relational tables (ROLAP)
• (^) Aggregated data stored in multidimensional structures (MOLAP)
• Pros Scalable (as ROLAP) Fast (as MOLAP)
• Cons High complexity Example: Microsoft Analysis Services, SQL Server

HOLAP

OLAP Operations Categories

• (^) Cube Restructuring : operations related to structure, manipulation and visualization of cubes: - (^) Rotate/pivot, Switch, Split and Nest
• (^) Data Granularity : Operations related to change of data detail level:
  • (^) Roll up and Drill down
• (^) Classic OLTP operations (project and select) : Operations related to data extraction and classical OLTP : - (^) Slice and Dice

M.Benkhalifa Advanced IT 14 Slicing Slicing: selection part of the cube based on condition on dimension:

• (^) We specify one fixed value for each dimension.
• (^) Exemple: Slice (2004) : only the 2004 cube part is kept.

M.Benkhalifa Advanced IT 15

Exemple : Slicing

M.Benkhalifa Advanced IT 16

Dicing

Dicing refers to range selection in multiple dimensions. ( Exp: select range 2-3 for dims 1 and 2, select range 1-2 for dim 3.

M.Benkhalifa Advanced IT 17

Exemple : Dice

Roll Up Example

Drill Down

SQL Extensions for OLAP

• (^) Extensions to support OLAP queries
• (^) Examples:
  • (^) Pivot and Crosstab
  • (^) Group by extensions
    • (^) Rollup, Cube
  • (^) …….

M.Benkhalifa Advanced IT 21 Pivot and CrossTabs Some operations are concerned with information display.

• Pivot: Rotate by swapping rows and columns
• CrossTabs: choose which dimensions to show in a (usually) 2-d rendering.

Pivot Example

M.Benkhalifa Advanced IT 23 CrossTab Example Registrar cube: Session×Student x Forcredit Grade Pivot choosing Student for x and Session for y

M.Benkhalifa Advanced IT 24 crosstab, showing > 2 dimensions You can pack several dimensions onto one display axis. (Typically, by Cartesian product). Choosing Student and ForCredit(yes, no) for x and Session for y student/ForCredit Jill Jack Al Semester Yes No Yes No Yes No Spring’03 42 76 Summer’03 86 88 89 38

M.Benkhalifa Advanced IT 25 GROUP BY Extensions

• Aggregation is a fundamental part of OLAP and data warehousing.
• To improve aggregation performance the following extensions to the Group By are used: Clause : CUBE and ROLLUP

Star model Example