Individuals

Learn how you can get certified, advance your career, and get promoted with our self-paced subscription of over 900 self-paced courses.

Learn More

Certification Exam Prep Questions For
Implementing a Microsoft SQL 2016 Data Warehouse (MS-20767)

Name: Practice Exam - Implementing a Microsoft SQL 2016 Data Warehouse
SKU: Practice Exam - Implementing a Microsoft SQL 2016 Data Warehouse
Price: 109 USD
Availability: InStock

QuickStart is now offering you assessment questions for Implementing a Microsoft SQL 2016 Data Warehouse (MS-20767). Whether you are deciding which exam to sign up for, or simply want to practice the materials necessary to complete certification for this course, we have provided a practice assessment to better aid in certification. 100% of the questions are real questions from a recent version of the test you will take for Implementing a Microsoft SQL 2016 Data Warehouse (MS-20767).

Assessment Modules:

1. An Introduction to Database Development

2. Designing and implementing Tables

3. Advanced Table Designs

4. Ensuring Data Integrity Through Constraints:

5. Introduction to Indexes

6. Designing Optimized Index Strategies

7. ColumnStore Indexes

8. Designing and Implementing Views

9. Designing and Implementing Stored Procedures:

10. Designing and Implementing User Defined Functions: Q1

11. Responding to Data Manipulation Via Triggers

12. Responding to Data Manipulation Via Triggers

13. Implementing Managed Code in SQL Server

14. Storing and Querying XML Data in SQL Server

15. Storing and Querying Spatial Data in SQL Server

Module 1

Introduction to Data Warehousing

What are the components of Data Warehousing Solution?

A. Data Sources: Incorrect.

B. Master Data Management (MDM): Incorrect.

C. Data Models: Incorrect.

D. All of the above: Correct!

Column-based index has the following property.

A. Improve performance on row level queries, insert and updates: Incorrect.

B. It is best to use in OLTP database: Incorrect.

C. It is best to use in Data Warehouse: Correct!

D. All the data in a row is processed: Incorrect.

Row-based index has the following property

A. Improve performance on queries that scan a table, aggregation and analytical queries: Incorrect.

B. It is best to use in OLTP database: Correct!

C. It is best to use in Data Warehouse: Incorrect.

D. Both rows and columns are processed: Incorrect.

Module 2

Planning Data Warehouse Infrastructure

How much Data Volume is required for a Small BI System?

A. 10 TB to hundreds of TB: Incorrect.

B. 100 GB Max: Incorrect.

C. Hundreds of GB to 1 TB: Correct!

D. 1 to 10 TB: Incorrect.

How much Data Volume is required for a Medium BI System?

A. 10 TB to hundreds of TB: Incorrect.

B. 100 GB Max: Incorrect.

C. Hundreds of GB to 1 TB: Incorrect.

D. 1 to 10 TB: Correct.

What is the specific formula for applying cores in a system?

A. ((Average query size in MB ÷ MCR) x Concurrent users) x Target response time: Incorrect.

B. ((Average query size in MB ÷ MCR) x Concurrent users): Incorrect.

C. ((Average query size in MB + MCR) x Concurrent users): Incorrect.

D. ((Average query size in MB ÷ MCR) x Concurrent users) ÷ Target response time: Correct.

Module 3

Designing and Implementing a Data Warehouse

What properties are true regarding snowflake schema?

A. Each fact table is directly related to its relevant dimension tables: Incorrect.

B. More space is required to store dimension table as compared to start schema: Incorrect.

C. To form a Snowflake schema one or more dimensions may be de-normalized into tables: Incorrect.

D. To form a Snowflake schema one or more dimensions may be normalized into tables: Correct.

In dimension table the business key is retained as?

A. Surrogate Key: Incorrect.

B. Alternate Key: Correct!

C. Special Key: Incorrect.

D. BI Key: Incorrect.

Which of the following statement is true for tempdb?

A. The tempdb system database can create local temporary tables only: Incorrect.

B. The tempdb system database can create global temporary tables only: Incorrect.

C. tempdb database is used for query processing that requires temporary objects: Correct!

D. tempdb does not work with temporary stored procedures: Incorrect!

Module 4

Columnstore Indexes

Columnstore indexes can...

A. process data in batches instead of row by row: Incorrect.

B. improve the performance for a typical data warehouse query by up to 10 times: Incorrect.

C. perform well because the columns often store matching data: Incorrect.

D. All of the above: Correct!

Which of the following is NOT the characteristic of Clustered Columnstore indexes

