Exam 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012/2014

Published: June 11, 2012
Languages: English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
Audiences: IT professionals
Technology: Microsoft SQL Server 2012/2014
Credit toward certification: MCP, MCSA, MCSE

Skills measured
This exam measures your ability to accomplish the technical tasks listed below. The percentages indicate the relative weight of each major topic area on the exam. The higher the percentage, the more questions you are likely to see on that content area on the exam. View video tutorials about the variety of question types on Microsoft exams.

Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.

Do you have feedback about the relevance of the skills measured on this exam? Please send Microsoft your comments. All feedback will be reviewed and incorporated as appropriate while still maintaining the validity and reliability of the certification process. Note that Microsoft will not respond directly to your feedback. We appreciate your input in ensuring the quality of the Microsoft
Certification program.

If you have concerns about specific questions on this exam, please submit an exam challenge.

If you have other questions or feedback about Microsoft Certification exams or about the certification program, registration, or promotions, please contact your Regional Service Center.

As of February 18, 2016, this exam includes content covering both SQL Server 2012 and 2014. Please note that this exam does not include questions on features or capabilities that are present only in the SQL Server 2012 product. For more information, please download and review this document.

Design and implement a data warehouse (10–15%)
Design and implement dimensions
Design shared/conformed dimensions; determine if you need support for slowly changing dimensions; determine attributes; design hierarchies; determine whether you need star or snowflake schema; determine the granularity of relationship with fact tables; determine the need for auditing or lineage; determine keys (business transactional or your own data warehouse/surrogate keys); implement dimensions; implement data lineage of a dimension table
Design and implement fact tables
Design a data warehouse that supports many to many relationships; appropriately index a fact table; using columnstore indexes; partitioning; additive measures; semi additive measures; non additive measures; implement fact tables; determining the loading method for the fact tables; implement data lineage of a fact table; design summary aggregation tables

Preparation resources
Introduction to dimensions (Analysis Services – multidimensional data)
Dimension relationships
Columnstore indexes

Extract and transform data (20–25%)
Define connection managers
Plan the configuration of connection managers; package level or project level connection manager; define a connection string; parameterization of connection strings
Design data flow
Define data sources and destinations; distinguish blocking and non-blocking transformations; use different methods to pull out changed data from data sources; determine appropriate data flow components; determine the need for supporting Slowly Changing Dimensions (SCD); determine whether to use SQL Joins or SSIS lookup or merge join transformations; batch processing versus row by row processing; determine the appropriate transform to use for a specific task; determine the need and method for identity mapping and deduplicating; fuzzy lookup, fuzzy grouping and Data Quality Services (DQS) transformation; determine the need for custom data sources, destinations, and transforms; determine what to do with erroneous rows; determine auditing needs; trusted/authoritative data sources, including warehouse metadata; extracting only changed rows
Implement data flow
Debug data flow; use the appropriate data flow components; SQL / SSIS data transformation; create SSIS packages that support slowly changing dimensions; use the lookup task in SSIS; map identities using SSIS fuzzy lookup (advanced); specify a data source and destination; use data flows; different categories of transformations; read, transform and load data; understand which transforms to use to accomplish a specific business task; data correction transformation; performance tune an SSIS dataflow; optimize Integration Services packages for speed of execution; maintain data integrity, including good data flow
Manage SSIS package execution
Schedule package execution by using SQL Server Agent; execute packages by using DTEXEC; execute packages by using SQL Server Management Studio; implement package execution; plan and design package execution strategy; use PowerShell to execute script; monitor the execution using Management Studio; use DTEXECUI; ETL restartability
Implement script tasks in SSIS
Determine if it is appropriate to use a script task; extending the capability of a control flow; perform a custom action as needed (not on every row) during a control flow

Preparation resources
Integration Services (SSIS) connections
Data flow
Slowly changing dimension transformation

