ETL Tools Training

1. Introduction to ETL and its importance in data warehousing
2. Setting up and installation of various ETL tools
3. Optimizing ETL tools for best results based on specific job requirements
4. OLAP, ETL and OLTP systems
5. Data modeling and dimensional modeling
6. Working with star and snowflake schemas
7. Dimensions, measures and fact tables in data warehousing
8. Data types and 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 and SQL Developers and Business Intelligence Professionals
• Database, Data Warehousing and Mainframe Professionals and Project Managers

What is data warehousing, understanding the Extract, Transform, Load process, what is data aggregation, data scrubbing, data cleansing and importance of Informatica PowerCenter ETL.

Configuring the Informatica tool, how to install the Informatica operational administration activities and integration services.

Hands-on Exercise – Step by step process for the installation of Informatica PowerCenter

Understanding the difference between active and passive transformations and the highlights of each transformation.

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

Hands-on Exercise – Calculate value on a single row using connected passive transformation

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

Hands-on Exercise – Transform data using Filter technique, Use a sequence generator, Use a Sorter

Joiner transformation to bring data from heterogeneous data sources.

Hands-on Exercise – Use Joiner transformation to bring data from heterogeneous data sources

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

Hands-on Exercise – Perform Ranking and Union transformation

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

Hands-on Exercise – Perform rank and dense rank functions

Understanding how router transformation works and its key features.

Hands-on Exercise – Perform router transformation

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

Hands-on Exercise – Perform 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?

Hands-on Exercise – Load data from a flat file,Implement ‘for loop’ in power center, Use pushdown optimization and partitioning, Do constraint based data loading, Use incremental aggregation technique to aggregate data

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

Hands-on Exercise – Create a mapplet and a worklet, Plan a target load, Load multiple targets

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

Hands-on Exercise – Do performance tuning by following different techniques

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

Hands-on Exercise – Manage tasks in repository manager

Understanding and adopting best practices for managing repository.

Common tasks in workflow manager, creating dependencies, scope of workflow monitor

Hands-on Exercise – Create workflow with dependencies of nodes

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

Hands-on Exercise – Define variables and parameter in functions, Use parameter of mapping, Use worklet and session parameter, Use workflow and service variable

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

Hands-on Exercise – Debug development errors, Read Workflow logs, Use Error handling framework

configurations and mechanisms in recovery, checking health of powercenter environment

Hands-on Exercise – Configure recovery options, Check health of Powercenter environment

infacmd, pmrep, infasetup, processing of flat file

Hands-on Exercise – Use commands infacmd, pmrep, infasetup

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

Hands-on Exercise – Perform expression transformations- sequence numbers, dynamic targeting using transaction control.

Dynamic target with use of transaction control, indirect loading.

Hands-on Exercise – Use of transaction control with dynamic target, indirect loading.

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

Hands-on Exercise – Use Java transformations to extend Power Center capabilities

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

Hands-on Exercise – Use unconnected stored procedure in Informatica in different scenarios

Use of SQL transformation (active and passive)

Hands-on Exercise – Use of SQL transformation (active and passive)

Understanding Incremental Loading and aggregation and comparison between them .

Hands-on Exercise – Do Incremental Loading and aggregation

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

Hands-on Exercise – Perform constraint based loading in a given order

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

Hands-on Exercise – Perform XML transformation, Configure 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.

Hands-on Exercise – Create data profiling in Informatica and validate the content

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

Hands-on Exercise – Create and delete workflow in Informatica

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

Hands-on Exercise – Create a new database connection in Informatica

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

Hands-on Exercise – Create mapping for loading data from flat files to relational database files

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

Hands-on Exercise – Deploy PowerCenter for seamless LinkedIn connectivity with Informatica PowerCenter.

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

Hands-on Exercise – Connect 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.

Hands-on Exercise – Optimize using Push down technique for load-balancing on the server for better performance, Create various types of partitioning for optimizing performance

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

Hands-on Exercise – Implement cache creation with, Work with session cache

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, Integrating with Azure Analysis Services, elastic nature of MSBI to integrate with the Azure cloud service, scale out deployment option for MSBI, working with cloud-borne data sources and query analysis. Scaling out the SSIS package, deploying for tighter windows, working with larger amount of data sources, SQL Server vNext for enhancing SQL Server features, more choice of development languages and data types both on-premise and in the cloud.

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.