Exams > Search for an Exam

Preparation Guide for Exam 70-446

PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005

Updated: April 11, 2008
Related Links
•Register for this exam
•Take a practice test
•Join an MCP newsgroup
•Find a course
•Buy a Microsoft Press book

On This Page
Exam newsExam news
Exam topics coveredExam topics covered
Audience profileAudience profile
Credit toward certificationCredit toward certification
Preparation tools and resourcesPreparation tools and resources
Skills measuredSkills measured

Exam news

The Microsoft Certified Professional (PRO) exam, Exam 70-446: PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005, was released in March 2007.

Top of pageTop of page

Exam topics covered

The following list includes the topic areas covered on this exam. The percentage indicates the portion of the exam that addresses a particular skill.

•

Planning BI Solutions (15 percent)

•

Designing SSIS Solutions (21 percent)

•

Designing SSRS Solutions (14 percent)

•

Designing SSAS Solutions (22 percent)

•

Deploying and Optimizing SSAS Solutions (15 percent)

•

Designing Data Mining Solutions (13 percent)

Top of pageTop of page

Audience profile

This exam is intended for IT professionals who design and plan business intelligence (BI) solutions by using Microsoft SQL Server 2005 tools. The qualified candidate for this exam typically has the following experience.

•

Experience working with SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS)

•

At least one year of experience designing both relational and multidimensional database schemas

•

Experience designing and building cubes to support business requirements

•

One to two years of experience writing both relational and multidimensional queries

•

Experience in applying data mining algorithms to business problem domains

•

Experience designing and creating reports to support business requirements

•

Experience aggregating data into a data warehouse from multiple sources in a heterogeneous data environment

•

Experience in designing enterprise-level solutions for very large multidimensional databases

Top of pageTop of page

Credit toward certification

When you pass this exam, you earn credit toward the following certification:

•MCITP: Business Intelligence Developer

Top of pageTop of page

Preparation tools and resources

To help you prepare for this exam, Microsoft Learning recommends that you have hands-on experience with the product and that you use the following training resources. These training resources do not necessarily cover all of the topics listed in the "Skills measured" section.

Classroom trainingMicrosoft E-LearningMicrosoft Press booksPractice tests

Course 2794: Designing a Business Intelligence Solution Architecture for the Enterprise Using Microsoft SQL Server 2005 (two days)

Course 2795: Designing an ETL Solution Architecture Using Microsoft SQL Server 2005 Integration Services (two days)

Course 2796: Designing an Analysis Solution Architecture Using Microsoft SQL Server 2005 Analysis Services (three days)

Course 2797: Designing a Reporting Solution Architecture Using Microsoft SQL Server 2005 Reporting Services (two days)

There are no Microsoft E-Learning courses currently available.

Microsoft SQL Server 2005 Analysis Services Step by Step

Microsoft SQL Server 2005 Reporting Services Step by Step

Inside Microsoft SQL Server 2005: The Storage Engine

Inside Microsoft SQL Server 2005: T-SQL Querying

Inside Microsoft SQL Server 2005: Query Tuning and Optimization

Programming Microsoft SQL Server 2005

MeasureUp (Measureup.com)

Self Test Software (Selftestsoftware.com)

Microsoft online resources

•

Learning Plan: Get started by creating a step-by-step study guide that is based on recommended resources for this exam.

•

Microsoft SQL Server 2005 – Learning Portal: Find special offers and information on training and certification.

•

Microsoft Learning Community: Join newsgroups and visit community forums to connect with your peers for suggestions on training resources and advice on your certification path and studies.

•

TechNet: Designed for IT professionals, this site includes how-to instructions, best practices, downloads, technical resources, newsgroups, and chats.

•

MSDN: Designed for developers, the Microsoft Developer Network (MSDN) features code samples, technical articles, downloads, newsgroups, and chats.

Top of pageTop of page

Skills measured

This certification exam measures your ability to design and create business intelligence applications by using Microsoft SQL Server 2005 business intelligence tools.

This exam measures your ability to accomplish the technical tasks listed in the following table. The table shows which Microsoft Official Curriculum courses may help you reach competency in the skills being tested in the exam.

KEY:
Blank - No coverage.
Basic - The course provides a general introductory overview of this task. You will need to supplement the course with additional work.
Partial - The course includes some material to prepare you for this task. You will need to supplement the course with additional work.
Full - The course includes material to prepare you for this task.

Skills measured by Exam 70-446Course 2794Course 2795Course 2796Course 2797
Planning BI Solutions    

Select appropriate BI technologies.





Full

Specify the appropriate SQL Server edition.


Full




Design dimensional models.

•

Design dimensions for each subject area.

•

Design fact tables for each subject area.




Full


Design a physical schema based on a logical design.

•

Identify current dimensions that can be reused.

•

Identify elements that must be added to existing dimensions or fact tables.

•

Design new physical objects based on a logical model.




Full


Design indexing, partitioning, and key structures for the relational database.