Load data (25–30%)
Design control flow
Determine control flow; determine containers and tasks needed; determine precedence constraints; design an SSIS package strategy with rollback, staging and transaction control; decide between one package or multiple packages; determine event handlers; determine variables; determine parameters on package and project level; determine connection managers and whether they are package or project level; determine the need for custom tasks; determine how much information you need to log from a package; determine the need for checkpoints; determine security needs
Implement package logic by using SSIS variables and parameters
User variables; variable scope, data type; implement parameterization of properties using variables; using variables in precedence constraints; referring to SSIS system variables; design dynamic SSIS packages; package configurations (file or SQL tables); expressions; package and project parameters; project level connection managers; variables; implement dynamic package behavior; configure packages in SSIS for different environments, package configurations (xmlconfiguration file, SQLServer table, registry entry; parent package variables, environment variable); parameters (package and project level); project connection managers; property expressions (use expressions for connection managers)
Implement control flow
Checkpoints; debug control flow; implement the appropriate control flow task to solve a problem; data profiling; use sequence containers and loop containers; manage transactions in SSIS packages; managing parallelism; using precedence constraint to control task execution sequence; creating package templates; using the execute package task
Implement data load options
Implement a full and incremental data load strategy; plan for an incremental update of the relational Data Mart; plan for loads into indexed tables; configure appropriate bulk load options; select an appropriate load technique (SSIS Destination versus T-SQL) and load partitioned tables
Implement script components in SSIS
Create an SSIS package that handles SCD Type 2 changes without using the SCD component; work with script component in SSIS; deciding when it is appropriate to use a script component versus a built in; source, transformation, destination component; use cases: web service source and destination, getting the error message

Preparation resources
Integration Services transactions
Developing a custom task
Integration Services (SSIS) parameters

Configure and deploy SSIS solutions (20–25%)
Troubleshoot data integration issues
Performance issues; connectivity issues; execution of a task or transformation failed; logic issues; demonstrate awareness of the new SSIS logging infrastructure; troubleshoot a failed package execution to determine the root cause of failure; troubleshoot SSIS package failure from an invalid datatype; implement break points; data viewers; profile data with different tools; batch cleanup
Install and maintain SSIS components
Software installation (IS, management tools); development box and server; install specifics for remote package execution; planning for installation (32- versus 64-bit); upgrade; provisioning the accounts; creating the catalog
Implement auditing, logging, and event handling
Audit package execution by using system variables; propagate events; use log providers; log an SSIS execution; create alerting and notification mechanisms; use Event Handlers in SSIS to track ETL events and errors; implement custom logging
Deploy SSIS solutions
Create and configure an SSIS catalog; deploy SSIS packages by using the deployment utility; deploy SSIS packages to SQL or file system locations; validate deployed packages; deploy packages on multiple servers; how to install custom components and tasks; deploy SSIS packages by using DTUTIL
Configure SSIS security settings
SSIS catalog database roles; package protection levels; secure Integration Services packages that are deployed at the file system; secure Integration Services parameters, configuration

Preparation resources
Troubleshooting tools for package development
Load-balancing packages on remote servers by using SQL Server Agent
Integration Services (SSIS) logging

Build data quality solutions (15–20%)
Install and maintain data quality services
Installation prerequisites; .msi package; adding users to the DQ roles; identity analysis, including data governance
Implement master data management solutions
Install Master Data Services (MDS); implement MDS; create models, entities, hierarchies, collections, attributes; define security roles; import/export; subscriptions
Create a data quality project to clean data
Profile Online Transaction Processing (OLTP) and other source systems; data quality knowledge base management; create data quality project; use data quality client; improve data quality; identity mapping and deduplicating; handle history and data quality; manage data quality/cleansing

Preparation resources
Install Data Quality Services
Install Master Data Services
Master Data Services features and tasks

---

QUESTION 1
You manage a member server that runs Windows Server 2008 R2. The server runs the Remote Desktop Gateway (RD Gateway) role service.
You need to find out whether a user named User1 has ever connected to his office workstation through the RD Gateway server.
What should you do?
A. View the events in the Monitoring folder from the RD Gateway Manager console.
B. View the Event Viewer Security log.
C. View the Event Viewer Application log.
D. View the Event Viewer Terminal Services-Gateway log.

Answer: D
Explanation:
By using TS Gateway Manager, you can specify the types of events that you want to monitor, such as unsuccessful or successful connection attempts to internal network computers through a TS Gateway server.
When these events occur, you can monitor the corresponding events by using Windows Event Viewer. TS
Gateway server events are stored in Event Viewer under Application and Services Logs\Microsoft\Windows
\Terminal Services-Gateway\.
Source: https://technet.microsoft.com/en-us/library/cc730618(WS.10).aspx

