Informatica Training in Bangalore - Classroom and Online in Marathalli Bangalore

Informatica Online Training Course

Data Warehouse Concepts:

• Introduction to Data warehouse

• What is Data warehouse and why we need Data warehouse

• Dimensional modeling

• Star schema/Snowflake schema/Galaxy schema

• Dimensions / Facts tables.

• Slowly Changing Dimensions and its types.

• Data Staging Area

• Different types of Dimensions and Facts.

• Data Mart vs Data warehouse

Informatica Power Center 9:

Software Installation:

Informatica 9 Server/Client Installation on Windows/Unix.

Power Center Architecture and Components:

• Introduction to Informatica Power Center

• Difference Between Power Center and Power Mart

• PowerCenter 9 architecture

• PowerCenter 7 architecture vs Power Center 8 and 9 architecture

• Extraction, Transformation and loading process 

Power Center tools: Designer, Workflow manager, Workflow Monitor,

Repository Manager, Informatica Administration Console.

• Repository Server and agent

• Repository maintenance

• Repository Server Administration Console

• Security, Repository, privileges and folder permissions

• Metadata extensions

Power Center Developer Topics:

Lab 1- Create a Folder.

• How to provide Privileges

• Source Object Definitions

        Source types

-       Relational Tables (Oracle, Teradata)
–       Flat Files (fix width, Delimiter Files)
–       Xml Files
–       COBOL Files
–       Sales Force

Source properties

Lab 2- Analyze Source Data, Import Source.

• Target Object Definitions

-       Target types
–       Target properties

Lab 3- Import Targets

• Transformation Concepts

• Transformation types and views

• Transformation features and ports

• Informatica functions and data types

Mappings

• Mapping components

• Source Qualifier transformation

• SQL and Post SQL

• Mapping validation

• Data flow rules

Lab 4 –Create a Mapping, session, and workflow

Workflows

• Workflow Tools

• Workflow Structure and configuration

• Workflow Tasks

• Workflow Design and properties

Session Tasks

• Session Task properties

• Session components

• Transformation overrides

• Session partitions

Workflow Monitoring

• Workflow Monitor views

• Monitoring a Server

• Actions initiated from the workflow Monitor

• chart View and Task view.

Lab 6 – Start and Monitor a Workflow

Debugger

• Debugger features

• Debugger windows

• Tips for using the Debugger

Lab 7 –The Debugger

Expression transformation

• Expression, variable ports, storing previous record values.

Different type of Ports

• Input/ output / Variable ports and Port Evaluation

• Filter transformation

• Filter properties

Lab 8- Expression and Filter

Aggregator transformation

• Aggregation function and expressions

• Aggregator properties

• Using sorted data

• Incremental Aggregation

Joiner transformation

• Joiner types

• Joiner conditions and properties

• Joiner usage and Nested joins

Lab 9 – Aggregator, Heterogeneous join

• Working with Flat files

• Importing and editing flat file sources & Targets

Lab Session – Use Flat file as source.

Sorter transformation

• Sorter properties

• Sorter limitations

Lab 10 – Sorter

• Propagate Attributes.

• Shared Folder and Working with shortcuts.

• Informatica built in functions.

Lookup transformation

• Lookup principles

• Lookup properties

• Lookup techniques

• Connected and unconnected lookup.

• Lookup Caches

Lab 11 – Basic and Advance Lookup

 Target options

• Row type indicators

• Row loading operations

• Constraint- based loading

• Rejected row handling options

Lab 12 – Deleting Rows

• Update Strategy transformation

• Update strategy expressions

Lab 13 – Data Driven Inserts and Rejects 

• Router transformation

Using a router

Router groups

Lab 14 – Router

• Conditional Lookup

Usage and techniques

Advantage

Functionality

Lab 15 – Straight Load
Lab 16 – Conditional Lookup

 Heterogeneous Targets

• Heterogeneous target types

• Target type conversions and limitations

Lab 17 – Heterogeneous Targets

M-applet

• Functionality and Advantages

• M-applet types and structure

• M-applet limitations

Lab 18 – M-applet

• Reusable transformations

• Advantages

• Limitations

• Promoting and copying transformations

Lab 19 – Reusable transformations

• Sequence Generator transformation

• Using a sequence Generator

Sequence Generator properties

• Dynamic Lookup

• Dynamic Lookup theory

• Usage and functionality

• Advantages

Lab 20 – Dynamic Lookup

• Concurrent and sequential WorkflowsStopping, Starting and suspending tasks and workflows

  • Concurrent Workflows
  • Sequential Workflows

Lab 21 – Sequential Workflow

• Additional TransformationsLab Sessions- For above transformations

  • Union Transformation
  • Rank transformation
  • Normalize transformation
  • Custom Transformation
  • Transformation Control transformation
  • XML Transformation
  • SQL Transformation
  • Stored Procedure Transformation
  • External procedure Transformation
  • SQL Transformation

• Error Handling

• Overview of Error Handling Topics

Lab 22 – Error handling fatal and non Fatal

• Workflow Tasks:

  • Command
  • Email
  • Decision
  • Timer
  • Control
  • Even Raise and Wait
  • Sequential Batch Processing