•

Design an indexing strategy.

•

Design a surrogate key strategy.

•

Identify appropriate business keys.

•

Design a partitioning strategy.




Full


Designing SSIS Solutions    

Design the extract process.

•

Identify design constraints.

•

Identify changed data in the source system.

•

Decide the strategy for decoding textual values.



Full



Design the load process.

•

Decide whether to implement fast load.

•

Design appropriate destination components to handle new and updated records during incremental loads.



Full



Design the transformation process.

•

Identify appropriate transformations and transformation options.

•

Design data flow.



Full



Design the control flow process.

•

Identify appropriate control flow items.

•

Design the control flow sequence.



Basic



Design logging and event handling.

•

Identify appropriate uses and placement of event handlers.

•

Identify appropriate uses and placement of checkpoints.

•

Identify appropriate uses of logging.

•

Identify appropriate uses of data flow error handling.


Partial




Partial

Designing SSRS Solutions    

Acquire report data.

•

Select appropriate uses of shared data sources.

•

Select appropriate uses of stored procedures or user-defined functions.





Full

Design security for SSRS.

•

Define appropriate security roles.

•

Specify folder security.

•

Specify field-level security.


Partial




Full

Choose an SSRS report creation strategy.

•

Select appropriate uses of Report Designer.

•

Select appropriate uses of Report Definition Language (RDL).

•

Select appropriate uses of Report Builder.





Full

Select appropriate report components.

•

Decide appropriate uses of datasets.

•

Decide appropriate uses of subreports.

•

Decide the appropriate placement of extensive business logic.





Basic

Design report execution modes to support business requirements.

•

Identify appropriate uses of report snapshots.

•

Identify appropriate uses of on-demand reports.

•

Identify appropriate uses of on-demand-from-cache reports.


Partial




Full

Design a report subscription strategy.

•

Identify appropriate uses of standard subscriptions.

•

Identify appropriate uses of data-driven subscriptions.

•

Identify appropriate report-delivery methods for subscriptions.





Full

Design a strategy to maintain SSRS objects on the report server.

•

Identify appropriate uses of the Reporting Services Web Service library.

•

Identify appropriate uses of the Reporting Services Configuration tool.





Full

Designing SSAS Solutions    

Design the data source view (DSV).

•

Select appropriate uses of named queries.

•

Select appropriate uses of named calculations.

•

Select appropriate uses of denormalization strategies.





Modify the SSAS dimensions.

•

Identify appropriate uses of attribute relationships.

•

Identify appropriate uses of column binding to support a user-defined reporting hierarchy.

•

Select a design for implementing a ragged hierarchy.

•

Select an appropriate strategy to implement member properties.




Partial


Modify the SSAS cube.

•

Identify appropriate uses of calculated members.

•

Identify appropriate uses of actions.

•

Identify appropriate uses of key performance indicators (KPIs).

•

Identify appropriate uses of perspectives.

•

Identify appropriate uses of translations.

•

Identify appropriate uses of drillthrough.




Partial


Design relationships between dimensions and measure groups.

•

Identify a relationship type.

•

Identify appropriate uses of role-playing dimensions.

•

Choose an appropriate strategy to handle unknown dimension members.




Partial


Design security for SSAS.

•

Define appropriate security roles.

•

Design dimension security.

•

Design cell security by using Multidimensional Expressions (MDX).




Partial


Maintaining and Optimizing SSAS Solutions    

Select an appropriate aggregation strategy.





Maintain SSAS solutions by using either online or offline methods.





Ensure that the SSAS solutions are refreshed with the most recent data.

•

Design a partitioning strategy for optimal data availability.

•

Decide whether proactive caching is an appropriate solution.





Select appropriate processing methods for SSAS objects.





Select appropriate storage settings.

•

Design partition storage settings.

•

Select a dimension storage mode.




Full


Designing Data Mining Solutions    

Identify applicability of data mining to a business need.



Basic



Design the data structure to support mining.



Basic



Design a data mining model.

•

Identify appropriate algorithms to meet requirements.

•

Classify data as input, key, predict, and ignore.



Basic



Select data mining delivery methods.

•

Select appropriate uses of SSRS Data Mining Extensions (DMX) queries.

•

Select appropriate uses of ActiveX Data Objects (Multidimensional) (ADOMD).

•

Select appropriate uses of SSIS Data Mining Query tasks.

•

Select appropriate uses of data mining viewer controls for Microsoft .NET Framework-based applications.



Basic



Select a processing method for data mining objects.

•

Select appropriate uses of full processing.

•

Select appropriate uses of structure processing.

•

Select appropriate uses of default processing.



Basic



Note This preparation guide is subject to change at any time without prior notice and at the sole discretion of Microsoft. Microsoft exams might include adaptive testing technology and simulation items. Microsoft does not identify the format in which exams are presented. Please use this preparation guide to prepare for the exam, regardless of its format.

•

Learn more and download samples


Top of pageTop of page