Difference between informatica 8.6 and 9

Difference between informatica 8.6 and 9

In informatica 8.6 unconnected lookup transformation returns only one port and in informatica 9 it returns multiple matched ports. 

The major difference is lookup transformation which is active (returns all the matched rows) in informatica 9.0. 

1. Now Lookup can be configured as an active transformation - it can return multiple rows on successful match.
2. Now you can write SQL override on un-cached lookup also. Previously you could do it only on cached lookup.
3. You can control the size of your session log. In a real-time environment you can control the session log file size or time.
4. Database deadlock resilience feature - this will ensure that your session does not immediately fail if it encounters any database deadlock (Time Base Commit Out), it will now retry the operation again. You can configure number of retry attempts.

Difference between normalization and demoralizations in data warehouse

Difference between normalization and demoralizations in data warehouse

3NF

Pro

• save most storage of all modeling techniques
• simple to understand

Con

• bad performance with multiple joins
• difficult to load (ETL)
• changes are very difficult to handle

Star Schema

Pro

• Simple design
• fast queries
• fits very well to olap models
• many DBMS are optimized for queries on star schemas

Con

• usually centralized on one fact table
• higher storage usage due to de normalization

Normalization includes reducing redundancy, redundancy means data repetition. By removing the repeated data from the table we can separate the redundant data and create another table that is we are separating repeated data from detail table and creating a master table. Like that we are separating the data. In this case we are not completely removing the redundant data from detail table we are maintain a common column in both the tables that is in master table & detail table. That is in master table common column is called primary key column, detail table common column is foreign key. This foreign key refers to primary key.

This is one stage normalization. Normalization has 3 steps 1normalform, 2, 3 normal from. In first normal form we separate redundant data from detail table and create the master tables.
2 normal from we maintain data consistency by creating primary key foreign key relations. In 3 normal from we remove any calculation columns from detail table.

This is normalization. Opposite of this above normal forms is de normalization.
That is we maintain redundant data in detail table in that case we maintain all the transaction data in one table .that is de normalization.

Normalization is done to remove redundancy (i.e. to remove duplicates) -----OLTP
Data warehousing contains denormalized data purposely since it’s used for analysis purpose. -----OLAP

Redundancy: repetition of data within a database, increasing the inefficiency of the possibility of errors in databases is called Redundancy.

In general, databases intended for on-line transaction processing (OLTP) are typically more normalized than databases intended for on-line analytical processing (OLAP).

Update without Update Strategy for Better Session Performance

Update without Update Strategy for Better Session Performance

You might have come across an ETL scenario, where you need to update a huge table with few records and occasional inserts. The straight forward approach of using Lookup transformation to identify the Inserts, Update and Update Strategy to do the Insert or Update may not be right for this particular scenario, mainly because of the Lookup transformation may not perform better and start degrading as the lookup table size increases.

In this article let’s talk about a design, which can take care of the scenario we just spoke.

The Theory

When you configure an Informatica Power Center session, you have several options for handling database operations such as insert, update, delete. 

Specifying an Operation for All Rows

During session configuration, you can select a single database operation for all rows using the Treat Source Rows As setting from the 'Properties' tab of the session.

1. Insert: - Treat all rows as inserts. 
2. Delete: - Treat all rows as deletes.
3. Update: - Treat all rows as updates. 
4. Data Driven :- Integration Service follows instructions coded into Update Strategy flag rows for insert, delete, update, or reject. 

Specifying Operations for Individual Target Rows

Once you determine how to treat all rows in the session, you can also set options for individual rows, which gives additional control over how each rows behaves. Define these options in the Transformations view on mapping tab of the session properties. 

1. Insert: - Select this option to insert a row into a target table. 
2. Delete: - Select this option to delete a row from a table. 
3. Update :- You have the following options in this situation: 
• Update as Update: - Update each row flagged for update if it exists in the target table. 
• Update as Insert: - Insert each row flagged for update. 
• Update else Insert: - Update the row if it exists. Otherwise, insert it. 
4. Truncate Table: - Select this option to truncate the target table before loading data.

Design and Implementation

Now we understand the properties we need to use for our design implementation.

We can create the mapping just like an 'INSERT' only mapping, with out LookUp, Update Strategy Transformation. During the session configuration lets set up the session properties such that the session will have the capability to both insert and update.

First set Treat Source Rows As property as shown in below image.

Now let’s set the properties for the target table as shown below.  Choose the properties Insert and Update else Insert.

That all we need to set up the session for update and insert without update strategy.

How to generate sequence numbers using expression transformation?

How to generate sequence numbers using expression transformation?
Solution:
In the expression transformation, create a variable port and increment it by 1. Then assign the variable port to an output port. In the expression transformation, the ports are:
V_count=V_count+1
O_count=V_count

Design a mapping to load the first 3 rows from a flat file into a target?

