MS SQL Server - 82 - OLTP and Business Intelligence MCQ

82 – OLTP and Business Intelligence MCQ

1 / 25

1. What is the primary purpose of Online Transaction Processing (OLTP) systems in SQL Server?

C. Managing day-to-day business transactions

A. Analyzing historical data

D. Generating complex reports

B. Supporting decision-making processes

2 / 25

2. Which of the following is a key characteristic of OLTP databases?

C. High volume of short and simple transactions

A. Focus on historical data

B. Optimized for read-heavy workloads

D. Designed for complex analytical queries

3 / 25

3. In OLTP systems, what is the typical database schema used to store data?

A. Star schema

B. Snowflake schema

C. Denormalized schema

D. Normalized schema

4 / 25

4. Which SQL Server component is commonly used for building and managing OLAP cubes for Business Intelligence?

C. SQL Server Analysis Services (SSAS)

A. SQL Server Reporting Services (SSRS)

B. SQL Server Integration Services (SSIS)

D. SQL Server Data Tools (SSDT)

5 / 25

5. What is the primary goal of Business Intelligence (BI) systems in SQL Server?

D. Automating data backups

B. Enabling real-time processing

A. Capturing transactional data

C. Supporting strategic decision-making

6 / 25

6. Which SQL Server tool is used for creating visually appealing reports and dashboards?

C. SQL Server Analysis Services (SSAS)

A. SQL Server Management Studio (SSMS)

B. SQL Server Reporting Services (SSRS)

D. SQL Server Data Tools (SSDT)

7 / 25

7. In a data warehouse for BI, what is the purpose of the ETL process?

C. Performing online transactions

B. Extracting, transforming, and loading data

A. Generating ad-hoc reports

D. Managing OLAP cubes

8 / 25

8. What is the role of SQL Server Integration Services (SSIS) in Business Intelligence?

A. Data analysis

B. Data transformation and loading

D. Query optimization

C. Report generation

9 / 25

9. Which type of data is typically stored in a data warehouse used for Business Intelligence?

A. Raw transactional data

C. Real-time streaming data

B. Aggregated and historical data

D. Unstructured data

10 / 25

10. What is a typical use case for SQL Server Reporting Services (SSRS) in Business Intelligence?

A. Data cleansing and transformation

B. Real-time data analysis

D. Building OLAP cubes

C. Creating and distributing reports

11 / 25

11. Which SQL Server feature is used to optimize query performance for large-scale BI workloads?

A. Columnstore indexes

B. Full-text search

C. Service Broker

D. Database snapshots

12 / 25

12. In the context of Business Intelligence, what does the term “OLAP” stand for?

C. Object-Level Access Protocol

A. Online Analytical Processing

B. Online Application Platform

D. Operational Level Application Programming

13 / 25

13. What is the primary benefit of using columnstore indexes in SQL Server for Business Intelligence?

B. Improved data encryption

A. Faster data loading

C. Enhanced compression

D. Reduced storage space

14 / 25

14. Which type of reporting tool is suitable for interactive, ad-hoc querying of BI data?

B. Scorecards

C. Drill-through reports

A. Dashboards

D. Paginated reports

15 / 25

15. In the context of Business Intelligence, what is the purpose of data mining?

A. Extracting data from external sources

D. Performing real-time transactions

C. Discovering hidden patterns and trends in data

B. Analyzing historical data

16 / 25

16. Which SQL Server component is responsible for managing data security and access control in a BI environment?

C. SQL Server Integration Services (SSIS)

D. SQL Server Security Manager

B. SQL Server Analysis Services (SSAS)

A. SQL Server Reporting Services (SSRS)

17 / 25

17. What is the primary role of SQL Server Data Tools (SSDT) in Business Intelligence?

A. Developing OLAP cubes

B. Designing ETL workflows

C. Creating database backups

D. Writing T-SQL queries

18 / 25

18. What does the term “ETL” stand for in the context of data warehousing and Business Intelligence?

D. Export, Tag, Locate

B. Execute, Transfer, Log

C. Edit, Test, Launch

A. Extract, Transform, Load

19 / 25

19. Which SQL Server component provides data visualization and business intelligence capabilities through Power BI integration?

A. SQL Server Analysis Services (SSAS)

D. SQL Server Data Tools (SSDT)

B. SQL Server Reporting Services (SSRS)

C. SQL Server Integration Services (SSIS)

20 / 25

20. In Business Intelligence, what is the primary purpose of a scorecard?

D. Building OLAP cubes

B. Real-time data analysis

C. Monitoring key performance indicators (KPIs)

A. Data transformation

21 / 25

21. What is the main difference between OLAP and OLTP databases?

B. OLAP databases are smaller in size compared to OLTP databases.

C. OLAP databases are designed for complex analytical queries, while OLTP databases are for simple transactions.

A. OLAP databases focus on historical data, while OLTP databases handle real-time transactions.

D. OLAP databases use a normalized schema, while OLTP databases use a denormalized schema.

22 / 25

22. Which SQL Server feature is used for real-time monitoring and alerting in Business Intelligence?

B. SQL Server Analysis Services (SSAS)

C. SQL Server Agent

D. SQL Server Profiler

A. SQL Server Reporting Services (SSRS)

23 / 25

23. What is a data warehouse in the context of Business Intelligence?

D. A tool for creating interactive dashboards

A. A storage facility for OLAP cubes

C. A transactional database for real-time processing

B. A repository of historical data for reporting and analysis

24 / 25

24. Which SQL Server tool can be used to schedule and automate the execution of ETL processes in Business Intelligence?

D. SQL Server Data Tools (SSDT)

B. SQL Server Integration Services (SSIS)

C. SQL Server Analysis Services (SSAS)

A. SQL Server Management Studio (SSMS)

25 / 25

25. What is the primary purpose of a dashboard in Business Intelligence?

D. Performing complex data transformations

C. Displaying key performance indicators (KPIs) at a glance

A. Storing raw data

B. Providing interactive data exploration

Your score is

MS SQL Server – 82 – OLTP and Business Intelligence MCQ