Implementing Operations to Navigate Semantic Star Schemas

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

2. Contents • Related Work • Multidimensional Algebra • Translation to SQL • New Drill-across possibilities • Conclusions Contents Alberto Abelló

3. Example of Multi-star Schema Related work Alberto Abelló

4. 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) Related work Alberto Abelló

5. 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) Related work Alberto Abelló

6. 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). Multidimensional Algebra c:L1 x .. x Ln Cc injective Alberto Abelló

7. Composition of functions Multidimensional Algebra Alberto Abelló

8. Primitive Operations • Drill-across • Change Base • Roll-up • Union • Dice • Projection Multidimensional Algebra Alberto Abelló

9. Operations (I) • Change Base • Drill-across  :Cic Coc injective Multidimensional Algebra co(x) =  (ci) =  (ci(x))  :Lo1 x .. x Lon Li1 x .. x Lim injective co(x) = (ci) = ci(  (x)) Alberto Abelló

10. Operations (II) • Roll-up • Union  :Lij Lok exhaustive co(x) =  (ci) =  ci(y) Multidimensional Algebra (y) = x { c1(x) if xDom(c1) c2(x) if xDom(c2) co(x) = c1 c2 = Alberto Abelló

11. co(x) = P(ci) = { ci(x) if P(x) undef if ¬P(x) Operations (III) • Dice Multidimensional Algebra • Projection co(x) = m1,..,mk(ci) = ci(x)[m1,..,mk] Alberto Abelló

12. Derived Operations: Slice co(x) = sliceLi=k(ci) =  L1x..xLi-1xLi+1x..xLn( Li=k(ci)) Multidimensional Algebra Alberto Abelló

13. Basic query Translation to SQL Alberto Abelló

14. Dice Translation to SQL Alberto Abelló

15. Roll-up Translation to SQL Alberto Abelló

16. Change Base Translation to SQL Alberto Abelló

17. Drill-across Translation to SQL Alberto Abelló

18. Projection Translation to SQL Alberto Abelló

19. Union Translation to SQL Alberto Abelló

20. Effects of operations Translation to SQL Alberto Abelló

21. UML Relationships New Drill-across Possibilities Alberto Abelló

22. 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 New Drill-across Possibilities Alberto Abelló

23. 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 New Drill-across Possibilities Alberto Abelló

24. 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 New Drill-across Possibilities Alberto Abelló

25. 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 New Drill-across Possibilities Alberto Abelló

26. Conclusions • Navigational (algebraic) queries • Smoothly implemented on SQL • Multistar semantic queries Conclusions Alberto Abelló

27. Questions Alberto Abelló