Тест №70-019: Designing and Implementing Data Warehouses With Microsoft® SQL Server™ 7.0
Продолжительность:
Языки теста: English
Online тест:
Кол-во вопросов:
Мин.проходной балл:
Темы: 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.
|
О нас
