Microsoft TS:MS SQL Server 2008.Business Intelligence Dev and Maintenan Sample Questions:

1. You manage the security in SQL Server 2008 R2 Reporting Services (SSRS).
You create a new role.
You need to grant the following permissions to the role:
View and modify security settings for reports, folders, resources, and shared data sources.
Create and delete reports.
Modify report properties.
View content, definition, and property for reports.
Which three actions should you perform? (Each correct answer presents part of the solution, Choose
three.)

A) Assign the Consume Reports task to the role.
B) Assign Set Security Policies for Items task to the role.
C) Assign the Manage Reports task to the role.
D) Assign the Manage All Subscriptions task to the role.
E) Assign the Manage Resources task to the role.

2. You design a package by using SQL Server 2008 Integration Services (SSIS). The package is designed by using your local development environment.
The package extracts data from a remote server and inserts it in the PROD database on the local SQL
Server 2008 instance. You deploy the package to the MSDB database on the production server.
You need to ensure that only users that belong to an appropriate database role can modify and execute the
deployed package. You also need to ensure that these users cannot access other packages that are
deployed to the server.
What should you do?

A) Assign the users to a custom database role in the MSDB database.
B) Assign the users to a custom database role in the PROD database.
C) Assign the users to the db_ssisoperator role.
D) Assign the users to the db_ssisltduser role.

3. You create a report by using SQL Server 2008 Reporting Services (SSRS).
You plan to deploy the report on a report server. The report will be delivered on a regular basis to a dynamic
list of subscribers.
You need to create a new subscription on the report server.
What should you do?

A) Select the report you created, and then click the New Subscription button.
B) Select the report you created. Click the Subscriptions tab, and then click the New Data-driven Subscription button.
C) Select the My Subscriptions page, and then click the New Data-driven Subscription button.
D) Select the report you created. Click the Subscriptions tab, and then click the New Subscription button.

4. 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

5. 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 and then ass a Named Calculation to that query, using OrderTotal -CostTotal as the expression.
C) Add a Named Query to the DSV, using Select *,OrderTotal CostTotal from SalesOrder as the expression.
D) Create a view, SalesProfit, as Select * from SalesOrder.
E) Create a view, SalesProfit, as Select *, OrderTotal CostTotal as Profit from SalesOrder.
F) Add a Named Calculation to the SalesOrder table, using OrderTotal,CostTotal as the expression.
G) Add a Named Calculation to the SalesOrder table, using OrderTotal CostTotal as the expression.
H) Add a Named Query to the DSV, using Select * from SalesOrder as the expression.

Solutions: