informatica

ECH SOLUTIONS, 9880 153 154 SOFT  T ECH

www.stsbangalore.com

DAT ATA A WAREHOUSING & IN FO FORMATICA RMATICA 8.6, BI Course Type

Course Cou rse Dur Durati ation on

: Regular & Week Ends

DWH Basics 1. 2. 3. 4. 5. 6.

7. 8. 9. 10. 11.

: 90 Hrs

22. The core parts (tables) (tables) of DWH. i. Its all about tables (DWH is nothing but tables, if you think Physically) ii. Ty Types pes of tab tables les in DWH 1. Dim Dimensi ension on Table ables s 2. Fact Table ables s 3. Fact less fact tab tables les 4. Bridge/In Bridge/Intermediate/ termediate/XRef XRef tables 23. Dimension Tables i. What is dime dimensi nsion on tab table? le? ii. What do we store in dimension tables? iii. What is Surrogate Key? iv.. Why we need surrogating ? iv 1. Bui Built-I lt-In n mean meanings ings? ? v. Type Types s of dimension tables 1. Conf Confirme irmed d Dime Dimensio nsion n 2. Slowly Changing Dimensio Dimension n a. Type Type-1 -1 Dimensio Dimension n Tables b. Type Type-2 -2 Dimensio Dimension n Tables c. Type Type-3 -3 Dimensio Dimension n Tables d. Zunk Dime Dimensio nsion n e. De-G De-Gener enerated ated Dim Dimensi ension on 24. Fact Tables i. Wha Whatt is fac factt ta table ble? ? ii. What do we we store store in a fact fact table table iii. Str Structure ucture of a fact fact table iv.. What is measure/fact iv measure/fact? ? v. Ty Types pes of meas measures ures vi. How to load load fact fact tables? tables? vii. Differe Different nt ways of loading fact tables tables in different cases with different types of dimension tables. viii. Types of fact tables loading (incremental) 25. Fact less fact tables tables i. What is fact less fact tab table? le? ii. What do do we store in fact fact less fact tables? iii. Purpose of fact fact less less fact fact tables? tables? iv.. The structure iv structure of fact less less fact table? v. How to load fact less fact tables? 26. Bridge Tables i. What is brid bridge ge tab table? le? ii. Why do we need or or purpose purpose of of bridge bridge table? table? iii. What do we store in in bridge bridge tables? tables? iv.. The structure iv structure of bridge bridge table? v. How to load bridge tables?

What is dat data a base base? ? What is dat data a wareho warehouse? use? What is Data Warehou Warehousing? sing? Why DWH? Why DB? What is OL OLTP? TP? Whatt is OL Wha OLAP AP? ? i . RO L A P ii.. MO ii MOLA LAP P iii. ii i. HO HOLAP LAP Different usages of DWH DWH in different areas. What is Dat Data a Mart Marts? s? Differences Differenc es among among data data marts, marts, databases databases & DWH Characteristics Characteris tics for DWH Real time cases and usage usage

12. Emerging Trends in DWH

BI Concepts 13. What is BI? Why we need BI? 14. BI Role in Organizations? Organizations? Where we need BI? 15. BI Tools. 16. Emerging Trends Trends in BI? 17. How to build/design DWH? 18. Subject Areas, Data Marts 19. Data Mining? 20. Data Modeling Modeling - Different models in building i. Star tartt Sch Schema ema ii. Snow Snowflak flake e Sche Schema ma iii.. Hyb iii Hybrid rid Sche Schema ma iv.. Why Star iv Star,, Snowflake Snowflake & Hybrid Hybrid Schemas? Schemas? 21. Different phases in building a DWH. i. Source St Staging aging Transfo Transform/Uniform rm/Uniform Target (DWH) ii. Sour Source ce (OL (OLTP) TP) 1. Differen Differentt types of sources 2. Fla Flatt Fil Files es 3. Dat Databa abases ses 4. Ot Othe hers rs iii. Staging (Critica (Criticall part part of ETL) 1. What and Why sta staging ging? ? 2. Purp Purpose ose of st stagin aging? g? 3. Typ Types es of of stages stages (Flat files, DB…) iv.. Transf iv Transform/Uniform orm/Uniform (Critical Part of ETL) 1. Necessity of transformati transformation on 2. St Standardizat andardization/uniform ion/uniform/integratio /integration n data before transferring to Target v. Target (DWH (DWH)) 1. Typ Types es of targets (DWH, Files, Cubes…) 2. Usag Usage e of tar target get

Continued... 1

SOFT  T ECH SOLUTIONS, 9880 153 154

www.stsbangalore.com d. MDM – Meta Data Management e. How to maintain repository? f. Global Repository 12) ETL Tools

