Microsoft Learning:

Exam 70-455:

Upgrade: Transition Your MCITP SQL Server 2005 BI Developer to MCITP SQL Server 2008 BI Developer

Published:November 10, 2008
Language(s):English, French, German, Japanese, Spanish, Chinese (Simplified)
Audience(s):Developers
Technology:Microsoft SQL Server 2008
Type:Proctored Exam
Special Offer:
Find out how you can save 25 percent off the price of this certification exam, and help advance your career.
About this ExamThis is an upgrade exam designed to assess candidates’ knowledge of implementing and maintaining solutions by using the Microsoft SQL Server 2008 Business Intelligence tool suite.
Audience Profile
Candidates for this exam are responsible for creating business intelligence (BI) solutions by using SQL Server 2008 for their organizations.  Candidates typically work with the full suite of BI technologies in SQL Server 2008, including Reporting Services, Analysis Services, and Integration Services.
This exam is intended for professionals who develop solutions by using the SQL Server 2008 BI tool suite.  The qualified candidate for this exam typically has:
  • one to two years of experience working with the Microsoft .NET Framework
  • two to three years of experience using SQL Server 2005 BI components
  • six months to one year of experience using SQL Server 2008
  • experience using the SQL Server 2008 Business Intelligence Development Studio (BIDS)
  • experience using Microsoft SQL Server Management Studio (SSMS)
  • knowledge of relational and multi-dimensional database schemas
  • experience creating and deploying a cube in SQL Server Analysis Services (SSAS)
  • experience creating and deploying a data mining model in SSAS
  • experience writing relational and multi-dimensional database queries
  • experience creating and deploying SQL Server Reporting Services (SSRS) reports
  • experience authoring and deploying SQL Server Integration Services (SSIS) packages
  • experience debugging, monitoring, and troubleshooting BI solutions
Credit Toward CertificationWhen you pass Exam 70-455: Upgrade: Transition Your MCITP SQL Server 2005 BI Developer to MCITP SQL Server 2008 BI Developer, you complete the requirements for the following certification(s):MCITP: SQL Server 2008 BI Developer
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.
Skills Being MeasuredThis exam measures your ability to accomplish the technical tasks listed below.The percentages indicate the relative weight of each major topic area on the exam.
 Implementing an SSIS Solution
  • Implement control flow.
    This objective may include but is not limited to: checkpoints; debug control flow; transactions; implement the appropriate control flow task to solve a problem; data profiling and quality
  • Implement data flow.
    This objective may include but is not limited to: debug data flow; implement the appropriate data flow components
  • Implement package configurations
Configuring, Deploying, and Maintaining SSIS
  • Manage SSIS package execution.
    This objective may include but is not limited to: schedule package execution by using SQL Server Agent; execute packages by using DTEXEC; execute packages by using SQL Server Management Studio; execute packages by using the SSIS .NET API
  • Configure SSIS security settings.
    This objective may include but is not limited to: MSDB database roles; package protection levels
  • Identify and resolve issues related to SSIS solution deployment.
    This objective may include but is not limited to: validate deployed packages; deploy packages and dependencies between servers
Implementing an SSAS Solution
  • Implement dimensions in a cube.
    This objective may include but is not limited to: translations; attribute relations; hierarchies
  • Implement measures in a cube.
    This objective may include but is not limited to: measure groups
  • Implement custom logic in a cube by using MDX.
     This objective may include but is not limited to:  actions; key performance indicators (KPI); calculated members; calculations
Configuring, Deploying, and Maintaining SSAS
  • Configure permissions and roles in SSAS.
    This objective may include but is not limited to: server roles; SSAS database roles; cube roles; enable client application access; implement custom access to data
  • Deploy SSAS databases and objects.
    This objective may include but is not limited to: Deployment Wizard; BIDS; SSMS; SSIS Analysis Services Execute DDL task
  • Diagnose and resolve performance issues.
    This objective may include but is not limited to: use SQL Profiler; performance monitor counters; DMVs; Usage Based Optimization Wizard
Implementing an SSRS Solution
  • Implement a report layout.
    This objective may include but is not limited to: apply conditional formatting; page configuration; headers and footers
  • Implement report parameters.
    This objective may include but is not limited to: databound parameters; multi-value parameters
  • Implement interactivity in a report.
    This objective may include but is not limited to: drilldown; drillthrough; interactive sorting
