Microsoft SQL Server 2005 Business Intelligence 70-448 Certification

Exam Name: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

Question: 1

You are creating a SQL Server 2008 Integration Services (SSIS) package for Company.com. The

package contains six Data Flow tasks and three Control Flow tasks. You should alter the package

.Which is the correct answer?

A. You should increase the two Control Flow tasks and one Data Flow task to a container.

Change the TransactionOption property of the container to Supported.

B. You should increase the two Control Flow tasks and one Data Flow task to a container.

Change the TransactionOption property of the container to Disabled.

C. You should increase the two Control Flow tasks and one Data Flow task to a container.

Change the TransactionOption property of the container to Required.

D. You should increase the two Control Flow tasks and one Data Flow task to a container.

Change the TransactionOption property of the container to RequiredNew.

Answer: C

Question: 2

You are managing a SQL Server 2008 Reporting Services (SSRS) sample which does not give

some same rendering extensions for Company.com. You should make sure that you could set the

server in order to render to Microsoft Word. Which is the correct answer?

A. You should change the AppSetttings.config file.

B. You should change the Global.asax file.

C. You should change the Machine.config file.

D. You should change the RSReportServer.config file

Answer: D

Question: 3

You are creating a SQL Server 2008 Integration Services (SSIS) package on a SQL Server 2008

database for Company.com. In order to develop a failure recovery plan that is published for a

SQL Server. Which is the correct answer?

A. You should back up the master database.

B. You should back up the local database.

C. You should back up the system database.

D. You should back up the systemdb databse.

Answer: A

Question: 4

You are developing a SQL Server 2008 Reporting Services (SSRS) instance of report model for

Company.com.In the Report Builder tool, the users should need to create their SSRS reports.

The data source they used will include a Microsoft SQL Server 2008 database. Which include

1000 tables? You should design the report model for users, and allow access to only the 20tables

which they require for reporting. Which is the correct answer?

A. You should develop DataSet using the Web Service to Schema(s) option.

B. You should develop DataTable using the Web Service to Schema(s) option.

C. You should develop a data source view and select only the required tables and views.

D. You should set the data source view in the setting file.

Answer: C

Question: 5

You are managing a SQL Server 2008 Analysis Services (SSAS) database for Company.com.A

sales manager called Clerk is responsible for the sales of bikes in the Northeast region. You

decide to give some rights to the rights to Clerk to visit the database.You won two roles below

called Southern Region and Nikes. Their schemas are listed below:You have make The Visual

Totals properties attribute true for roles above.You should make sure that Clerk could browser the

workers in the Product dimension which link to the Nikes category in the Southern region. Which

is the correct answer?

A. You should increase Clerk to a Nikes role

B. You should increase Clerk to the Southern Region role.

C. You should increase Clerk to a new role which owns components below:

D. [ Products Territory Region].[Southern]} as the permitted configuration.{[Product].[Product

Category].[Product Category].[Nikes]} as the permitted

E. You should increase Clerk to the default Region role.

Answer: C

Question: 6

You are managing a SQL Server 2008 Analysis Services (SSAS) database which includes a Sale

dimension that includes the Category and Subcategory properties for Company.com.There is a

rigid relationship type for properties. The data source for the Sale dimension alters

the relationship between the Type and Sub Type values. You should make sure that you could

run an XML to operate he dimension to reflect the change normally for Analysis (XMLA). Which is

the correct answer?

A. You should utilize the ProcessDefault command.

B. You should utilize the ProcessClear command.

C. You should utilize the ProcessIndexes command.

D. You should utilize the ProcessDefault and the ProcessClear commands.

Answer: D

Question: 7

You are managing a SQL Server 2008 Analysis Services (SSAS) instance for Company.com. In

order to execute the Usage-Based Optimization Wizard you should make query logging enable.

Which is the correct answer? Which is the correct answer?

A. You should make the QueryLogSampling server attribute default value.

B. You should make the QueryLogSampling server attribute 5.

C. You should configure the server property of DefaultFolders.

D. You should configure the QueryLogConnectionString server attribute and set a valid

connection string.

Answer: D

Question: 8

You are managing a SQL Server 2008 Analysis Services (SSAS) database for

