SQL Server Advanced Data Warehousing: Expert-Level Quiz

Which of the following is not a type od SCD?

Complete the script to create a clustered columnstore index:

CREATE ____________ CCI_SalesON Sales;

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

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

Fill in the missing code to create a partitioned table:

CREATE TABLE Sales (    SaleID INT,    SaleDate DATE,    Amount DECIMAL(10, 2)) ON ___ (SaleDate);

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

What is the primary purpose of a data warehouse?

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

Which of the following is the right sequence of jobs to load data into warehouse?

Complete the script to create an indexed view:

CREATE VIEW vw_SalesSummary____________ASSELECT SaleDate, SUM(Amount) AS TotalSalesFROM dbo.SalesGROUP BY SaleDate;

What is the syntax for sp_add_collector_type procedure?

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 ___;

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?

Fill in the missing code to create a data warehouse schema:

CREATE SCHEMA ___;

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

_________ vw_ProductSalesASSELECT ProductID, SUM(Amount) AS TotalSalesFROM FactSalesGROUP BY ProductID;

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

The major difference between non-addictive facts and conformed facts is that conformed facts cannot sum up any of the dimensions in the fact table. Whereas, a non-additive fact is a table across multiple data marts and fact tables.

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?

Complete the script to create a data warehouse staging table:

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

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