General Research Report Implementation and Optimization Issues of the ROLAP Algebra

1
General Research ReportImplementation and
Optimization Issues of the ROLAP Algebra

• F. Ramsak, M.S. (UIUC)
• Dr. V. Markl
• Prof. R. Bayer, Ph.D.

2
Contents

• Motivation
• ROLAP Algebra Recap
• Optimization Issues
• Handling of Restrictions
• Aggregation Networks
• Future Work Summary

3
Example DW Model
4
Users View of a Report
Grouping combinations used to fill pivot
table (1)Y,Q,R,N (2)Y,Q,R (3) Y,Q (4)
Y,R,N (5)Y,R (6)Y (7) R,N (8) R
(9) ALL
5
POT Pivot Organized Tuples

• We may also write for POT(R,G,F).

(R)
G,F
6
POT-Example

• POT(R,A,A,B,sum(D))
• yields the table

Sum(
)
A
B
D
a
ALL

1


ALL

a
n
a
b

1
1



a

n



a
b

1
m



a
b

n
m
7
POT Extension Group Filtering

• Filtering of generated groups (like with the
  HAVING clause in SQL)with H containing a
  predicate Hg for each grouping g in G

8
Group Filtering Example

• Report Years, Product-Group sales totals and
  sales/year for PGs with less than 10 Mio sales

9
Straight Forward SQL Generation

• POT(R,A,A,B,sum(D)) maps directly to
• SELECT A, ALL, sum(D)
• FROM R
• UNION
• SELECT A, B, sum(D)
• FROM R
• Disadvantages
• Efficient execution depends on optimizer of
  underlying DBMS
• no UB-Tree support on SQL interface guaranteed

10
Handling Restrictions

• Semantic of ALL value
• Pushing Restrictions Down
• Pushing Through POT Restrictions on all
  groups
• Pushing down inside POT Restrictions on
  individual groups may be pushed down (i.e.,
  before grouping) if they do not contain
  constraints on the aggregation results

11
(No Transcript)
12
(No Transcript)
13
Aggregation Networks

• Efficient generation of multiple groups
• Usage of previous generated (more finer) groups
  instead of fact table as starting point
• Only one access to the fact table for multiple
  groups
• Problems Size of aggregation nets
• Hierarchy semantic reduces aggregation nets
  significantly
• UB-Tree Tetris techniques have high potential
  to optimize aggregation nets
• Grouping requires sorting
• Sorted writing of large temporary results saves
  additional processing time

14
Example of anAggregation Network
( )
(Month)
(Year)
(Productgroup)
(Year, Month)
(Year, Productgroup)
(Month, Productgroup)
(Year, Month, Productgroup)
15
AggregationNet withHierarchies
( )
(Year)
(Productgroup)
Sort according to PG (or sorted writingScan)
(Year, Month)
(Year, Productgroup)
Tetris sort according to Y
(Year, Month, Productgroup)
16
POT and AggregationNets
17
Optimization Issues ofAggregationNets

• Find minimal spanning tree for the specified
  groupings
• Vertices groupings
• Edge weights cost of computing new group
• Cost factors
• Group size
• Required sorting
• ...

18
OptimizationIssuesofAggregationNets
( )
C6
C7
(Year)
(Productgroup)
C4
C3
C5
(Year, Month)
(Year, Productgroup)
C2
C1
(Year, Month, Productgroup)
19
Summary and Future Work

• Aggregation networks have a very potential to
  speed up POT operations
• Standard grouping/aggregation algorithms may
  benefit significantly from UB-Tree/Tetris
  techniques
• Upon availability of resources Implementation of
  basic ROLAP algebra processing as part of a
  master thesis