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. On-Line Analytical Processing (OLAP)
Mik ASE
Bread
Aalborg | 57 | 45
Copenhagen | 123 | 127
2000 2001
* On-Line Analytical Processing
= Interactive analysis
= Explorative discovery
= Fast response times required
* OLAP operations/queries
= Aggregation, e.g., SUM
= Starting level, (Year, City)
¢ Roll Up: Less detail
« Drill Down: More detail ——————--
= Slice/Dice: Selection, Year=2000
Milk 56
Bread
Aalborg
Copenhagen
102
250
Milk 56
Bread
Aalborg
Copenhagen
123 | 127
67
67
2000 2001
Fait
Commande Produit
Commande
ROLAP
ROLAP
Outils
moteur
ROLAP
SGBD
relationnel
vues multi-dimensionnelles
ep
( Index spéciaux
. données denormalisées
table Employé
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
| outils |
SGBD relationnel
=
serveur
multi-dimensionnel
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 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. Exemple : Dice
Centre éc rous
Ouest ~se vis
Est -< 0 “50 “>~boulon
50
cc
2002 est
2003
2004
1996
1995
Dice
M.Benkhalifa Advanced IT
Roll Up Example
ANNEES
2010 2011 2012 2013
Foulard tout-ANNEES
Foulard Botes Gants
= Nimes Nimes 150 110 160
S Lille 70 80 90
= Lille Paris 140 20 80
Paris
tout-MAGASIN
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
Chicago
wn
$c New York
gs
3 é Toronto
Vancouver] 605 | 825 | 14 | 400
Mobile Modem Phone Security
item (types)
Pivot Coa
Mobile 605
Modem 825
Item
(types) Phone 14
Security| 400
Chicago New Toronto Vancouver
York
Location (cities)
M.Benkhalifa Advanced IT 23 CrossTab Example Registrar cube: Session×Student x Forcredit Grade Pivot choosing Student for x and Session for y
Star model
n(ation)
n_id
n_iso_code
n_name
Example
t(ime) p(roduct)
t_id p_id
t_day_name p_name
Ae nen sales) p_desc
t_cal_year s_p_id pace
t_cal_week_n s_c_id a
im 3 tid p_list_price
s_m_id
s_quant_sold c(ustomer)
m(edia) s_amnt_sold c_id
m_id c_first_name
m_desc c_last_name
m_class c_n_id
n_region
M.Benkhalifa Advanced IT 27 ROLLUP Example SELECT m_desc, t_cal_month_desc, n_iso_code, SUM(s_amount_sold) FROM spctmn WHERE m_desc IN ('Direct Sales', 'Internet') AND t_cal_month_desc IN ('2000-09', '2000-10') AND n_iso_code IN ('GB', 'US') GROUP BY ROLLUP(m_desc, t_cal_month_desc, n_iso_code); • Rollup from right to left • Computes and combines the following groupings – m_desc, t_cal_month_desc, n_iso_code – m_desc, t_cal_month_desc – m_desc – - M.Benkhalifa Advanced IT 28 ROLLUP Example Results M_DESC T_CAL_MO N_iso code SUM(S_amount_sold) ------------ -------- -- ------------------------------------------------------------------------------------ Internet 2000-09 GB 16569.36 Internet 2000-09 US 124223.75 Internet 2000-10 GB 14539.14 Internet 2000-10 US 137054.29 Direct Sales 2000-09 GB 85222.92 Direct Sales 2000-09 US 638200.81 Direct Sales 2000-10 GB 91925.43 Direct Sales 2000-10 US 682296.59 Internet 2000-09 140793.11 Internet 2000-10 151593.43 Direct Sales 2000-10 774222.02 Direct Sales 2000-09 723423.73 Internet 292386.54 Direct Sales 1497645.75 1790032.29
