Microsoft 70-452 Real Exam Questions

January 26, 2011 admin Certification Vendors, MICROSOFT

PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008

1: You design a Business Intelligence (BI) solution by using SQL Server 2008. You plan to deploy a new database to the SQL Server 2008 Analysis Services (SSAS) instance. The database contains a cube. The cube contains three Type 1 slowly changing dimensions. The database is updated throughout the day by adding 5,000 rows of data every hour. You need to ensure that the cube always contains up-to-date data. You also need to ensure that the users can access the cube during cube processing. What should you do?
A.Use the relational online analytical processing (ROLAP) cube storage model.
B.Use the hybrid online analytical processing (HOLAP) cube storage model. Use the snapshot isolation level in the relational database that the cube is built on.
C.Use the automatic multidimensional online analytical processing (MOLAP) cube storage model.
D.Use the hybrid online analytical processing (HOLAP) cube storage model. Use SQL Server 2008 Integration Services (SSIS) pipeline tasks to schedule periodic cube updates.
Correct Answers: A

2: You design a Business Intelligence (BI) solution by using SQL Server 2008.
You have deployed a SQL Server 2008 Reporting Services (SSRS) application.
The number of users for the SSRS application increases. This results in performance problems. The datasets for the reports are optimized.
You investigate the SSRS deployment and discover the following characteristics:
Users report that the reports take a long time to run.
There are a large number of graphical reports that summarize data.
Subscription processing affects the performance of reports that are run by interactive users.
You need to modify the SSRS infrastructure to resolve the performance problems.
Which scale-out strategy should you use?
A.Single SSRS server and a single reporting database server
B.Multiple SSRS servers and a single reporting database server
C.Single SSRS server and multiple reporting database servers
D.Multiple SSRS servers and multiple reporting database servers
Correct Answers: B

3: You design a Business Intelligence (BI) solution by using SQL Server 2008.
At the end of every business day, an application records the inventory to the Products table.
The business solution for the application must accommodate the following features:
The content of the Products table varies every day.
Historical product attributes are not stored.
You need to identify an appropriate dimensional model to meet the business solution.
Which model should you use?
A.Degenerate Dimension
B.ParentCChild Dimension
C.Type I Slowly Changing Dimension
D.Type II Slowly Changing Dimension
Correct Answers: C

4: You are the lead developer for a SQL Server 2008 data warehousing project.
The source database for the project is an online transaction processing (OLTP) system. The OLTP system executes 4,000 transactions every minute during business hours.
The OLTP system records only the date and time of insertion of a new row and not for the updates of existing rows.
You plan to design an extract, transform, and load (ETL) process for the project that populates a data warehouse from the source database.
The ETL process must be configured in the following manner:
To run after business hours
To capture new rows and existing rows that have been modified
You need to ensure that only new rows or modified rows from the database tables are processed by the ETL process.
What should you do?
A.Configure the data warehouse database to support the Type I Slowly Changing Dimension transformation.
B.Configure the data warehouse database to support the Type II Slowly Changing Dimension transformation.
C.Configure the Change Data Capture feature on all the source database tables that will be processed by the ETL process.
D.Configure the Change Data Capture feature on all the data warehouse database tables that will be processed by the ETL process.
Correct Answers: C

5: You design a Business Intelligence (BI) solution by using SQL Server 2008.
You plan to design a dimensional modeling strategy for a new data warehouse application.
The application contains the following dimensions:
     Product
     Time
     Customer
     SalesPerson
The application contains the following cubes:
    Sales that contains all the dimensions
    Products that contain the Product and the Time dimensions
    Customers that contain the Customer and the Time dimensions
You need to design an appropriate dimensional modeling strategy for the Product and the Time dimensions.
Which dimensional model should you use?
A.Conformed dimensions
B.Degenerate dimensions
C.ParentCChild dimensions
D.Reference dimensions
Correct Answers: A

