Microsoft Implementing a SQL Data Warehouse - 070-767 Free Exam Questions

QUESTION NO: 1
You have a fact table in a data warehouse that stores financial data. The table contains eight column configured as shown in the following table.

You need to identify a column that can be aggregated across all dimensions.
Which column should you identify?

QUESTION NO: 2
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 discover that the job loads changes from the previous three days only.
You need re ensure that the job loads changes from the previous week.
Which stored procedure should you execute?

QUESTION NO: 3
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.
Your company uses Microsoft SQL Server to deploy a data warehouse to an environment that has a SQL Server Analysis Services (SSAS) instance. The data warehouse includes the Fact.Order table as shown in the following table definition. The table has no indexes.

You must minimize the amount of space that indexes for the Fact.Order table consume. You run the following queries frequently. Both queries must be able to use a columnstore index:

You need to ensure that the queries complete as quickly as possible.
Solution: You create one columnstore index that includes the [Order Date Key], [Tax Amount], and [Total Excluding Tax] columns.
Does the solution meet the goal?

QUESTION NO: 4
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?

QUESTION NO: 5
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question In the series.
Start of repeated scenario
Contoso. Ltd. has a Microsoft SQL Server environment that includes SQL Server Integration Services (SSIS), a data warehouse, and SQL Server Analysis Services (SSAS) Tabular and multidimensional models.
The data warehouse stores data related to your company sales, financial transactions and financial budgets. All data for the data warenouse originates from the company's business financial system.
The data warehouse includes the following tables:

The company plans to use Microsoft Azure to store older records from the data warehouse. You must modify the database to enable the Stretch Database capability.
Users report that they are becoming confused about which city table to use for various queries. You plan to create a new schema named Dimension and change the name of the dbo.du_city table to Diamension.city. Data loss is not permissible, and you must not leave traces of the old table in the data warehouse.
Pal to create a measure that calculates the profit margin based on the existing measures.
You must implement a partitioning scheme few the fact. Transaction table to move older data to less expensive storage. Each partition will store data for a single calendar year, as shown in the exhibit (Click the Exhibit button.) You must align the partitions.

You must improve performance for queries against the fact.Transaction table. You must implement appropriate indexes and enable the Stretch Database capability.
End of repeated scenario
You need to resolve the problems reported about the dia__city table.
How should you complete the Transact-SQL statement? 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.

QUESTION NO: 6
You are testing a Microsoft SQL Server Integration Services (SSIS) package. The package includes the Control Flow task shown in the Control Flow exhibit (Click the Exhibit button) and the Data Flow task shown in the Data Flow exhibit. (Click the Exhibit button.)


You declare a variable named Seed as shown in the Variables exhibit. (Click the Exhibit button.) The variable is changed by the Script task during execution.

You need to be able to interrogate the value of the Seed variable after the Script task completes execution.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

QUESTION NO: 7
You have Microsoft Azure SQL Database instances named DB1 and DB2.
The databases are hosted in different regions and contain different tables.
You must allow Transact-SQL queries in DB1 to access data in DB2.
You need to configure DB1 to ensure that you can run queries that join tables from DB1 and DB2.
What should you do?

QUESTION NO: 8
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 developing a Microsoft SQL Server Integration Services (SSIS) package.
You need to cleanse a data flow source by removing duplicate records based on approximate matches.
Which SSIS Toolbox item should you use?

QUESTION NO: 9
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 captule tables.
You remove the Integration Services job.
You need to stop tracking changes to the database temporarily. The solution must ensure that tracking changes can be restored quickly in a few weeks.
Which stored procedure should you execute?

QUESTION NO: 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.
You create a set of Microsoft SQL Server Integration Services (SSIS) packages to support an ETL process.
You need to deploy the SSIS packages and meet the following requirements:
* Maximize ease of use for backup processes.
* Manage security of packages from a single place.
* Maintain versioning pf packages.
* Implement parameters to assign values to packages.
* Use stored procedures to run SSIS packages.
* Use SQL Server Agent to run packages.
Solution: You use the Project Deployment model with an SSIS catalog to store and manage packages.
Does this meet the goal?

QUESTION NO: 11
You need to ensure that a downstream system can consume data in a Master Data Services (MDS) system.
What should you configure?

QUESTION NO: 12
Note: This question is part of a series of questions that present the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenarios is exactly the same in each question in this series.
You are developing a Master Data Management (MDM) solution for a company by using Microsoft SQL Server Integration Services (SSIS), SQL Server Master Data Services (MDS), and SQL Server Data Quality Services (DQS).
You have an MDS model named Geography that contains the entities described in the following table.

You define a domain-based attribute in the State entity that references the CountryRegion entity. You define another domain-based attribute in the city entity that references the State and CountryRegion entities. A single derived hierarchy named Geography supports navigation between the CountryRegion, State, and City levels.
Subscription views exist for all entities. The subscription views have the same name as the entity on which they are based.
You initialize each entity member. New City entity members are imported daily based on customer city values in a Customer Relationship Management (CRM) database. The CRM database is a SQL Server relational database. When new cities are imported from the CRM database, the state codes must be standardized to those already defined in the State entity.
In the CRM database, sales managers and assigned to countries/regions. A sales manager may be assigned to one or more countries/regions. A country/region may have one or more assigned sales managers. The CRM database contains a table named ManagerCountryRegion that stores a row for each manager-country/region relationship.
You create the following MDS users and map each user to an Active Directory Domain Services (AD DS) user account: User1, User2, and User3. Both User1 and User2 belong to the Explorer functional area.
Users must be able to complete the tasks described in the following table.

You need to complete the development of the SSIS data flow to load cities into the City staging table.

Which transformation type should you use for Component 3?

QUESTION NO: 13
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.

QUESTION NO: 14
You have a database named DB1 that contains millions of rows.
You plan to perform a weekly audit of the changes to the rows.
You need to ensure that you can view which rows were modified and the hour that the modification occurred.
What should you do?

QUESTION NO: 15
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?