Certification Exam Prep Questions For Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463)


QuickStart is now offering assessment questions for Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463). Whether you are deciding which exam to sign up for, or simply want to practice the materials necessary to complete certification for this course, we have provided a practice assessment to better aid in certification. 100% of the questions are real questions; from a recent version of the test you will take for Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463).


Arrow
 

1

After attempting to run the Powershell script to execute an SQL Server Integration Services (SSIS) package you received an overload error. Review the code shown below. Which line of code most likely caused the error? Add-Type -AssemblyName "Microsoft.Sqlserver.ManagementintegrationServices,Version=t1.0.0.0,Culturerneutral,PublicKeyToken=8984 Sdcd9080cc9r SsqIConnectionString = "Data Sourcerlocalhostanitial Catalog:master:Integrated Security=SSPI" $sq1Connection = New-Object System.Data.Sq1Client.Sq1Connection SsqIConnectionString $ssisServer = New-Object Microsoft.SqlServer.ManagementintegrationServicesintegrationServices Ssq1Connection $ssisCatalog = SssisServer.CatalogsrSSISDIE11 SssisFolder = $ssisCatalog.FoldersrProducts1 $ssisProject = SssisFolder.ProjectsrProductPopulationl $ssisPackages = $ssisProject.Packages[TroductPop.dtsx1 SssisPackages.Execute(‘false’)

2

You are the corporate Business Intelligence (BI) administrator. You are responsible for deploying multiple legacy SQL Server Integration Services (SSIS) 2008 R2 packages to your recently upgraded production Microsoft SQL Server 2012 Database instance. Several of the packages to be deployed rely on the same backend datasource and had to be configured separately. You would like to deploy the SSIS packages without having to configure connection settings on each of the individual packages. How can you achieve this with the least amount of administrative effort?

3

You are a SQL developer for a large company that is using Microsoft SQL Server 2012. You are designing a SQL Server Integration Services (SSIS) package that will import employee data into the data warehouse. You need to use the Project Deployment method to store this package on the 5515 server. In which database should you store your SSIS package?

A. MSDB
Incorrect.
B. Model
Incorrect.
C. Master
Incorrect.
D. SSISDB
Correct!
4

You use Microsoft SQL Server 2012 to implement a data warehouse. You are creating a data flow that imports data from a flat file and saves it to a Microsoft Excel 2013 spreadsheet, a SQL Server 2012 database. and an XML file. You need to use a transformation component that allows you to accomplish this goal. Which component should you use?

A. Multicast
Correct!
B. Merge
Incorrect.
C. RMerge Join
Incorrect.
D. Union All
Incorrect.
5

You use Microsoft SQL Server 2012 to implement a data warehouse. You install SQL Server Data Quality Services (DQS) on an instance of SQL Server. You must ensure that a user named James can create and execute a DQS project. However. James should not be allowed to create knowledge bases. You need to add James to the appropriate database role. To which role should you add James?

A. dqs_kb_administrator
Incorrect.
B. dqs_kb_editor
Incorrect.
C. sysadmin
Incorrect.
D. dqs_kb_operator
Correct!
6

You are a SQL developer for a large company that is using Microsoft SQL Server 2012. You installed the Data Quality Server component of SQL Server Data Quality Services (DQS). You need to locate the published knowledge bases. In which DQS database would you locate the DQS Knowledge Bases?

A. DQS_MAIN
Correct!
B. DQS_PROCS
Incorrect.
C. DQS_PROCS
Incorrect.
D. DQS_STAGING_DATA
Incorrect.
7

You are a SQL developer for a large company that is using Microsoft SQL Server 2012. You are designing a data warehouse to keep track of accounting data. You decide to use a star schema. You have designed your Dimensions tables and Fact table. You want a single fact within your Fact table to reference more than one dimension members. Which type of relationship should you use in your design?

A. Regular Dimension
Incorrect.
B. Many-to-Many
Correct!
C. Fact Dimension
Incorrect.
D. Reference Dimension
Incorrect.
8

You are developing a transformation package for use in a SQL Server 2012 Integration Services (5515) data flow. The transformation package must be built so that the total number of rows processed can be used to generate a report of the transformation completion. You need to determine the number of data flows needed to build the package. How many data flows should you create?

A. Two
Correct!
B. One
Incorrect.
C. Three
Incorrect.
D. Four
Incorrect.
9