Company.com.You get the Duplicate Key error when you operate the Analysis Services

database.You should alter the ErrorConfiguration attribute in order to make processing run

normally. Which is the correct answer?

A. You should alter the Dynamic Management View (DMV)

B. You should alter the Local Group

C. You should alter the dimension

D. You should alter the Transactions Log

Answer: C

Question: 9

You are managing a SQL Server 2008 Analysis Services (SSAS) database for Company.com. In

order to update data in a partition each hour you should run the incremental processing method.

In order to solve the problem, which is the correct answer?

A. You should utilize ProcessAdd for Analysis (XMLA) command

B. You should utilize default command for Analysis (XMLA)

C. You should utilize ProcessNone for Analysis (XMLA) command

D. You should utilize ProcessView for Analysis (XMLA) command

Answer: A

Question: 10

You are developing a SQL Server 2008 Reporting Services (SSRS) report for

Company.com.Assembly should be created in order to run real-time lookup and currency

conversion. The assembly has a static class named daily which lives in the namespace

HomeCalc.there is a method called DMO which need two arguments, Cuurnt and

HomeCalcWhen the report is operating, you should reference the ToEUR method in an

expression to convert USD to USO. Which is the correct answer?

A. You should use the expression of =Code. HomeCalc. Daily. USO (Fields! Cuurnt.Value," DMO

")

B. You should use the expression of =Code! HomeCalc. Daily. USO (Fields! Cuurnt.Value,"

DMO")

C. You should use the expression of = HomeCalc. Daily.USO (Fields! Cuurnt.Value,"USD")

D. You should use the expression of = HomeCalc! Daily.USO (Fields! Cuurnt.Value,"USD")

Answer: C

Question: 11

You are managing a SQL Server 2008 Analysis Services (SSAS) instance which includes a

database for Company.com. You alter the DataDir attribute of the instance to a new folder. You

should make sure that instance utilizes the new folder in least time.Which is the correct answer?

A. You should operate the database.

B. You should operate the database at the same time.

C. You should stop the Analysis Services service.

D. Before stopping the Analysis Services service you should remove the files in the original

folder,

Answer: A

Question: 12

You are managing a SQL Server 2008 Analysis Services (SSAS) database which includes a

dimension called Product for Company.com. You should set the Dimension Data configurations

as following:Deny access to the {[ Product].[Country].[USA],[ Product].[Country].[China]} configure

of property workers.New workers increased to the property are seen by default. Which is the

correct answer?

A. You should increase the whole country members.

B. You should increase property below:

[Country].[ China],[Product].[Country].[ USA]}

C. You should increase property below:

[Country].[ China],[Product].[Country].[ USA]}

D. You should increase property below:

[Country].[ China],[Product].[Country].[ USA]}

Answer: B

Question: 13

You are creating SQL Server 2008 Integration Services (SSIS) packages which is stored in a

folder on the network for Company.com.All members of the team can view the packages but not

the attribute values. Your task is to make the attribute values be protected by password. Which is

the correct answer below?

A. You should Use a batch file that runs the dtutil program to set the PackageProtectionLevel

property to EncryptSensitiveWithPassword on each of the SSIS packages you have

B. You should Use a batch file that runs the dtutil program to set the PackageProtectionLevel

property to ServerStorage on each of the SSIS packages you have created.

C. You should Use the SSIS package designer to set the PackageProtectionLevel property to

EncryptAllWithUserKey on each of the previously saved packages.

D. You should Use the SSIS package designer to set the PackageProtectionLevel property to

EncryptSensitiveWithUserKey on each of the previously saved packages.

Answer: A

Question: 14

You are managing a SQL Server 2008 Analysis Services (SSAS) database for Company.com.

Through utilizing SQL Profiler, you decide to troubleshoot search function t. You should make

sure that it will cost a lot of time to query .Which is the correct answer?

A. You should utilize store procedure.

B. You should utilize Query Subcube

C. You should utilize a Dynamic Management View (DMV)

D. You should utilize Transactions Log.

Answer: B

Question: 15

You are developing a report which is difficult and cost a lot of time to produce. You use the

technology of SQL Server 2008 Reporting Services (SSRS).You should make sure that you could

set the report server to cache the report for 40 minutes at the beginning of the request. Which is