---

QUESTION 2
Your company has an Active Directory domain. All the servers in the company run either Windows Server 2008 R2 or Windows Server 2003. A Windows Server 2003 server named
Server1 runs Microsoft SQL Server 2005 SP2 and Microsoft Windows SharePoint Services
(WSS) 2.0.
The company plans to migrate to WSS 3.0 SP2 on a Windows Server 2008 R2 server named Server2.
You need to migrate the configuration and content from Server1 to Server2.
What should you do?
A. Back up the SharePoint configuration and content from Server1. Install WSS 3.0 SP2 on Server2. Restore the backup from Server1 to Server2.
B. Upgrade Server1 to Windows Server 2008 R2. Back up the SharePoint configuration and content from Server1. Install WSS 3.0 SP2 on Server2. Restore the backup from Server1 to Server2.
C. Back up the SQL Server 2005 configuration and the WSS 2.0 databases from Server1. Install SQL Server 2005 on Server2. Restore the SQL Server 2005 backup from Server1 to Server2.
D. Back up the WSS 2.0 configuration and content from Server1. Install WSS 2.0 on Server2. Restore the backup from Server1 to Server2. Perform an in-place upgrade of WSS 2.0 to WSS 3.0 SP2 on Server2.

Answer: D
Explanation:
To migrate to SharePoint Services (WSS) 3.0. from Server1 to Server2 with all the configuration and content, you need to install WSS 2.0 on Server2. Back up the WSS 2.0 configuration and content from Server1 and restore the backup from Server1 to Server2. Perform an in-place upgrade of WSS 2.0 to WSS 3.0 on Server2.
When you run an in-place upgrade, all content and configuration data is upgraded in-place, at one time. When you start the in-place upgrade process, the Web server and Web sites remain offline until the upgrade has been installed. In-place upgrades are best for a stand-alone server and small installations as in this case
Reference: Install and configure Office SharePoint Server for an in-place upgrade
https://technet.microsoft.com/en-us/library/cc263212(TechNet.10).aspx
Reference: Determine upgrade approach (Office SharePoint Server)
https://technet.microsoft.com/en-us/library/cc263447(TechNet.10).aspx

---

QUESTION 3
Your company has an Active Directory domain. You have a server named KMS1 that runs Windows Server 2008 R2. You install and configure Key Management Service (KMS) on KMS1. You plan to deploy Windows Server 2008 R2 on 10 new servers. You install the first two servers. The servers fail to activate by using KMS1.
You need to activate the new servers by using the KMS server.
What should you do first?
A. Complete the installation of the remaining eight servers.
B. Configure Windows Management Instrumentation (WMI) exceptions in Windows Firewall on the new servers.
C. Install Volume Activation Management Tool (VAMT) on the KMS server and configure Multiple Activation Key (MAK) Proxy Activation.
D. Install Volume Activation Management Tool (VAMT) on the KMS server and configure Multiple Activation Key (MAK) Independent Activation.

Answer: A
Explanation:
Key Management Service
With KMS, IT pros can complete activations on their local network, eliminating the need for individual computers to connect to Microsoft for product activation. KMS is a lightweight service that does not require a dedicated system and can easily be co-hosted on a system that provides other services. By default, volume editions of Windows 7 and Windows Server 2008 R2 connect to a system that hosts the KMS service to request activation. No action is required from the user.
KMS requires a minimum number of computers (physical or virtual machines) in a network environment.
The organization must have at least five computers to activate Windows Server 2008 R2 and at least 25 computers to activate clients that are running Windows 7. These minimums are referred to as activation thresholds.
To use KMS activation with Windows 7, the computer must have the qualifying OS license (often obtained through OEMs as part of the new PC purchase) and contain a Windows Marker in BIOS.
Source: https://technet.microsoft.com/en-us/library/ff793423.aspx

---

QUESTION 4
You have four Remote Desktop Session Host Servers that run Windows Server 2008 R2. The Remote Desktop Session Host Servers are named Server1, Server2, Server3, and Server4.
You install the Remote Desktop Connection Broker role service on Server1.
You need to configure load balancing for the four Remote Desktop Session Host Servers. You must ensure that Server2 is the preferred server for Remote Desktop Services sessions.
Which tool should you use?
A. Group Policy Management
B. Remote Desktop Session Host Configuration
C. Remote Desktop Connection Manager
D. RD Gateway Manager

