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

1. You develop a SQL Server 2008 Reporting Services (SSRS) report.
A Multidimensional Expressions (MDX) query used in the report contains a parameter to display sales data
for various product categories. You write the following query.
SELECT NON EMPTY {[Measures].[Reseller Sales-Order Quantity] } ON COLUMNS,
NON EMPTY
{([Date].[Calendar Year].[Calendar Year].ALLMEMBERS *
[Product].[Category].[Category].ALLMEMBERS ) }
ON CHAPTERS
FROM
(SELECT (STRTOSET(@ProductCategory,CONSTRAINED))
ON COLUMNS FROM [Adventure Works Cube])
You discover that the query generates an error when executed.
You need to ensure that the query executes successfully.
What should you do?

A) Remove the NON EMPTY clauses.
B) Rewrite the query to display the Order Quantity on the COLUMNS axis, the Calendar Year on the ROWS axis, and the Category data on the PAGES axis.
C) Replace the CHAPTERS axis with the ROWS axis.
D) Replace the CHAPTERS axis with the SECTIONS axis.

2. You maintain a report in a SQL Server 2008 Reporting Services (SSRS) instancE.The report contains
query parameters that allow users to filter the contents of the report. The report queries the data source
randomly.
You configure the report to be rendered from a report execution snapshot.
You need to ensure that users can continue to use parameters to filter the report.
What should you do?

A) Remove the filters from the dataset queries. Use the parameters to filter the datasets.
B) Move the queries to the stored procedures. Use the stored procedures to populate and filter the datasets of the report.
C) Add a default value to all parameters.
D) Modify the datasets to use the Table query type.

3. You create a SQL Server 2008 Analysis Services (SSAS) solution.
You create a dimension named DimProduct that contains an attribute named Price. You use the
DimProduct dimension in a cube.
You need to ensure that aggregations are not associated with the Price attribute.
What should you do?

A) Set the MembersWithData property of the Price attribute to NonLeafDataHidden.
B) Set the GroupingBehavior property of the Price attribute to DiscourageGrouping.
C) Set the IsAggregatable property of the Price attribute to False.
D) Set the AttributeHierarchyEnabled property of the Price attribute to False.

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

5. You develop a SQL Server 2008 Integration Services (SSIS) project. You receive information on sales leads from the sales department. These leads are contained in Microsoft Excel files. You have to load these leads on a daily basis by designing, configuring, deploying, and scheduling steps.
Because this is a daily task, loading Excel files with the Import and Export Data (32-bit or 64-bit) wizard is NOT an option.
When designing the SSIS package, you receive the error shown below and in the Exhibit. (Click the Exhibit button.)
Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
You need to resolve the error.
Which change should you make to the debug options?

A) Set RunInOptimizedmode to True.
B) Set RunInOptimizedmode to False.
C) Set Run64BitRuntime to False.
D) Set Run64BitRuntime to True.

Solutions: