1

You are hired as a SQL developer for a large organization. The organization uses Microsoft SQL Server 2012. You have to import employee data into organization’s data warehouse for which you have to design a SQL Server Integration Services (SSIS) package. You have to store this package on the SSIS server and you use the Project Deployment method for that. Identify the database in which you should store your SSIS package?

2

Your organization has tasked you to implement a data warehouse and you decide to use Microsoft SQL Server 2012 for that. With the help of the data flow created, data is saved from a flat file to a Microsoft Excel 2013 spreadsheet, a SQL Server 2012 database. and an XML file. Which of the following options would you choose as a transformation component to accomplish this goal?

3

Your organization has tasked you implement a data warehouse for which you are using Microsoft SQL Server 2012. SQL Server Data Quality Services (DQS) is installed on a SQL Server instance. You have to enable a user named John to create and execute a DQS project. John, however, should be restricted from creating knowledge bases. You need to add John to the appropriate database role. Identify the role to which you should add James?

4

You received an overload error after you attempted to run the Powershell script to execute a SQL Server Integration Services (SSIS) package. Below is given a code. Can you review the code shown below and identify from the options 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’)

5

You are working for a company as corporate Business Intelligence (BI) administrator. You have to deploy multiple legacy SQL Server Integration Services (SSIS) 2008 R2 packages to your recently upgraded production Microsoft SQL Server 2012 Database instance. Many of the packages that were to be deployed had to be configured separately as they rely on the same backend datasource. You are required to deploy the SSIS packages but the connection settings must not be configured on each of the individual packages. Identify from the options below how this can be achieved with the least amount of administrative effort.

6

You are hired in a large organization as a SQL developer. The organization is using Microsoft SQL Server 2012. As a SQL developer, you were tasked to install the Data Quality Server component of SQL Server Data Quality Services (DQS). You have to locate the published DQS knowledge bases into DQS database. Which DQS database that be?

7

You are hired in a large organization as a SQL developer. The organization is using Microsoft SQL Server 2012. For tracking of accounting data purposes, you design a data warehouse. Star schema is decided to be used. Your Dimensions tables and Fact table has been designed. In your Fact table, you want a single fact to reference multiple dimension members, not just one. Identify the type of relationship you should use in your design.

8

There is a SQL Server 2012 Integration Services (SSIS) data flow for which you are developing a transformation package. You want to build the transformation package in a way that allows the total number of rows processed to be used to generate a report of the transformation completion. Can you identify the number of data flow you should create to build the package?

9

You work for a major manufacturing company as a member of the Data Warehouse design team. A data warehouse needs to be implemented and your team is in the design phase of implementation. You are creating the structure for a product dimensional table. The table will be used for sales and marketing purposes and used by the respective departments. Upon looking at the products tables in the production and archived Line of Business (LOB) applications, it is realized by you that there can be multiple locations manufacturing a single product. What will be the requirements of the dimension table?

You work for a major manufacturing company as a member of the Data Warehouse design team. A data warehouse needs to be implemented and your team is in the design phase of implementation. You are creating the structure for a product dimensional table. The table will be used for sales and marketing purposes and used by the respective departments. Upon looking at the products tables in the production and archived Line of Business (LOB) applications, it is realized by you that there can be multiple locations manufacturing a single product. What will be the requirements of the dimension table?

10

Identify from the following options one item that is NOT a process of Building a Data Quality Services (DQS) Knowledge Base (KB)?

11

You are hired as a data warehouse designer for a company and you support the sales and marketing departments. You are tasked to model a fresh data warehouse and sales department comes up with a requirement to slice information based on the Invoice Number. You want to maintain optimum performance and satisfy the sales department’s request. What is the best way to do that?

12

You are hired as a SQL developer for a large-scale company. The company uses Microsoft SQL Server 2012. The Data Quality Server component of SQL Server Data Quality Services (DQS) is installed by you. Can you identify which DQS database the DQS stored procedures be located?

13