You are member of the Data Warehouse design team for a major manufacturing company. The team is in the design phase of a data warehouse implementation. You are creating the structure for a product dimensional table that will support the sales and marketing departments. When you look at the products tables in the production and archived Line of Business (LOB) applications, you notice that a single product can be manufactured in multiple locations. The requirements for the dimensional table are:

10

Which item is NOT a process of Building a Data Quality Services (DQS) Knowledge Base (KB)?

A. Domain Management
Incorrect.
B. Knowledge Discovery
Incorrect.
C. Reference data services
Incorrect.
D. Normalize Domain
Correct!
11

You are the data warehouse designer for an international retailer that has over 1000 stores. The company has grown rapidly over the last three years, and the demands on the data warehouse that supports inventory has grown by 200%. The main fact table is approximately 15GB in size and contains over 500 million rows of time sensitive data that has to be updated weekly. Critical reports are produced daily. There are growing complaints concerning the performance of reports that depend on the data warehouse. You also observe that the time required to update the fact table is increasing and causing a delay in delivering the data for reporting. The strategy for loading and processing the fact table requires revision to decrease the load time and increase the performance. Choose the single best strategy for decreasing the load time and increasing performance.

12

You are the data warehouse designer who supports the sales and marketing departments. You are modeling a new data warehouse and the sales department wants to slice information based on the Invoice Number. What is the best way to satisfy this request while maintaining optimum performance?

13

You are a SQL developer for a large company that is using Microsoft SQL Server 2012. You have installed the Data Quality Server component of SQL Server Data Quality Services (DQS). You need to locate the DQS stored procedures. In which DQS database would you locate the DQS stored procedures?

A. DQS_PROCEDURES
Incorrect.
B. DQS_PROJECTS
Incorrect.
C. DQS_MAIN
Correct!
D. DQS_STAGING_DATA
Incorrect.
14

You are a SQL developer for a large company that is using Microsoft SQL Server 2012. You are designing a SQL Server Integration Services (5515) package that will import employee data into the data warehouse. You are adding a Script task to the package. You need to specify Microsoft Visual Cu as the programming language for this task. Where should you specify the language in the Script Task Editor?

15

You are a SQL developer for a large company that is using Microsoft SQL Server 2012. You are designing a SQL Server Integration Services (5515) package. You want to run the SSIS package from a command prompt. How should you accomplish this task?

16

You use Microsoft SQL Server 2012 to implement a data warehouse. A Microsoft Excel spreadsheet is shown in the exhibit. This spreadsheet contains employees and their years of experience in specific skill areas. You need to transform this data to match this schema: Employee Skill Years You need to choose an appropriate data transformation component. Which transformation component should you use?

17

You use Microsoft SQL Server 2012 to implement a data warehouse. You create the package shown in the exhibit. You create a read-write variable named current ltem and assign to it the value of the current item in the loop. You must be able to access the value of this variable in the code used by the Script task. However, the value of the variable must not be changed by the code. You need to modify the package to access the value of the current ltem variable. What should you do?

18

You are the Data Warehouse Administrator for your company. The company is considering upgrading to Microsoft SQL Server 2012. You presently have many mission critical Extraction, Transformation, and Load (ETL) operations that cannot be disrupted. You have configured a testing environment and upgraded one of your most critical packages to the new Project Deployment Model. Everything appeared to proceed without error, but when you attempt to execute the package. you get an error concerning the Provider. Packages that were created in the new SQL Server Data Tools Integrated Development Environment (IDE) execute completely without error. You need to eliminate the error and allow for the processing of the package. Your solution should require the least amount of administrative effort. What action or actions should you take?

19

You use Microsoft SQL Server 2012 to implement a data warehouse. You create a SQL Server Integration Services (SSIS) project that contains a Web Service task, an Execute Package task, and a Send Mail task, in that sequence. You must ensure that if the Execute Package task fails, the Send Mail task continues to execute. You need to modify the project to accomplish your goal. What should you do?

20

You are a SQL developer for a large company that is using Microsoft SQL Server 2012. You are designing a SQL Server Integration Services (SSIS) package that will import employee data into the data warehouse. In SQL Server Data Tools (SSDT), you want to use a data viewer to troubleshoot data rows because they pass through the pipeline. Which data viewer should you use within SSDT?

A. Histogram
Incorrect.
B. Grid Display
Correct!
C. Scatter Plot
Incorrect.
D. Column Chat
Incorrect.
21

You are a SQL developer for a large company that is using Microsoft SQL Server 2012. You have installed the Master Data Services (MDS) to centralize data management. You have a Products table with a Subcategory field. You want to use this field to populate a field in the Subcategory table. What type of MDS Structure do you need to create in the Subcategory table?

