“Add Derived Data to Your DBMS [performance tuning] Strategy” Group 3 Andrew Hall Zihong Huang Relationship to our course: Performance tuning is the focus for weeks 2-6 We learned many tricks in chapters 17, 18, 19. Derived data is another trick commonly used in Data Warehouses! Motivation ● So why would derived data be needed in a DBMS? o o Performance (think of materialized views) Quick responses Citation: http://www.psdgraphics.com/file/hourglassicon.jpg Types of Derived Data ● ● ● ● ● Aggregates We’ll talk about just this one Text analytics Calculated scores ETL (extract, transform and load) Adjusted data Aggregation: Materialized Views CREATE TABLE country ( name char(50), year char(4), population decimal(11), primary key (name,year) ); Traditional selection with aggregates Pre-computed aggregation via views CREATE VIEW Pop_View as SELECT name, AVG(population) average_population FROM country GROUP BY name; SELECT name, AVG(population) FROM country GROUP BY name; Better performance if view is materialized! SELECT * FROM Pop_View; Aggregates Examples ● Course Registration o The available seats in a class ● Number of patients prescribed blood-thinning drugs ● Amount of Kemps milk sold at Cub Foods each month ● Total number of flights and the average percentage of filled seats in those flights Companies/Products Supporting Materialized Views • Oracle • PostgreSQL • IBM DB2 (materialized query tables) • Microsoft SQL Server (indexed views) Questions? References 1. 2. 3. Monash, Curt. “Add Derived Data To Your DBMS Strategy.” InformationWeek. N.p., n.d. Web. 10 Feb. 2015. http://www.spatial.cs.umn.edu/Courses/Spring15/5708/labs_2015/country.sql. Web. 10 Feb. 2015. “Materialized View.” Wikipedia, the free encyclopedia 31 Jan. 2015. Wikipedia. Web. 11 Feb. 2015.