Mar 2016 updated: Exambible Microsoft 70-463 practice question 129-144

Exam Code: 70-463 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Implementing a Data Warehouse with Microsoft SQL Server 2012
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-463 Exam.

2016 Mar 70-463 Study Guide Questions:

Q129. 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? 

A. For each region, create a view of the factSales table that includes a WHERE clause for the region. 

B. Create a userRegion table that contains primary key columns from the dimUser and dimRegion tables. 

C. Add a region column to the dimUser table. 

D. Partition the factSales table on the region column. 

Answer: D 


Q130. You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night. 

The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table of allowed values. The number of rows in the reference table is very large. 

If no acceptable match is found, the Fuzzy Lookup transformation passes a null value. 

The current setting for the Fuzzy Lookup similarity threshold is 0.50. 

Many values are incorrectly matched. 

You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance. 

What should you do? 

A. Change the Exhaustive property to True. 

B. Change the similarity threshold to 0.55. 

C. Change the similarity threshold to 0.40. 

D. Increase the maximum number of matches per lookup. 

Answer: B 

Explanation: 

http://msdn.microsoft.com/en-us/library/ms137786.aspx 


Q131. You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables. 

You have the following requirements: 

. Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time. 

. Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database. 

. Maximize performance at execution time. 

. Minimize development effort. 

You need to design a solution that meets the requirements. 

What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created. 

B. Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package. 

C. Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime. 

D. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments. 

Answer: A 


Q132. You are editing a SQL Server Integration Services (SSIS) package that uses checkpoints. 

The package performs the following steps: 

1. Download a sales transaction file by using FTP. 

2. Truncate a staging table. 

3. Load the contents of the file to the staging table. 

4. Merge the data with another data source for loading to a data warehouse. 


The checkpoints are currently working such that if any of the four steps fail, the package will restart from the failed step the next time it executes. 

You need to modify the package to ensure that if either the Truncate Staging Table or the Load Sales to Staging task fails, the package will always restart from the Truncate Staging Table task the next time the package runs. 

Which three steps 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.) 


Answer: 



Q133. You are designing a data warehouse for a fresh food distribution business that stores sates 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? 

A. Create two product tables, dimProduct and dimProductCategory. Connect factSales to dimProduct and factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes. 

B. Create one product table, dimProduct, which contains product detail, category, and subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factProductTarget. 

C. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory, and a fourth bridge table that joins products to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes. 

D. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory. Connect factSales to all three product tables and connect factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes. 

Answer: B 


70-463 exam engine

Improve 70-463 exam engine:

Q134. You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job. 

You make changes to the package that should improve performance. 

You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal. 

What should you do? 

A. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package. 

B. Configure the package to send you an email upon completion that includes information about the duration of the package. After 10 executions, view the emails. 

C. Enable logging to the Application Event Log in the package control flow for the OnInformation event. After 10 executions, view the Application Event Log. 

D. Enable logging to the Application Event Log in the package control flow for the OnPostExecute event. After 10 executions, view the Application Event Log. 

Answer: A 

Explanation: The All Executions Report displays a summary of all Integration Services executions that have been performed on the server. There can be multiple executions of the sample package. Unlike the Integration Services Dashboard report, you can configure the All Executions report to show executions that have started during a range of dates. The dates can span multiple days, months, or years. 

The report displays the following sections of information. 

* Filter 

Shows the current filter applied to the report, such as the Start time range. 

* Execution Information 

Shows the start time, end time, and duration for each package execution.You can view a 

list of the parameter values that were used with a package execution, such as values that 

were passed to a child package using the Execute Package task. 


Q135. You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed. 

You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables. 

You need to configure the SQL Server environment to support these packages. 

What should you do? 

A. Create SSIS configuration files for the packages. 

B. Create an Integration Services catalog. 

C. Install Data Quality Services. 

D. Install Master Data services. 

Answer: B 

Reference: http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx 


Q136. You are using the Master Data Services (MDS) Add-in for Excel to configure the entities in a model- The model consists of two entities: one named Customer and one named State. You opened the Customer entity. 

Currently, data stewards can enter any text value in the Customer entity's State attribute. You must restrict the entry of values in the State attribute to members defined in the State entity. 

You need to configure the State attribute of the Customer entity. 

