(Advanced Data Warehousing) Chapter 5 - Lesson Summary - Data Warehouse Optimization
About this set
Created by:
jontbrooks on January 19, 2011
Subjects:
Classes:
Log in to favorite or report as inappropriate.
Order by
31 terms
Terms | Definitions |
|---|---|
 Aggregation refers to... | summarizing fact data |
| You can aggregate data on the fly when you run a _____, or you can aggregate data ahead of time and store the result, which is known as _____ | query, pre-aggregation |
| A base fact table stores... | fact data at the lowest levels at which a source system records transactions |
| An aggregate fact table is a fact table where the data is... | pre-aggregated and stored at a higher level for one or more hierarchies |
| An aggregate fact table is also know as... | a summary table |
| You create aggregate fact tables to... | increase query performance |
| When devising the aggregation strategy for your data warehouse, you should consider the following factors: | • query profile • attribute relationship volatility • compression rates |
| You should only create aggregate fact tables that users will... | frequently query when running reports |
| Attributes that have relationships that change frequently are considered... | volatile attributes |
| True or False Creating aggregate fact tables that include volatile attributes can significantly decrease maintenance overhead? | False (it significantly increases it) |
| For some database platforms, you can use _____ _____ as an alternative to creating aggregate fact tables | materialized views |
| Materialized views are especially useful if you need to create aggregate fact tables that include... | volatile attributes |
| The compression ratio is the... | average number of child records you combine to create a single parent record when aggregating data |
| You can calculate the compression ratio for two attributes based on the number of... | elements that exist for each attribute |
| Pre-aggregating data is only cost effective if... | the compression ratio is significant |
| Partitioning is the division of... | a larger table into smaller tables |
| There are two basic types of partitioning: | • server level • application level |
| Server-level partitioning involves... | dividing one physical table into logical partitions in the database environment |
| Application-level partitioning involves... | dividing one large table into several separate, smaller physical tables called partition base tables |
| MicroStrategy supports application-level partitioning for fact tables through... | warehouse partitioning mapping or metadata partitioning mapping |
| Application-level partitioning can provide the following advantages over server-level partitioning: | • Ability to partition a single fact table by multiple hierarchies or dimensions • Logic to determine which partitions must be accessed for a given query without requiring a filter on a specific attribute element • Less time involved to scan physical partitions rather than logical partitions |
| When devising the partitioning strategy for your data warehouse, you should consider the following factors: | • attribute relationship volatility • distribution of data across partitions • partition table size and the number of partition tables • impact of the partitioning strategy on the ETL and batch process |
| Partitioning tables based on volatile attributes can significantly increase... | maintenance overhead |
| You should choose a partitioning strategy that minimizes the size of... | partition tables while also reducing the number of tables you have to access to resolve queries |
| Your partitioning strategy should not unnecessarily lengthen or complicate the... | ETL or batch process that is used to load data in the warehouse |
| Indexes are database objects that enable quick access to the _____ in a table based on _____ values. Indexing increases _____ by ordering rows in a table so that the _____ can more easily find the particular _____ that are needed to resolve a given query | data, key, performance, database, rows |
| The simplest kind of index is a _____ _____ index. It is created _____ when you define the primary key for a _____. For tables that contain a large amount of data or are highly _____, you often need to create additional _____ besides the primary key index. | primary key, automatically, table, denormalized, indexes |
| A _____ index has a structure like a family tree. The index begins with a _____, and each row in the table is compared to this _____ value and placed within the B-tree structure. This type of index works best for higher _____ attributes | B-tree, root, root, cardinality |
| A _____ index orders the rows in a table using _____ _____ that are generated and assigned by the _____. This type of index works best for the _____ tables of low _____ attributes. | bitmap, binary strings, database, lookup, cardinality |
| An _____-_____ table stores data in the physical table in index order. This type of index is very useful for _____ tables or _____ tables of high _____ attributes | index-organized, fact, lookup, cardinality |
| When you build indexes on tables, you should consider the following factors: | • columns on which you join tables • degree of denormalization for tables • attribute elements on which you frequently filter reports • number of indexes on tables • disk storage configuration |
First Time Here?
Welcome to Quizlet, a fun, free place to study. Try these flashcards, find others to study, or make your own.