the correct answer?

A. Report snapshots should be run for report.

B. You should run report execution caching for report.

C. You should set the data source to utilize a DBCC INDEXDEFRAG.

D. You should set the data source to utilize a DBCC SHRINKFILE. INDEXDEFRAG

Answer: B

Question: 16

You are managing a SQL Server 2008 Reporting Services (SSRS) sample for Company.com.The

report users decide to transfer reports by web tools. You should make sure that you could set the

server for e-mail delivery through utilizing a server. Which is the correct answer?

A. You should change the AppSetttings.config file.

B. You should change the RSReportServer.config file

C. You should change the Machine.config file.

D. You should change the Web.config file.

Answer: B

Question: 17

You are developing a SQL Server 2008 Analysis Server (SSAS) method which includes a

measure group called TestGroup with a one partition for Company.com. The condition listed

below should be includedSearches which utilize TestGroup on the newest version of the

information.In a many aspects format, the whole collections which are member of TestGroup are

reserved.In order to solve the problem, which is the correct answer?

A. You should configure Real-time hybrid online analytical processing (HOLAP)

B. You should configure Real-time relational offline analytical processing (ROLAP).

C. You should configure scheduled relational offline analytical processing (ROLAP).

D. You should configure scheduled online processing (MOLAP).

Answer: A

Question: 18

You are developing a SQL Server 2008 Reporting Services (SSRS) method for Company.com.

There are some components listed below:A hierarchy called Test in a time dimension called

DimTestTimeA method called SalesNumber You decide to increase codes to the cube??s

calculation11. . . .12Freeze([Measures].[ SalesNumber],13[DimTestTime].[ Test].[Quarter].[Q0

2008]);14. . . . For every month in the second quarter of 2008, not only should you add the value

of the SalesNumber measure through 80%. But also you should make sure that the value for the

second quarter of 2008 keeps common. Which is the correct answer?

A. You should increase codes below at line 14. Scope([Measures].[DimTime].[Date].[Quarter].

[Q0 2008]);This=[DimTime].[Date].currentmember * 3;End Scope;

B. You should increase codes below at line 11. Scope([Measures].[ SalesNumber],[

DimTestTime].[ Test].[Quarter]. [Q0 2008]);End Scope;

C. You should increase codes below at line 14 [ SalesNumber],Descendants([DimTestTime].[

Test].[Quarter]. [Q0 2008][ DimTestTime].[ Test].[Month]));This=[ DimTestTime].[

Test].currentmember * 5;End Scope;

D. You should increase codes below at line 11. Scope([Measures]

SalesNumber],Descendants([DimTestTime].[ Test].[Second].[ Q0 2008][ DimTestTime].[ Test].[

Second]));This=[ [ Test] [Second].currentmember * 1.5;End Scope;

Answer: C

Question: 19

Through utilizing SQL Server 2008 Analysis Services (SSAS) for Company.com,you are

developing a model of data mining. Through utilizing the listed Data Mining Extensions (DMX)

codes, you could develop a structureCREATE MINING STRUCTURE [Clerk Maker] ( [User Key]

LONG KEY,[Total Age] LONG DISCRETIZED (Automatic,20),[Clerk Maker] LONG

ISCRETE,[Distance] TEXT DISCRETE,[Monthly Income] DOUBLE CONTINUOUS) Not only

should you develop a mining model which could be operated through utilizing this structure, but

also you should make sure that the model utilizes the Microsoft algorithm. Which is the correct

answer?

A. You should add codes below:

ALTER MINING STRUCTURE [Clerk Maker] ADD MINING MODEL [Decision Tree] ([User

Key], [Total Age], [Clerk Maker] PREDICT, [Distance], [Monthly Income])

B. You should add codes below:

INSERT INTO [Decision Tree] ([User Key], [Clerk Maker], [Monthly Income]) OPENQUERY

([Clerk Maker],'Select [User Key], [Clerk Maker], [Monthly Income]')

C. You should add codes below: SELECT * FROM [Decision Tree] USING

[Microsoft_Decision_Trees] FROM [Decision Tree]]

D. You should add codes below: SELECT * FROM [Decision Tree] USING

