Designing and Implementing Data Warehouses With Microsoft SQL Server 7.0 (70-019)

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:

• Elective credit toward Microsoft Certified Database Administrator on Microsoft SQL Server 2000 certification .
• Elective credit toward Microsoft Certified Systems Engineer on Microsoft Windows 2000 certification.
• Elective credit toward Microsoft Certified Systems Engineer on Microsoft Windows NT 4.0. certification.
• Elective credit toward Microsoft Certified Solution Developer certification.

Exam Objectives and Sub-Objectives:

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.

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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 an dreferential 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.

7. 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.

Exam Detail:

Number of Question:	45
Type of Questions:	Hot Area, Active Screen, Drag and Drop, Build List and Reorder, Create a Tree, Windows 2000 Simulation
Passing Score:	700
Time Duration:	175 min