MS SQL Server - 83 - Data Warehouse MCQ

83 – Data Warehouse MCQ

1 / 25

1. What is the primary purpose of a data warehouse in SQL Server?

D. Data encryption and security

B. Storing raw operational data

C. Analyzing historical data for decision support

A. Real-time transaction processing

2 / 25

2. Which term refers to the process of extracting, transforming, and loading data into a data warehouse?

D. DDL

B. ETL

A. OLAP

C. SQL

3 / 25

3. In a data warehouse, what is the typical data model used for storing data?

C. Denormalized schema

A. Star schema

D. Third normal form (3NF)

B. Snowflake schema

4 / 25

4. What is the primary goal of data warehousing in SQL Server?

A. Capturing real-time data

C. Enabling complex queries and reporting

D. Managing online transactions

B. Supporting transactional processing

5 / 25

5. Which SQL Server component is commonly used for building and managing OLAP cubes in a data warehouse?

D. SQL Server Data Tools (SSDT)

C. SQL Server Analysis Services (SSAS)

A. SQL Server Reporting Services (SSRS)

B. SQL Server Integration Services (SSIS)

6 / 25

6. In the context of data warehousing, what does the term “OLAP” stand for?

B. Online Application Platform

C. Object-Level Access Protocol

A. Online Analytical Processing

D. Operational Level Application Programming

7 / 25

7. What is the main purpose of a data warehouse’s staging area?

C. Running complex analytical queries

D. Generating reports

B. Preparing data for loading into the warehouse

A. Storing raw operational data

8 / 25

8. In data warehousing, what is a dimension table?

D. A table that holds transactional data

C. A table that describes characteristics or attributes of data

B. A table that stores calculated values

A. A table that contains historical data

9 / 25

9. Which SQL Server feature is used to optimize query performance for large-scale data warehousing workloads?

C. Service Broker

A. Columnstore indexes

D. Linked Servers

B. Full-text search

10 / 25

10. What is a fact table in the context of data warehousing?

D. A table that holds historical data

B. A table that describes dimensions

C. A table that stores calculated values

A. A table that contains transactional data

11 / 25

11. In data warehousing, what is the purpose of a data mart?

B. Centralized storage for all data

C. A subset of a data warehouse tailored to a specific business area

D. Managing OLAP cubes

A. Storing raw transactional data

12 / 25

12. What is the role of SQL Server Integration Services (SSIS) in data warehousing?

A. Running complex analytical queries

C. Extracting, transforming, and loading data

B. Building and maintaining OLAP cubes

D. Designing ETL workflows

13 / 25

13. In a data warehouse, what is the primary source of data for analysis and reporting?

C. Dimension tables

D. Raw operational data

B. Staging tables

A. Fact tables

14 / 25

14. What is the primary benefit of using columnstore indexes in SQL Server for data warehousing?

D. Reduced storage space

B. Improved data encryption

C. Enhanced compression

A. Faster data loading

15 / 25

15. Which SQL Server tool can be used to create visually appealing reports and dashboards for data warehousing?

B. SQL Server Reporting Services (SSRS)

A. SQL Server Management Studio (SSMS)

C. SQL Server Analysis Services (SSAS)

D. SQL Server Data Tools (SSDT)

16 / 25

16. In data warehousing, what is the primary role of SQL Server Data Tools (SSDT)?

C. Creating database backups

A. Developing OLAP cubes

B. Designing ETL workflows

D. Writing T-SQL queries

17 / 25

17. What does the term “ETL” stand for in the context of data warehousing?

A. Extract, Transform, Load

D. Export, Tag, Locate

B. Execute, Transfer, Log

C. Edit, Test, Launch

18 / 25

18. Which SQL Server component provides data visualization and business intelligence capabilities for data warehousing?

D. SQL Server Data Tools (SSDT)

B. SQL Server Reporting Services (SSRS)

A. SQL Server Analysis Services (SSAS)

C. SQL Server Integration Services (SSIS)

19 / 25

19. In data warehousing, what is the primary purpose of a slowly changing dimension (SCtable?

A. Storing historical data

B. Maintaining calculated values

D. Running real-time queries

C. Storing transactional data

20 / 25

20. Which SQL Server feature is used for real-time monitoring and alerting in data warehousing?

D. SQL Server Profiler

B. SQL Server Analysis Services (SSAS)

A. SQL Server Reporting Services (SSRS)

C. SQL Server Agent

21 / 25

21. What is the main difference between data warehouses and operational databases (OLTP)?

C. Data warehouses are smaller in size compared to OLTP databases.

B. Data warehouses use a normalized schema, while OLTP databases use a denormalized schema.

A. Data warehouses store historical data for analysis, while OLTP databases handle real-time transactions.

D. Data warehouses focus on generating complex reports.

22 / 25

22. Which SQL Server tool is used for creating and managing ETL workflows in data warehousing?

C. SQL Server Analysis Services (SSAS)

B. SQL Server Integration Services (SSIS)

D. SQL Server Data Tools (SSDT)

A. SQL Server Management Studio (SSMS)

23 / 25

23. What is the primary role of SQL Server Reporting Services (SSRS) in data warehousing?

D. Building OLAP cubes

B. Designing ETL processes

C. Creating and distributing reports

A. Extracting and transforming data

24 / 25

24. In the context of data warehousing, what is the purpose of a conformed dimension?

B. A dimension that can be shared across multiple data marts

D. A dimension that describes facts

A. A dimension that contains calculated values

C. A dimension that stores raw operational data

25 / 25

25. Which SQL Server feature is used for managing data security and access control in a data warehouse?

C. SQL Server Integration Services (SSIS)

B. SQL Server Analysis Services (SSAS)

A. SQL Server Reporting Services (SSRS)

D. SQL Server Security Manager

Your score is

MS SQL Server – 83 – Data Warehouse MCQ