[Microsoft_Decision_Trees] FROM [Clerk Maker]

Answer: A

Question: 20

You are creating a SQL Server 2008 Integration Services (SSIS) package which should be

intercalated on 20 servers for Company.com. You can??t visit the file system of any server. You

should change the package setting attributes when package is running. Which is the correct

answer below?

A. You should create an XML configuration file. Select the XML configuration type in the SSIS

Package Configurations Organizer.

B. You should create a batch file that uses the dtutil utility program to set the package

configuration properties while the package deploys into production.

C. You should Use the SSIS Package Configuration Wizard to create a SQL Server Configuration

type. Select a centralized Microsoft SQL Server to store the package configuration

D. You should create a .REG file that contains entries for each of the configuration properties.

Select a single Registry Configuration type in the SSIS Package Configurations

Answer: C

Question: 21

You are developing a SQL Server 2008 Analysis Services (SSAS) method for Company.com.

There is a dimension called TestProduct which includes the listed properties.ProductCityCountry

The TestProduct dimension is connect with a measure group named Products which owns the

Granularity attribute link to City. It is a key for Product. You should make sure that you could

search values according to the City and Country properties. Which is the correct answer?

A. You should configure the source property to Product and the connected property to City. And

then configure the source property to City and the connected property to Country.

B. You should configure the source property to Product and the connected property to City.

C. You should modify the source property to Product and the connected property to City.

D. You should delete the source property to Product and the connected property to City.

Answer: A

Question: 22

You are developing a SQL Server 2008 Reporting Services (SSRS) method for Company.com.

The cube structure is listed below: For the DimCustomer dimension, you should make sure that

the Amount measure could be gathered. Which is the correct answer?

A. You should develop a one-to-many relationship between DimCustomer and FactTransaction.

B. You should develop a many-to-one relationship between DimCustomer and FactTransaction.

C. Before configuring FactCustomerAccount as an intermediate measure group, you should

develop a many-to-many relationship between DimCustomer and FactTransaction.

D. Before setting FactCustomerAccount as an advanced measure group, you should develop a

one-to-one relationship between DimCustomer and FactTransaction.

Answer: C

Question: 23

You are creating a Microsoft SQL Server 2005 Analysis Services (SSAS) project for

Company.com. In order to move database from Analysis Services 2000 to Analysis Services

2008, which is the correct answer?

A. You should close a new SSAS project by using Business Intelligence Development Studio

(BIDS). Right-click Project. Click the Add option, and then click the Existing Item option.

B. You should link to the SQL Server 2008 SSAS instance by using Microsoft SQL Server 2005

Reporting Services (SSRS). Right-click Instance in Object Explorer, and then click the

C. You should link to the SQL Server 2008 SSAS instance by using Microsoft SQL Server 2005

Integration Services (SSIS). Right-click Instance in Object Explorer, and then click the

D. You should link to the SQL Server 2008 SSAS instance by using Microsoft SQL Server

Management Studio (SSMS). Right-click Instance in Object Explorer, and then click the

Migrate

Answer: D

Question: 24

You are developing a SQL Server 2008 Analysis Services (SSAS) method for

Company.com.There is a table called Product which includes listed columns

Microsoft SQL Server 2005 Business Intelligence 70-446 Certification

Case Study # 1:

BACKGROUND

Fabrikam, Inc. is a manufacturing company that provides components for the engineering

industry. Fabrikam has a sister company named Contoso, Ltd.

Work orders are processed by the following departments:

• The Production department handles request for components that are not in stock.

• The Warehouse department handles the storage and stock management of premanufactured

components.

• The Packaging department handles the packaging of stock in the warehouse or the

production department.

• The Distribution department handles the delivery of orders to clients.

The company uses a flexible resourcing structure where individual employees can be reassigned

between departments. Productivity is higher when specific groups of staff members are assigned

to a particular department.

EXISTING ENVIRONMENT

Fabrikam uses a Microsoft SQL Server 2005 database named FabrikamSQLDB to control and

route client requests as work orders to the respective department.

New Client and ProductOrder records can be added to the FabrikamSQLDB database throughout

the day. These records must be reflected correctly in any reports with zero latency.

The FabrikamSQLDB schema is shown in the following exhibit.

