Microsoft Implementing a SQL Data Warehouse (70-767) Free Practice Test
Question 1
You have a database named OnlineSales that contains a table named Customers. You plan to copy incremental changes from the Customers table to a data warehouse every hour.
You need to enable change tracking for the Customers table.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
You need to enable change tracking for the Customers table.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Correct Answer:
Explanation
Box 1: DATABASE [OnlineSales]
Before you can use change tracking, you must enable change tracking at the database level. The following example shows how to enable change tracking by using ALTER DATABASE.
ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
Box 2: CHANGE_TRACKING = ON
ALTER SET CHANGE_RETENTION
Box 3: ALTER TABLE [dbo].[Customers]
Change tracking must be enabled for each table that you want tracked. When change tracking is enabled, change tracking information is maintained for all rows in the table that are affected by a DML operation.
The following example shows how to enable change tracking for a table by using ALTER TABLE.
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
Box 4: ENABLE CHANGE_TRACKING
References:
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-
Question 2
You manage Master Data Services (MDS).
You need to create a new entity with the following requirements:
* Maximize the performance of the MDS system.
* Ensure that the Entity change logs are stored.
You need to configure the Transaction Log Type setting. Which type should you use?
You need to create a new entity with the following requirements:
* Maximize the performance of the MDS system.
* Ensure that the Entity change logs are stored.
You need to configure the Transaction Log Type setting. Which type should you use?
Correct Answer: E
Question 3
You have a data warehouse.
You need to move a table named Fact.ErrorLog to a new filegroup named LowCost.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
You need to move a table named Fact.ErrorLog to a new filegroup named LowCost.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Correct Answer:
Explanation
Step 1: Add a filegroup named LowCost to the database.
First create a new filegroup.
Step 2:
The next stage is to go to the 'Files' page in the same Properties window and add a file to the filegroup (a filegroup always contains one or more files) Step 3:
To move a table to a different filegroup involves moving the table's clustered index to the new filegroup.
While this may seem strange at first this is not that surprising when you remember that the leaf level of the clustered index actually contains the table data. Moving the clustered index can be done in a single statement using the DROP_EXISTING clause as follows (using one of the AdventureWorks2008R2 tables as an example) :
CREATE UNIQUE CLUSTERED INDEX PK_Department_DepartmentID
ON HumanResources.Department(DepartmentID)
WITH (DROP_EXISTING=ON,ONLINE=ON) ON SECONDARY
This recreates the same index but on the SECONDARY filegroup.
References:
http://www.sqlmatters.com/Articles/Moving%20a%20Table%20to%20a%20Different%20Filegroup.aspx
Question 4
You have a Microsoft SQL Server Data Warehouse instance that uses SQL Server Analysis Services (SSAS).
The instance has a cube containing data from an on-premises SQL Server instance. A measure named Measure1 is configured to calculate the average of a column.
You plan to change Measure1 to a full additive measure and create a new measure named Measure2 that evaluates data based on the first populated row.
You need to configure the measures.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
The instance has a cube containing data from an on-premises SQL Server instance. A measure named Measure1 is configured to calculate the average of a column.
You plan to change Measure1 to a full additive measure and create a new measure named Measure2 that evaluates data based on the first populated row.
You need to configure the measures.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation
Box 1:
The default setting is SUM (fully additive).
Box 2:
FirstNonEmpty: The member value is evaluated as the value of its first child along the time dimension that contains data.
References:
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/define-semiadditive-behavior
Question 5
You are developing a Microsoft SQL Server Integration Services (SSIS) package that loads a data warehouse.
You need to inspect the data that is being processed by the package. What should you do first?
You need to inspect the data that is being processed by the package. What should you do first?
Correct Answer: C
Question 6
You have a server that has Data Quality Services (DQS) installed.
You create a matching policy that contains one matching rule.
You need to configure the Similarity of Similar percentage that defines a match.
Which similarity percentage will always generate a similarity score of 0?
You create a matching policy that contains one matching rule.
You need to configure the Similarity of Similar percentage that defines a match.
Which similarity percentage will always generate a similarity score of 0?
Correct Answer: D
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 7
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are loading data from an OLTP database to a data warehouse.
The OLTP database includes a table for sales data and a table for refund data.
The data warehouse contains a single table for all the sales and refund data.
Which component should you use to load the data to the data warehouse?
You are loading data from an OLTP database to a data warehouse.
The OLTP database includes a table for sales data and a table for refund data.
The data warehouse contains a single table for all the sales and refund data.
Which component should you use to load the data to the data warehouse?
Correct Answer: D
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 8
A company is designing a database storage strategy. The company wants to segment online transaction processing (OLTP) and analytic workloads.
You need to design the workload.
Which index types should you use? To answer, drag the appropriate index types to the correct workloads. Each index type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
You need to design the workload.
Which index types should you use? To answer, drag the appropriate index types to the correct workloads. Each index type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation
OLTP: nonclustered index
OLTP memory-optimized indexes. Such indexes must be one of the following:
Memory-optimized Nonclustered index (meaning the default internal structure of a B-tree) Hash index Real-time analytics: clustered columnstore In Azure, all of the following data stores will meet the core requirements for OLAP:
SQL Server with Columnstore indexes
Azure Analysis Services
SQL Server Analysis Services (SSAS)
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/indexes-for-memory-optimized-tables
https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-analytical-processing
Question 9
You manage a data warehouse in a Microsoft SQL Server instance. Company employee information is imported from the human resources system to a table named Employee in the data warehouse instance. The Employee table was created by running the query shown in the Employee Schema exhibit. (Click the Exhibit button.)
The personal identification number is stored in a column named EmployeeSSN. All values in the EmployeeSSN column must be unique.
When importing employee data, you receive the error message shown in the SQL Error exhibit. (Click the Exhibit button.).
You determine that the Transact-SQL statement shown in the Data Load exhibit in the cause of the error.
(Click the Exhibit button.)
You remove the constraint on the EmployeeSSN column. You need to ensure that values in the EmployeeSSN column are unique.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
The personal identification number is stored in a column named EmployeeSSN. All values in the EmployeeSSN column must be unique.
When importing employee data, you receive the error message shown in the SQL Error exhibit. (Click the Exhibit button.).
You determine that the Transact-SQL statement shown in the Data Load exhibit in the cause of the error.
(Click the Exhibit button.)
You remove the constraint on the EmployeeSSN column. You need to ensure that values in the EmployeeSSN column are unique.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation
With the ANSI standards SQL:92, SQL:1999 and SQL:2003, an UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values.
In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not.
From SQL Server 2008, you can define a unique filtered index based on a predicate that excludes NULLs.
References:
https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls
Question 10
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
Each night you receive a comma separated values (CSV) file that contains different types of rows. Each row type has a different structure. Each row in the CSV file is unique. The first column in every row is named Type. This column identifies the data type.
For each data type, you need to load data from the CSV file to a target table. A separate table must contain the number of rows loaded for each data type.
Solution: You create a SQL Server Integration Services (SSIS) package as shown in the exhibit. (Click the Exhibit tab.)
Does the solution meet the goal?
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
Each night you receive a comma separated values (CSV) file that contains different types of rows. Each row type has a different structure. Each row in the CSV file is unique. The first column in every row is named Type. This column identifies the data type.
For each data type, you need to load data from the CSV file to a target table. A separate table must contain the number of rows loaded for each data type.
Solution: You create a SQL Server Integration Services (SSIS) package as shown in the exhibit. (Click the Exhibit tab.)
Does the solution meet the goal?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 11
You are developing a Microsoft SQL Server Master Data Services (MDS) solution.
The model contains an entity named Product. The Product entity has three user-defined attributes named Category, Subcategory, and Price, respectively.
You need to ensure that combinations of values stored in the Category and Subcategory attributes are unique.
What should you do?
The model contains an entity named Product. The Product entity has three user-defined attributes named Category, Subcategory, and Price, respectively.
You need to ensure that combinations of values stored in the Category and Subcategory attributes are unique.
What should you do?
Correct Answer: C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 12
You are developing a Microsoft SQL Server Integration Services (SSIS) package. You create a data flow that has the following characteristics:
* The package moves data from the table [source].Tabid to DW.Tablel.
* All rows from [source].Table1 must be captured in DW.Tablel for error.Tablel.
* The table error.Tablel must accept rows that fail upon insertion into DW.Tablel due to violation of nullability or data type errors such as an invalid date, or invalid characters in a number.
* The behavior for the Error Output on the "OLE DB Destination" object is Redirect.
* The data types for all columns in [sourceJ.Tablel are VARCHAR. Null values are allowed.
* The Data access mode for both OLE DB destinations is set to Table or view - fast load.
Use the drop-down menus to select the answer choice that answers each question.
* The package moves data from the table [source].Tabid to DW.Tablel.
* All rows from [source].Table1 must be captured in DW.Tablel for error.Tablel.
* The table error.Tablel must accept rows that fail upon insertion into DW.Tablel due to violation of nullability or data type errors such as an invalid date, or invalid characters in a number.
* The behavior for the Error Output on the "OLE DB Destination" object is Redirect.
* The data types for all columns in [sourceJ.Tablel are VARCHAR. Null values are allowed.
* The Data access mode for both OLE DB destinations is set to Table or view - fast load.
Use the drop-down menus to select the answer choice that answers each question.
Correct Answer:
Question 13
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1 that has change data capture enabled.
A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.
You remove the Integration Services job.
You need to stop tracking changes to the database. The solution must remove all the change data capture configurations from DB1.
Which stored procedure should you execute?
You have a database named DB1 that has change data capture enabled.
A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.
You remove the Integration Services job.
You need to stop tracking changes to the database. The solution must remove all the change data capture configurations from DB1.
Which stored procedure should you execute?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 14
Your company manufactures several types of products.
The company has a production tracking application that stores the following data about the products:
The production date
The cost of production
The names of the products
The amount of waste created
The number of products produced
The name of the facility where the products are produced
You are designing a data warehouse for the data. You add a Date dimension.
You need to ensure that you can create a composite primary key for the fact table.
Which two columns should you add to the new dimension tables? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
The company has a production tracking application that stores the following data about the products:
The production date
The cost of production
The names of the products
The amount of waste created
The number of products produced
The name of the facility where the products are produced
You are designing a data warehouse for the data. You add a Date dimension.
You need to ensure that you can create a composite primary key for the fact table.
Which two columns should you add to the new dimension tables? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
Correct Answer: A,C