6: You design a Business Intelligence (BI) solution by using SQL Server 2008.
The instance contains an SQL Server 2008 Analysis Services (SSAS) database. The SSAS database contains a cube named Sales. The Sales cube has a dimension named Geography and a role named roleEurope.
The Geography dimension has a hierarchy that contains the following members:
    Continent
    Region
    City
You plan to design the security configuration for the Sales cube.
You need to enable the Read permissions for the roleEurope role. You also need to ensure that the roleEurope role can access only the Fact rows that are members of the Europe continent.
Which Multidimensional Expressions (MDX) statement should you use?
A.MEASURES.CURRENTMEMBER IS EUROPE
B.MEASURES.CURRENTMEMBER[CONTINENT] IS EUROPE
C.ANCESTOR(GEOGRAPHY.CURRENTMEMBER) IS EUROPE
D.ANCESTOR(GEOGRAPHY.CURRENTMEMBER,[CONTINENT]) IS EUROPE
Correct Answers: D

7: You design a Business Intelligence (BI) solution by using SQL Server 2008. You plan to design a report that uses data obtained from a SQL Server 2008 Analysis Services (SSAS) instance. The SSAS cube contains five parentCchild key performance indicators (KPIs). Each KPI has nine children. You need to create an executive dashboard in Microsoft Office SharePoint Server (MOSS) that displays the KPIs and depicts the parentCchild relationship. Which technology should you use?
A.Microsoft Office Excel
B.MOSS KPI Library
C.MOSS Business Data Catalog
D.Microsoft Office PerformancePoint Server
Correct Answers: D

8: You design a Business Intelligence (BI) solution by using SQL Server 2008. You plan to upgrade a Microsoft SQL Server 2005 Integration Services (SSIS) package. The package performs an extract, transform, and load (ETL) process for a sales data warehouse. You discover that the Lookup Transformation process runs slow. The package uses only OLE DB sources and destinations. You need to optimize the performance of the process that populates the Fact tables. What should you do?
A.Add a Fuzzy Lookup Transformation.
B.Add an Import Column Transformation.
C.Add a Raw File Source to populate the Lookup Transformation
D.Add a Cache Transformation to populate the Lookup Transformation.
Correct Answers: D

9: You design a Business Intelligence (BI) solution by using SQL Server 2008. You design a solution that analyzes the usage of discount vouchers issued by the company. The data warehouse of the company contains a dimension table named Vouchers. The dimension table contains two columns named VoucherNumber and CustomerFullName. A value for the CustomerFullName column is not available till the voucher is used. You need to configure the Slowly Changing Dimension transformation to load the Vouchers dimension even if there is a NULL value in the CustomerFullName column. What should you do?
A.Enable the support for inferred members.
B.Set the Change Type option to Fixed Attribute for the CustomerFullName column.
C.Set the Change Type option to Historical Attribute for the VoucherNumber column.
D.Set the Change Type option to Changing Attribute for the VoucherNumber column.
Correct Answers: A

10: You design a Business Intelligence (BI) solution by using SQL Server 2008.
You develop 10 SQL Server 2008 Integration Services (SSIS) packages. You plan to include package configuration for all the packages.
The package configuration has the following requirements:
   All configurations are stored in a single location.
   Configuration variables are easily backed up and restored.
   Indirect configuration is used.
The database administrators will use SQL Server client tools to change the configuration values.
You need to create package configurations for the packages to meet the configuration requirements.
What should you do?
A.Store all configuration information in a SQL Server table. Specify configuration database connectivity settings in an environment variable.
B.Store all configuration information in a SQL Server table. Specify configuration database connectivity settings in the SQL Server table.
C.Use XML configuration files for all packages. Store each XML configuration file in a common folder. Specify the XML configuration file location in an environment variable.
D.Use XML configuration files for all packages. Store each XML configuration file in a common folder. Specify the XML configuration file location in the configuration settings.
Correct Answers: A

Download | Password: certificatexam.com

Microsoft 70-452, SQL Server