Oracle

ORACLE 10g

DBMS:

• Concepts of DBMS

· Introduction

· Importance and Need

· Database Models and Normalization

· Models

· Hierarchical

· Network

· Relational

· Object

· Normalization Techniques

Introduction to RDBMS:

• Concepts of RDBMS
• Introduction
• Comparison with DBMS
• CODD rules
• Normalization Revisited

· Concepts of ORDBMS & OODBMS

· Introduction to ORDBMS

· Introduction to OODBMS

· Comparison and analysis

Structured Query Language

• Introduction to SQL
• Predefined Data Types
• Introduction to database Objects
• Table, view

Commands in SQL

• Data definition language (DDL)
• Data manipulation language (DML)
• Data control language (DCL)
• Transaction control language (TCL)
• Database security and privileges (DCL)

Working with DDL, DML,TCL Commands

· Operators

· Conditional

· Logical

· Comparison

· SET

· Functions

· Date

· Number

· Character

· String

· Group

· Group by ,Having clause

· Transformation function:- DECODE, TRANSLATE

· NVL ,NVL2(9i)

· Aggregate Functions

· Integrity Constraint

· Different Types of Integrity Constraint

· Use of Integrity constraint in table

· Joins

· Usage of joins

· Different Types of joins

§ Equip join, Cartesian join

§ Self join, outer join

· Sub queries

· Need of sub queries

· Nested sub queries

· Co-related sub quires

· Flashback queries (9i)

· View

· Importance of views

· Creating, manipulating views

· Materialized views(9i)

· Working with database objects

o Sequences and synonyms

o Index, cluster, creating cluster table

Using external tables(9i)

o System privileges

o Table privileges

· Locks and SQL plus editor commands

PL/SQL

• Introduction to PL/SQL
• Advantages of PL/SQL
• Data types in PL/SQL
• Variable and constants
• Assignments and constants
• Referencing NON-PL/SQL Variable
• Using built in functions
• Conditional and Iterative statements
• SQL in PL/SQL
• Composite data types
• Cursors
• Exception:-predefined, user-defined
• Records and PL/SQL table types

Advanced PL/SQL

• Introduction to Triggers
• Types of triggers:-row levels, statements level
• Before and after, instead of triggers
• Procedures, functions in PL/SQL
• Argument modes
• User defined packages
• Dropping triggers, procedures, functions
• Reference cursors
• Usage of ref cursors in procedures, functions, packages

Datastage

DATASTAGE PARALLEL JOBS

DATA WAREHOUSING CONCEPTS

History of Data Warehousing

· Need of a Datawarehousing

· OLTP Systems

· Definition of a Data Warehouse

· Difference b/w OLTP & DW

· Data warehouse Architecture

· Data Warehouse vs. Data Mart

· Data warehousing Approaches

· Design of a Datawarehousing

· Dimension table & types

· Slowly Changing Dimensions-Type 1,

Type 2 and Type 3

· Confirmed Dimension

· Junk Dimension

· Degenerate Dimension

· Dirty Dimension

· Fact Table & types

· Measure and Fact

· Types of Facts

· Data warehousing Schemas( Star, Snowflake)

· Data Acquisition process

· Project Life Cycle

DATASTAGE

· Introduction to Data stage

· Parallel Processing

· Pipeline Parallelism

· Partition Parallelism

· Client and Server Components

Data Stage Designer

· Introduction to Data Stage Designer

· Repository

· Palette

· General Stages

· Sequential File

· Data Set

· File Set

· Lookup File Set

· Complex Flat File

· DB2 /UDB Enterprise

· Oracle Enterprise

· Teradata Enterprise

· Dynamic RDBMS

· Copy and Modify

· Transformer

· Aggregator

· Join, Merge, Lookup

· Sort

· Funnel

· Remove Duplicates

· Filter

· Change Capture

· Change Apply

· Surrogate Key

· Head, Tail, Sample and Peek

· Row Generator and Column Generator

· Pivot

· ODBC Stage

· Filter & switch

· Funnel

· Difference

· Compare

Data Stage Director

· Introduction to Director

· Validating Jobs

· Running, Stopping, Resetting Jobs

· Restarting Job Sequences

