CDP Data Warehousing and Business Intelligence 2 — Questions and Answers
Question 1: What is 'conformed dimensions' in data warehousing?
- Dimensions that automatically resize based on data volume
- Dimensions that have identical definitions and content, shared across multiple fact tables or data marts (Correct answer)
- Dimensions containing only current (non-historical) data
- Dimensions stored in a separate database from fact tables
Correct answer: Dimensions that have identical definitions and content, shared across multiple fact tables or data marts
Conformed dimensions share identical definitions, keys, and hierarchies across multiple fact tables or data marts, enabling consistent cross-subject-area analysis.
Question 2: What is the purpose of a staging area in a data warehouse ETL process?
- To serve final reports to end users
- To temporarily hold extracted source data before transformation and loading into the warehouse (Correct answer)
- To store the final dimensional model
- To cache frequently-run queries
Correct answer: To temporarily hold extracted source data before transformation and loading into the warehouse
The staging area is a temporary storage zone where raw extracted data is held and processed before being transformed and loaded into the permanent data warehouse tables.
Question 3: Which BI concept refers to the ability to analyze data from multiple perspectives simultaneously?
- Pivot / Cross-tab Analysis (Correct answer)
- Sequential Analysis
- Regression Analysis
- Longitudinal Analysis
Correct answer: Pivot / Cross-tab Analysis
Pivot or cross-tabulation analysis allows users to analyze data from multiple dimensions simultaneously, rotating the view to see measures from different perspectives.
Question 4: What is a Key Performance Indicator (KPI) in business intelligence?
- A database index for high-performance queries
- A measurable value that demonstrates how effectively an organization is achieving key business objectives (Correct answer)
- A type of ETL transformation rule
- A report template in a BI tool
Correct answer: A measurable value that demonstrates how effectively an organization is achieving key business objectives
KPIs are quantifiable measurements that evaluate the success of an organization in meeting its strategic objectives, such as revenue growth or customer satisfaction scores.
Question 5: What is 'slowly changing dimension Type 1' (SCD Type 1)?
- Adding new rows for each change to track history
- Overwriting the old attribute value with the new value, losing historical data (Correct answer)
- Creating a separate history table for changed attributes
- Flagging changed records with an 'is_current' indicator
Correct answer: Overwriting the old attribute value with the new value, losing historical data
SCD Type 1 simply overwrites the old attribute value with the new value, keeping only the current state and losing the history of previous values.
Question 6: What does ROLAP stand for in OLAP technology?
- Rapid OLAP
- Relational OLAP — storing OLAP data in relational database tables rather than multidimensional cubes (Correct answer)
- Remote OLAP
- Replicated OLAP
Correct answer: Relational OLAP — storing OLAP data in relational database tables rather than multidimensional cubes
ROLAP (Relational OLAP) generates analytical queries dynamically against relational database tables rather than pre-computing and storing results in multidimensional cubes.
CDP Data Warehousing and Business Intelligence 2
What is 'conformed dimensions' in data warehousing?