Which command should you use? (To answer, select the appropriate command in the answer area.) 


Answer: 



Q137. You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse. 


You need to establish the correct order for loading each table to maximize parallel processing. 

Which order should you use? 

A. 1. DimCurrency, DimScenario, DimAccount in parallel 

2. DimOrganization 

3. FactFinance 

B. 1. DimCurrency, DimOrganization in parallel 

2. DimScenario, DimAccount in parallel 

3. FactFinance 

C. 1. DimCurrency, FactFinance in parallel 

2. DimOrganization, DimScenario, DimAccount in parallel 

D. 1. FactFinance 

2. DimOrganization, DimScenario, DimAccount in parallel 

3. DimCurrency 

E. 1. DimCurrency 

2. DimOrganization 

3. DimScenario, DimAccount in parallel 

4. FactFinance 

Answer: A 

Explanation: References: http://msdn.microsoft.com/en-us/library/ms139892.aspx 

http://msdn.microsoft.com/en-us/library/ms141261.aspx 


Q138. 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 

Answer: C 


70-463 practice exam

Downloadable 70-463 actual exam:

Q139. You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may be executed in any order. The T-SQL statements have unpredictable execution times. 

You have the following requirements: 

. The package must maximize parallel processing of the T-SQL statements. 

. After all the T-SQL statements have completed, a Send Mail task must notify administrators. 

You need to design the SSIS package. 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.) 


Answer: 



Q140. You administer a large and complex SQL Server Integration Services (SSIS) solution in the SSIS catalog. 

You are instructed to execute a package by using PowerShell. You need to create the correct PowerShell command. 

How should you place the four code segments in sequence? (To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.) 


Answer: 



Q141. You are designing a data warehouse that contains a product dimension. 

The product dimension contains the original product attributes as well as the most recent set of updated attributes. When a product is updated, the original product attributes must be preserved. 

The product dimension design must: 

. Support a Type 3 Slowly Changing Dimension (SCD) process 

. Minimize the total storage consumed 

You need to design a solution that meets these requirements. 

What should you do? 

A. Design two tables. One table will hold the original product surrogate key and attributes. The other table will hold the most recent product surrogate key and attributes. 

B. Design a table as a Type 2 SCD dimension that holds the surrogate key for each product and its attributes. Limit the number of records per product within the table to two. 

C. Design a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes. 

D. Design a table to hold the surrogate key for each product and its attributes. Add a column to store an indicator that identifies the record as original data or recent data. 

Answer: C 


Q142. You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL task named Create Batch. After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the batch as successful. 


You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails. 

Which three steps 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.) 


Answer: 



Q143. To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place. 

The methodology has the following requirements: 

The deployment process must be simplified. 

All the logs must be centralized in SQL Server. 

Log data must be available via reports or T-SQL. 

Log archival must be automated. 

You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort. 

What should you do? 

A. Open a command prompt and run the gacutil command. 

B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. 

C. Add an OnError event handler to the SSIS project. 

D. Use an msi file to deploy the package on the server. 

E. Configure the output of a component in the package data flow to use a data tap. 

F. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. 

G. Open a command prompt and run the dtexec /rep /conn command. 

H. Open a command prompt and run the dtutil /copy command. 

I. Open a command prompt and run the dtexec /dumperror /conn command. 

J. Configure the SSIS solution to use the Project Deployment Model. 

K. Create a reusable custom logging component and use it in the SSIS project. 

Answer: J 

Explanation: 

References: http://msdn.microsoft.com/en-us/library/ms140246.aspx 

http://msdn.microsoft.com/en-us/library/hh231187.aspx 


Q144. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. 

You add an Execute SQL task to the control flow. The task must execute a simple INSERT statement. 

The task has the following requirements: 

. The INSERT statement must use the value of a string package variable. The variable name is StringVar. . The Execute SQL task must use an OLE DB Connection Manager. 

In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter. 

You must configure the SQLStatement property of the Execute SQL task. 

Which SQL statement should you use? 

A. INSERT INTO dbo.Table (variablevalue) VALUES (@StringVar) 

B. INSERT INTO dbo.Table (variablevalue) VALUES ($Project::StringVar) 

C. INSERT INTO dbo.Table (variablevalue) VALUES (?) 

D. INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar) 

Answer: C