ETL [Extraction, Transformation, Loading] 27. Extraction (From Source) i. What is extraction? ii. Extracting different types of tables discussed iii. Extracting from different types of sources? iv. Different ways of extraction? (incremental) v. The run date 28. Staging i. What is staging? ii. Staging Area iii. The necessity of staging. iv. Staging area can be database or files v. Maintenance of staging vi. How to stage data in staging area? 29. Transformation/Uniform/Generalization/ Integration i. What is transformation? (The business logic) ii. How to transform data using Informatica tool? iii. What is uniform/Generalization/ Integration? iv. Need of uniform/generalization/ Integration 30. Loading (To Target) i. What is loading? ii. Loading different types of tables iii. The order of loading iv. Different techniques of loading v. Loading to different targets (DB, Files)

Informatica Power Center – V 8.6.0 1) Introduction to Informatica 2) Server Components a. Informatica Admin Console i. Configuring Repository Services ii. Configuring Integration Services iii. Backing up the repository 1. Cold Backup 2. Hot Backup iv. Restoring Repositories v. Running Modes 1. Normal Mode 2. Exclusive Mode 3) Client Components a. Repository Manager b. Mapping Designer i. Source Analyzer ii. Target Analyzer iii. Transformation Developer iv. Mapplet Designer v. Mapping Designer c. Workflow Designer i. Task Developer ii. Worklet Designer iii. Workflow Designer d. Workflow Monitor i. Usage of Monitor ii. Differences between Stop and Abort e. Why we need different Client Components in Informatica 4) Informatica Architecture a. Distributed Services and SOA b. Domain? c. Grid? d. Nodes? e. Services i. Repository Services ii. Integration Services 1. Threading Concept in Informatica 2. Types of Threads iii. Repository Agent f. DTM Process g. How all server components are architectured for informatica 5) Transformations a. Types of Transformations i. Active Transformations ii. Passive Transformations iii. Using Functions in Transformations iv. What is User-Defined Function

ETL Concepts: 1) 2) 3) 4) 5)

6) 7) 8) 9)

10) 11)

What is ETL? Why we need ETL in Data Warehousing? ETL Architecture? Meta Data? Extraction? a. Source Tables b. Types of Sources i. Database ii. Flat files 1. Fixed Width 2. Delimited Transformation Why we need Transformation, its importance Importance of Data Integration? Loading, types of loading? a. Full Load b. Incremental Load c. Stage Loading d. DIM Loading e. Fact Loading f. AGG Fact Loading g. Dependencies while loading Deferent Targets? Repository a. Metadata b. Where Meta Data is storing? c. Why we need repository? 2

SOFT  T ECH SOLUTIONS, 9880 153 154

www.stsbangalore.com

b. c. d. e. f. g. h.

Source Qualifier Transformation Filter Transformation Expression Sorter Router Ranker Sequence Generator i. Aggregator i. Incremental Aggregation ii. Sorted Input Concept j. Joiner i. Normal Join ii. Master Out Join iii. Detail Out Join iv. Full Out Join k. Update Strategy l. Stored Procedure i. Connected ii. Un-Connected m. Normalizer n. Union o. SQL i. SQL Mode ii. Script Mode p. Lookup i. Connected ii. Un-Connected iii. Cached lookup 1. Static Cache 2. Dynamic Cache 3. Persistence Cache iv. Un-Cached lookup q. Transaction Control r. Other Advanced Transformations 6) How to create a simple mapping? 7) How we can do integration with informatica? 8) Parameters and Variables 9) Concept of Generic Code and how to make Generic Coding in Informatica 10) Usage of Parameterization 11) Creating Parameter Files 12) Creating Mappings for different scenarios a. Implementing SCD Type-1 b. Implementing SCD Type-2 c. Implementing SCD Type-3 d. Extracting data from different sources using informatica i. DBs ii. Flat Files iii. Excel Sheets and others e. Other Scenarios

13) Task a. Type of Tasks b. Sessions c. Command Task d. E-Mail Task e. Decision f. Event – Wait g. Event – Raise h. Timer i. Assignment j. Control 14) Worklet a. How to create worklets b. Use of worklets 15) Workflows a. Use of workflow b. How to create workflows 16) Connections a. What is connections b. Types of connections i. Relational Connections ii. FTP Connections iii. Other Connections 17) Invoking Workflow from Command line a. pmrep commands b. pmcmd commands c. encrypting Informatica Passwords for scripts 18) Scheduling 19) Link Conditions 20) Other Advanced Topics a. Versioning b. Partitioning 21) Environments a. Development b. QA c. UAT d. Production (Live) e. Migration Process i. Importing ii. Exporting f. Role of Unix Shell scripting in ETL process

3