Parallel Batch Processing

• Lab Sessions – With Workflow tasks

• Link Conditions

• Team Based Development

Version Control

Checking out and checking in objects.

• Performance Tuning

• Overview of System Environment

• Identifying Bottlenecks.

Optimizing Source, Target, mapping, Transformation, session.

• Mapping Parameters and Variables

Introduction to Mapping Variables and Parameters

• Creating Mapping Variables and Updating Variables

• Creating Parameter File and associating file to a Session

• System Variables

• Variables functions

Lab 26 – Override Mapping Variable with Parameter Files
Lab 27 – Dynamically Updating a Source Qualifier with Mapping Variable

• Slowly Changing Dimensions Type 1, Type 2, Type 3

• Incremental Loading

Lab 28– SCD 1, 2, 3

• Reusable Workflow Tasks

• Work Lets

• Work lets Limitation

• Sessions

• Reusable Sessions

Lab 29 – Create Worklet using Tasks

• Command Line Interface

• Overview of PM REP and functions.

• PM REP

• Informatica Migrations:

Copying Objects

• Objects export and import (XML)

• Deployment groups

• Workflows Scheduling:

• Using Informatica

• Unix cron tab, third party tools.

Lab 30: Informatica Project- Case Study

• Sales Data mart.

• Loading Dimensions and Facts.

• ETL Best Practices and methodologies

• Review the Industry best practices in ETL Development

• Review Real time project experiences of trainer

• Discuss what is learned techniques are useful in real world

• How to design effective ETL process

• Important considerations in designing ETL process

• Discuss real world production issues and support

• Discuss various roles in ETL world

• Business Analyst, System Analyst

• System Architect

• Technical Architect, ETL Lead

• Stakeholders, Business users

• Effective ways of using Data warehouse

• Review various BI Reporting methods

  • Q/A-Interview preparation/Placements

  • Answer students questions

  • Tips for interview preparation

  • How we can assist in placement and future growth

  • Discuss other related technologies like Business Intelligence (BI)

  • Advancing career options

Big Data Lake Implementation - Moving Data from OLTP (MySQL) to HDFS using Apache Sqoop - Example scripts

To persist the entire history data in the Big Data Lake, we started with the ingestion and storage of all records in the OLTP system (based on MySQL) to HDFS cluster.

Below is a sample sqoop import call that allows us to do this with ease.

sqoop import --connect jdbc:mysql://localhost/test_nifi --username root --table apache_nifi_test -m 1

We can also persist the data directly onto a Hive table :

./sqoop import –connect jdbc:mysql://w.x.y.z:3306/db_name –username user_name –password **** –hive-import –hive-database -table oltp_table1 -m 1

The m 1 creates only one file for the table. This is used if you don't have a primary key defined. Sqoop uses the primary key for partitioning the data.

The below diagram will explain the steps we ran to achieve the data copy.

Step 1: Creation of a table on MySQL with data

Copy MySQL data onto HDFS using Sqoop for Big Data Lake implementation

Step 2: Running sqoop to extract data from the MySQL table and dump on HDFS

MySQL to HDFS data transer via Sqoop

As you can see in the diagram, by default the data is copied into :
/user/<login-user>/<tablename>
The path from where you execute the sqoop import command also stores the .java file that was used to import the data.

Let us know if you like our Blog. Thanks!!

CSUM and ROW_NUMBER comparison in Teradata - Example script with statistics and syntax - Surrogate key generation in Teradata

We keep getting questions about which one to use for sequence number generation in Teradata.

Many people use CSUM(1,1) as its easier and other databases support that. But, in Teradata , it is not recommended.

To test this out, we ran the below queries today on a table that contains 60K rows and another with just 1000 rows.

Example script:

--- Using CSUM
SELECT CSUM(1, SEED1.ID_MAX),  L_ORDERKEY
  ,ID_MAX
 FROM ITEMPPI AS E
 CROSS JOIN
 (SELECT 1 AS ID_MAX)AS SEED1;

--- Using Row Number 
 SELECT ROW_NUMBER() OVER(ORDER BY L_ORDERKEY) + SEED1.ID_MAX, L_ORDERKEY
  ,ID_MAX
 FROM ITEMPPI AS E
 CROSS JOIN
 (SELECT 0 AS ID_MAX)AS SEED1;


 SELECT * FROM DBC.DBQLogTbl 
 WHERE CAST(COLLECTTIMESTAMP AS DATE FORMAT 'YYYY-MM-DD')= CURRENT_DATE
 AND SESSIONID = 1624
 ORDER BY COLLECTTIMESTAMP DESC;

When you check the QueryLog after running a CSUM(1,1) you'll notice that a single AMP (usually vproc 0) processed all the data ,resulting in high cpu/io and spool.

For OLAP functions there is distribution of the data based on PARTTITION and ORDER and for CSUM(1,1) there's only 1 partition and no order. This can be seen in the diagram below:

Statistics and query log comparison between CSUM and ROW_NUMBER in Teradata

For a smaller table, the difference was not noteworthy, but for larger datasets, we see a big difference.

Let us know if you need more information. Like us on Twitter or Facebook.