Wednesday, April 22, 2009

Dynamically generate parameter files

I've seen this in many places, but just want to tell you the way I have been doing it for few years now. I will take the context in a bit more detailed manner to make every one understand it.

1. PowerCenter objects – Introduction:

• A repository is the highest physical entity of a project in PowerCenter.


• A folder is a logical entity in a PowerCenter project. For example, Customer_Data is a folder.


• A workflow is synonymous to a set of programs in any other programming language.


• A mapping is a single program unit that holds the logical mapping between source and target with required transformations. A mapping will just say a source table by name EMP exists with some structure. A target flat file by name EMP_FF exists with some structure. The mapping doesn’t say in which schema this EMP table exists and in which physical location this EMP_FF table going to be stored.


• A session is the physical representation of the mapping. The session defines what a maping didn’t do. The session stores the information about where this EMP table comes from. Which schema, with what username and password can we access this table in that schema. It also tells about the target flat file. In which physical location the file is going to get created.


• A transformation is a sub-program that performs a specific task with the input it gets and returns some output. It can be assumed as a stored procedure in any database. Typical examples of transformations are Filter, Lookup, Aggregator, Sorter etc.


• A set of transformations, that are reusable can be built into something called mapplet. A mapplet is a set of transformations aligned in a specific order of execution.

As with any other tool or programing language, PowerCenter also allows parameters to be passed to have flexibility built into the flow. Parameters are always passed as data in flat files to PowerCenter and that file is called the parameter file.

2. Parameter file format for PowerCenter:

For a workflow parameter which can be used by any session in the workflow, below is the format in which the parameter file has to be created.

[Folder_name:WF.Workflow_Name]
$$parameter_name1=value
$$parameter_name2=value

For a session parameter which can be used by the particular session, below is the format in which the parameter file has to be created.

[Folder_name:WF.Workflow_Name:ST.Session_Name]
$$parameter_name1=value
$$parameter_name2=value


3. Parameter handling in a data model:

• To have flexibility in maintaining the parameter files.


• To reduce the overhead for the support to change the parameter file every time a value of a parameter changes


• To ease the deployment,
all the parameters have to be maintained in Oracle or any database tables and a PowerCenter session is created to generate the parameter file in the required format automatically.


For this, 4 tables are to be created in the database:

1. FOLDER table will have entries for each folder.


2. WORKFLOWS table will have the list of each workflow but with a reference to the FOLDERS table to say which folder this workflow is created in.


3. PARAMETERS table will hold all the parameter names irrespective of folder/workflow.


4. PARAMETER_VALUES table will hold the parameter of each session with references to PARMETERS table for parameter name and WORKFLOWS table for the workflow name. When the session name is NULL, that means the parameter is a workflow variable which can be used across all the sessions in the workflow.

To get the actual names because PARAMETER_VALUES table holds only ID columns of workflow and parameter, we create a view that gets all the names for us in the required format of the parameter file. Below is the DDL for the view.


a. Parameter file view:


CREATE OR REPLACE VIEW PARAMETER_FILE
(
HEADER,
DETAIL
)
AS
select '['fol.folder_name'.WF:' wfw.workflow_name']' header
,pmr.parameter_namenvl2(dtl.logical_name, '_'dtl.logical_name, NULL)'='
dtl.value detail
from folder fol
,parameters pmr
,WORKFLOWS wfw
,PARAMETER_VALUES dtl
where fol.id = wfw.folder_id
and dtl.pmr_id = pmr.id
and dtl.wfw_id = wfw.id
and dtl.session_name is null
UNION
select '['fol.folder_name'.WF:' wfw.workflow_name'.ST:' dtl.session_name']' header
,decode(dtl.mapplet_name, NULL, NULL, dtl.mapplet_name'.')
pmr.parameter_namenvl2(dtl.logical_name, '_'dtl.logical_name, NULL)'=' dtl.value detail
from folder fol
,parameters pmr
,WORKFLOWS wfw
,PARAMETER_VALUES dtl
where fol.id = wfw.folder_id
and dtl.pmr_id = pmr.id
and dtl.wfw_id = wfw.id
and dtl.session_name is not null


b. FOLDER table

ID (NUMBER)
FOLDER_NAME (varchar50)
DESCRIPTION (varchar50)


c. WORKFLOWS table

ID (NUMBER)
WORKFLOW_NAME (varchar50)
FOLDER_ID (NUMBER) Foreign Key to FOLDER.ID
DESCRIPTION (varchar50)


d. PARAMETERS table

ID (NUMBER)
PARAMETER_NAME (varchar50)
DESCRIPTION (varchar50)


e. PARAMETER_VALUES table

ID (NUMBER)
WF_ID (NUMBER)
PMR_ID (NUMBER)
LOGICAL_NAME (varchar50)
VALUE (varchar50)
SESSION_NAME (varchar50)

• LOGICAL_NAME is a normalization initiative in the above parameter logic. For example, in a mapping if we need to use $$SOURCE_FX as a parameter and also $$SOURCE_TRANS as another mapping parameter, instead of creating 2 different parameters in the PARAMETERS table, we create one parameter $$SOURCE. Then FX and TRANS will be two LOGICAL_NAME records of the PARAMETER_VALUES table.

• m_PARAMETER_FILE is the mapping that creates the parameter file in the desired format and the corresponding session name is s_m_PARAMETER_FILE.

20 comments:

Pratip Sen said...

Hi,
Its great to see your posts.
I have a little query: In one of my mappings I used a "Mapping variable". I assigned a value say 100 using SET variable function and then ran the corrosponding workflow.
This value 100 was retrived from database.

Next I changed the database value 100 to say 50 and reran the workflow.