Design a mapping to load the first 3 rows from a flat file into a target?
Solution:
You have to assign row numbers to each record. Generate the row numbers either using the expression transformation as mentioned above or use sequence generator transformation.
Then pass the output to filter transformation and specify the filter condition as O_count <=3

Different time format use in OBIEE 11g

OBIEE Timestamp

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS'
TIMESTAMP '1990-01-10 13:35:43'

Time stamp difference in obiee 
I have two columns with TIMESTAMP data type(One is In time and the other is Outtime).The values are as below.
In Time Out Time
14-jan-08 1:40:45 AM             14-jan-08 1:30:45 PM
15-jan-08 3:22:53 PM             15-jan-08 5:23:54 PM
16-jan-08 3:20:50 PM             16-jan-08 4:23:54 PM
17-jan-08 3:13:44 PM             17-jan-08 7:23:54 PM

I need the out put as below
00:11:50:00
00:2:01:01
00:1:03:04
00:4:10:10

Solution:
CAST( TimestampDiff(SQL_TSI_DAY, timestamp1, timestamp2) AS CHAR(2)) || CAST( TimestampDiff(SQL_TSI_HOUR, timestamp1, timestamp2) AS CHAR(2)) || CAST( TimestampDiff(SQL_TSI_MINUTE, timestamp1, timestamp2) AS CHAR(2)) || CAST( TimestampDiff(SQL_TSI_SECOND, timestamp1, timestamp2) AS CHAR(2))

Convert VARCHAR2 to TIMESTAMP in Oracle

DATE and TIMESTAMP are two different datatypes. So you need to use the correct conversion function for each, which in your case would be TO_TIMESTAMP().

SELECT TO_TIMESTAMP('14-SEP-11 12.33.48.537150 AM', 'DD-MON-RR HH:MI:SS.FF AM')

FROM DUAL;

select from_tz(to_timestamp('14-SEP-11 12.33.48.537150 PM', 'DD-Mon-RR HH.MI.SS.FF AM'), 'EUROPE/LONDON') from dual

TO_TIMESTAMP(<date_string>, <format mask>, <NLS_PARAMETER>)

RETURN TIMESTAMP

-- convert a character string to a timestamp. The character 

-- string is not in the default TIMESTAMP format, so the 

-- format mask must be specified:

SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')

   FROM DUAL;

TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')

---------------------------------------------------------------------------

10-SEP-02 02.10.10.123000000 PM

I have direct query and when error is occured

i see presentation variables replaced with value in format yyyy-mm-dd.

part of the sql:

POLISA.DATUM_IZDANJA <= cast(TimestampAdd(SQL_TSI_MONTH, -12, cast(DATE '2013-06-19' as TIMESTAMP)) as date))) AND (POLISA.DATUM_IZDANJA BETWEEN date '2013-06-01' AND date '2013-06-19') 

1. Using evaluate function we can modify the format.

ex:

USING EVALUATE

cast(Evaluate('to_char(%1,%2)',"Inventory"."Catalog"."dbo"."Dim_W_DAY_D_Inv_Unallocated_Run_Month"."CALENDAR_DATE",'YYYYMon') as varchar(10))

cast(Evaluate('to_char(%1,%2)',<<COLUMN HERE>>,'YYYY-Mon') as varchar(10))

2. Open your report then click column properties then select Data format then select custom then here you can put which format you want.

I can suggest you one things, Please you can try this.

1. Use instead of to_char you can use to_date and remove the cast statement in the condition.

Problem starts when you click on the button apply of the prompt, then obiee transfers date column into sql statement in format yyyy-mm-dd.

My default obiee date format is dd.mm.yyyy

When I pickup date from calendar date on prompt is shown as dd.mm.yyyy, but when I click apply that date goes in format yyyy-mm-dd into sql statement.

I want when I click on apply to keep dd.mm.yyyy format of the date.

How to know the format that OBIEE use ?

Apart the fact that you can find it by checking the OBIEE configuration, an easy way to discover it is to create a formula column in a answer with a statement like this one :

cast(Calendar."Time Id" AS CHAR)

The idea is by casting a date column as CHAR, we can see the date format :

When the cast depend of the database :

Informatica Administration And PowerCenter Topics

Informatica Administration

Topic Topics covered

Topic 1: Architecture of Power Center (Domain, Node and Services)
Installing Oracle (Pre requsit)
Create domain user and Repository Users & grant Privilages
Installing Informatica ( Server & client tools)

Topic 2: Creating the Repository Service
Creating and Deleting Repository Content
Enabling Version Control
Managing Repository User Connections and Locks
Backing Up and Restoring the Repository

Topic 3 : Creating an Integration Service
Enabling and Disabling the Integration Service
Configuring the Integration Service Properties
Configuring the Associated Repository
Configuring the Integration Service Processes

