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
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)?

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?

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?

Product Image

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

Self-Paced

Learning Style

Practice Exam

Learning Style

Beginner

Difficulty

1 Hour

Course Duration

Course Info

Download PDF

Certificate

See Sample

Buy Individually
tab
About Individual Course:
  • Individual course plan gives you access to this course
$109.00
$109.00
/ Each
0 Learners Have Enrolled For This Course

You have already taken demo for this course.

If you want to get access to demo again, feel free to contact our support at (855) 800-8240

Outline

More Information

More Information
Lab AccessNo
Learning StyleSelf-Paced Learning
Learning TypePractice Exam
DifficultyBeginner
Course Duration1 Hour
LanguageEnglish

Reviews

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

This Subscription Includes:

Information
Virtual instructor Led

Virtual Classroom Courses

Our virtual instructor-led courses give you access to live instructors training you with other live students in a virtual classroom environment.

< Play Video >
250+ Virtual Instructor-Led Courses
Information
Self-Paced

Online Self-Paced Courses

Take self-paced online courses at your convenience and own pace, with unlimited access to courses in various emerging technologies.

900+ Self-Paced Courses
Information
College

E-books blogs, case studies, articles

As part of informal learning, our platform will recommend E-books, whitepapers, case studies, articles, and videos. This is AI curated content closely aligned with your learning objectives.

E-books, blogs, & case studies
Information
College

Self-Assessments

Set yourself on the right path with self-assessments that allow you to gauge what you already know. Assessments then personalize your learning paths, and allow you to focus only on what you need to succeed.

Self-Assessments
Information
Dashboard

Full Learning Dashboard & Analytics

Access all your enrolled, completed, course statistics, and community discussions from one centralized and intuitive learning dashboard with built in analytics, course tracking, time spent, and more.

Analytics/Reporting
Information
Social

QuickStart Discussions

Engage with other learners where you can directly chat, ask questions, and socialize with other learners experts and instructors on a course subject.

Community Access Community Access
Information
Labs

Virtual Labs

Videos and lectures only go so far. Get real world, hands-on practice with virtual labs (not available for all courses).

Virtual Labs Virtual Labs
Information
Live Instructor Support

Live Instructor Mentoring & Support

Get your IT problems solved through a community of mentors, experts and peers. Get live help from experts to answer questions on course material or guidance on a project.

Mentoring & Discussions Mentoring & Discussions
Information
Dashboard

Career Paths

Start a learning pathway towards understanding and mastering your career. With QuickStart career paths, you can fully understanding and being the best in your field.

Learning Paths Learning Paths
Information
Dashboard

Informal Learning

Access to AI curated content from various content publishers which can help in self-directed learning.

Informal Learning Informal Learning
click here