Microsoft 70-463 Practice Questions 4

You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse. You are importing data from a relational table named Projects. The table has change data capture enabled on all columns. You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package. Which query should you use as the data source?

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?

You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on SQL Azure. One of the columns in the data source is named ProductCode. Some of the data to be loaded will reference products that need special processing logic in the data flow. You need to enable separate processing streams for a subset of rows based on the source product code. Which data flow transformation should you use?

You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation. The reference data to be used in the transformation does not change. You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance. What should you do?

You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors. Products for each distributor source must be combined for insertion into a single product table destination. You need to select the appropriate data flow transformation to meet this requirement. Which transformation types should you use? (Each correct answer presents a complete solution. Choose all that apply.)

You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns:

• [ID]
• [QuoteDate]
• [Open]
• [Close]
• [High]
• [Low]
• [Volume]

Each of the following queries must be able to use a columnstore index:

SELECT AVG ([Close]) AS [AverageClose]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.

SELECT AVG([High] – [Low]) AS [AverageRange]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.

SELECT SUM([Volume]) AS [SumVolume]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’

You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize the number and size of the indexes. What should you do?

You are creating a SQL Server Master Data Services (MDS) model. This model is used to store a master list of products. An attribute must be added to the Product entity to define the sales manager responsible for each product. You need to create an attribute in the Product entity that prevents users from entering invalid sales manager values. Which type of attribute should you create?

You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure. Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart. You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented. What should you do?

You are maintaining a Data Quality Services (DQS) environment. The production server failed and a new server has been set up. The DQS databases are restored to a new server. All the appropriate permissions are granted. DQS users are experiencing issues connecting to the new Data Quality Server. You need to enable users to connect to the new server. Which Surface Area Configuration property should you enable?

You are developing a SQL Server Integration Services (SSIS) package. You need to design a package to change a variable value during package execution by using the least amount of development effort. What should you use?

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?

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?

You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on Server A. The package includes a data flow and is executed on ServerB. The destination table has its own identity column. The destination data load has the following requirements:

• The identity values from the source table must be used.
• Default constraints on the destination table must be ignored.
• Batch size must be 100,000 rows.

You need to add a destination and configure it to meet the requirements. Which destination should you use?

You are installing the Data Quality Client on user desktops. You need to ensure that the prerequisite software components are installed. Which components must be present to meet this goal? (Each correct answer presents part of the solution. Choose all that apply.)

You manage a SQL Server Master Data Services (MDS) environment. A new application requires access to the product data that is available in the MDS repository. You need to design a solution that gives the application access to the product data with the least amount of development effort. What should you do?

You manage a SQL Server Master Data Services (MDS) environment. A new application requires access to the product data that is available in the MDS repository. You need to design a solution that gives the application access to the product data with the least amount of development effort. What should you do?

You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure. The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model. Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. Each job passes a different folder path to the package. You need to configure the package to accept the folder path from each job. Which package configuration should you use?

You are installing SQL Server Data Quality Services (DQS). You need to give users belonging to a specific Active Directory group access to the Data Quality Server. Which SQL Server application should you use?

You are installing SQL Server Data Quality Services (DQS). You need to give specific user’s access to the Data Quality Server. Which SQL Server application should you use?

You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project. The first package that you create in this project contains a package connection that accesses a flat file. Additional packages in the project must also access this file. You need to define and reuse the flat file connection in all project packages. What should you do?

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 develop and deploy a SQL Server Integration Services (SSIS) package. The package is stored in the file system. You need to execute the package without importing it to the SSIS server. What should you use to execute the package? (Each correct answer presents a complete solution. Choose all that apply.)

You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior administrators. You are discussing the use of Slowly Changing Dimension Transformation Outputs. One of the output options causes Derived Column transformations to create columns for the expired row and the current row indicators. Which option is the output that causes this?

You work as a database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You have recently created an SQL Server Integration Services (SSIS) project catalog that contains two Environments. A single Environment Variable, named ConnectionString, of type string is specified by each environment. The project also has a project Connection Manager configured to link up with the data warehouse. You have been instructed to make sure that deployed packages are executed using any of the specified Environments. Which of the following suitably describes an Environment Variable?

You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse. The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis. You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum. What should you do?

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?

You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure. The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode. You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode. What should you do?

You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure. Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart. You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented. What should you do?