SQL Server Advanced Data Warehousing: Expert-Level Quiz

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

Fill in the missing code to create a partitioned table:

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

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

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 of the following stores information about how the management data warehouse reports should group and aggregate performance counters?

What describes dimension tables?

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

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

Complete the script to create an indexed view:

CREATE VIEW vw_SalesSummary
____________
AS
SELECT SaleDate, SUM(Amount) AS TotalSales
FROM dbo.Sales
GROUP BY SaleDate;

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.

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

CREATE SCHEMA ___;

What is the primary purpose of a data warehouse?

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?

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;

Fill in the missing code to create a data warehouse ETL process using SSIS:

CREATE PACKAGE ___;

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

What is data scrubbing?

Which of the following is not a type od SCD?

What is the syntax for sp_add_collector_type procedure?