To my surprise this, the worflow still used the same 100 instead of the new 50. I did a google on this and found that the repository stores the value. The only way that this could be reset was to checkout the WF - view the"persistent values" of the corr session" - reset and check.

Now this is not a valid solution.

Do you have any idea how this could be done at runtime? I mean something that it will reset the variable everytime the session runs?

Note, I am using aggregation type as MAX and expression as FALSE in the mapping parameter/variable definations.

Radhakrishna Sarma said...

Hi Pratip,

Thank you for visiting my blog article. When you say you have changed the value in the database, which database? Repository database? Also, how are you assigning the value to the variable? Is it in the mapping?

Cheers
Sarma.

Pratip Sen said...

i meant changing the database value... not the repository

assignment done using setvariable command

Radhakrishna Sarma said...

Hi pratip,

From what you are saying, I don't see any reason why PowerCenter should retain the same value. Try to assign some constant value to the variable and try to run it?

setvariable($$variable_name, 99)


Cheers
Sarma.

karthikeyan said...

Hey,
Can u exlpain me elaborately on the diff types if cache and how it works.It wl b nice if u provide me with some examples.

Thank you

Warm regards
Informatican.

Winkey Reddy said...

Appreciate and very good post. I have understood now.Keep Going.

-Winkey

krishna said...

Hi Radhakrishna sir

I need to run the workflow daily
i want to filter the every yesterdays data fo that
i need to setup startdate and enddate
in paramter file. so i write it once it will work for one day
for the next day i need to change again the dates. this is my problem i want to automate it means without changing the paramter file how can i get yesterday data........ my thought is like when i am filtering is it possible to write systemdate-1
like this

Thanks in Advance

robert said...

I am William..I just browsing through some blogs and came across yours! Excellent blog, good to see someone actually uses for quality posts. Your site kept me on for a few minutes unlike the rest :) Keep up the good work!Thanks for sharing a important information on informatica

srikanth said...

Iam really satisfy by your information.
It's well-written, to the point, and relative to what I do.
I like it very much for giving information on
Excellent Informatica Online Training
.

Shiva Ram said...

Hi Radhakrishna,

Excellent. This is what I was looking for sometime. Well explained, but here is my question. Where do you draw the line for parameterization? I am in a situation that, I want to avoid the hardcoding as much as possible in my maps (like filters , or reference to any columns like Status Code='XYZ' and what not). But this approach will work only, if you feel that

(1) the parameters might change between environments
(2) the underlying values in the business might change tomorrow , because the source system started sending a new set of values

What other benefits does it provide?

Once again, thanks.

Anonymous said...

ESTIMADOS HERMANOS:
La razon de mi oprobio sexual con mi país Guatemala debe a mi prefecto parafílico de hostigarme necrofílicamente por las impostoras de mi abnegacion célibe durante 21 años de percance apologal por mis conspiradores polares del vórtice virtual que me explora esotéricamente para denigrarme con la especulacion bastarda. Prefecto se refiere según mi teoría a un adjetivo que no es defecto ni tampoco perfecto. Por lo tanto el blog del SAI BABA ENCARNACION DIVINA comete una aberracion por denegarme del servicio divino en el sitio AMILIUS-SAIBABA.BLOGSPOT.COM como tambien mi kama sutra parafílico y mi tantrismo parafílico consagrados con las hechiceras hindúes del brahmantismo empezando por la hechiera hindú del sector de las margaritas de residenciales San José del municipio de San José Pinula del departamento de Guatemala de mi país Guatemala de la América Central y finalizando con la hechicera hindú del avatar de la Diosa Kalí denominada NANDU PARVATI DEVI DASI quien es directora del templo de HARE KRISHNA de mi país Guatemala de la América Central.

Atentamente:
Jorge Vinicio Santos Gonzalez,
Documento de identificacion personal:
1999-01058-0101 Guatemala,
Cédula de Vecindad:
ORDEN: A-1, REGISTRO: 825,466,
Ciudadano de Guatemala de la América Central.

Ankit Kansal said...

Thank you for sharing this informative article, however I found this article on informatica best practices for cleaner development

babbo said...

Thank you for your efforts here. Do you or anyone reading know how to query the metadata table to confirm the input for an expression? For example if I have an input field i_Fieldname for an expression and the output field is o_Fieldname and o_Fieldname = LTRIM(RTRIM(i_Fieldname)) how can I show from a sql query that o_Fieldname is actually downstream of i_Fieldname? The designer shows that connection when you right click "select link path - backward" I dont know how its able to do that. Besides trying to parse the expression and find the i_Fieldname.

Rashmitha Reddy Bangaru said...


Good points, all.
http://www.informaticaonlinetraining.co/

Venkat M said...

Best Informatica Online Training By 9+Years Of Realtime Expert

Below is the link for Course Content and Demo Class

https://informaticaonlinetraing.blogspot.com

Priya Sethuraman said...

Everything is fine, am happy about your blog. Thanks admin for sharing the unique content, you have done a great job I appreciate your effort and I hope you will get more positive comments from the web users.
Hadoop Training in Chennai
Hadoop Training
Best Hadoop Training in Chennai
Best Hadoop Training Institute in Chennai

VYCET ....EEE said...

Hi,

Thanks for all your info.

Could please post unix scripts for ETL Coding standards

Arun Kumar said...

I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Informatica , kindly contact us http://www.maxmunus.com/contact
MaxMunus Offer World Class Virtual Instructor led training on Informatica. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.

For Free Demo Contact us:
Name : Arunkumar U
Email : arun@maxmunus.com
Skype id: training_maxmunus
Contact No.-+91-9738507310
Company Website –http://www.maxmunus.com


David h said...

Nice Information provided, please visit below for more information
Informatica Online Training

sunitha vishnu said...

It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
Android Training in Chennai
Ios Training in Chennai

 
HTML Hit Counters