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

1. You create a SQL Server 2008 Analysis Services (SSAS) solution.
You create a key performance indicator (KPI) named GPMargin for your solution. You set the Value
expression of the KPI in the following manner.
[Measures].[Amount],[Account].[Accounts].[Operating Expenses] /
[Measures].[Amount],[Account].[Accounts].[Gross Margin]
Your solution has a time dimension named DimTime.
You write the following Multidimensional Expressions (MDX) statement. (Line numbers are included for
reference only.)
01 IIf(
02 & &
03ParallelPeriod
04( [DimTime].[Calendar].[Quarter],1,[DimTime].[Calendar].CurrentMember
05)?), 1, -1 )
You need to set a Trend expression of the KPI to show a difference in the values compared with the
previous quarter.
Which MDX code segment should you insert at line 02?

A) KPIGoal( "GPMargin" ) >?( KPIValue( "GPMargin" ),
B) KPITrend( "GPMargin" ) >?( KPIValue ( "GPMargin" ),
C) KPIStatus( "GPMargin" ) >?( KPIValue( "GPMargin" ),
D) KPIValue( "GPMargin" ) >?( KPIValue( "GPMargin" ),

2. You are designing a SQL Server 2008 Integration Services (SSIS) packagE.The package includes a Data
Flow task that copies several million rows. The data flow requires a match against a reference table that
contains 10,000 rows. Each row in the reference table will be used during the data flow.
You need to ensure that the data flow executes in the minimum amount of time.
Which data flow component should you select?

A) a Lookup transformation along with the Partial cache option
B) a Fuzzy Lookup transformation
C) a Lookup transformation along with the Full cache option
D) a Lookup transformation along with the No cache option

3. You maintain a SQL Server 2008 Analysis Services (SSAS) database that contains a Product dimension.
The dimension contains the Category and Subcategory attributes.
The attributes have a Rigid relationship typE.The data source for the Product dimension changes the
relationship between the Category and Subcategory values.
You need to execute an XML for Analysis (XMLA) command to process the dimension to successfully
reflect the changE.What should you do?

A) Use the ProcessAdd command.
B) Use the ProcessIndexes command.
C) Use the ProcessUpdate command.
D) Use the ProcessClear and the ProcessDefault commands.

4. You plan to use SQL Server 2008 Integration Services (SSIS) on a SQL Server 2008 failover cluster.
You have a complex package that takes a long time to run.
You need to ensure that if the package executes during a failover, the package can be resumed at the point
of failure.
What should you do?

A) Cluster the SSIS service. Add the SSIS service to the SQL Server cluster resource group.
B) Implement transactions within your package. Restart the package whenever a failover occurs.
C) Cluster the SSIS service. Add the SSIS service to its own cluster resource group.
D) Implement checkpoints within your package. Restart the package whenever a failover occurs.

5. 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 users can view, execute, and delete their own packages. What should you do?

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

Solutions: