Course Content

Module 1: Data warehouse Concepts

·         Business Intelligence

·         Data Warehousing Concepts (What/Why/How)

·         Data Modeling (Schemas, FACTS and DIMENSIONS)

·         Data Mart

·         Metadata

·         OLAP VS OLTP

·         Dimension

·         Fact

·         Types of Dimension tables

·         Types of Fact tables

·         Star Schema

·         Snowflake Schema

·         Slowly Changing Dimensions

·         Top down and Bottom up approach

Module 2: SQL Concepts

·         Conceptual Explanation on SQL: DDL, DML, DRL, TCL, DCL

·         Constraints, Joins, sub queries, Analytical functions

·         200 exercises for self study & Practice

Module 3: E T L Informatica Power Center 9.X.X

·         Why do we need ETL tool and Why Informatica?

Introduction to Informatica

·          Introduction

·          Getting started

·          Informatica Suite Components

·          Informatica Architecture

·          Informatica Server

Module 4: Informatica Power Center Client Components

·          Repository Manager

·          Designer

·          Workflow Manager

·          Workflow Monitor

Module 5: Informatica Building Blocks

·          Source & Targets

·          Transformations

·          Mappings

·          Mapplets

·          Sessions

·          Workflows

Sources

·          Working with Relational Source

·          Working with Flat file Source

·          Working with heterogeneous sources

Targets

·          Working with Relational Targets

·          Working with Flat file Targets

·         Direct and Indirect file loading

Module 6: Different Transformations

·         Active and Passive Transformations

·         Connected and un-connected transformations

1.       Source Qualifier Transformation

2.       Filter Transformation

Lab & Self study exercises:

3.       Expression Transformation

Lab & Self study exercises:

4.       Sorter Transformation

5.       Aggregator Transformation

Lab & Self study exercises:

6.       Router Transformation

7.       Rank Transformation

Lab & Self study exercises:

8.        Sequence Generator Transformation

9.       Joiner Transformation

Lab & Self study exercises:

10.   Lookup Transformation

-          Connected Lookup

-          Un Connected Lookup

Lab & Self study exercises:

11.   Update Strategy Transformation

12.    Union Transformation

Lab & Self study exercises:

13.   Normalizer Transformation

Lab & Self study exercises:

14.   Transaction Control Transformation

15.   Stored Procedure Transformation

Lab & Self study exercises:

·            Mapping Parameter & Variable

·            Mapplet

·            Target Load Plan

·            Debugger

Lab & Self study exercises:

Module 7: Informatica Advanced Concepts

Slowly Changing Dimension (SCD) Types

·         SCD type1 – LAB

·         SCD type2 – LAB

·         SCD type3 – LAB

Module 8: Workflow task

·         Decision Task

·         Email Task

·         Command task

·         Control task

·         Event wait

·         Event raise

·         Timer

·         Lab exercise:

·         Self exercise

Module 9: Session level Properties

·         Pre and Post SQL

·         Static and dynamic cache

·         Persistent cache

·         Parameter variable and file

Lab exercise:

Self exercise:

Module 10: Performance Tuning

 Informatica Tuning (Source, target, mapping & Session)

·         Push down optimization

·         Partitioning Types

·         Migration concepts

·         Real time project explanation

·         Resume Preparation

Module 11: Case studies

 Case A, Case B, Case C – 15 Exercises 

Trainer Profile

Interview Questions & Answer

1) What is ETL?

 ETL is an important component In data warehousing architecture. it which manages the data for any business process. ETL stands for Extract, Transform and Load.

• Extract does the process of reading data from a database.
•  Transform does the converting of data into a format that could be appropriate for reporting and analysis.
• Load does the process of writing the data into the target database.

2) What are the types of data warehouse applications and explain difference between data mining and data warehousing?

The types of data warehouse applications are as follows:

• Info Processing
• Analytical Processing
• Data Mining

Data mining is the process of extracting hidden predictive information from large databases and interpret the data on the other hand data warehousing may make use of a data mine for analytical processing of the data in a faster way. Data warehousing is the process of aggregating data from multiple sources into one common repository.

 3) What is Informatica ETL Tool?

Informatica ETL tool is the market leader in data integration and data quality services. Informatica is a successful ETL and EAI tool with significant industry coverage.  Data integration tools are different from other software platforms and languages.

They have no inbuilt feature to build user interface where end user can see the transformed data. Informatica ETL tool “power center” has the capability to manage, integrate, and migrate enterprise data.

4) What is Grain of Fact?

Grain fact is the level at which the fact information is stored. It is also known as Fact Granularity.

5) What is Bus Schema?

For the various business process to identify the common dimensions, BUS schema is used. It comes with a conformed dimension along with a standardized definition of information.

6) what is Star Schema?

Star schema is the simplest style of data mart schema in computing. It is an approach which is widely used to develop data warehouses and dimensional data marts. It features one or more fact tables referencing to numerous dimension tables.

7) what is Snowflake Schema?

Snowflake Schema is a logical arrangement of tables in a multidimensional database. It is represented by centralized fact tables which are connected to multidimensional tables. Dimensional tables in a star schema are normalized using snowflaking. Once normalized, the resultant structure resembles a snowflake with the fact table at the middle. Low-cardinality attributes are removed, and separate tables are formed.

8) What is Metadata?

Metadata is defined as the data about data. It also describes the entity and attributes description.

9) What do you mean by word ‘session’? Give an explanation of how to combine execution with the assistance of batches?

 Session means converting a data from a source to a target is generally implemented by a teaching service. Usually, session's manager executes the session. In order to combine session’s executions, batches are used in two ways - serially or parallelly. 

10) Define OLAP?

OLAP or Online Analytical Processing is a specific category of software that allows users to analyze information from multiple database systems simultaneously. Using OLAP, analysts can extract and can have a look at business data from different sources or points of view.

11) What are the different types of OLAP?

The different types of OLAP are:

• ROLAP: ROLAP or Relational OLAP is an OLAP server that maps multidimensional operations to standard relational operations.
• MOLAP: MOLAP or Multidimensional OLAP uses array-based multidimensional storage engines for multidimensional views on data. Numerous MOLAP servers use two levels of data storage representation to handle dense and sparse datasets.
• HOLAP: HOLAP of Hybrid OLAP combines both ROLAP and MOLAP for faster computation and higher scalability of data

11) Define Aggregator transformation?

 In Aggregator transformation we can do aggregate calculations such as averages, sum, etc. It is different from expression transformation in which we can do calculations in set.

12) What is Expression transformation?

It is used for performing nonaggregate calculations. We can test conditional statements before the output results are moved to the target tables.

13) Define Filter transformation?

Filter transformation is a way of filtering rows in a mapping. It has all ports of input/output, and the row which matches with that condition can only pass by that filter.

14) What is Joiner transformation?

It combines two associated mixed sources located in different locations, on the other hand a source qualifier transformation can combine data rising from a common source.

15) What do you mean by Lookup transformation?

Lookup transformation is used for maintaining data in a relational table through mapping. We can use multiple lookup transformations in a mapping.

16) Define mapplet.

A mapplet is a recyclable object that uses a mapplet designer.

17) What is Informatica PowerCenter?

Informatica PowerCenter is an ETL/data integration tool which has a wide range of applications. This tool allows users to connect and fetch data from different heterogenous sources and subsequently process the same.

For example, users can connect to a SQL Server Database or an Oracle Database, or both, and also integrate the data from both these databases to a third system.

18) Why do we use mapping parameters and mapping variables?

The values that alter during the session’s implementation is known as mapping variables, on the other hand the values that don’t alter during the session’s implementation is known as mapping parameters.

Basically, mapping parameters and mapping variables represent values in mappings and mapplets.

Mapping Parameters

• Mapping parameters represent constant values that are defined before running a session.
• After creation, parameters appear in Expression Editor.
• These parameters can be used in source qualifier filter, in user-defined joins, or for overriding.

Mapping Variables

• As different from mapping parameters, mapping variables can change values during the sessions.
• The last value of a mapping variable is saved to the repository at the end of each successful session by the Integration Service. However, it is possible to override saved values with parameter files.
• Basically, mapping variables are used to perform incremental reads of data sources.

19) How to use PowerCenter Command Line in Informatica?

 The transformation language provides two comment specifiers to let you insert comments in expression:

- Two Dashes ( - - )

- Two Slashes ( / / )

The Power center integration service ignores all text on a line preceded by these two comment specifiers.

20) What are the Limitations of Pushdown Optimization?

1. Rank T/R cannot be pushed
2. Transaction control T/R
3. Sorted aggregation.

Procedure:

1. Design a mapping with filter, rank and expression T/R.

2. Create a session --> Double click the session select properties tab.

3. Select the mapping tab --> set reader, writer connection with target load type normal.

4. Click apply --> click ok  --> save the session.

5. Create & start workflow.

Pushdown Optimization Viewer:-

Double click the session --> Select the mapping tab from left window --> select pushdown optimization.

Blog