Boot Camp Pricing
Windows 2000 MCSE | NT 4.0 MCSE | MCSE Classes | MCSE FAQs | MCSE Exam Info

Exam 70-019

Designing and Implementing Data Warehouses With Microsoft SQL Server 7.0

Exam 70-019 has been available since December 2, 1999.

This document includes information on:

• Microsoft Certification Path
• Skills Being Measured

Microsoft Certification Path

When you pass this exam, you achieve MCP (Microsoft Certified Professional) status. You also earn elective credit toward your MCSE (Microsoft Certified Systems Engineer) certification, elective credit toward the MCDBA (Microsoft Certified Database Administrator) certification, and elective credit toward the MCSD (Microsoft Certified Solution Developer) certification.

Skills Being Measured

This certification exam measures your ability to design and implement data warehouse solutions by using Microsoft SQL Server version 7.0 with OLAP Services and Data Transformation Services (DTS) installed. Before taking the exam, you should be proficient in the job skills listed below.

Analyzing Business Requirements

Analyze the scope of a project.

• Identify the major subject areas that will be incorporated into the data warehouse.

Analyze the extent of a business requirement.

Analyze security requirements.

Analyze performance and scalability requirements.

Analyze maintainability requirements.

Analyze human factors requirements, such as target audience, localization, accessibility, roaming users, Help, and special needs.

Defining the Technical Architecture for a Solution

Identify which technologies are appropriate for implementation of a given business solution. Technologies include design tools, data transformation tools, storage tools, presentation access tools, management tools, and scheduling tools.

Choose a data storage architecture.

Developing the Logical Design

Identify the sources of data from the operational databases.

Identify the encoding structure and key structure for integrating all data.

Identify the filtering requirements for operational data.

Assess whether a data mart schema should be integrated within the enterprise data warehouse schema.

Assess the level of detail required for data.

Deriving the Physical Design

Assess how a given logical design impacts performance, maintainability, extensibility, scalability, availability, and security.

Assess whether data should be queried from a relational database or a multidimensional database.

Choose a schema design for a relational database. Design options include normalized, star, or snowflake.

Group data into fact tables and dimension tables by applying denormalization rules.

Creating Data Services

Use Microsoft ActiveX Data Objects (ADO), ActiveX Data Objects Multidimensional (ADO MD), multidimensional expressions (MDX), or Microsoft English Query to access or manipulate a data source.

Write SQL statements that retrieve and summarize data. SQL statements include SELECT, ROLLUP, CUBE, and HAVING.

Replicate data among data marts.

Implementing a Physical Data Warehouse and Implementing OLAP Services

Implement a data storage architecture by creating and managing files and filegroups.

Use visual database tools to create databases and database tables that enforce data integrity and referential integrity.

Populate the data warehouse with data from an external data source by using Data Transformation Services (DTS). External data sources include other SQL Server databases, comma-separated files, delimited files, and OLE DB for ODBC.

• Track data lineage.
• Store DTS packages in the repository.

Choose an indexing strategy to optimize performance for relational decision support.

Create, maintain, and optimize indexes.

Design the multidimensional OLAP model.

• Create the dimension hierarchy.
• Create measures.
• Assign member properties.

Create and maintain OLAP aggregations.

• Choose the data storage mechanism, specifically MOLAP, ROLAP, or HOLAP.
• Build the aggregations.
• Partition data for scalability.
• Perform incremental updates of cubes.
• Merge incremental updates with the main partition.
• Monitor and optimize aggregations based on usage.

Implement security for databases and cubes.

Configure SQL Server options for optimal performance.

Maintaining a Database and VLDB

Monitor and optimize the amount of space in the database.

Perform backup procedures, restore procedures, and roll-off procedures on the data warehouse.

• Develop archiving procedures.
• Develop methods for refreshing data.

Perform disaster recovery procedures on the database.

Maintain database indexing.

Verify database consistency.

Monitor and optimize query performance.

Automate maintenance tasks by using alerts and agents.

• Schedule DTS events.
• Schedule backup events.
• Schedule replication events.

[ Back to top ]

Home | Technical Schedule | Application Classes | Class Outlines | MCSE, MCDBA, MCSD Training | Microsoft .NET Programming | Cisco Classes | Linux, Unix, AIX | CompTIA Certification | Webmaster Training | Pricing | Locations | Financing | E-mail Us