A. It must contain all columns: Incorrect.

B. It Optimizes data for storage and performance: Incorrect.

C. It can be filtered: Correct!

D. On top the row bases indexes can be added: Incorrect.

What does this statement do “sys.dm_db_index_physical_stats”?

A. The statement is incorrect: Incorrect.

B. Helps to determine the index fragmentation: Correct!

C. The correct statement is dm.sys_db_index_physical_stats: Incorrect.

D. Helps to determine each request that is executing within SQL Server: Incorrect.

Module 5

Implementing an Azure SQL Data Warehouse

) What is the key characteristic of PolyBase?

A. PolyBase can access structured data in other systems: Incorrect.

B. PolyBase can not be used with Azure SQL for external tables: Incorrect.

C. PolyBase can link to data sources by setting up external table: Correct!

D. PolyBase can not query the external table as normal table: Incorrect.

Hash distribution is a part of?

A. Memory Distribution: Incorrect.

B. Data Distribution: Correct!

C. Round- Robin Distribution: Incorrect.

D. None of the above: Incorrect.

) What are the data types that are NOT supported in Azure SQL Data Warehouse?

A. varchar(max): Correct!

B. nvarchar(4000): Incorrect.

C. varchar(8000): Incorrect.

D. all of the above: Incorrect.

Module 6

Creating an ETL Solution

_______ is the primary ETL tool for SQL Server.

A. SSRS: Incorrect.

B. SSAS: Incorrect.

C. SSIS: Correct!

D. None of the above: Incorrect.

In which deployment model multiple packages are deployed in a single project?

A. Package deployment model: Incorrect.

B. Project deployment model: Correct!

C. Product deployment model: Incorrect.

D. Single deployment model: Incorrect.

In which deployment model SSIS packages are deployed and managed individually?

A. Package deployment model: Correct!

B. Project deployment model: Incorrect.

C. Product deployment model: Incorrect.

D. Single deployment model: Incorrect.

Module 7

Implementing Control Flow in an SSIS Package

In Control Flow task, what WMI stands for?

A. Windows Message Instrumentation: Incorrect.

B. Windows Management Instrumentation: Correct!

C. Windows Monitor Instrumentation: Incorrect.

D. Windows Monitor Integration: Incorrect.

In Database Tasks, what CDC stands for?

A. Change Data Capture: Correct!

B. Capture Data Control: Incorrect.

C. Change Data Control: Incorrect.

D. Change Deployment Control: Incorrect.

Following is NOT the part of System Variable?

A. MachineName: Incorrect.

B. StartTime: Incorrect.

C. UserName: Incorrect.

D. Scope: Correct!

Module 8

Debugging and Troubleshooting SSIS Packages

“dtutil” helps to ..

A. Deploy packages: Incorrect.

B. Generate dump file: Correct!

C. Delete log files: Incorrect.

D. All of the above: Incorrect.

Mark the following events that are the part of SSIS logging supports?

A. OnError: Correct!

B. OnStart: Incorrect.

C. OnInformation: Incorrect.

D. None of the above: Incorrect.

In Log Schema mark the appropriate integer value with respect to its execution result of the following DataCode?

A. 0 = Failure: Incorrect.

B. 1 = Success: Incorrect.

C. 3 = Cancelled: Correct!

D. All of the above: Incorrect.

Module 9

Implementing a Data Extraction Solution

Which statement is true for Single-Stage ETL?

A. Data is staged for a coordinated load: Incorrect.

B. Data is extracted quickly to a landing zone, and then staged prior to loading: Incorrect.

C. Data is transferred directly from source to data warehouse: Correct!

D. None of the above: Incorrect.

Which statement is true for Two-Stage ETL?

A. Data is staged for a coordinated load: Correct!

B. Data is extracted quickly to a landing zone, and then staged prior to loading: Incorrect.

C. Data is transferred directly from source to data warehouse: Incorrect.

D. None of the above: Incorrect.

Which statement is true for Three-Stage ETL?

A. Data is staged for a coordinated load: Incorrect.

B. Data is extracted quickly to a landing zone, and then staged prior to loading: Correct!

C. Data is transferred directly from source to data warehouse: Incorrect.

D. None of the above: Incorrect.

Module 10

Enforcing Data Quality

In SQL what does DQS stands for?

A. Data Query Service: Incorrect.

B. Data Quality Server: Incorrect.

C. Data Quality Service: Correct!

D. None of the above: Incorrect.

Which statement is true for DQS