The performance of a department is measured against the following factors:

• Time to resolve a work order

• Number of work orders completed in a given period

Existing SSAS Solution

Fabrikam uses a live Microsoft SQL Server 2005 Analysis Services (SSAS) database named

FabrikamASDB. At present, the company processes the Fabrikam SSAS solution every evening.

This processing takes three hours.

The Analysis Server Query Log is enabled.

The SSAS solution contains the following objects:

• A data source view. The data source view within the FabrikamASDB database sources data

from the FabrikamSQLDB database.

• A cube named FabrikamCube. FabrikamCube exists within the FabrikamASDB database.

FabrikamCube contains the following measure groups:

• The ProductOrder measure group. This measure group is based on the ProductOrder table.

o The ProductOrder measure group has Regular relationships to the Client, Currency, and

Product dimensions.

o The ProductOrder measure group uses real-time ROLAP that has no aggregations.

o Reports against the ProductOrder measure group currently tend to perform poorly when

aggregates are produced across a large range of data.

o Users have drillthrough capability to individual rows within the ProductOrder measure

group.

• The ProductOrderStatus measure group. This measure group is based on the

ProductOrderStatus table.

o The ProductOrderStatus measure group has approximately 20 million rows.

o The ProductOrderStatus measure group is split into the following two partitions:

o The CurrentDay partition. This partition is designed to contain all order status data for

the current working day and any future-dated orders.

o The Historical partition. This partition is designed to contain all historical data prior to

the current working day.

o Data within each partition is filtered by a query. Each query contains dates when the

measure group was partitioned.

o Data older than the current working day is used within highly aggregated reports.

o Data for the current working day is used for individual order status reviews.

• The EmployeeAssignment measure group. This measure group is based on the

EmployeeAssignment table.

o Records are added to the EmployeeAssignment data table continuously throughout the

day.

FabrikamCube contains the following dimensions:

• A Client dimension. Clients are divided into three regions: North, South, and West. Each

region is stored within the Region attribute of the Client dimension.

Case Study # 1 (Questions):

Question: 1

You need to ensure the Client dimension is able to support new orders and new client records.

Which setting should you configure?

A. ROLAP storage

B. Lazy aggregations

C. Write Enabled property

D. Unknown Member functionality

Answer: A

Question: 2

You need to meet the requirements of the accounts team.

What should you do?

A. Set the Dimension type to Currency.

B. Set the Currency member name as a unique attribute.

C. Hide non-leaf data within the Currency dimension key attribute.

D. Disable the All member of the Currency dimension key attribute.

Answer: D

Question: 3

You need to make structural changes to the cube. You also need to ensure that appropriate

aggregations are created after the changes are made.

What should you do?

A. Record a weeks user activity by using SQL Profiler. Apply structural changes. Replay the

activity and then run the Usage Based Optimization Wizard.

B. Apply structural changes. Run the Database Engine Tuning Advisor.

C. Apply structural changes. Run the Usage Based Optimization Wizard.

D. Record a weeks user activity by using SQL Profiler. Apply structural changes. Run the

Database Engine Tuning Advisor.

Answer: A

Question: 4

You need to select appropriate processing methods for the EmployeeAssignment measure group

and the Employee dimension.

Which method should you choose?

A. Run a Process Update against the Employee dimension. Run a Process Full against the

EmployeeAssignment measure group.

B. Run a Process Full against the EmployeeAssignment measure group. Run a Process Update

against the Employee dimension.

C. Run a Process Full against the Employee dimension. Run a Process Incremental against the

EmployeeAssignment measure group.

D. Run a Process Incremental against the EmployeeAssignment measure group. Run a Process

Full against the Employee dimension.

Answer: A

Question: 5

You need to ensure that the new sales manager is able to view clients within only the North and

South regions.

What should you do?

A. Add a Denied member set for the West clients to NorthRole.

B. Add an Allowed member set for the South clients to NorthRole.

C. Create a new role for the sales manager and add a Denied member set for the South clients.

D. Create a new role for the sales manager and add an Allowed member set for the South clients.

Remove the sales manager from SouthAndWestRole.

Answer: D

Question: 6

You need to meet the business requirements of the packaging department.

What should you do?

