Test 716: DB2 Data Warehouse Edition V9.1

Section 1 - ARCHITECTING WAREHOUSE BASED ANALYTICS (15%)

1. Explain how warehouse vs. front end based analytics impacts Business Intelligence Analytics architecture
2. Differentiate multidimensional database vs. relational database warehouse, and MOLAP vs. ROLAP
3. Explain how metadata effects analytical queries
4. Explain metadata exchange management
5. Explain scorecards, key performance indicators, dashboards, charts, grids and other visualization methods
6. Given customer requirement criteria, select appropriate front-end features based on criteria such as presentation, level of interactivity, web-versus-FAT client, static versus dynamic, and user skill level
7. Translate warehouse based analytics into proper schemas, aggregations, and SQL
8. Explain front end tool based analytics, and be able to derive these from warehouse based analytics
9. Differentiate IBM provided analytical front ends with partner provided front end analytics
10. Determine when to use the DB2 Design Advisor vs the CV Advisor
11. Explain how Query Patroller fits into warehouse based analytics
12. Distinguish between physical and logical data models
13. Describe the use of projects within Design Studio

Section 2 - INSTALLATION AND CONFIGURATION (5%)

1. Describe the architecture of DWE in terms of its components
2. Describe the architecture of DWE in terms of the three physical nodes and where they are installed
3. Plan how to install DWE in an environment
4. Describe the required hardware and software
5. Load the installation CD images
6. Run the installation program
7. Identify and perform the configuration tasks using the DWE configuration tool

Section 3 - DB2 PHYSICAL DATA MODELING (15%)

1. Create a Data Design Project in the Project Explorer as a container for physical data modeling
2. Reverse engineer an existing DB2 schema (as database or script) or subset into a physical data model
3. Navigate the model in the graphical editor
4. Design or modify a physcial data model describing the data warehouse (including constraints)
5. Run the analyze function to validate the physcial model
6. Run the compare function to identify changes in the new model
7. Perform impact analysis to indentify all model or database dependencies
8. Generate and edit the DDL from the model, and save as script for later deployment
9. Execute the generated DDL
10. Define the connection to a JDBC database local or remote
11. Filter the Database Explorer list of objects based on used-supplied criteria
12. View the contents of database objects
13. Run the compare function to compare database objects
14. Define a SQL query and execute against Explorer database objects

Section 4 - CUBE AND MULTIDIMENSIONAL MODELING (15%)

1. Determine what dimensions and facts are needed for the model(s) being created
2. Identify candidate fact and dimension tables in the warehouse for the models
3. Create cube model(s) and Cubes
1. Define levels and hierarchies
2. Define and create measures using SQL Expression Builder
3. Define and create a dimension object


4. Create MQT recommendations using wizard
5. Troubleshoot ineffective MQT's
6. Import and export operations
7. Validate model(s)

Section 5 - MODELING PREDICTIVE ANALYTICS (15%)

1. Import a mining model as PMML from another tool, and verify the import was successful
2. Create a mining project in the Project Explorer
3. Formulate a data mining task from a business problem
4. Define a preprocessing function to prepare data for the modeling run
5. Edit the properties of a mining operator to "train" the model
6. Apply a visualizer operator to a mining flow
7. Run the mining flow against data in the warehouse to create a populated model
8. Create a scoring function based on an existing model
9. View the results of a modeling or scoring run using the visualizer
10. Generate SQL for the mining flow to embed within Alphablox or other application
11. Create an automated refresh of the model to maintain model "freshness"

Section 6 - SQL WAREHOUSING TOOL (15%)

1. Describe the use cases for the SQL Warehousing Tool
2. Using the DWE Design Studio, create, setup and navigate a new Datawarehouse Project
3. Interface with the Data Design Project to create/import/maintain physical data models and manage corresponding database structures
4. Understand and describe the concepts of dataflows, subflows and control flows
5. Build a dataflows and subflows by adding, connecting and defining properties of SQL Warehousing Dataflow Operators
6. Understand data stations: why, when and how to use a data station in a dataflow
7. Understand the use of operator variables and how/when the variables are resolved
8. Build control flows by adding, connecting and defining properties of SQL Warehouse Control Flow Operators and dataflows
9. Prepare a Datawarehouse Project application for deployment to test and/or production environments
10. Using the DWE Administration Console, deploy a Datawarehouse Project application to a test and/or production environment

Section 7 - RUN TIME ADMINISTRATION AND MONITORING OF THE WAREHOUSE (20%)

1. Navigate the DWE Administration Console
1. Describe the major components of the DWE Administration Console and their relationships with one another
2. Create, Manipulate, and remove database profiles
3. Enable or Disable a database for Cube Views and/or Data Mining and check enablement status
4. Using the SQW (DWE SQL Warehousing), manage resources, processes, and activities
5. Perform the major functions in the DWE OLAP items


2. Administer Query Patroller
1. Describe the functions of the Query Patroller server, system administrator, client tools
2. Configure System settings
3. Setup and perform Query Workload Management
4. Setup and perform Historical Analysis
5. Maintain and Tune Query Patroller