Configuring, Deploying, and Maintaining SSRS
  • Configure report execution and delivery.
    This objective may include but is not limited to: subscriptions; report caching; schedules; snapshot history
  • Install and configure SSRS instances.
     This objective may include but is not limited to: deploy an SSRS Web farm
  • Configure authentication and authorization for a reporting solution.
    This objective may include but is not limited to: configure server-level and item-level role-based security; configure Windows authentication and custom authentication
Designing and Managing Reports
  • Design report architecture.

    This objective may include but is not limited to: linked reports, drill-down reports, drill-through reports, migration strategies, access report services API, sub reports, Code-Behind strategies
  • Design a data acquisition strategy.

    This objective may include but is not limited to: data query parameters,  creating appropriate SQL queries for an application (MDX queries), managing data rights and security, extracting data from Analysis Services, balancing query-based processing vs. filter-based processing, managing data sets through the use of stored procedures
  • Define a report parameter strategy.

    This objective may include but is not limited to: report cache parameters, expressions, Code-Behind
Designing Data Mining Models
  • Design a mining model and structure.

    This objective may include but is not limited to: assigning a data source, specifying filter properties, reconciling heterogeneous data sources, selecting a refresh strategy (partial or full)
  • Select data mining algorithms.

    This objective may include but is not limited to: sequence, clustering, time series, neural net, association, classification, decision tree
Administering a BI Solution
  • Maintain server health.

    This objective may include but is not limited to: Resource Governor, performance counters, Analysis Server dynamic management views (DMV)
  • Design a backup strategy.

    This objective may include but is not limited to: offsite backup, setting up SSAS backup,  Reporting Services backup (databases and keys)
  • Plan and manage upgrades.

    This objective may include but is not limited to: data migration, code migration, data security, parallel systems
Designing the BI Architecture
  • Design integration between data visualization components.

    This objective may include but is not limited to: MOSS, SSRS, SSAS, designing Excel PivotTable reports, application architecture (Microsoft ASP.NET,  Web Service, MicrosoftReportViewer Windows Forms control), Windows Mobile, other data visualization tools (Microsoft ProClarity, Microsoft PerformancePoint)
  • Design the data warehouse.

    This objective may include but is not limited to: datamarts, integrating legacy data, creating a dimensional model (star schema, snowflake, matrix bridge, conformed dimensions), utilizing  upsert in design (TSQL-merge)
  • Design a security strategy.

    This objective may include but is not limited to: cube, project,  reports (security roles, folders, field-level security), encryption keys, SQL services accounts management, encrypted data transmission, SSIS vs. SQL server authentication issues (derail Trojan attacks)
Designing and Deploying SSIS Packages
  • Design control flow.

    This objective may include but is not limited to: tasks and precedence constraints, fuzzy logic control, maintenance tasks, accessing remote data (Web services, FTP), using appropriate variables, data profile
  • Design data flow.

    This objective may include but is not limited to: data transformation, blocking transformations (lookup task), extractions (design constraints), designing the load process, creating a package to load facts, creating an SSIS package to load dimension (SCD2), using capture CDC to locate changed data for SSIS
  • Plan the deployment of SSIS packages.

    This objective may include but is not limited to: design the structure of packages, troubleshoot and design form problems with package, create a package that reports any errors to a log
Designing an Analysis Services Database
  • Design cube architecture.

    This objective may include but is not limited to: creating and populating a cube, creating KPIs, actions, calculated members, drillthrough, designing SSAS aggregation, creating and manipulating dimensions (Ragged hierarchy, Flexible, Rigid, Semi-additive, Periodicity, Fact Relationships), selecting a processing mode
  • Design and create business-driven Multidimensional Expressions (MDX) calculations.

    This objective may include but is not limited to: calculated measures, creating a named set in MDX
  • Analyze cube performance.

    This objective may include but is not limited to: optimizing SSAS aggregation, query cube design
Preparation Tools and ResourcesTo 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" tab.
Classroom Training There is no classroom training currently available.
Microsoft E-Learning There is no Microsoft E-Learning training currently available.
Microsoft Press Books There are no Microsoft Press books currently available.
Practice Tests There are no practice tests currently available.
Have Questions? For advice about training and certification, connect with peers: For questions about a specific certification, chat with a Microsoft Certified Professional (MCP): To find out about recommended blogs, Web sites, and upcoming Live Meetings on popular topics, visit our community site:
Exam index

How To Get It

Schedule your exam through the following exam provider:

Related Services