OLAP Implementation The earliest OLAP systems used multidimensional arrays in memory to store data cubes,and are referred to as multidimensional OLAP (MOLAP)systems. OLAP implementations using only relational database features are called relational OLAP(ROLAP)systems Hybrid systems,which store some summaries in memory and store the base data and other summaries in a relational database,are called hybrid OLAP(HOLAP)systems. Database System Concepts-5th Edition,Aug 26,2005 18.12 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 18.12 ©Silberschatz, Korth and Sudarshan th Edition, Aug 26, 2005 OLAP Implementation The earliest OLAP systems used multidimensional arrays in memory to store data cubes, and are referred to as multidimensional OLAP (MOLAP) systems. OLAP implementations using only relational database features are called relational OLAP (ROLAP) systems Hybrid systems, which store some summaries in memory and store the base data and other summaries in a relational database, are called hybrid OLAP (HOLAP) systems
OLAP Implementation (Cont.) Early OLAP systems precomputed all possible aggregates in order to provide online response Space and time requirements for doing so can be very high 2n combinations of group by It suffices to precompute some aggregates,and compute others on demand from one of the precomputed aggregates Can compute aggregate on(item-name,color)from an aggregate on (item-name,color,size) -For all but a few"non-decomposable"aggregates such as median -is cheaper than computing it from scratch Several optimizations available for computing multiple aggregates Can compute aggregate on (item-name,color)from an aggregate on (item-name,color,size) Can compute aggregates on (item-name,color,size), (item-name,color)and(item-name)using a single sorting of the base data Database System Concepts-5th Edition,Aug 26,2005 18.13 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 18.13 ©Silberschatz, Korth and Sudarshan th Edition, Aug 26, 2005 OLAP Implementation (Cont.) Early OLAP systems precomputed all possible aggregates in order to provide online response Space and time requirements for doing so can be very high 2 n combinations of group by It suffices to precompute some aggregates, and compute others on demand from one of the precomputed aggregates Can compute aggregate on (item-name, color) from an aggregate on (item-name, color, size) – For all but a few “non-decomposable” aggregates such as median – is cheaper than computing it from scratch Several optimizations available for computing multiple aggregates Can compute aggregate on (item-name, color) from an aggregate on (item-name, color, size) Can compute aggregates on (item-name, color, size), (item-name, color) and (item-name) using a single sorting of the base data
Extended Aggregation in SQL:1999 The cube operation computes union of group by's on every subset of the specified attributes E.g.consider the query select item-name,color,size,sum(number) from sales group by cube(item-name,color,size) This computes the union of eight different groupings of the sales relation: {(item-name,color,size),(item-name,color), (item-name,size), (color,size), (item-name), (color, (size), ()} where ()denotes an empty group by list. For each grouping,the result contains the null value for attributes not present in the grouping. Database System Concepts-5th Edition,Aug 26,2005 18.14 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 18.14 ©Silberschatz, Korth and Sudarshan th Edition, Aug 26, 2005 Extended Aggregation in SQL:1999 The cube operation computes union of group by’s on every subset of the specified attributes E.g. consider the query select item-name, color, size, sum(number) from sales group by cube(item-name, color, size) This computes the union of eight different groupings of the sales relation: { (item-name, color, size), (item-name, color), (item-name, size), (color, size), (item-name), (color), (size), ( ) } where ( ) denotes an empty group by list. For each grouping, the result contains the null value for attributes not present in the grouping
Extended Aggregation (Cont.) Relational representation of cross-tab that we saw earlier,but with null in place of all,can be computed by select item-name,color,sum(number) from sales group by cube(item-name,color) The function grouping()can be applied on an attribute Returns 1 if the value is a null value representing all,and returns 0 in all other cases. select item-name,color,size,sum(number), grouping(item-name)as item-name-flag, grouping(color)as color-flag, grouping(size)as size-flag, from sales group by cube(item-name,color,size) Can use the function decode()in the select clause to replace such nulls by a value such as all E.g.replace item-name in first query by decode(grouping(item-name),1,'all',item-name) Database System Concepts-5th Edition,Aug 26,2005 18.15 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 18.15 ©Silberschatz, Korth and Sudarshan th Edition, Aug 26, 2005 Extended Aggregation (Cont.) Relational representation of cross-tab that we saw earlier, but with null in place of all, can be computed by select item-name, color, sum(number) from sales group by cube(item-name, color) The function grouping() can be applied on an attribute Returns 1 if the value is a null value representing all, and returns 0 in all other cases. select item-name, color, size, sum(number), grouping(item-name) as item-name-flag, grouping(color) as color-flag, grouping(size) as size-flag, from sales group by cube(item-name, color, size) Can use the function decode() in the select clause to replace such nulls by a value such as all E.g. replace item-name in first query by decode( grouping(item-name), 1, ‘all’, item-name)
Extended Aggregation (Cont.) The rollup construct generates union on every prefix of specified list of attributes E.g. select item-name,color,size,sum(number) from sales group by rollup(item-name,color,size) Generates union of four groupings: {(item-name,color,size),(item-name,color),(item-name),() Rollup can be used to generate aggregates at multiple levels of a hierarchy. E.g.,suppose table itemcategory(item-name,category)gives the category of each item.Then select category,item-name,sum(number) from sales,itemcategory where sales.item-name itemcategory.item-name group by rollup(category,item-name) would give a hierarchical summary by item-name and by category. Database System Concepts-5th Edition,Aug 26,2005 18.16 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 18.16 ©Silberschatz, Korth and Sudarshan th Edition, Aug 26, 2005 Extended Aggregation (Cont.) The rollup construct generates union on every prefix of specified list of attributes E.g. select item-name, color, size, sum(number) from sales group by rollup(item-name, color, size) Generates union of four groupings: { (item-name, color, size), (item-name, color), (item-name), ( ) } Rollup can be used to generate aggregates at multiple levels of a hierarchy. E.g., suppose table itemcategory(item-name, category) gives the category of each item. Then select category, item-name, sum(number) from sales, itemcategory where sales.item-name = itemcategory.item-name group by rollup(category, item-name) would give a hierarchical summary by item-name and by category