IKM Data Analysis and Business Intelligence 2 — Questions and Answers
Question 1: Which SQL aggregate function would you use to count the number of non-NULL values in a column?
- SUM()
- AVG()
- COUNT() (Correct answer)
- MAX()
Correct answer: COUNT()
COUNT(column_name) returns the number of rows where the specified column is not NULL, making it the correct function for counting non-null values.
Question 2: What is a 'pivot table' used for in data analysis tools like Excel or Power BI?
- Joining two datasets on a common key column
- Summarizing and reorganizing data to identify patterns and trends (Correct answer)
- Encrypting sensitive fields before exporting a report
- Writing back corrected values to the source database
Correct answer: Summarizing and reorganizing data to identify patterns and trends
A pivot table dynamically summarizes, groups, and aggregates large datasets, allowing analysts to reorganize rows and columns to reveal patterns and trends quickly.
Question 3: Which chart type is most appropriate for showing the distribution of a single continuous variable?
- Pie chart
- Line chart
- Histogram (Correct answer)
- Scatter plot
Correct answer: Histogram
A histogram groups continuous data into intervals (bins) and displays the frequency of values in each bin, making it ideal for visualizing the distribution of a single variable.
Question 4: In data analysis, what does the term 'data granularity' refer to?
- The level of detail or precision at which data is stored or reported (Correct answer)
- The speed at which new records are written to the database
- The number of data sources integrated into a single warehouse
- The statistical variance across rows in a numerical column
Correct answer: The level of detail or precision at which data is stored or reported
Data granularity refers to the level of detail in data records — fine granularity means each row represents a single transaction, while coarse granularity means rows represent daily or monthly summaries.
Question 5: A correlation coefficient of -0.92 between two variables indicates which of the following?
- No meaningful relationship between the two variables
- A strong positive linear relationship
- A strong negative linear relationship (Correct answer)
- A moderate positive linear relationship
Correct answer: A strong negative linear relationship
A correlation coefficient close to -1 (such as -0.92) indicates a strong negative linear relationship, meaning as one variable increases, the other tends to decrease significantly.
Question 6: What is the primary purpose of data profiling in a data quality initiative?
- Encrypting personally identifiable information before analysis
- Examining data to understand its structure, content, and quality issues (Correct answer)
- Compressing large tables to reduce storage costs in the warehouse
- Defining business rules that govern how data should be used
Correct answer: Examining data to understand its structure, content, and quality issues
Data profiling involves analyzing a dataset to discover its structure, completeness, uniqueness, and anomalies, which is the essential first step in any data quality improvement effort.
Question 7: Which BI concept refers to pre-computing and storing aggregated query results to speed up report performance?
- Data masking
- Aggregate caching / materialized views (Correct answer)
- Row-level security
- Slowly Changing Dimensions
Correct answer: Aggregate caching / materialized views
Materialized views (or aggregate caches) store the results of expensive queries so that subsequent requests can retrieve pre-computed results instead of recalculating them each time.
IKM Data Analysis and Business Intelligence 2
Which SQL aggregate function would you use to count the number of non-NULL values in a column?