Topic 4: Managing the Domain
Starting and stopping Informatica services
Configuring Connectivity / Connecting to an Oracle Database (TNS Entry)
Administration Console
Viewing Domain Logs Repo Logs Integration service Logs

Topic 5: Users and User Accounts
Managing Users
Managing Permissions and Privileges
Monitoring User Activity

Topic 6: Using the Repository Manager
Connecting to Domains and Repositories
Viewing Object Dependencies
Validating Multiple Objects
Comparing Repository Objects
Truncating Workflow and Session Log Entries

Topic 7: Folders Overview
Managing Folder Properties
Comparing Folders

Topic 8: Managing Object Permissions
Assigning Permissions
Changing the Object Owner

Topic 9: Working with Versioned Objects
Working with Version Properties
Tracking Changes to Versioned Objects
Checking Out and Checking In Objects
Deleting and Recovering Objects
Purging Versions of Objects

Topic 10: Code Migration Process
Copy Paste
Folder to Folder Copy

Topic 11: Exporting and Importing Objects
Exporting Objects
Importing Objects

Topic 12: Deployment Groups
Create a Deployment Group
Creating Lable
Query Lable and associate objects to a deployment group
Copying the deployment group

Topic 13: Managing Connection Objects
Creating Relational Database Connections
Managing Connection Objects

Topic 14: Working with Workflow Manager
Assigning an Integration Service
Scheduling a Workflow
Validating a Workflow
Manually Starting a Workflow

Topic 15: Monitoring Workflows
Viewing Repository Service Details
Viewing Integration Service Properties
Viewing Repository Folder Details
Viewing Workflow Run Properties
Viewing Session Task Run Properties
Viewing Performance Details
Stopping or Aborting the Workflow
Session and Workflow Logs
Log Events
Working with Log Files
Workflow Logs
Session Logs

Topic 16: Best Practices and procedure of the admin Activities
Command Line referance

INFORMATICA POWERCENTER 9.1

DATAWARE HOUSING FUNDAMENTALS

1. What is Database?

2. What is Data Warehousing?

3. Difference between OLTP Database and DWH.

4. ETL FUNDAMENTALS.

i) Data Extraction

ii) Data Transformation

iii) Data Loading

5. What is ETL Client?

6. DataMart and Types of Data Marts.

7. DataWareHouse-Database Design.

i) Star Schema.

ii) Snow Flake Schema.

iii) Galaxy Schema.

8. OLAP and Types of OLAPs

9. DataWareHouse Architecture.

INFORMATICA POWERCENTER 9.1

1. Power Center Components.

• Power Center Clients
• Power Center Repository.
• Power Center Domain
• Integration service
• Repository Service
• Administration Console

2. Difference between Informatica 8.X and 9.X

3. Power Center Architecture

4. Integration Service Architecture

TRANSFORMATIONS

a). ACTIVE TRANSFORMATIONS.

·         Filter Transformation

·         Router Transformation

·         Aggregator Transformation

·         Joiner Transformation

·         Union Transformation

·         Sorter Transformation

·         Rank Transformation

·         Transaction Control Transformation

·         Update Strategy Transformation

·         Normalizer Transformation

·         Source Qualifier Transformation

·         SQL Transformation.

 b). PASSIVE TRANSFORMATION

EXPRESSION TRANSFORMATION
SEQUENCE GENERATOR TRANSFORMATION
STORED PROCEDURE TRANSFORMATION
LOOKUP TRANSFORMATION (Upgradations in 9.X)
XML SOURCE Qualifier Transformation.

c). CONNECTED AND UNCONNECTED TRANSFORMATIONS.

d). FLAT FILES AND ITS TYPES.

POWER CENTER ADVANCED

1). Unconnected lookup Transformation
2).Unconnected Stored Procedure to Create and Drop Index
3). Mapplet And Types Of Mapplets
4). Defining Reusable Transformations.
5). User Defined Functions.
6). Tasks And Types Of Tasks.
7). Worklet and Types OfWorklets.
8). Scheduling Work flow
9). Constraint Based Laod Ordering.
10). Target Load Plan
11). Types Of Batch Processing
12). Link conditions
13).Mapping Parameters
14).Mapping Variables
15).Defining Session Parameters
17).Session Recovery
18). Difference Between Normal and Bulk Loading.
19). Push Down Optimization.
20). PMCMD Utility
21).PMREP Utility
22).Incremental Aggregation

TYPES of LookUp Caches.

·         Static LookUp Cache

·         Dynamic LookUp Cache

·         Persistence lookUp Cache

·         Shared LookUp Cache

24). Sorter and Rank Cache.

25). ETL UNIT Testing (8 test Cases)
26). ETL PERFORMANCE TESTING (3 test cases)
27). ETL Performance
Optimization techniques

Task Implementations. 

·         Session

·         Command

·         EMAIL

·         Timer

·         Event Wait

·         Event Raise

·         Decision

·         Assignment

·         Control

·         Worklet