MCTS Self-Paced Training Kit (Exam 70-448)

### MCTS Self-Paced Training Kit (Exam 70-448): Microsoft SQL Server 2008 - Business Intelligence Development and Maintenance #### **Overview** The MCTS Self-Paced Training Kit (Exam 70-448) is designed to prepare IT professionals for the Microsoft Certified Technology Specialist (MCTS) certification exam related to developing and maintaining business intelligence solutions using Microsoft SQL Server 2008. This comprehensive training kit covers essential topics in Business Intelligence (BI) development and maintenance, including implementing SSIS (SQL Server Integration Services), SSAS (SQL Server Analysis Services), and SSRS (SQL Server Reporting Services) solutions. #### **Key Knowledge Points** ##### **Implementing an SSIS Solution** **Objective:** To implement an SSIS solution, candidates must understand how to control flow, data flow, dynamic package behavior, and package logic. - **Control Flow Implementation (Chapter 1, Lesson 1; Chapter 2, Lesson 1):** Candidates should be able to design and implement control flow in SSIS packages, including managing tasks and their execution sequence. - **Data Flow Implementation (Chapter 1, Lesson 1; Chapter 2, Lessons 1-3):** Understanding how to configure and manage data flows between different sources and destinations is crucial. This includes working with transformations such as lookup, merge join, and derived column. - **Dynamic Package Behavior Using Property Expressions (Chapter 3, Lesson 1):** Implementing property expressions allows for dynamic changes within an SSIS package based on runtime values. - **Package Logic Using Variables (Chapter 1, Lesson 2):** Variables can be used to enhance the logic of an SSIS package, enabling more complex scenarios such as conditional executions. - **Package Configurations (Chapter 3, Lesson 1):** Learning how to configure packages to use different settings for different environments (e.g., development, test, production). - **Auditing, Logging, and Event Handling (Chapter 2, Lessons 2-3):** Implementing logging and event handling mechanisms to track package execution and troubleshoot issues. - **Extending SSIS Packages with .NET Code (Chapter 1, Lesson 2):** Customizing SSIS packages by adding custom components written in .NET. ##### **Configuring, Deploying, and Maintaining SSIS** - **Installing and Maintaining SSIS Components (Chapter 1, Lesson 1; Chapter 2, Lesson 1; Chapter 4, Lesson 1):** Candidates should know how to install and maintain the SSIS service, catalog, and other components necessary for deployment. - **Deploying an SSIS Solution (Chapter 3, Lesson 2):** Learning how to deploy SSIS packages and solutions into different environments, including testing and production. - **Managing SSIS Package Execution (Chapter 4, Lesson 2):** Controlling the execution of SSIS packages through scheduling, parameters, and other mechanisms. - **Configuring SSIS Security Settings (Chapter 4, Lesson 1):** Ensuring that SSIS solutions adhere to security policies and best practices. - **Identifying and Resolving Deployment Issues (Chapter 1-3, Lessons 1-2):** Troubleshooting common deployment issues and resolving them effectively. ##### **Implementing an SSAS Solution** - **Implementing Dimensions in a Cube (Chapter 5, Lessons 2-3; Chapter 6, Lesson 1):** Candidates should understand how to create and manage dimensions within an SSAS cube, including configuring relationships and hierarchies. - **Implementing Measures in a Cube (Chapter 5, Lessons 2-4):** Learning how to define and manage measures, which represent the numerical data stored in a cube. - **Implementing a Data Source View (Chapter 5, Lesson 1):** Creating a logical view of the underlying data sources that an SSAS cube uses. - **Configuring Dimension Usage in a Cube (Chapter 6, Lesson 1):** Configuring how dimensions are used within a cube, including setting up relationships and aggregations. - **Implementing Custom Logic in a Cube Using MDX (Chapter 6, Lessons 2-3):** Writing MDX queries and scripts to customize cube behavior and calculations. - **Implementing Data Mining (Chapter 9, Lessons 1-4):** Learning how to integrate data mining models into SSAS cubes to perform predictive analytics. - **Implementing Storage Design in a Cube (Chapter 7, Lesson 1):** Designing the storage architecture for an SSAS cube, including choosing between different storage modes (e.g., multidimensional, tabular). ##### **Configuring, Deploying, and Maintaining SSAS** - **Configuring Permissions and Roles in SSAS (Chapter 8, Lesson 1):** Setting up security roles and permissions to control access to SSAS databases and objects. - **Deploying SSAS Databases and Objects (Chapter 7, Lesson 2):** Deploying SSAS databases and their associated objects into production environments. - **Installing and Maintaining an SSAS Instance (Chapter 8, Lessons 2-3):** Installing and configuring the SSAS service, managing instances, and ensuring high availability. - **Diagnosing and Resolving Performance Issues (Chapter 8, Lesson 3):** Identifying performance bottlenecks and implementing optimizations to improve query response times. - **Implementing Processing Options (Chapter 7, Lesson 3):** Understanding different processing options available in SSAS and how they affect data loading and refreshing. ##### **Implementing an SSRS Solution** - **Implementing Report Data Sources and Datasets (Chapter 10, Lesson 1; Chapter 12, Lesson 3):** Candidates should learn how to create and manage report data sources and datasets, including connecting to various data sources. - **Implementing a Report Layout (Chapter 10, Lessons 1-3; Chapter 11, Lesson 2):** Designing the layout and appearance of SSRS reports, including formatting, grouping, and sorting. - **Extending an SSRS Solution with Code (Chapter 11, Lesson 4):** Customizing SSRS reports by adding code to enhance functionality or provide dynamic content. - **Creating an SSRS Report (Chapter 10, Lessons 1-3; Chapter 11, Lesson 2):** Implementing complete SSRS reports from data retrieval to presentation, including using charts, tables, and other visual elements. This comprehensive guide provides a structured approach to preparing for the MCTS Exam 70-448, covering all the essential knowledge points required for developing and maintaining BI solutions using Microsoft SQL Server 2008.