“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 Performance (think of materialized views) o Quick responses Citation: http://www.psdgraphics.com/file/hourglass icon.jpg

Types of Derived Data

● Aggregates ● Text analytics ● Calculated scores

We’ll talk about just this one

● 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 SELECT name, AVG(population) FROM country GROUP BY name; Pre-computed aggregation via views CREATE VIEW Pop_View as SELECT name, AVG(population) average_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.