Data Analysis and OLAP Online Analytical Processing (OLAP) Interactive analysis of data,allowing data to be summarized and viewed in different ways in an online fashion(with negligible delay) We use the following relation to illustrate OLAP concepts sales (item_name,color,clothes_size,quantity) This is a simplified version of the sales fact table joined with the dimension tables,and many attributes removed (and some renamed) Database System Concepts-7th Edition 11.17 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 11.17 ©Silberschatz, Korth and Sudarshan th Edition Data Analysis and OLAP ▪ Online Analytical Processing (OLAP) • Interactive analysis of data, allowing data to be summarized and viewed in different ways in an online fashion (with negligible delay) ▪ We use the following relation to illustrate OLAP concepts • sales (item_name, color, clothes_size, quantity) This is a simplified version of the sales fact table joined with the dimension tables, and many attributes removed (and some renamed)
Example sales relation item name color clothes_size quantity dress dark small 2 dress dark medium dress dark large 12 dress pastel small 4 dress pastel medium 3 dress pastel large 3 dress white small 2 dress white medium 3 dress white large 0 pants dark small 14 pants dark medium 6 pants dark large 0 pants pastel small 1 pants pastel medium 0 pants pastel large 1 pants white small 3 pants white medium 0 pants white large shirt dark small shirt dark medium 6 shirt dark large 6 shirt pastel small 4 shirt pastel medium 1 shirt pastel large 2 shirt white small 17 shirt white medium 1 shirt white large 10 skirt dark small 2 skirt dark medium Database System Concepts-7th Edition 11.18 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 11.18 ©Silberschatz, Korth and Sudarshan th Edition Example sales relation ... ... ... ... ... ... ...
Cross Tabulation of sales by item_name and color clothes size all color dark pastel white total skirt 8 35 10 53 item name dress 20 10 5 35 shirt 14 7 28 49 pants 20 2 5 27 total 62 54 48 164 The table above is an example of a cross-tabulation(cross-tab),also referred to as a pivot-table. Values for one of the dimension attributes form the row headers Values for another dimension attribute form the column headers Other dimension attributes are listed on top Values in individual cells are (aggregates of)the values of the dimension attributes that specify the cell. Database System Concepts-7th Edition 11.19 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 11.19 ©Silberschatz, Korth and Sudarshan th Edition Cross Tabulation of sales by item_name and color ▪ The table above is an example of a cross-tabulation (cross-tab), also referred to as a pivot-table. • Values for one of the dimension attributes form the row headers • Values for another dimension attribute form the column headers • Other dimension attributes are listed on top • Values in individual cells are (aggregates of) the values of the dimension attributes that specify the cell
Data Cube A data cube is a multidimensional generalization of a cross-tab Can have n dimensions;we show 3 below Cross-tabs can be used as views on a data cube 2 5 3 11 4 7 6 12 729 2 8 5 7 22 16 dark 8 20 14 20 62 4 34 0J03 18 pastel 35 10 7 2 54 21 45 white 10 5 28 5 48 42 77 small medium all 53 35 49 27 164 large all clothes_size skirt dress shirt pants all item name Database System Concepts-7th Edition 11.20 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 11.20 ©Silberschatz, Korth and Sudarshan th Edition Data Cube ▪ A data cube is a multidimensional generalization of a cross-tab ▪ Can have n dimensions; we show 3 below ▪ Cross-tabs can be used as views on a data cube
Online Analytical Processing Operations Pivoting:changing the dimensions used in a cross-tab E.g.,moving colors to column names Slicing:creating a cross-tab for fixed values only E.g.,fixing color to white and size to small Sometimes called dicing,particularly when values for multiple dimensions are fixed. Rollup:moving from finer-granularity data to a coarser granularity E.g.,aggregating away an attribute E.g.,moving from aggregates by day to aggregates by month or year a Drill down:The opposite operation-that of moving from coarser- granularity data to finer-granularity data Database System Concepts-7th Edition 11.21 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 11.21 ©Silberschatz, Korth and Sudarshan th Edition Online Analytical Processing Operations ▪ Pivoting: changing the dimensions used in a cross-tab • E.g., moving colors to column names ▪ Slicing: creating a cross-tab for fixed values only • E.g., fixing color to white and size to small • Sometimes called dicing, particularly when values for multiple dimensions are fixed. ▪ Rollup: moving from finer-granularity data to a coarser granularity • E.g., aggregating away an attribute • E.g., moving from aggregates by day to aggregates by month or year ▪ Drill down:The opposite operation - that of moving from coarsergranularity data to finer-granularity data