Preparation Guide for Exam 70-019

Designing and Implementing Data Warehouses With Microsoft SQL Server 7.0

This exam is scheduled to be discontinued on June 30, 2004.

On This Page

	Exam News
	Credit Toward Certification
	Preparation Tools and Resources
	Skills Being Measured

Exam News

Exam 70-019 is scheduled to be discontinued and will not be available after June 30, 2004. For more information about discontinued exams, please see the Discontinuation of Exams page.

Exam 70-019 is available December 2, 1999.

Top of page

Credit Toward Certification

When you pass the Designing and Implementing Data Warehouses with Microsoft SQL Server™ 7.0 exam, you achieve Microsoft Certified Professional status. You also earn credit toward the following certifications:

Top of page

Preparation Tools and Resources

We make a wealth of preparation tools and resources available to you, including courses, books, practice tests, and Microsoft Web sites. When you are ready to prepare for this exam, here's where you should start.

Instructor-led Course for This Exam

Microsoft Press Self-Paced Training

Microsoft Certified Practice Tests

Microsoft Online Resources

Top of page

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.

Key to the matrix:

KEY:

= The course provides a general introductory overview of this task. You will need to supplement the course with additional work.     = The course includes some material to prepare you for this task. You will need to supplement the course with additional work.     = The course includes material to prepare you for this task.

Skills measured by exam 70-019	Course 1502
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.

Note: This preparation guide is subject to change at any time without prior notice and at Microsoft's sole discretion. Microsoft exams might include adaptive testing technology and simulation items. Microsoft does not identify the format in which exams are presented. Please use the exam objectives listed in this preparation guide to prepare for the exam, regardless of its format. Learn more, and download samples, on the Testing Innovations page.

Top of page