Graphical Querying of Multidimensional Databases

1
Graphical Querying of Multidimensional Databases

• Franck Ravat, Olivier Teste, Ronan Tournier and
  Gilles Zurfluh.
• IRIT Institut de Recherche en Informatique de
  Toulouse (FRANCE).
• Presentation Ronan TOURNIER
• tournier_at_irit.fr

2
Overview

• Introduction
• Context OLAP
• Example
• Solution
• Conceptual Model
• Algebra Multidimensional Operators
• Graphical Manipulation Language
• Conclusion

3
Context

• On-Line Analytical Processing (OLAP)

Context
4
Context

• Lack of formal standards Rizzi et al., 2006
• Different models for multidimensional data
• Cube Agrawal et al., 1997
• Multidimensional models Pedersen et al., 2001
• Different manipulation algebras
• Adaptation of the relational algebra Gyssens et
  al., 1997
• Cube algebra Cabibbo et al., 1997
• Graphic languages
• Incomplete in comparison with algebraic operators
  Cabibbo et al., 1998
• Lack of formal description Stolte et al., 2002

5
Example

• Example of an analysis
• Extraction of a Slice from the cube

6
Objectives

• An integrated solution for the manipulation of
  multidimensional data
• Global multidimensional model
• Multiple subjects, multiple analysis axes,
  multiple hierarchies, separation of structure
  from content
• Visualisation adapted to decision makers
• OLAP algebra for the specification of complex
  analyses
• Closed core of operators
• Graphical language for decision makers

7
Model

• Introduction
• Conceptual Model
• Concepts
• Example
• Algebra Multidimensional Operators
• Graphical Manipulation Language
• Conclusion

8
Concepts

• Constellation Cs
• Facts FCs f1,, fm
• Dimensions DCs D1,, Dn
• StarCs a function linking facts and dimensions
• Fact Fi
• Measures MFi m1,,mw

9
Concepts

• Dimension Di
• Hierarchies HDi h1,, hr
• Attributes ADi a1,, as
• Hierarchy Hi
• Parameters ParamHi ltp1,, pnpgt, pj?ADi
• WeakHi is the association of weak attributes to
  parameters (complementary information)

10
Example

• Graphical formalism of a constellation

11
Multidimensional Table

• mTable T (S,C,L,R)
• Subject S(FS, MS)
• Columns/Lines C(DC,HC,PC) L(DL,HL,PL)
• Restrictions R

12
Multidimensional Table

• Construction operation DISPLAY

DISPLAY(

)TR1
IMPORTS, SUM(Amount),
SUPPLIER, HGEO,
DATES, HTIME
13
Algebra

• Introduction
• Conceptual Model
• Algebra Multidimensional Operators
• Core of manipulation operators
• Example
• Graphical Manipulation Language
• Conclusion

14
Algebra

• Principle

Constellation
mTable
Manipulation operators
mTable

15
Algebraic Core

• Categories
• Drilling
• Selections
• Rotations
• Fact modification
• Dimension modification
• Ordering
• Aggregation

• Operators
• DRILLDOWN / ROLLUP
• SELECT
• ROTATE
• ADDM / DELM
• PUSH / PULL
• NEST
• SWITCH
• AGGREGATE / UNAGGREGATE

16
Example
TR1
DRILLDOWN (
SELECT(
ADDM(
ROTATE(
TR1
, SUPPLIERS, Country),
PRODUCT.ClassElectronic AND DATES.Year2005),
AVG(Amount))
, DATES, COMPANIES, HGFr)
TR2
TR2
17
Example
TR2
ROTATE(
UNSELECT(
DELM(
, SUPPLIERS, Continent)
TR2
ROLLUP(
,
)
TR1
, COMPANIES, DATES, HTime)
AVG(Amount))
TR1
18
Graphic Language

• Introduction
• Conceptual Model
• Algebra Multidimensional Operators
• Graphical Manipulation Language
• Principles
• mTable spécification
• mTable manipulation
• Conclusion

19
Principles

• Based on the Algebra
• 9 graphical operators
• Each rests on algebraic operators combinations
• Manipulation of graphic elements
• Manipulation of the Constellation
• Manipulation of the mTable

20
Principles

• Graphic operations
• Display as subject / column / Line
• Insert in column / line
• Delete from table
• Restrict data
• Switch data

21
Principles

• Example Graphic DISPLAY

Subject specification  DIS_SUBJ(E) Subject specification  DIS_SUBJ(E)
EF F?FCs DISPLAY(NCs, FS, MS, DL, HL, DC, HC) with FSF and MSf1?(M1),,fw?(Mw))
EMi Mi?MF ? F?FCs DISPLAY(NCs, FS, MS, DL, HL, DC, HC) with FSF, MSfi?(Mi)
Column specification DIS_COL(E) / DIS_LN(E) Column specification DIS_COL(E) / DIS_LN(E)
ED ? H?HD ? D?DCs DISPLAY(NCs, FS, MS, DL, HL, DC, HC) with DCD, HCH?
EH H?HD? D?DCs DISPLAY(NCs, FS, MS, DL, HL, DC, HC) with DCD, HCH
Epi pi?H ? H?HD? D?DCs DRILLDOWN(ROLLUP(DISPLAY(NCs, FS, MS, DL, HL, DC, HC), DC, All), DC, pi) with DCD, HCH?
22
mTable Specification

• DISPLAY Detailed steps

DISPLAY(

)TR1
IMPORTS, SUM(Amount),
SUPPLIER, HGEO,
DATES, HTIME
23
mTable Manipulation
ADDM(
ROTATE(
TR1
DRILLDOWN (
SELECT(
, SUPPLIERS, Country),
PRODUCT.ClassElectronic AND DATES.Year2005),
AVG(Amount))
, DATES, COMPANIES, HGFr)
TR2
24
Conclusion

• Introduction
• Conceptual Model
• Algebra Multidimensional Operators
• Graphical Manipulation Language
• Conclusion

25
Conclusion

• Summary
• Multidimensional conceptual model (Constellation)
• Multi hierarchy, multi-fact
• Algebraic operator core
• Minimal and closed
• Graphic language
• Complete with regard to the core
• User-oriented analysis operations
• Future Works
• Optimized mapping to relational operations
• SQL translation
• Version management

26

• Thank you
• ADBIS 2007, Ronan Tournier, tournier_at_irit.fr
• SIG/ED Systèmes dInformations Généralisés /
  Entrepôts de Données(Generalised Information
  Systems / Data Warehouses)
• IRIT, Institut de Recherche en Informatique de
  Toulouse(Computer Science Research Institute of
  Toulouse)
• University Paul Sabatier, Toulouse 3, FRANCE.