denormalize

[cognos11]

My source table looks like the following.And my target table has to be populated as shown below.
I cant use a normalizer here because visitid occurs only once with all the three values.Besides i cant use a router either.

Any suggestions as to how to do this.

visitid name value
10006795 website yahoochat
10006795 SIZE 728
10006795 PLACE north

10006799 website rocketmail
10006799 SIZE 729
10006799 PLACE south




Target Table
------------

visitid size place website

10006795 728 north yahoochat
10006799 729 south rocketmail

 

[blom0344]

Your data is normalized, you want to denormalise it. I think this transformation will be available in the latest versions, but for now you can (mis)-use aggregator/expression transformation to get the job done...

Use expression with outgoing ports using IIF statements and then use an aggregator with max() operator to get everything consolidated into one record again...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[cognos11]

T.Blom

Thanks for the solution.I need your opinion on this.

In the orinal process, we used to query the lookup table ie queryargs four times to get website value first and then size value second and so.This is because informatica will let you choose only one value from multiple matches when doing a lookup ie first or last value or it will throw an error.

visitid name value
101 website yahoochat
101 size big
101 place north
102 website mail
102 size small
102 place south


We are reviewing that process to find out if we could eliminate the looking up the table four times sinnce it is a huge table.

The new approach was to use that table as a source and then use a joiner transofrmation with the table visit whose a primary key is visitid.

Later on use aggreate and expression as suggested by you in earlier to populate the target table.

I would like to know if this is a better way of populating the targe than the earlier process of lookingup the same table multiple times.

Your thoghts on this appreciated.

 

[blom0344]

You can probably use an alternative scenario by creating a database view to de-normalize the data prior to reading it into a mapping, like:

create or replace view <view-name> as
select visitid,
case when name = 'website' then value else null end as 'website',
case when name = 'size' then value else null end as 'size',
case when name = 'place' then value else null end as 'place'
from <table>

This is general syntax !!

Use the view as source for the mapping and test whether this is a better solution. The expression/aggregation step will not be needed in this case....


T. Blom
Information analyst
tbl@shimano-eu.com

 

[alexle]

This is the exact information I'm looking for. However, can you help me to get an idea how to do it in Informatica ETL Mapping?

You were referring to Expression and Aggregator.

thank you.

 

[blom0344]

I returned your email with an xml example. Hope this will get you under way....

Ties Blom
Information analyst