Transcript YAM2 A Multidimensional Conceptual Model

Implementing Operations to
Navigate Semantic Star Schemas
Alberto Abelló, José Samos and Fèlix Saltor
U. Politècnica de Catalunya & U. de Granada
November 7th, 2003
DOLAP
Contents
Contents





Related Work
Multidimensional Algebra
Translation to SQL
New Drill-across possibilities
Conclusions
November 7th, 2003
Alberto Abelló
2
Related work
Example of Multi-star Schema
November 7th, 2003
Alberto Abelló
3
Related work
Multi-star Schemas





Kimball: Share Dimensions
Giovinazzo: Galaxy sharing Dimensions
Pedersen and Jensen: Multidimensional
Object Family sharing subdimensions
Gopalkrishnan, Li, and Karlapalem:
Multi-star Schemas normalizing fact
tables
Moody and Kortink:
– Constellation (hierarchically linked fact tables)
– Galaxy (share Dimensions)
– Star Cluster (sharing subDimensions)
November 7th, 2003
Alberto Abelló
4
Related work
Semantic Relationships



Tryfona, Busborg, and Christiansen: EER
Trujillo, Palomar, Gómez and Song: UML
(Generalization and Association inside a
Star)
Abelló, Samos and Saltor: UML
(Generalization, Association, Derivation
and Flow between Stars)
November 7th, 2003
Alberto Abelló
5
Multidimensional Algebra
Cube
A Cube is and injective function from an n-dimensional
finite space (defined by the cartesian product of n
functionally independent Levels {L1,..,Ln}), to the set of
instances of a Cell (Cc).
c:L1 x .. x Ln  Cc injective
November 7th, 2003
Alberto Abelló
6
Multidimensional Algebra
Composition of functions
November 7th, 2003
Alberto Abelló
7
Multidimensional Algebra
Primitive Operations






Drill-across
Change Base
Roll-up
Union
Dice
Projection
November 7th, 2003
Alberto Abelló
8
Multidimensional Algebra
Operations (I)

Drill-across
 :Cic  Coc injective
co(x) =  (ci) =  (ci(x))

Change Base
 :Lo1 x .. x Lon  Li1 x .. x Lim injective
co(x) =  (ci) = ci(  (x))
November 7th, 2003
Alberto Abelló
9
Multidimensional Algebra
Operations (II)

Roll-up
 :Lij  Lok exhaustive
co(x) =  (ci) =


(y) = x
ci(y)
Union
{
co(x) = c1  c2 =
November 7th, 2003
c1(x)
c2(x)
Alberto Abelló
if xDom(c1)
if xDom(c2)
10
Multidimensional Algebra
Operations (III)

Dice
{
co(x) = P(ci) =

ci(x) if P(x)
undef if ¬P(x)
Projection
co(x) = m1,..,mk(ci) = ci(x)[m1,..,mk]
November 7th, 2003
Alberto Abelló
11
Multidimensional Algebra
Derived Operations: Slice
co(x) = sliceLi=k(ci) =  L1x..xLi-1xLi+1x..xLn( Li=k(ci))
November 7th, 2003
Alberto Abelló
12
Translation to SQL
Basic query
November 7th, 2003
Alberto Abelló
13
Translation to SQL
Dice
November 7th, 2003
Alberto Abelló
14
Translation to SQL
Roll-up
November 7th, 2003
Alberto Abelló
15
Translation to SQL
Change Base
November 7th, 2003
Alberto Abelló
16
Translation to SQL
Drill-across
November 7th, 2003
Alberto Abelló
17
Translation to SQL
Projection
November 7th, 2003
Alberto Abelló
18
Translation to SQL
Union
November 7th, 2003
Alberto Abelló
19
Translation to SQL
Effects of operations
Clause
SELECT
ChangeBase Drill-across Dice Roll-up Projection Union
Level
(replace)
FROM
Dimension
(add)
WHERE
links
(add)
GROUP BY Level
(replace)
ORDER BY Level
(replace)
November 7th, 2003
Measure
(add)
Fact
(add)
links
(add)
Level
Measure
(replace) (remove)
AND
OR
Level
(replace)
Level
(replace)
Alberto Abelló
20
New Drill-across Possibilities
UML Relationships
November 7th, 2003
Alberto Abelló
21
New Drill-across Possibilities
Derivation


Implementation: Updatable views
ChangeBase
– View to table -> OK
– Table to view -> Iff view subsumes WHERE

Drill-across
– View to table -> OK
– Table to view -> Outer join
November 7th, 2003
Alberto Abelló
22
New Drill-across Possibilities
Generalization


Implementation: One table for superclass
and another one for each subclass
ChangeBase
– Sub to super -> OK
– Super to sub -> Iff criterion subsumes WHERE

Drill-across
– Sub to super -> OK
– Super to sub -> Outer join
November 7th, 2003
Alberto Abelló
23
New Drill-across Possibilities
Association


Implementation: Foreign keys
ChangeBase
– 1:1 -> OK
– 1:N and N:1 -> Depends on the bases

Drill-across
– 1:1 -> OK
– 1:N -> Do it to an upper detail level being 1:1
– N:1 -> Double counting
– Zeros -> Outer join
November 7th, 2003
Alberto Abelló
24
New Drill-across Possibilities
Flow


Implementation: Foreign keys
ChangeBase
– #old=#new -> OK
– #old<>#new -> Weighting factors for facts
– Appear or disappear -> Dice apropriately

Drill-across
– #old=#new -> OK
– #old<>#new -> ChangeBase
– Appear or disappear -> Outer join
November 7th, 2003
Alberto Abelló
25
Conclusions
Conclusions



Navigational (algebraic) queries
Smoothly implemented on SQL
Multistar semantic queries
November 7th, 2003
Alberto Abelló
26
Questions
November 7th, 2003
Alberto Abelló
27