ETL Tool Training

1. Introduction to ETL and its importance for data warehousing
2. Setting up and Installation of various ETL Tools
3. Optimizing the ETL tools for the best results based on specific job requirements
4. Learning all about OLAP, ETL and OLTP systems
5. Mastering data modeling, dimensional modeling
6. Working with star and snowflake schemas
7. Dimensions, measures and fact tables in data warehousing
8. Data types, performance tuning in ETL
9. SCD types and various methods of handling SCD data
10. Various types of data transformation techniques
11. Source qualifier transformation and mappings.

• ETL & SQL Developers and Business Intelligence Professionals
• Database, Data Warehousing, Mainframe Professionals, Project Managers

Introduction to data warehousing, what is ETL, and overview of data cleansing, data scrubbing, data aggregation, learn what is Informatica PowerCenter ETL.

Overview of Informatica configuration, Integration Services, Installation of Informatica, operational administration activities.

Learn what is active and passive transformation and the differences between the two.

Learning about expression transformation, connected passive transformation to calculate value on a single row.

The different types of transformations like Sorter, Sequence Generator and Filter, the characteristics of each and where they are used.

Joiner transformation to bring data from heterogeneous data sources.

Understanding the Ranking and Union transformation, the characteristics and deployment.

Learn about the rank and dense rank functions and the syntax for them.

Understanding how router transformation works and its key features.

 Lookup transformation Overview and different types of lookup transformation:Connected, unconnected, Dynamic and Static

What is SCD?,processing in xml, learn how to handle flat file, list and define various transformations, implementing ‘for loop’ in power center, concepts of pushdown optimization and partitioning, what is constraint based loading?, what is incremental aggregation?

Different types of designer: Mapplet, Worklet, target load plan, loading to multiple targets, linking property

Objectives of performance tuning, defining performance tuning, learning the sequence for tuning

Managing repository, repository manager – client tool, functionalities of previous versions, important tasks in repository manager

Understanding and adopting best practices for managing repository.

Common tasks in workflow manager, creating dependencies, scope of workflow monitor, define variable and parameter in Informatica.

parameter files and their scope, parameter of mapping, worklet and session parameter, workflow and service variable, basic development errors

session and workflow log, using debuggers, error handling framework in informatica, failover and high availability

configurations and mechanisms in recovery, checking health of powercenter environment

infacmd, pmrep, infasetup, processing of flat file

Fixed length and delimited, expression transformations- sequence numbers, dynamic targeting using transaction control.

Dynamic target with use of transaction control, indirect loading.

Importance of Java transformations to extend Power Center capabilities, transforming data, active and passive mode.

Understanding unconnected stored procedure in Informatica, the different scenarios of unconnected stored procedure usage.

Use of SQL transformation (active and passive)

Understanding Incremental Loading and aggregation and comparison between them .

Working with database constraints using PowerCenter, understanding constraint based loading and target load order.

The various types of XML transformation in Informatica, configuring a lookup as active.

Understanding what is data profiling in Informatica, its significance in validating content, ensuring quality and structure of data as per business requirements.

Understanding workflow as a group of instruction/command for integration services, learning how to create and delete workflow in Informatica.

Understanding the Database Connection, creating a new database connection in Informatica, the various steps involved.

Working with relational database tables in Informatica, mapping for loading data from flat files to relational database files.

Understanding how to deploy PowerCenter for seamless LinkedIn connectivity with Informatica PowerCenter.

Connecting Informatica PowerCenter with various data sources like Social media channels like Facebook, Twitter, etc.

Push down optimization for load-balancing on the server for better performance, the various types of partitioning for optimizing performance.

Understanding session cache, importance of cache creation with, implementing session cache, calculating cache requirement

Introduction to Business Intelligence, understanding the concept of Data Modeling, Data Cleaning, learning about Data Analysis, Data Representation, Data Transformation.

Introduction to ETL, the various steps involved Extract, Transform, Load, using a user’s email ID to read a flat file, extracting the User ID from email ID, loading the data into a database table.

Introduction to Connection Managers – logical representation of a connection, the various types of Connection Managers – Flat file, database, understanding how to load faster with OLE DB, comparing the performance of OLE DB and ADO.net, learning about Bulk Insert, working with Excel Connection Managers and identifying the problems.

Learning what is Data Transformation, converting data from one format to another, understanding the concepts of Character Map, Data Column and Copy Column Transformation, import and export column transformation, script and OLEDB Command Transformation, understanding row sampling, aggregate and sort transformation, percentage and row sampling.

Understanding Pivot and UnPivot Transformation, understanding Audit and Row Count Transformation, working with Split and Join Transformation, studying Lookup and Cache Transformation.

Understanding data that slowly changes over time, learning the process of how new data is written over old data, best practices.Detail explanation of three types of SCDs –Type1, Type2 and Type3, and their differences.

Understanding how Fuzzy Lookup Transformation varies from Lookup Transformation, the concept of Fuzzy matching,

Learning about error rows configuration, package logging, defining package configuration, understanding constraints and event handlers.