Answer: B
Explanation: ExplanationExplanation:
You can configure a Remote Desktop Session Host (RD Session Host) server to join a farm in RD Connection
Broker, and to participate in RD Connection Broker Load Balancing, by using the Remote Desktop Session Host Configuration tool.
To configure RD Connection Broker settings
1. On the RD Session Host server, open Remote Desktop Session Host Configuration. To open Remote Desktop Session Host Configuration, click Start, point to Administrative Tools, point to Remote Desktop Services, and then click Remote Desktop Session Host Configuration .
2. In the Edit settings area, under RD Connection Broker, double-click Member of farm in RD Connection Broker.
3. On the RD Connection Broker tab of the Properties dialog box, click Change Settings.
4. In the RD Connection Broker Settings dialog box, click Farm member.
5. In the RD Connection Broker server name box, type the name of the RD Connection Broker server.
6. In the Farm name box, type the name of the farm that you want to join in RD Connection
Broker.
7. Click OK to close the RD Connection Broker Settings dialog box.
8. To participate in RD Connection Broker Load Balancing, select the Participate in Connection Broker Load-Balancing check box.
9. Optionally, in the Relative weight of this server in the farm box, modify the server weight. By default, the value is 100. The server weight is relative. Therefore, if you assign one server a value of 50, and one a value of 100, the server with a weight of 50 will receive half the number of sessions.
10. Verify that you want to use IP address redirection. By default, the Use IP address redirection setting is enabled. If you want to use token redirection mode, select Use token redirection. For more information, see About IP Address and Token Redirection.
11. In the Select IP addresses to be used for reconnection box, select the check box next to each IP address that you want to use.
12. When you are finished, click OK.
Source: https://technet.microsoft.com/en-us/library/cc771383.aspx

---

QUESTION 5
You have a server that runs Windows Server 2008 R2. The server has Microsoft SharePoint Foundation 2010 installed. The server is configured to accept incoming email.
You create a new document library.
You need to ensure that any user can send e-mail to the document library.
What should you do?
A. Modify the RSS setting for the document library.
B. Modify the permissions for the document library.
C. Modify the incoming email settings for the document library.
D. Enable anonymous authentication for the Web application.

Answer: C
Explanation:
Enable and configure email settings for a library
Use this procedure to enable and configure email settings for a library to receive email
messages in the
SharePoint document library in a site.
Enable and configure email settings for a library
1. Open the site in which you want to receive email messages by using either of the following methods:
– In Internet Explorer, type the URL o the site.
– On the View Site Collection page, click the site collection that you want to view.
2. In the left navigation pane of the home page, click View All Site Content.
3. In the Documents section, click a document library name to open the library for which you want to enable and configure email settings.
4. On the Settings menu, click Document Library Settings, Picture Library Settings, or
Form Library Settings, depending on the kind of library that you are enabling and
configuring.
5. In the Communications section, click Incoming email settings.
6. In the Email section, select Yes to enable this library to receive email messages.
7. In the Email address box, type a unique name to use as part of the email address for this library.
8. In the Email Attachments section, decide where to save and how to group the email attachments in this library, and then choose whether to overwrite files that have the same name.
Note: If you decide not to overwrite files that have the same name and then later try to save a file that has the same name as one that already exists in the library, four random digits are appended to the file name for the new attachment. If this action fails, a globally unique identifier (GUID) is appended to the file name. If neither of these actions can produce a unique file name, the attachment is discarded.
9. In the Email Message section, choose whether to save the original email message in this library. If you select Yes, the original message is saved as a separate item in the library.
10. In the Email Meeting Invitations section, choose whether to save the attachments to your meeting invitations in this library.
11. In the Email Security section, choose whether to archive email messages only from members of the site who can write to the library or to archive regardless of who sends the email message.
12. Click OK to save the changes that you have made in the settings.
Source: https://technet.microsoft.com/en-us/library/cc262800.aspx

 

Click here to view complete Q&A of 70-463 exam
Certkingdom Review

Best Microsoft MCTS Certification, Microsoft 70-463 Training at certkingdom.com