Microsoft Transition Your MCITP: Business Intelligence Developer 2008 to MCSE: Business Intelligence Sample Questions:

1. You are designing a SQL Server Reporting Services (SSRS) report to display vineyard names and their year-to-date (YTD) grape yield. Grape yield values are classified in three bands:
High Yield
Medium Yield
Low Yield
You add a table to the report. Then you define two columns based on the fields named VineyardName and YTDGrapeYield.
You need to set the color of the vineyard text to red, yellow, or blue, depending on the value of the YTD grape yield values.
What should you do?

A) Use an expression for the Style property of the vineyard text box.
B) Add an indicator to the table.
C) Use an expression for the Font property of the vineyard text box.
D) Use an expression for the TextDecoration property of the vineyard text box.
E) Use an expression for the Color property of the vineyard text box.

2. You are modifying a star schema data mart that feeds order data from a Microsoft Azure SQL Database database into a SQL Server Analysis Services (SSAS) cube. The data mart contains two large tables that include flags and indicators for some orders. There are 100 different flag columns, each with 10 different indicator values. Some flags reuse indicators. The tables both have a granularity that matches the fact table.
You have the following requirements:
- Allow users to slice data by all flags and indicators. - Modify the date dimension table to include a surrogate key of a numeric data type and add the surrogate key to the fact table. - Use the most efficient design strategy for cube processing and queries.
You need to modify the schema.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A) Define the surrogate key as a BIGINT data type. Combine the distinct flag/indicator combinations into a single dimension.
B) Define the surrogate key as an INT data type. Combine the distinct flag/indicator combinations into a single dimension.
C) Define the surrogate key as a BIGINT data type. Create a single fact dimension in each table for its flags and indicators.
D) Define the surrogate key as an INT data type. Create a single fact dimension in each table for its flags and indicators.

3. You are developing a SQL Server Analysis Services (SSAS) tabular project.
A column named City must be added to the table named Customer. The column will be
used in the definition of a hierarchy. The City column exists in the Geography table that is
related to the Customer table.
You need to add the City column to the Customer table.
How should you write the calculation?

A) Option A
B) Option F
C) Option E
D) Option D
E) Option C
F) Option B

4. You need to use SQL Server Management Studio (SSMS) to make the SSAS databases available for application testing.
What should you do?

A) Restore the SSAS databases from the latest backup to SSAS01.
B) Script the databases as a Create script to a new window and then execute the script on SSAS01.
C) Detach the SSAS databases from the SSASCluster, and then attach them to SSAS01.
D) Use the Import/Export Wizard to copy the databases from the production server to the development server.

5. DRAG DROP
You have a business intelligence (BI) infrastructure.
You create a SQL Server Integration Services (SSIS) project that contains the packages for the BI infrastructure.
You need to automate the deployment of the SSIS project. The solution must ensure that the deployment can occur as part of an unattended Transact-SQL script.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Solutions: