WorksheetsCSA1620 DWDM Quiz on Data Warehouse
Total questions: 100
Worksheet time: 1hrs 15mins
What is the primary purpose of a Data Warehouse?
To store current transactional data
To store historical data for analysis and reporting
To manage day-to-day operations
To process real-time data
Which of the following is NOT a characteristic of a Data Warehouse?
Subject-oriented
Integrated
Transaction-oriented
Time-variant
Which of the following is typically used for Decision Support Systems (DSS)?
Relational Database Management Systems (RDBMS)
Online Analytical Processing (OLAP)
Data Mining Algorithms
All of the above
Which of the following is a main feature of a Decision Support System (DSS)?
It focuses on routine operational decisions.
It is intended to support non-routine, complex decision-making.
It is only used for financial reporting.
It is used for real-time transaction processing.
In a Data Warehouse, what does the term 'ETL' stand for?
Extract, Transfer, Load
Extract, Transform, Load
Execute, Transfer, Load
Execute, Transform, Link
Which of the following is true about OLAP (Online Analytical Processing)?
OLAP is optimized for complex calculations and querying large volumes of data.
OLAP is primarily used for transaction processing in operational systems.
OLAP databases are mainly used for real-time processing of small datasets.
OLAP does not support multidimensional data views.
Which of the following is NOT a common use of Decision Support Systems?
Forecasting market trends
Supporting customer service calls
Analyzing sales performance
Helping in strategic decision-making
What is the main difference between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing)?
OLTP is used for analytical queries, while OLAP is used for transaction processing.
OLTP involves complex queries and large data volumes, while OLAP involves simple queries and small data volumes.
OLTP focuses on real-time processing and operational tasks, while OLAP focuses on complex, multi-dimensional queries and analysis.
There is no difference; both terms refer to the same type of system.
Which of the following is NOT a typical component of a Decision Support System (DSS)?
Data Management System
Model Management System
Knowledge Management System
Transaction Processing System
In the context of Data Warehousing, what is the purpose of a 'Fact Table'?
To store metadata
To store dimension data
To store aggregated or transactional data
To store historical data
What is the main objective of using Data Warehousing in Decision Support Systems?
To create a centralized data repository for easier management of data
To support real-time decision-making based on transactional data
To provide a historical data repository for complex querying and analysis
To store unstructured data for machine learning applications
Which of the following is an example of a Data Warehousing tool?
Microsoft Excel
IBM Cognos
MySQL
Oracle Database
What is the primary purpose of an Operational System?
To support decision-making processes
To support day-to-day transactions
To analyze historical data
To generate reports for top management
Which of the following is a characteristic of a Decision Support System (DSS)?
It processes day-to-day transactions
It is used for long-term strategic decision-making
It only supports transactional queries
It operates in real-time for operational tasks
Which of the following is true about Operational Systems?
They are used for analytical decision-making
They support real-time, transactional data
They store data for future analysis
They handle complex queries for business intelligence
Which of the following is an example of a Decision Support System?
A system used for real-time billing
A system used for inventory management
A system used for market trend forecasting
A system used for real-time order processing
What type of data does an Operational System typically store?
Historical data for analysis
Data that supports real-time transactions
Aggregated data from multiple sources
Data that supports long-term decision-making
Decision Support Systems (DSS) are primarily used to:
Automate routine operations
Analyze historical data to make non-routine decisions
Process real-time transactional data
Monitor inventory levels
Which of the following is a characteristic of a Decision Support System (DSS)?
Supports real-time processing of operational data
Focuses on routine tasks and transactions
Supports complex analysis and decision-making for business strategies
Primarily processes data from the internet
In which system do users typically perform routine queries and transactions?
DSS
Data Warehouse
OLAP
Operational System
What is the main difference between operational and decision support systems?
Operational systems are used for analysis; DSS are used for transactions.
DSS help with strategic decision-making, whereas operational systems help with day-to-day operations.
Operational systems focus on business strategy; DSS focus on daily tasks.
There is no difference.
Which type of system would be best for performing trend analysis?
Operational System
DSS
OLAP
CRM system
What kind of data is typically stored in Decision Support Systems?
Real-time transaction data
Large, historical datasets used for analysis
Data related to individual user behaviour
Data from daily operations
Which system is most likely to use complex queries and aggregate large amounts of data?
OLTP
Operational System
DSS
Real-time system
Which of the following systems would a company use for decision-making based on sales performance?
Operational System
DSS
OLAP
CRM System
What system would be used to process transactions in real-time?
Decision Support System
OLAP
Operational System
Data Warehouse
Which system typically deals with high transaction volumes and real-time operations?
Data Warehouse
DSS
OLTP
OLAP
Which of the following is the central component of a Data Warehouse architecture?
OLTP
Data Warehouse Database
Reporting Tools
Operational Data Store (ODS)
What is the purpose of the staging area in a Data Warehouse architecture?
To store metadata
To perform data transformations and cleansing before loading
To store aggregated data
To store transactional data
Which of the following is NOT part of a typical Data Warehouse Architecture?
Data Source Layer
ETL Layer
OLAP Engine
Transaction Processing System
In a Data Warehouse architecture, what does the ETL layer stand for?
Execute, Transform, Load
Extract, Transform, Load
Extract, Transfer, Load
Execute, Transfer, Link
Which of the following best describes the Data Mart layer in a Data Warehouse architecture?
A place for storing real-time transactional data
A data storage area optimized for specific business areas
A place where ETL processes occur
A storage layer for historical operational data
What is the main purpose of an Operational Data Store (ODS)?
To provide real-time operational data
To perform data analysis
To store historical data for reporting
To aggregate large datasets
In Data Warehouse architecture, which component is responsible for querying and reporting?
Data Mart
ETL Process
Data Warehouse Database
Reporting Tools
Which of the following layers handles the extraction of data from source systems?
Data Mart
ETL Layer
Reporting Tools
Data Warehouse Database
Which is the final stage in the ETL process?
Data Extraction
Data Transformation
Data Loading
Data Cleansing
Which of the following is a feature of Data Warehouse architecture?
It supports real-time transaction processing
It is designed to handle large volumes of historical data
It is used for operational tasks
It operates primarily in the OLTP environment
What role does the OLAP layer serve in a Data Warehouse architecture?
It stores transactional data
It provides data transformation services
It supports multidimensional data analysis and reporting
It stores raw data from source systems
In a Data Warehouse architecture, which of the following processes is part of the ETL function?
Real-time data analytics
Data aggregation
Data extraction, transformation, and loading
Data visualization
In a Data Warehouse architecture, the Data Warehouse Database stores:
Real-time transactional data
Cleaned and transformed historical data
Metadata only
Data marts for specific departments
Which of the following components of Data Warehouse architecture is responsible for data analysis and reporting?
OLAP Engine
Data Warehouse Database
Staging Area
Operational Data Store
Which layer in the Data Warehouse architecture stores the raw transactional data before it is processed?
Data Warehouse Database
Operational Data Store (ODS)
Data Mart
ETL Layer
Which of the following is the first step in the ETL process?
Data Transformation
Data Extraction
Data Loading
Data Cleansing
What does the "Transform" phase in the ETL process involve?
Extracting data from source systems
Loading data into the data warehouse
Converting data into a consistent format and cleaning it
Analysing data for reporting
Which of the following tools is commonly used for the ETL process?
SQL
Hadoop
Talend
Excel
Why is data transformation important in the ETL process?
It stores raw data for future use
It standardizes the data, making it suitable for analysis
It speeds up data extraction
It is used for real-time reporting
Which of the following best describes the "Load" phase in ETL?
Extracting data from source systems
Converting data into a suitable format
Loading the cleaned and transformed data into the target database
Validating data for accuracy
ETL processes typically run:
In real-time
On a schedule or batch basis
Only once
When data is queried
Which of the following would be an example of a source system for ETL?
Data Warehouse
CRM System
OLAP Cube
Data Mart
Which of the following is an important benefit of ETL?
It stores unprocessed data
It ensures that data is loaded in its raw form
It improves the accuracy and quality of data for reporting
It generates real-time reports
Which of the following data transformation operations could be done during the ETL process?
Normalization
Aggregation
Data Cleaning
All of the above
Which of the following types of data transformation is NOT performed during the ETL process?
Data Mapping
Data Normalization
Data Cleansing
Data Visualization
What happens if data fails to load correctly during the ETL process?
The data is permanently lost
The ETL process stops immediately
An error is logged, and the process can be restarted
The data is automatically corrected
Which of the following steps would be part of data transformation during ETL?
Changing date formats
Filtering out invalid data
Calculating new values or metrics
All of the above
ETL is a process that typically:
Loads raw transactional data into the data warehouse
Extracts, transforms, and loads data into a Data Warehouse for analysis
Manages real-time data processing
Analyses business trends for decision-making
What type of errors does data cleansing in the ETL process typically address?
Missing values
Invalid data formats
Duplicate records
All of the above
Which of the following would be an example of a transformation performed during ETL?
Removing duplicates
Converting all text to lowercase
Calculating totals and averages
All of the above
What does OLAP stand for?
Online Application Processing
Online Analytical Processing
On-demand Analytical Processing
Offline Analytical Processing
Which of the following is a feature of OLAP systems?
Real-time transaction processing
Multi-dimensional analysis
Relational database design
High transaction throughput
Which of the following is the main objective of OLAP?
Transactional data storage
Analysis of business data
Real-time transaction tracking
Predicting sales trends
A data cube in OLAP represents data in which type of structure?
One-dimensional
Two-dimensional
Multi-dimensional
Tabular
Which operation in OLAP allows users to view data from different perspectives?
Drill-down
Roll-up
Slice-and-Dice
Pivot
What is a fact table in OLAP?
A table that stores only dimensions
A table that stores aggregated measures
A table with raw transaction data
A table containing only key values
Which operation in OLAP involves aggregating data to a higher level?
Drill-down
Roll-up
Slice
Pivot
The term "slicing" in OLAP refers to:
Filtering data from a single dimension
Aggregating data
Changing the structure of the data cube
Adding new dimensions
Which of the following is a popular OLAP tool?
Excel
Microsoft Access
Power BI
All of the above
In OLAP, a “dimension” is:
A measure of business performance
A structure that categorizes facts
A way of organizing data
A method for aggregating data
What does a Data Cube allow users to do in OLAP?
Perform complex calculations
Extract data from multiple tables
Slice and dice data along multiple dimensions
Create new reports
Which OLAP operation involves zooming into detailed data?
Roll-up
Drill-down
Slice
Dice
In OLAP, a "measure" is typically:
A dimension
A key in the fact table
A column in the fact table containing numerical data
An attribute of a dimension
Which type of OLAP system is typically used for multidimensional analysis of data stored in relational databases?
MOLAP
ROLAP
HOLAP
None of the above
Which of the following OLAP operations allows users to change the dimensions of the cube for a more intuitive view?
Pivot
Slice
Roll-up
Drill-down
In a star schema, the fact table is:
At the centre, surrounded by dimension tables
At the top of the hierarchy
An independent table
Split into multiple sub-tables
In which schema do dimension tables normalize their data into multiple related tables?
Star Schema
Snowflake Schema
Fact Schema
Galaxy Schema
What is the main advantage of the star schema?
Reduced redundancy
Increased query performance
Simplicity and fast querying
Reduced complexity
Which of the following is true about the snowflake schema?
It involves more data redundancy than a star schema
It does not support multiple fact tables
It normalizes dimensions
It stores only aggregated data
What is the main disadvantage of a snowflake schema?
More complex queries
High redundancy in data
Lack of support for complex analytics
Fewer dimensions
A star schema typically contains which of the following?
One central fact table and multiple dimension tables
Multiple fact tables and dimension tables
Several fact tables only
Only dimension tables
In a star schema, the dimension tables are:
Denormalized
Highly normalized
Fully indexed
Empty
What is the main purpose of using the snowflake schema?
To reduce redundancy and improve normalization
To simplify queries
To increase data redundancy
To support transactional processing
Which of the following is an example of a fact table in a star schema?
Sales table containing order totals
Product table
Customer information table
Date table
Which schema design is most efficient in terms of storage?
Star Schema
Snowflake Schema
Hybrid Schema
Relational Schema
What is the key feature of the snowflake schema?
Normalized dimension tables
Denormalized fact tables
Multiple central fact tables
Lack of dimension tables
In a snowflake schema, how are dimension tables structured?
Denormalized
Hierarchically structured and normalized
Partitioned
A single table for all dimensions
Which schema is more difficult to design and maintain?
Star Schema
Snowflake Schema
Hybrid Schema
Both star and snowflake schemas
Which schema is considered more complex for querying?
Star Schema
Snowflake Schema
Hybrid Schema
Both
In a star schema, how are dimension tables related to the fact table?
Through foreign keys
Through primary keys
Directly via indexing
There are no relationships
What is the primary objective of query processing?
To optimize the performance of database queries
To design the database schema
To normalize the data
To store the query results
Which query processing step involves converting SQL queries into a sequence of operations?
Parsing
Optimization
Execution
Plan Generation
What is query optimization in relational databases?
Generating an execution plan for a query
Deciding which indexes to create
Reducing the time it takes to execute a query
Transforming the query into an equivalent one
Which of the following is a factor in query optimization?
Query execution time
Table schema
Index availability
All of the above
Which of the following is true about a query execution plan?
It shows how the database will execute a query
It contains only the raw data
It is irrelevant to query optimization
It directly impacts database storage size
What is the purpose of indexing in query processing?
To store the data in the correct format
To speed up query performance
To reduce storage space
To back up the data
What does the "selectivity" of a query refer to?
The number of operations performed in the query
The ratio of data retrieved by the query
The complexity of the query
The size of the result set
What is a cost-based query optimization technique?
Using heuristics to determine the best plan
Selecting the least expensive execution plan based on estimated costs
Using random trial-and-error methods
Rewriting queries for better performance
Which of the following is an example of a join operation in SQL query processing?
Merge join
Cross join
Inner join
All of the above
In query processing, what does the term "cardinality" refer to?
The type of data in the table
The number of rows in the result set
The size of the query
The number of joins used in the query
Which of the following is an example of a query processing algorithm?
Cost-based optimization
Query rewrite rules
Join ordering algorithms
All of the above
What does an "execution plan" in query processing typically contain?
Query results
A detailed sequence of operations
Only the SQL query itself
The raw database data
Which of the following can improve the performance of complex queries?
Indexing
Denormalization
Partitioning tables
All of the above