A. DQS is a knowledge-based solution for Data Cleansing only not for Data Matching: Incorrect.

B. DQS is a knowledge-based solution for Data Matching only not for Data Cleansing: Incorrect.

C. DQS is a knowledge-based solution for Data Cleansing and for Data Matching: Correct!

D. DQS stands for Data Quantity Service: Incorrect.

What does Data Cleansing do?

A. Finds duplicate data entities: Incorrect.

B. Applies data quality rules to data: Incorrect.

C. Identifies invalid data values and does correction as well: Correct!

D. Creates and Manages data quality services knowledge: Incorrect.

Module 11

Master Data Services

What statements are true for Master Data Services?

A. Entity-focused: Incorrect.

B. Domain-focused: Incorrect.

C. Applicable to any dataset that contains domains: Incorrect.

D. Applicable to specific instances: Correct!

What statements are true for Data Quality Services?

A. Entity-focused: Incorrect.

B. Applicable to any dataset that contains domains: Correct!

C. Applicable to specific instances: Incorrect.

D. None of the above: Incorrect.

What are the components of Master Data Services?

A. Master Data Services database: Incorrect.

B. Master Data Manager web application: Incorrect.

C. Master Data Services Configuration Manager: Incorrect.

D. All of the above: Correct!

Module 12

Extending SQL Server Integration Services

What following settings can be configured using Script Task Editor?

A. EntryPoint: Incorrect.

B. ReadOnlyVariables: Incorrect.

C. ReadWriteVariables: Incorrect.

D. All of the above: Correct!

What are the custom components through which you can extend SSIS?

A. Control flow tasks: Incorrect.

B. Log providers: Correct!

C. Enumerators: Incorrect.

D. All of the above: Incorrect.

Which statement is true regarding ScriptLanguage?

A. ScriptLanguage is the programming language in which you write the script.: Correct!

B. There is no property in SQL like ScriptLanguage, there is only one that is Language: Incorrect.

C. Deletes sets in the scripting language of the active script: Incorrect.

D. None of the above is true: Incorrect.

Module 13

Deploying and Configuring SSIS Packages

Which following statement is NOT true regarding SSIS catalogue?

A. SSIS catalog is a storage repository for SSIS projects: Incorrect.

B. The catalog contains only packages and parameters for projects using project deployment model: Incorrect.

C. The catalog is stored as the SSISDB database in SQL Server: Incorrect.

D. Most administration is performed on the SSISDB node inside the Integration Services node: Correct!

If a package is being executed with “dtexec”, which means?

A. The debug dump file is deleted: Incorrect.

B. The debug dump file is created: Correct!

C. It will create .exe file: Incorrect.

D. All of the above: Incorrect.

Which statement is true regarding Dtexecui.

A. Dtexecui is a GUI that runs Dtexe.: Correct!

B. Dtexecui helps you to open command prompt: Incorrect.

C. There is no utility or syntax like Dtexecui: Incorrect.

D. Dtexecui is a GUI that runs Dtexe.: Incorrect.

Module 14

Consuming Data in a Data Warehouse

Data _________ improves decision making by revealing patterns in data.

A. Analysis: Correct!

B. Mining: Incorrect.

C. Quality Service: Incorrect.

D. Storage: Incorrect.

What does Self-Service Reporting means?

A. Business users consume reports on demand by browsing to an online report repository: Incorrect.

B. Reports are integrated into an application or portal to provide contextualized business information at a glance: Incorrect.

C. Business users can use authoring tools to create their own reports from data model: Correct!

D. None of the above: Incorrect.

ROLAP Stands for?

A. Relational Online Analytical Processing: Correct!

B. Relational Online Analytical Point: Incorrect.

C. Relational Online Analytical Procedure: Incorrect.

D. Relational Online Analytical Partition: Incorrect.

Practice Exam - Implementing a Microsoft SQL 2016 Data Warehouse

Be the first to review this course

Self-Paced

Learning Style

Practice Exam

Learning Style

Beginner

Difficulty

1 Hour

Course Duration

Course Info

Download PDF

Certificate

See Sample

Buy Individually Timezone

tab
About Individual Course: Individual course plan gives you access to this course $109.00 / Each

Outline

More Information

More Information
Lab Access	No
Learning Style	Self-Paced Learning
Learning Type	Practice Exam
Difficulty	Beginner
Course Duration	1 Hour
Language	English

Reviews

Write Your Own Review

Only registered users can write reviews. Please Sign in or create an account

By Roles or Function

By Technologies