25 million 21025 million 031510 031510 prof navneet

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 0% 50% 50% 80% 80% 80% 100% Grand Total # Records 100 million 25 million 50 million 25 million 4 million 2 million 4 million 0.25 million 210.25 million 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 33 Aggregate Design Principle Each aggregate must summarize at least 10 and preferably 20 or more lower-level items 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 34 Aggregates 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 35 Aggregates: Shrunken Dimensions 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 36 Aggregates: Shrunken Dimensions 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 37 Aggregates: Shrunken Dimensions 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 38 Aggregates: Shrunken Dimensions 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 39 Aggregate Navigator How queries are directed to the appropriate aggregates? Do end user query tools have to be “hardcoded” to take advantage of aggregates? If DBA changed the aggregates all end user applications have to be recoded How do we overcome this problem? 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 40 Aggregate Navigator Aggregate Navigator (AN) is the solution So what is an AN? A middleware sitting between user queries middleware and DBMS and With AN, user applications speak just base With level SQL level AN uses metadata to transform base level AN SQL into “Aggregate Aware” SQL SQL 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 41 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 42 AN Algorithm 1. Rank Order all the aggregate fact tables from the smallest to the largest 2. For the smallest FT, look in associated DTs to verify that all the dimensional attributes of the current query can be found.If found, we are through. Replace the base-level FT with the aggregate fact and aggregate DTs 3. If step 2 fails, find the next smallest aggregate FT and try step 2 again. If we run out of aggregate FTs, then we must use base tables 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 43 Desig...
View Full Document

Ask a homework question - tutors are online