Introduction to OBIEE,installation of OBIEE,What are data models and why you need them? The scope, reach and benefits of data modeling,data warehousing,sample OBIEE Report, the business requirement intrinsic in data modeling, various case studies, the data modeling implications and the impact of data modeling on business intelligence.

Introduction to Business Intelligence, the architecture of data flow,OBIEE architecture, stack description of BI technology,BI Server,BI Scheduler,displaying report with data,need for reporting in business, distinction between OLTP and OLAP, the BI platform in BI technology stack, the product and dimension hierarchy, multidimensional and relational analytical processing, types of Reports, multidimensional modelling.

Online Analytical Processing,the OBIEE admin tools, RPD, the important concepts & terminology, significance of OLAP in business intelligence life cycle, understanding various data schemas like star,designing with Star Schema,creation of physical layer & simple RPD, enterprise information model, snow flake and constellation, aggregate and calculated measures.

Introduction to Oracle Business Intelligence Enterprise Edition, overview of the OBIEE product, the Architecture of OBIEE, key features and components,creating a simple report, business model, hierarchy, presentation and mapping.

Understanding what is Oracle Business Intelligence Repository, installation of OBIEE on Windows system, directory structure installation, services,, analytics and interactive reporting, dashboard creation, multiple report creation, formula editing, column properties, altering

Understanding how to build a Business Model and Mapping Layer in BI Repository, creating the Presentation Layer,formatting of data, conditional formatting, saving the report, creating and sharing folder. Topics – Data format, Conditional format, Removing filters,Like,Advanced ,Save the report , Shared folder and my folder, Creating new folder

working with the Enterprise Manager, testing and validating the Repository, cache disabling,dashboard prompt, filtering, editing of dashboard with action link.Water fall model.

Working with the Repository, creating Test Report, adding calculations, deploying OBIEE analysis, coming up with landing page UI and its features,repository variables, session and presentation variables.

Learning about the Oracle BI Presentation Catalog, accessing and managing objects, Report archiving and exporting, data grouping and limiting in analyses, data formatting, conditional formatting, master detail report, report creation with multiple subject areas, data mashup, visual analyzer, performance tile, BI functionality, waterfall model, graphs, pivot table, Pie chart, KPI watchList.

The OBIEE dashboard setup, basics of dashboard and dashboard pages, deploying Dashboard Builder for building Dashboards, editing, sharing, and saving Dashboard analysis,cache creation & clearing, ODBC functions in OBIEE, Logical Table Source, summary & detail report.

Securing the Oracle Business Intelligence Suite with Enterprise Manager, creating alerts, managing grouping and maintenance,administrating, the various types of security in OBIEE, object, task and folder level security, Report scheduling.

Data– Working with Admin Console

This is project that will help you understand the Informatica admin console. You will work on a real world scenario of creating and managing roles and responsibilities for various users and designing the various sessions and workflow in the Informatica ETL environment. This project also includes assigning users to groups, creating a collaborative environment, lock handling on repository objects of Informatica in order to develop a complete mastery of working with the Informatica tool.

Project – Configuration and Logging

Topics – In this SQL Server Integration Services (SSIS) project you will extensively work on multiple data from heterogeneous source into SQL Server. As part of the project you will learn to clean and standardize data and automate the administrative work. Some of the tasks that you will be performing are adding logs to SSIS package, configuration and saving it to an XML file. Upon completion of the project you will have hands-on experience in handling constraints, error row configuration and event handlers.

Project – Report Formatting

Data – Revenue

Topics – This is an Oracle Business Intelligence project that is associated with creating complex dashboards and performing formatting of the report. You will gain hands-on experience in filtering and sorting of the report data depending on the business requirements. This project will also help you understand how to convert the data into graphs for easy visualization and analysis. As part of the project you will gain experience in calculating the subtotal and grand total in a business scenario while finding the revenue generated.

Project Work

1. Project – Jobs

Problem Statement – It describes that how to create a job using metadata. For this it includes following actions:

Create XML File,Create Delimited File,Create Excel File,Create Database Connection

2. Hadoop Projects

A. Project – Working with Map Reduce, Hive, Sqoop

Problem Statement – It describes that how to import mysql data using sqoop and querying it using hive and also describes that how to run the word count mapreduce job.

B. Project – Connecting Pentaho with Hadoop Eco-system

Problem Statement – It includes:

Quick Overview of ETL and BI,Configuring Pentaho to work with Hadoop Distribution,Loading data into Hadoop cluster,Transforming data into Hadoop cluster
Extracting data from Hadoop Cluster

Project – SCD2 Implementation

Data –Supplier data

Topics – This project is associated with working on the Slowly Changing Dimensions type 2 where entire history is stored in the database. You will learn how to create a surrogate key generator for implementing SCD. This involves creating additional dimensions and segmenting old and new values for extraction.

Project– Pentaho Interactive Report

Data– Sales, Customer, Product

Topics – In this Pentaho project you will be exclusively working on creating Pentaho interactive reports for sales, customer and product data fields. As part of the project you will learn to create a data source, build a Mondrian cube which is represented in an XML file. You will gain advanced experience in managing data sources, building and formatting Pentaho report, change the report template and scheduling of reports.