際際滷

際際滷Share a Scribd company logo

Agreggates iii

Claudia Gomez
Claudia Gomez

The document discusses using aggregates in a data warehouse for improved query performance. It describes requirements for an aggregate navigator tool that would transparently determine the best aggregate table to use for a given query based on available dimensions and facts, table sizes, and availability of aggregates. This allows queries to always use the most performant aggregate while abstracting aggregate selection complexity from users and applications.

1 of 11
Download to read offline
Using Aggregates Ing. Julio Ernesto Carre単o Vargas
Using Aggregates the proper use of aggregates introduces a new set of requirements for the data warehouse architecture. Mechanisms must be in place to ensure that the most appropriate table is used to respond to every warehouse query. 2
Which Tables to Use? 3
The Schema Design Dimensionally designed aggregates can answer a question if all facts and dimensions required by the question are present in the aggregate. Order dollars by month for 2006 4
Relative Size Use the relative number of rows in each aggregate fact table or pre-joined aggregate to estimate the performance ranking of each option. Order dollars by month for 2006 5
Aggregate Portfolio and Availability Aggregates should be chosen each time a query is run. This allows consideration of availability status and accommodates a changing portfolio of aggregates. 6
Requirements for the Aggregate Navigator 7
aggregate navigator Evaluating schema design, table size, and availability can be confusing for an end user. And since these evaluations should be made each time a query is run, how is a query to be saved in a report? The aggregate navigator solves these problems. 8
REQUIREMENTS FOR THE AGGREGATE NAVIGATOR The aggregate navigator should permit users and applications to deal with the base schema only. The aggregate navigator should support aggregate star schemas (including aggregate dimensions) and pre-joined aggregates. It should not require changes to the base schema design. The aggregate navigator should be able to identify all necessary information automatically, through examination of the database catalog. This includes aggregate families, conformance, and relative table size. 9
REQUIREMENTS FOR THE AGGREGATE NAVIGATOR The aggregate navigator translates base-schema SQL into aggregate-aware SQL at runtime. This process is transparent to the applications that issue SQL queries. The aggregate navigator should respond to the dynamic availability of aggregates. The aggregate navigator should facilitate easy addition and removal of aggregates from the data warehouse. A single aggregate navigator should service all front-end applications. A single aggregate navigation system should service all back-end databases. 10
Bibliograf鱈a Mastering Data Warehouse Aggregates.Solutions for Star Schema Performance. Christopher Adamson. 11

More Related Content

Agreggates iii

  • 1. Using Aggregates Ing. Julio Ernesto Carre単o Vargas
  • 2. Using Aggregates the proper use of aggregates introduces a new set of requirements for the data warehouse architecture. Mechanisms must be in place to ensure that the most appropriate table is used to respond to every warehouse query. 2
  • 4. The Schema Design Dimensionally designed aggregates can answer a question if all facts and dimensions required by the question are present in the aggregate. Order dollars by month for 2006 4
  • 5. Relative Size Use the relative number of rows in each aggregate fact table or pre-joined aggregate to estimate the performance ranking of each option. Order dollars by month for 2006 5
  • 6. Aggregate Portfolio and Availability Aggregates should be chosen each time a query is run. This allows consideration of availability status and accommodates a changing portfolio of aggregates. 6
  • 7. Requirements for the Aggregate Navigator 7
  • 8. aggregate navigator Evaluating schema design, table size, and availability can be confusing for an end user. And since these evaluations should be made each time a query is run, how is a query to be saved in a report? The aggregate navigator solves these problems. 8
  • 9. REQUIREMENTS FOR THE AGGREGATE NAVIGATOR The aggregate navigator should permit users and applications to deal with the base schema only. The aggregate navigator should support aggregate star schemas (including aggregate dimensions) and pre-joined aggregates. It should not require changes to the base schema design. The aggregate navigator should be able to identify all necessary information automatically, through examination of the database catalog. This includes aggregate families, conformance, and relative table size. 9
  • 10. REQUIREMENTS FOR THE AGGREGATE NAVIGATOR The aggregate navigator translates base-schema SQL into aggregate-aware SQL at runtime. This process is transparent to the applications that issue SQL queries. The aggregate navigator should respond to the dynamic availability of aggregates. The aggregate navigator should facilitate easy addition and removal of aggregates from the data warehouse. A single aggregate navigator should service all front-end applications. A single aggregate navigation system should service all back-end databases. 10
  • 11. Bibliograf鱈a Mastering Data Warehouse Aggregates.Solutions for Star Schema Performance. Christopher Adamson. 11