A. Add the ProductSize data table and the ProductSizeDescriptor column to the Product

dimension. Set the order of the ProductSKU attribute to the ProductSizeDescriptor column.

B. Add the ProductSize data table as a dimension and then add this dimension to the cube as a

Referenced dimension. Set the order of the key attribute of the ProductSize dimension to the

ProductSizeDescriptor column.

C. Add the ProductSize data table and the ProductSizeDescriptor column to the Product

dimension. Create a user-defined hierarchy that has ProductSKU as the top-level attribute and

ProductSizeDescriptor as the bottom-level attribute.

D. Add the ProductSize data table as a dimension and then add this dimension to the cube as a

Referenced dimension. Create a user-defined hierarchy within the ProductSize dimension. Set

the ProductSizeDescriptor attribute as the top-level attribute.

Answer: A

Question: 7

You need to ensure the Order Control team is able to track the number of orders that reach the

production department.

What should you do?

A. Create a new Time dimension based on the Holiday table. Use derived columns for the month

and the year.

B. Create a Server Time dimension. Within this dimension, enable the time periods year, month,

and date.

Select the Additional Manufacturing calendar option.

C. Create a new Time dimension based on the date values within the ProductOrderStatus table.

Flag those days on which no orders were received as Holiday.

D. Create a new Time dimension based on a data source view named query. Derive the data for

this data source view from unique dates within the ProductOrderStatus table. Use a subquery

to identify the holidays and derived columns for the month and the year.

Answer: D

Question: 8

You need to design a storage mode for the ProductOrderStatus measure group.

What should you do?

A. Use ROLAP for the Historical partition and MOLAP for the CurrentDay partition.

B. Use MOLAP for the Historical partition and ROLAP for the CurrentDay partition.

C. Use ROLAP for the Historical partition and Automatic MOLAP for the CurrentDay partition.

D. Use HOLAP for the Historical partition and Low-latency MOLAP for the CurrentDay partition.

Answer: B

Question: 9

You need to maintain the partitions on the ProductOrderStatus measure group.

What should you do?

A. Create a Microsoft SQL Server 2005 Integration Services (SSIS) package that alters the

partition queries and processes aggregated partitions every evening.

B. Enable proactive caching on the partitions. Set the cache to update periodically with a latency

of 24 hours.

C. Set the rebuild interval of the partitions to 36 hours. Create a Microsoft SQL Server 2005

Integration Services (SSIS) package that processes aggregated partitions every evening.

D. Enable proactive caching on the ProductOrderStatus measure group. Set scheduled polling

that re-processes the measure group whenever the system dates change.

Answer: A

Question: 10

You need to ship a copy of the FabrikamASDB metadata to Contoso, Ltd.

What should you do?

A. Use the Synchronize Database Wizard.

B. Describe the FabrikamASDB database in an XML for Analysis (XMLA) script. Ship the script to

Contoso, Ltd.

C. Copy the folder structure of the live SSAS solution to a compressed file. Ship the compressed

file to Contoso, Ltd.

D. Backup the FabrikamASDB database. Run a Process Full on the restored database. Ship the

database to Contoso, Ltd.

Answer: B

Question: 11

You need to help the HR team improve the resource allocation process.

What should you do?

A. Perform data mining regression analysis to identify the correlation between productivity and

specific employee groups.

B. Set the GroupingBehaviour property within the Employee dimension.

C. Partition the EmployeeAssignment measure group into groups by department.

D. Use the Fuzzy Grouping task in Microsoft SQL Server 2005 Integration Services (SSIS).

Answer: A

Case Study # 2:

BACKGROUND

Woodgrove Bank is a banking institution that offers deposit accounts and mortgage accounts.

At present, the bank holds 1,000,000 accounts. On an average 50,000 new accounts are added

every year.

Ten percent of the accounts are mortgage accounts. The balances of mortgage accounts are

recalculated every week.

Some of the existing Microsoft SQL Server Reporting Services (SSRS) reports are doublecounting

transactions where there is more than one account holder for the same account.

EXISTING ENVIRONMENT

Woodgrove Bank has partially implemented a data warehouse named WoodgroveDW.

WoodgroveDW Data Store

The WoodgroveDW database schema is shown in the exhibit.