Latest MCTS 70-448 Dumps Real Exam Download 1-10

Ensurepass

QUESTION 1

You are an ETL developer for a mortgage company. In your application you have to load data from a Microsoft Excel source and an OLE DB source.

The Excel source contains vender data and has approximately 1000 records.

The OLE source contains loan data and has approximately 1,000,000 records.

Sample vendor data (Excel source) is shown in the following table:

VendorID VendorName

1 VendorA

2 VendorB

3 VendorC

4 VendorD

5 VendorE

Sample loan data (OLE DB source) is shown in the following table:

LoanNumber VendorID LoanAmount

I-100 1 120000

I-200 1 130000

I-300 2 150000

I-400 3 150000

I-500 4 160000

I-600 4 170000

I-700 5 180000

The CIO asks you to generate an Excel file with the loan list information for each vendor. You build a SQL Server 2008 Integration Services (SSIS) package that can generate the Excel files dynamically. The Excel files should have the vendor name as the prefix and the date as the suffix (e.g., VendorA_DDMMYYYY.xlsx).

You get the error message shown in the exhibit. (Click the Exhibit button.)

You need to sort both sources in order to merge join the data.

What should you do?

 

A. Set the IsSorted property to True for both the OLE DB and Excel sources.

B. Use the sort transformation after the OLE DB source and set the IsSorted property to True for the Excel destination.

C. Sort the data in the select statement of the OLE DB source and use sort transformation after the Excel source.

D. Set the ISSorted property to True for the OLE DB, sort the data in the selct statement of the OLE DB source, and use sort transformation after the Excel source.

 

Correct Answer: D

Explanation/Reference:

 

 

QUESTION 2

You are an ETL developer for a mortgage company. Your application has two tables names Vendor and Loan.

Vendor

VendorID VendorName

1 VendorA

2 VendorB

3 VendorC

4 VendorD

5 VendorE

Loan

LoanNumber VendorID LoanAmount

I-100 1 120000

I-200 1 130000

I-300 2 150000

I-400 3 155000

I-500 4 160000

I-600 4 170000

I-700 5 180000

You need to build a SQL Server 2008 Integration Services (SSIS) package that meets the following requirements:

The package should be able to generate the Excel files dynamically. Each file should have the vendor name as a prefix and the date as the suffix (e.g.,

VendorA_DDMMYYYY.xlsx).

All the vendor names should be added to a variable and loop through the variable to generate and load separate files for each vendor dynamically.

Which property in the Excel file destination connection manager should you set with a variable?

 

A. Provider

B. Name

C. Excel File Path

D. Initial Catalog

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 3

You are an ETL developer for a mortgage company. In your application you have two tables names Vendor and Loan.

Vendor

VendorID VendorName

1 VendorA

2 VendorB

3 VendorC

4 VendorD

5 VendorE

Loan

LoanNumber VendorID LoanAmount

I-100 1 120000

I-200 1 130000

I-300 2 150000

I-400 3 150000

I-500 4 160000

I-600 4 170000

I-700 5 180000

You need to build a SQL Server 2008 Integration Service (SSIS) package that meets the following requirements:

The package should be able to generate the Excel files dynamically. Each file should have the vendor name as a prefix and the date as the suffix (e.g.,

VendorA_DDMMYYYY.xlsx).

All the vendor names should be added to a variable and loop through the variable to generate and load separate files for each vendor dynamically.

Which Type of variable should you create?

 

A. Table

B. String

C. Object

D. Int32

E. Int64

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 4

You company network includes a SQL Server 2008 R2 database server with the SQL Server 2008 Analysis Services (SSAS) role enabled.

You design a cube named Materials with a measure group named MaterialsInventory. This group has a measure, QualityOnHand, which shows the total stock in inventory for the company by the time.

You need to determine the inventory at the end of the quarter. What should you do?

 

A. Define the QuantityOnHand measure as a semi-additive measure.

B. Define the QuantityOnHand measure as an additive measure.

C. Define the MaterialsInventory group as a semi-additive measure group.

D. Define the MaterialsInventory group as an additive measure group.

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 5

You create a SQL Server 2008 Reporting Services (SSRS) report. This report includes a matrix containing three groups. The last group is Customer Name.

When a user clicks on a customer name, a new report should open with detailed customer information.

You need to enable this functionality. What option should you use?

 

A. Enable Interactive Sorting

B. Go to Bookmark

C. Go to Report

D. Go to URL

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 6

You are working as an ETL developer for a payroll company. You configure a SQL Server 2008 Integration Services (SSIS) package to use checkpoints.

The SSIS package truncates the dbo.Employee_Attendance table, loads the new employees in the dbo.Employee table, and then loads dbo.Employee_Attendance for a particular month.

