Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 - 70-463 Exam Practice Test
You are designing a data warehouse for a fresh food distribution business that stores sales by individual product. It stores sales targets by product category. Products are classified into subcategories and categories.
Each product is included in only a single product subcategory, and each subcategory is included in only a single category.
The data warehouse will be a data source for an Analysis Services cube.
The data warehouse contains two fact tables:
*factSales, used to record daily sales by product
*factProductTarget, used to record the monthly sales targets by product category Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets.
You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements.
What should you do?
Each product is included in only a single product subcategory, and each subcategory is included in only a single category.
The data warehouse will be a data source for an Analysis Services cube.
The data warehouse contains two fact tables:
*factSales, used to record daily sales by product
*factProductTarget, used to record the monthly sales targets by product category Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets.
You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements.
What should you do?
Correct Answer: C
You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse currently includes the dimUser and dimRegion dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse, and the dimRegion table contains information about sales regions.
The system is accessed by users from certain regions, as well as by area supervisors and users from the corporate headquarters.
You need to design a table structure to ensure that certain users can see sales data for only certain regions.
Some users must be permitted to see sales data from multiple regions.
What should you do?
The system is accessed by users from certain regions, as well as by area supervisors and users from the corporate headquarters.
You need to design a table structure to ensure that certain users can see sales data for only certain regions.
Some users must be permitted to see sales data from multiple regions.
What should you do?
Correct Answer: D
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?
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?
Correct Answer: C
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to-subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company.
Which hierarchy type should you use?
The source data for the company is stored in a single table that contains the manager-to-subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company.
Which hierarchy type should you use?
Correct Answer: A
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project.
Which stored procedure should you use?
You need to add the Environment to the project.
Which stored procedure should you use?
Correct Answer: B
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns.
The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain.
You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.
How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)
The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain.
You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.
How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)
Correct Answer:
Explanation
You are developing a SQL Server Integration Services (SSIS) package that loads data from a source transactional tables in a data warehouse.
You discover that one of the data flows loads incorrect data to one of the destination tables.
You need to identify the cause of the incorrect data flow. Which tool should you use?
You discover that one of the data flows loads incorrect data to one of the destination tables.
You need to identify the cause of the incorrect data flow. Which tool should you use?
Correct Answer: C
You install a SQL Server 2012 database engine instance on a production server. A month later, you install SQL Server 2012 Integration Services (SSIS).
You must develop an SSIS project and deploy it to the server by using the Project Deployment model.
Operations log records that are outside the configured retention period must be cleaned automatically.
You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
You must develop an SSIS project and deploy it to the server by using the Project Deployment model.
Operations log records that are outside the configured retention period must be cleaned automatically.
You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
Correct Answer: B,E
You have a SQL server integration Services (SSIS) package named package1.
You discover that the Data Flow task for Package1 runs slower than expected.
You need to reduce the amount of time required to run the Data Flow task.
Which two actions should you perform? (Select Two)
You discover that the Data Flow task for Package1 runs slower than expected.
You need to reduce the amount of time required to run the Data Flow task.
Which two actions should you perform? (Select Two)
Correct Answer: C,E
You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned.
You need to design the fact table and load it with data.
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.)
The table must have a columnstore index. The table cannot be partitioned.
You need to design the fact table and load it with data.
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
Box 1: Create the fact table.
Box 2: Load the data.
Box 3: Create the columnstore index with the MAXDOP keyword.
Only nonclustered columnstore indexes are available in SQL Server 2012 so when the index is created, you cannot update the table (without using partition switching). Therefore you have to load the data BEFORE creating the index.
In SQL Server 2014, you could create a clustered columnstore index which would allow updates to the table after the index is created.
You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).
The package includes custom scripts that must be upgraded.
You need to upgrade the package to SQL Server 2012.
Which tool should you use?
The package includes custom scripts that must be upgraded.
You need to upgrade the package to SQL Server 2012.
Which tool should you use?
Correct Answer: B
You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).
You need to complete the post-installation configuration.
What should you do?
You need to complete the post-installation configuration.
What should you do?
Correct Answer: A