· Scheduling Jobs and Sequences

· Debugging

· Cleanup Job Resources

· Clearing Job Status File

· Saving Job Log into a file

· Creating, Running, Scheduling a Job Batch

· Monitoring Jobs

· View Status,Schedule,Log

Data Stage Manager

· Introduction to Manager

· Importing Data stage components

· Exporting Data stage components

· Message Handling

· Routines

· Configuration File

· Adding, Deleting and Moving Projects

· Limiting Access to Projects

· Data Set Management

Data Stage Administrator

· Introduction to Administrator

· Adding & Deleting project

· Cleaning up Project files

· Changing & Upgrading licenses

· Auto purging

· Permissions to Users

· NLS Configuration

· RCP

· Enable Remote execution of Parallel Jobs

· Add Environmental Variables

Job Sequencer

· Introduction to Sequencer

· Arrange job activities in Sequencer

· Triggers in sequencer

· Job Activity

· Notification Activity

· Wait- for- file Activity

· Routine Activity

· Add Check points

· Terminator Activity

· User Variable Activity

· SCD Implementations

· Performance Tuning in parallel Jobs

· Data Stage Server Jobs

OUR Additional Features

v Interview Questions

v Real Time Scenarios

v Resume Preparation

v Real Time Project

MS-BI

Microsoft – BI

Planning for ETL:

· Source Identify Data Sources and Destinations

· Evaluating Data

· Identify Staging Requirements

Lab: Planning an ETL Solutions

Planning SSIS Solutions:

· Planning Packages

· Planning Package development

· Designing Package Control Flow

Lab: Implementing SSIS Packages

Designing Data Flow:

· Understanding Data Flow

· Designing Data Flow Operations

· Handling Data Changes

Lab: Implementing Data Flow

Logging Error Handling and Reliability:

· Logging ETL Operations

· Handling Errors in SSIS

· Implementing Reliable ETL Process with SSIS

Lab: Implementing Reliable Packages

Optimizing an SSIS Solution:

· Monitoring SSIS Performance

· Optimizing SSIS Packages

· Scaling out SSIS Packages

Lab: Optimizing Packages

Deploying and Operating an SSIS Solution

· Deploying SSIS Packages

· Operating an SSIS Solution

Lab: Optimizing Packages

Contents:

Introduction to Microsoft SQL Server 2005 Analysis Services

· Overview of Data Analysis Solutions.

· Over of SQL Server 2005 Analysis Services.

· Installing SQL Server 2005 Analysis Services.

Creating Multidimensional Analysis Solution

· Developing Analysis Service Solutions.

· Data Sources and Data Sources Views.

· Creating a Cube.

Lab: Creating a multidimensional Analysis Solution

Working with Dimensions

· Configuring Dimensions.

· Defining Hierarchies.

· Sorting and Grouping Attributes.

Lab: Defining Dimensions

Working with Measure and Measure Groups

· Working with Measure.

· Working with Measure Groups.

Lab: Configuring Measure and Measure Groups

· MDX Fundamentals.

· Adding Calculations to a Cube.

Lab: Querying a Cube

Customizing Cube Functionality

· Implementing Key Performance Indicators.

· Implementing Actions.

· Implementing Perspectives.

· Implementing Translations.

Lab: Customizing a Cube

Deploying and Securing an Analysis Services Database

· Deploying an Analysis Services Database.

· Securing an Analysis Services Database.

Lab: Deploying and Securing an Analysis Services Database

Maintaining a Multidimensional Solution

· Configuring Processing Settings.

· Logging, Monitoring and Optimizing an Analysis Services Solution.

· Backing Up and Restoring an Analysis Service Database.

Lab: Maintaining an Analysis Services Database

Introduction to Data Mining

· Overview of Data Mining.

· Creating a Data Mining Solution.

· Validating Data Mining Models

Lab: Implementing Data Mining

SSRS – Objectives:

1. Describe SQL Server Reporting Services and its Components.

2. Create a Reporting Services report.

3. Enhance a Reporting Service report.

4. Create and manipulate data sets.

5. Use report models to implement reporting for business users.

6. Configure report publishing and execution settings.

7. Implementing subscription for reports.

8. Implement custom Reporting Services applications.