A. Domain-based attributes
Correct!
B. Entities
Incorrect.
C. Attributes
Incorrect.
D. Attributes groups
Incorrect.
22

You are a SQL developer for a large company that is using Microsoft SQL Server 2012. You are designing a SQL Server Integration Services (SSIS) package that calculates complex data that needs to be handled by a custom task component. You need to ensure that the custom task is deployed correctly. How should you accomplish this task?

23

You use Microsoft SQL Server 2012 to implement a data warehouse. You create a SQL Server Integration Services (SSIS) project to import data from a Microsoft Excel 2013 spreadsheet to a SQL Server 2012 database. You create the data flow shown in the exhibit. When you hover over the red X icon, a tooltip displays this message: The number of input columns for OLE DB Destination.Inputs[OLE DB Destination Input] cannot be zero. You need to solve this problem. What should you do?

24

You are creating a new data warehouse for your local sales operations. All data loading operations will be carried out locally. During data loading there will be a need to make transformations to the column data before it is loaded into SQL server. You need to choose the most appropriate SQL Server Integration Services (SSIS) destination to meet the requirements. Which destination should you choose?

A. SQL destination
Correct!
B. RecordSet destination
Correct!
C. OLE DB destination
Incorrect.
D. ADO NET destination
Incorrect.
25

You use Microsoft SQL Server 2012 to implement a data warehouse. You use SQL Server Data Tools to create a SQL Server Integration Services (SSIS) package. You enable the SSIS log provider for SQL Server for the package, and you set the configuration of the provider to reference a SQL Server database named Sales. You execute the package in the debugger. You need to view the log results. What should you do?

26

You use Microsoft SQL Server 2012 to implement a data warehouse. You create a SQL Server Integration Services (SSIS) project to import data from a Microsoft Excel 2013 spreadsheet to a SQL Server 2012 database. You create the data flow shown in the exhibit. When you hover over the red X icon, a tooltip displays this message: Columns "ProductName" and "Name" cannot convert between unicode and non-unicode string data types. You need to solve this problem. What should you do?

27

You are a SQL developer for a large company that is using Microsoft SQL Server 2012. You are designing a SQL Server Integration Services (SSIS) package that will import employee data into the data warehouse. You are setting the properties of a precedence constraint that will participate in a Logical AND relationship. The constraints between the four tasks are: * Task A is linked to Task D by a Success constraint. * Task B is linked to Task D by a Failure constraint. * Task C is linked to Task D by a Success constraint. You need to set the ShowAnnotation property to enable annotations on demand. Which setting should you use for the ShowAnnotation property?

A. ConstraintName
Incorrect.
B. ConstraintDescription
Incorrect.
C. ConstraintOptions
Incorrect.
D. AsNeeded
Correct!
28

You use Microsoft SQL Server 2012 to implement a data warehouse. You create a SQL Server Integration Services (SSIS) package and store it in the msdb database on a SQL Server instance. You need to execute the package. Which tool or application should you use to successfully execute the package?

A. SQL Server Data Tools
Incorrect.
B. Dtexec utility
Correct!
C. Managed API
Incorrect.
D. Dtutil utility
Incorrect.
29

You are a SQL developer for a large company that is using Microsoft SQL Server 2012.You are designing a SQL Server Integration Services (SSIS) package that will import employee data into the data warehouse. You are setting the properties of precedence constraints that will participate in a Logical AND relationship. The constraints between the four tasks are: * Task A is linked to Task D by a Success constraint. * Task B is linked to Task D by a Failure constraint. * Task C is linked to Task D by a Success constraint. For Task D to run, how should Task A, B, and C be completed?

30

You use Microsoft sql server 2012 to implement a data warehouse. You use sql server data tools to create a sql server integration services (SSIS) Package. you enable log events from the SSIS menu and start menu and start debugging the package. However , no events are displayed in the log events window. What should you do?

PDP Url

Sample Question - Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463)


Self-Paced

Learning Style

Beginner

Difficulty

1 Hour

Course Duration

Certificate

See Sample

Buy Individually
About Individual Course:
  • Individual course plan gives you access to this course
$109.00
$109.00
/ Each

Outline

More Information

More Information
Lab Access No
Learning Style Self-Paced Learning
Difficulty Beginner
Course Duration 1 Hour
Language English

Reviews

Write Your Own Review
Only registered users can write reviews. Please Sign in or create an account

Contact A Learning Consultant


click here