You are hired as a SQL developer for a large-scale company. The company uses Microsoft SQL Server 2012. For the purpose of importing employee data into the data warehouse, you are tasked to design a SQL Server Integration Services (SSIS) package. In the process, you have to add a Script task to the package for which you need to specify Microsoft Visual Cu as the programming language. Can you identify from the options where should you specify the language in the Script Task Editor?

14

You are hired as a SQL developer for a large-scale company. The company uses Microsoft SQL Server 2012. You are required to design a SQL Server Integration Services (5515) package and you decide the SSIS package to be run from a command prompt. What step will you take to accomplish this task?

15

You implement a data warehouse using Microsoft SQL Server 2012. A SQL Server Integration Services (SSIS) project is created by you. The project contains a Web Service task, an Execute Package task, and a Send Mail task, in that sequence. You have to make sure that in case the Execute Package task fails, the Send Mail task must go on with execution. You have to modify the project in order to accomplish your goal. What step would you take?

16

You are hired as a SQL developer for a large-scale company. The company uses Microsoft SQL Server 2012. A SQL Server Integration Services (SSIS) package is being designed by you for the purpose of importing employee data into the data warehouse. You have to use a data viewer in SQL Server Data Tools (SSDT) so data rows can be troubleshooted. Data rows need troubleshooting because they pass through the pipeline. Can you identify the data viewer you must use within SSDT?

17

You are hired as a SQL developer for a large-scale company. The company uses Microsoft SQL Server 2012. For the purpose of centralizing data management, you have installed the Master Data Services (MDS). There is a Products table and it contains a Subcategory field. You decide to populate a field in the Subcategory table using this field. Can you identify the what type of MDS Structure do you need to create in the Subcategory table?

18

You are hired as a SQL developer for a large-scale company. The company uses Microsoft SQL Server 2012. A SQL Server Integration Services (SSIS) package is being designed by you. The package calculates complex data which must be handled by a custom task component. You have to make sure the custom task is properly deployed. What steps will you take to accomplish this task?

19

You are tasked to create a new data warehouse for the company’s sales operations. It is decided for the data loading operations to be carried out locally. The column data will have to be transformed during data loading, and the transformation will be made before the data is loaded into SQL server. To meet the requriements, you have to select the most suitable SQL Server Integration Services (SSIS) destination. Identify the destination you think would be most suitable.

20

You are hired as a SQL developer for a large-scale company. The company uses Microsoft SQL Server 2012 A SQL Server Integration Services (SSIS) package is being designed by you for the purpose of importing employee data into the data warehouse. You are setting the properties of a precedence constraint that will participate in a Logical AND relationship. Following are the constraints between the four tasks: - a Success constraint links Task A to Task D -a Failure constraint links Task B to Task D - a Success constraint links Task C to Task D How do you think should Task A, B, and C be completed for Task D to run?

21

You implement a data warehouse using Microsoft SQL server 2012. You create a SQL server integration services (SSIS) Package using SQL server data tools. From the SSIS menu and start menu, log events are enabled, and the package starts getting debugged. However, You do not see any events in the log events window. What would be your step of action?

22

A data warehouse s implemented using Microsoft SQL Server 2012. You create a SQL Server Integration Services (SSIS) package using SQL Server Data Tools. For the package, the SSIS log provider for SQL Server is enabled configured to reference a SQL Server database named Sales. You execute the package in the debugger. What would you do to view the log results?

23

You are hired as a SQL developer for a large-scale company. The company uses Microsoft SQL Server 2012. A SQL Server Integration Services (SSIS) package is being designed by you for the purpose of importing employee data into the data warehouse. You are setting the properties of a precedence constraint that will participate in a Logical AND relationship. Following are the constraints between the four tasks: - a Success constraint links Task A to Task D - a Failure constraint links Task B to Task D - a Success constraint links Task C to Task D You want to enable annotations on demand for which you will have to set the ShowAnnotation property. Identify from below the setting you would use for the ShowAnnotation property.

24

You implement a data warehouse using Microsoft SQL Server 2012. A SQL Server Integration Services (SSIS) package is created and stored in the msdb database on a SQL Server instance. You have to now execute the package, for which you have to use a tool or application. Which of the following would you use so you can successfully execute the package?