The control flow for the package is shown in the Control Flow exhibit. (Click the Exhibit button.)

The data flow for the package is shown in the Data Flow exhibit. (Click the Exhibit button.)

The package fails at the Derived Column transformation within the data flow task.

You need to determine where the package execution will begin after you correct the errors and run the package again.

Where does the package execution begin?

 

A. Truncate Employee_ Attendance table in the Execute SQL task

B. Derived Column Transformation in the data Flow task

C. Load Employee in the Data Flow task.

D. Load Emp_Attendance in the Data Flow task.

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 7

You are an ETL developer. The CIO asks you to develop a SQL Server 2008 Integration Services (SSIS) package to load data from a source to a destination system. As part of the solution you have to use the script component as a source to load the data in the SQL Server table dbo.Employee.

The table dbo.Employee has the following fields:

Emp_ID int NOT NULL Emp_Name varchar (100) NOT NULL Emp_City varchar(50) NOT NULL Emp_State varchar(2) Emp_Zip varchar(10)

The Script Transformation Editor, which displays the column properties of the script component, is shown in the following exhibit. (Click the Exhibit button.)

You need to code the CreateNewOutputRows() method to assign values to the buffer.

Which code snippet should you use?

 

A. Public override void CreateNewOutputRows() ( OutoutBuffer0.AddRow(); OutoutBuffer0.EmpID = 1; OutoutBuffer0.EmpName = “Jeff Price”; OutoutBuffer0.EmpCity = “Any City”; OutoutBuffer0.EmpState = “TX”; OutoutBuffer0.EmpZip = “88001”; )

B. Public override void CreateNewOutputRows() ( InputBuffer.AddRow(); InputBuffer.EmpID = 1; InputBuffer.EmpName = “Jeff Price”; InputBuffer.EmpCity = “Any City”; InputBuffer.EmpState = “TX”; InputBuffer.EmpZip = “88001”; )

C. Public override void CreateNewOutputRows() ( EmployeeBuffer.AddRow(); EmployeeBuffer.EmpID = 1; EmployeeBuffer.EmpName = “Jeff Price”; EmployeeBuffer.EmpCity = “Any City”; EmployeeBuffer.EmpState = “TX”; EmployeeBuffer.EmpZip = “88001”; )

D. Public override void CreateNewOutputRows() ( OutoutBuffer.AddRow(); OutoutBuffer.EmpID = 1; OutoutBuffer.EmpName = “Jeff Price”; OutoutBuffer.EmpCity = “Any City”; OutoutBuffer.EmpState = “TX”; OutoutBuffer.EmpZip = “88001”; )

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 8

You create a SQL Server 2008 Analysis Services (SSAS) solution.

Your solution contains a cube with a dimension named Factories. The dimension has a property names ProductionLines. This solution also includes data about the suers employed at each factory.

You need to provide a way to browse the data within the dimension.

What should you do?

 

A. Change the DataSourceFolder properties.

B. Create a URL action on the User feature.

C. Set “get values from a query” in the Report Parameters dialog.

D. Configure a drillthrough action on the Factories dimension.

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 9

You have a SQL Server 2008 R2 Analysis Services (SSAS) solution.

You add a new Data Source View (DSV) to the solution. The DSV includes a SalesOrder table with columns for OrderTotal and CostTotal.

You need to derive the profit for each order without modifying the underlying data.

What should you do?

 

A. Add a Named Calculation to the SalesOrder table, using OrderTotal CostTotal as the expression.

B. Add a Named Query to the DSV, using Select *,OrderTotal CostTotal from SalesOrder as the expression.

C. Create a view, SalesProfit, as Select *, OrderTotal CostTotal as Profit from SalesOrder.

D. Add a Named Query to the DSV and then ass a Named Calculation to that query, using OrderTotal CostTotal as the expression.

E. Create a view, SalesProfit, as Select * from SalesOrder.

F. Add a Named Query to the DSV, using Select * from SalesOrder as the expression.

G. Add a Named Calculation to the SalesOrder table, using OrderTotal,CostTotal as the expression.

H. Add a Named Calculation to the SalesOrder table, using OrderTotal + CostTotal as the expression.

 

Correct Answer: AB

Explanation

Explanation/Reference:

 

 

QUESTION 10

You create a SQL Server 2008 R2 Analysis Services (SSAS) solution.

You create a new data mining query for you instance.

Which tool should you use?

 

A. SQL Server Configuration Manager.

B. SQL Server Business Intelligence Development Studio

C. SQL Server Error and Usage Reporting.

D. SQL Server management Studio

 

Correct Answer: D

Explanation

Explanation/Reference:

 

Download Lates MCTS 70-448 Real Free Tests , help you to pass exam 100%.