SQL Server Advanced Data Warehousing: Expert-Level Quiz

SQL Server Advanced Data Warehousing (Expert-Level Quiz)

Test your knowledge of SQL Server Advanced Data Warehousing with this expert-level quiz. These questions will test your knowledge of advanced data warehousing practices, focusing on practical implementation through scripts and code and general practical questions. Seasoned professionals looking to validate their expertise and ensure their data warehousing solutions are efficient and robust will find this quiz intriguing.

1 / 20

You are tasked with configuring the Similarity of Similar percentage that defines a match in a matching policy containing one matching rule. In what similarity percentage will a similarity score of 0 always be generated?

100

2 / 20

What describes dimension tables?

Entities

Facts

Units of measures

Keys

3 / 20

Which SQL Server feature allows you to create a data warehouse ETL process?

SQL Server Analysis Services (SSAS)

SQL Server Management Studio (SSMS)

SQL Server Reporting Services (SSRS)

SQL Server Integration Services (SSIS)

4 / 20

One of the following is not a primary function performed by OLAP.

Slice

Update

Pivot

Roll up

5 / 20

What is the purpose of the WITH SCHEMABINDING option in a view?

To ensure the view is bound to the schema of the underlying tables

To create a partitioned view

To create an indexed view

To encrypt the view

6 / 20

Which command is used to create a view in SQL Server?

_________ vw_ProductSales
AS
SELECT ProductID, SUM(Amount) AS TotalSales
FROM FactSales
GROUP BY ProductID;

CREATE VIEW

CREATE TABLE

CREATE SCHEMA

CREATE INDEX

7 / 20

Which of the following stores information about how the management data warehouse reports should group and aggregate performance counters?

core.wait_categories

core.supported_collector_types_internal

core.performance_counter_report_group_items

core.snapshots_internal

8 / 20

What is the syntax for sp_add_collector_type procedure?

core.sp_add_collector [ @collector_type_uid = ] ‘collector_type_uid’

core.sp_add_collector_type [ @collector_type_uid = ] ‘collector_type_uid’

core.sp_add_collector_type [ @collector_type_uid = ].

None of the above

9 / 20

Partitioning is done for many reasons, such as assisting backup recovery, enhancing performance, and easy management.

False

True

10 / 20

Fill in the missing code to create a data warehouse fact table:

CREATE TABLE FactSales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    CustomerID INT,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
) ON ___;

DIMENSION

CREATE_FACT_TABLE

PRIMARY

FACT

11 / 20

To enable a downstream system to process data from a Master Data Services (MDS) system, what configuration is necessary?

Downstream system view

Similarity percentage view

A matching rule view

A subscription view

12 / 20

Which of the following transformations would you use to extract the maximum number of posts made about a person from a social media archive?

Fuzzy Lookup Transformation

Term Extraction Transformation

Data Mining Query Transformation

Term Lookup Transformation

13 / 20

Which SQL Server feature allows you to create a read-only replica of a database?

Database Mirroring

Log Shipping

Failover Clustering

Always On Availability Groups

14 / 20

What is data scrubbing?

A process to upgrade the quality of data before it is moved into a data warehouse

A process to load the data in the data warehouse and to create the necessary indexes

A process to upgrade the quality of data after it is moved into a data warehouse

A process to load the data in the data warehouse and to create the necessary indexes

15 / 20

Which of the following is not a type od SCD?

None of the above

Type 1

Type 0

Type 2

16 / 20

There is a Microsoft SQL server equipped with Data Quality Services (DQS); your task is to evaluate the data's completeness and uniqueness within the matching policy. To accomplish this, you decide to establish a matching rule. Would this approach achieve the intended objective?

False

True

17 / 20

What is the primary purpose of a data warehouse?

To store and manage transactional data

To store historical data for analysis and reporting

To secure sensitive data

To automate backups

18 / 20

Which of the following is the most important part of data warehouse environment is that data within the data where house is?

Time Variant

All of the above

Subject Oriented

Integrated

19 / 20

Complete the script to create a clustered columnstore index:

CREATE ____________ CCI_Sales
ON Sales;

NONCLUSTERED COLUMNSTORE INDEX

CLUSTERED COLUMNSTORE INDEX

CLUSTERED INDEX

NONCLUSTERED INDEX

20 / 20

When considering the task of importing country data from a flat file into SQL Server and Oracle databases, which transformation would be employed?

Merge Transformation

Cache Transform

Lookup Transformation

Multicast Transformation

Your score is

The average score is 0%

SQL Server advanced data warehousing is critical for businesses seeking to optimize data storage, retrieval, and analysis.

Besides, the domain of SQL Server data warehousing is reserved for those who have ventured beyond basic database administration. It’s for those engaged in strategic data handling at scale.

This expert-level quiz will test your knowledge of complex queries, data transformations, and warehouse architectures that support vast amounts of data.

Good luck.