The purpose of this Sample Question Set is to provide you with information about the Microsoft Implementing a Data Warehouse using SQL exam. These sample questions will make you very familiar with both the type and the difficulty level of the questions on the 70-767 certification test. To get familiar with real exam environment, we suggest you try our Sample Microsoft Implementing a Data Warehouse using SQL Certification Practice Exam. This sample practice exam gives you the feeling of reality and is a clue to the questions asked in the actual Microsoft Certified Solutions Expert (MCSE) -Data Management and Analytics certification exam.
These sample questions are simple and basic questions that represent likeness to the real Microsoft 70-767 exam questions. To assess your readiness and performance with real time scenario based questions, we suggest you prepare with our Premium Microsoft Implementing a Data Warehouse using SQL Certification Practice Exam. When you solve real time scenario based questions practically, you come across many difficulties that give you an opportunity to improve.
Microsoft 70-767 Sample Questions:
01. You are installing SQL Server Data Quality Services (DQS). You need to give specific users access to the Data Quality Server.
Which SQL Server application should you use?
a) SQL Server Configuration Manager
b) SQL Server Data Tools
c) SQL Server Management Studio
d) Data Quality Client
02. You are building a SQL Server Integration Services (SSIS) package that will implement an extract, transform, and load (ETL) process in your organization.
The package will extract data from a sales database and load it into a data warehouse. Members of staff are stored in the Staff table in the source database. The Staff table includes the region column and the department column. Staff sometimes move from one region to another or from one department to another.
You are asked to keep historical values of region and department for each staff member in the data warehouse, so that each sale can be attributed to the region and department where the staff member worked at the time. You need to implement this functionality.
Which transformation should you use?
a) Lookup transformation
b) Slowly Changing Dimension transformation
c) Merge Join transformation
d) Term Extraction transformation
03. You develop and deploy a SQL Server Integration Services (SSIS) package. The package is stored in the file system. You need to execute the package without importing it to the SSIS server.
What should you use to execute the package?
(Choose Two)
(Choose Two)
a) catalog.start_package
b) dtexec
c) SQL Server Management Studio
d) SQL Server Agent
04. Your company has several line-of-business applications. The applications use a server that has SQL Server installed and contains several databases. You need that the business applications can access curated and validated data from the databases.
Which features should you deploy to the server?
a) Data Quality Services (DQS)
b) SQL Server Analysis Services (SSAS) dimension hierarchies.
c) SQL Server Integration Services (SSIS)
d) Master Data Services (MDS) subscriptions.
05. You are the administrator of a server that hosts Data Quality Server for a large retail company. The server had a hardware failure during business hours.
You need to restore the server that hosts Data Quality Server to another server. You have a recent backup of all the required databases.
What should you do?
(Choose Two)
(Choose Two)
a) Restore the DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases to another server as soon as possible.
b) Execute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the appropriate parameter.
c) Restore only the DQS_MAIN and DQS_STAGING_DATA databases to another server as soon as possible.
d) Execute the DQS_MAIN.internal_core.InitServer stored procedure with the appropriate parameter.
06. You need to ensure that a downstream system can consume data in a Master Data Services (MDS) system. What should you configure?
a) a Data Collector
b) a knowledge base
c) a matching policy
d) a subscription view
07. You implement a business intelligence project for a company named Customerl. Customer1 provides data from their SQL Server. The data has lot of duplicates. The data must be cleaned and loaded to a text file.
You create a Data Quality Services (DQS) knowledge database. You need to deduplicate the data by using the DQS knowledge database. Your solution must minimize development effort.
Which two actions should you perform?
a) Use the DQs Cleansing transformation.
b) Create a SQL Server Integration Services (SSIS) package.
c) Use the Data Mining Query transformation.
d) Use the Fuzzy Grouping transformation.
e) Create a data quality project.
08. A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL Server table named Transaction. Each file corresponds to a different store and is imported in parallel with the other files.
The data flow tasks use OLE DB destinations in fast load data access mode. The number of daily transactions per store can be very large and is growing. The Transaction table does not have any indexes.
You need to minimize the package execution time. What should you do?
a) Partition the table by day and store.
b) Create a clustered index on the Transaction table.
c) Increase the value of the Row per Batch property.
d) Run the package in Performance mode.
09. You are designing a data warehouse that your company will use to analyze its sales and marketing performance. You are designing the Products dimension table.
The table includes the following rows:
- ProductlD: A surrogate key that assigns a unique integer ID to each product in the dimension table. This column does not appear in the source database, which is named SalesDB.
- ProductNumber: A 10-character alpha-numeric string that uniquely identifies the product and is used by sales staff. This column is the primary key for the Products table in the source database.
- ProductName: A varchar column that stores the full product name, as used by sales staff and customers.
- ProductModifiedDate: A date column that stores the date when the product was last revised.
You want to implement a clustered index on the Products table in the data warehouse. What should you do?
a) Implement a clustered row index on the ProductlD column.
b) Implement a clustered row index on the ProductModifiedDate column.
c) Implement a clustered row index on the ProductNumber column.
d) Implement a clustered composite row index on the ProductModifiedDate and ProductName K y columns in that order.
10. You are designing an Extract, Transform and Load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps.
You need to ensure that the design can provide:
- Auditing information for compliance and business user acceptance
- Tracking and unique identification of records for troubleshooting and error correction
What should you do?
a) Develop a Master Data Services (MDS) solution.
b) Develop a Data Quality Services (DQS) solution.
c) Create a version control repository for the ETL solution.
d) Develop a custom data lineage solution.
Answers:
|
Question: 01 Answer: c |
Question: 02 Answer: b |
Question: 03 Answer: b, d |
Question: 04 Answer: d |
Question: 05 Answer: a, b |
|
Question: 06 Answer: d |
Question: 07 Answer: a, b |
Question: 08 Answer: c |
Question: 09 Answer: a |
Question: 10 Answer: d |
Note: For any error in Microsoft Certified Solutions Expert (MCSE) -Data Management and Analytics (70-767) certification exam sample questions, please update us by writing an email on feedback@edusum.com.