Data Warehousing MCQs with Explanations

In the context of data warehousing and database design, fact tables are typically structured to be completely denormalized . Denormalization is a process where data is intentionally organized in a way that reduces or eliminates the need for joins between tables, often at the expense of redundancy. This is done to improve query performance in data warehousing environments, where analytical queries are common. Here's an explanation of the other options: Option B: Partially denormalized doesn't accurately describe fact tables. While some degree of denormalization may occur, the term "completely denormalized" is a more appropriate characterization. Option C: Completely normalized is not typically the case for fact tables in data warehousing. Normalization involves minimizing data redundancy by organizing data into separate tables based on relationships, which is the opposite of what is done in a fact table. Option D: Partially normalized also doesn't accurately describe fact tables. Fact tables are intentionally denormalized for performance reasons, so "completely denormalized" is the more suitable term. So, the correct answer is Option A: Completely denormalized . Fact tables in data warehousing are often structured in a way that optimizes query performance, even if it means duplicating data to reduce the need for complex joins.

Data transformation involves various processes that include: Changing data from a detailed level to a summary level: This process is a key aspect of data transformation where detailed data is aggregated or summarized to provide a higher-level view. This process helps in creating summaries and reports that are useful for analysis and decision-making. Other options involve joining or separating data, which are related to data integration and partitioning, not specifically to data transformation.

A process to upgrade the quality of data before it is moved into a data warehouse is the correct answer to “Data scrubbing is which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

All of the above is the correct answer to “The @active data warehouse architecture includes which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

To explain some observed event or condition is the correct answer to “A goal of data mining includes which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

A system that is used to run the business in real time and is based on current data. is the correct answer to “An operational system is which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

Organized around important subject areas. is the correct answer to “A data warehouse is which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

All of the above is the correct answer to “A snowflake schema is which of the following types of tables”. This question is standard in competitive examination syllabi for Data Warehousing.

Data that can extracted from numerous internal and external sources is the correct answer to “The generic two-level data warehouse architecture includes which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

Current data intended to be the single source for all decision support systems. is the correct answer to “Reconciled data is which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

A process to load the data in the data warehouse and to create the necessary indexes is the correct answer to “The load and index is which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

Capturing a subset of the data contained in various operational systems is the correct answer to “The extract process is which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

One-to-many is the correct answer to “A star schema has what type of relationship between a dimension and fact table”. This question is standard in competitive examination syllabi for Data Warehousing.

Data in which changes to existing records cause the previous version of the records to be eliminated is the correct answer to “Transient data is which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

All of the above is the correct answer to “A multifield transformation does which of the following”. This question is standard in competitive examination syllabi for Data Warehousing.

True is the correct answer to “A data mart is designed to optimize the performance for well-defined and predicable uses.”. This question is standard in competitive examination syllabi for Data Warehousing.

True is the correct answer to “Successful data warehousing requires that a formal program in total quality management (TQM) be implemented.”. This question is standard in competitive examination syllabi for Data Warehousing.

True is the correct answer to “Data in operational systems are typically fragmented and inconsistent.”. This question is standard in competitive examination syllabi for Data Warehousing.

True is the correct answer to “Most operational systems are based on the use of transient data.”. This question is standard in competitive examination syllabi for Data Warehousing.

True is the correct answer to “Independent data marts are often created because an organization focuses on a series of short-term business objectives.”. This question is standard in competitive examination syllabi for Data Warehousing.

False is the correct answer to “Joining is the process of partitioning data according to predefined criteria.”. This question is standard in competitive examination syllabi for Data Warehousing.

False is the correct answer to “The role of the ETL process is to identify erroneous data and to fix them.”. This question is standard in competitive examination syllabi for Data Warehousing.

True is the correct answer to “Data in the data warehouse are loaded and refreshed from operational systems.”. This question is standard in competitive examination syllabi for Data Warehousing.

False is the correct answer to “Star schema is suited to online transaction processing, and therefore is generally used in operational systems, operational data stores, or an EDW.”. This question is standard in competitive examination syllabi for Data Warehousing.

False is the correct answer to “Periodic data are data that are physically altered once added to the store.”. This question is standard in competitive examination syllabi for Data Warehousing.

True is the correct answer to “Both status data and event data can be stored in a database.”. This question is standard in competitive examination syllabi for Data Warehousing.

False is the correct answer to “Static extract is used for ongoing warehouse maintenance.”. This question is standard in competitive examination syllabi for Data Warehousing.

True is the correct answer to “Data scrubbing can help upgrade data quality; it is not a long-term solution to the data quality problem.”. This question is standard in competitive examination syllabi for Data Warehousing.

True is the correct answer to “Every key used to join the fact table with a dimensional table should be a surrogate key.”. This question is standard in competitive examination syllabi for Data Warehousing.

False is the correct answer to “Derived data are detailed, current data intended to be the single, authoritative source for all decision support applications.”. This question is standard in competitive examination syllabi for Data Warehousing.