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.

54 comments:

HealthMan 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.

HealthMan 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.

Unknown 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

Unknown 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

Unknown 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.

Unknown said...

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

Anonymous 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.

Unknown said...


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

INFORMATICA INTERVIEW QUESTIONS 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

Unknown 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

Unknown 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


Unknown said...

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

gowsalya said...

Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
Digital Marketing Training in Chennai

Digital Marketing Training in Bangalore

digital marketing training in tambaram


digital marketing training in annanagar

digital marketing training in marathahalli


ragul ragul said...

It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
Blueprism training in annanagar

Blueprism training in velachery

Blueprism training in marathahalli


AWS Training in chennai

shalinipriya said...

I’ve desired to post about something similar to this on one of my blogs and this has given me an idea. Cool Mat.

Data Science Training in Chennai
Data science training in bangalore
Data science online training
Data science training in pune

Unknown said...

A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.I wanted to leave a little comment to support you and wish you a good continuation. Wishing you the best of luck for all your blogging efforts read this.
java training in jayanagar | java training in electronic city

java training in chennai | java training in USA

Anonymous said...

Your very own commitment to getting the message throughout came to be rather powerful and have consistently enabled employees just like me to arrive at their desired goals.

angularjs Training in bangalore

angularjs Training in bangalore

angularjs Training in btm

angularjs Training in electronic-city

angularjs online Training

Ishu Sathya said...

After reading your blog, I was quite interested to learn more about this topic. Thanks
Selenium Training in Chennai
Best Selenium Training Institute in Chennai
ios developer training in chennai
Digital Marketing Training in Chennai
web development courses in chennai with placement
Web designing training centers in chennai

afiah b said...

Its really an Excellent post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog. Thanks for sharing....
Java training in Pune

Java interview questions and answers

Java training in Chennai | Java training institute in Chennai | Java course in Chennai

Java training in Bangalore | Java training institute in Bangalore | Java course in Bangalore

sunshineprofe said...

Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
fire and safety course in chennai

Unknown said...

Thanks for your informative article, Your post helped me to understand the future and career prospects & Keep on updating your blog with such awesome article.

Data Science Training in Chennai | Best Data science Training in Chennai
Data Science training in kalyan nagar
Data science training in Bangalore | Data Science training institute in Bangalore
Data Science training in marathahalli | Data Science training in Bangalore
Data Science interview questions and answers
Data science training in jaya nagar | Data science Training in Bangalore

genga g said...

You got an extremely helpful website I actually have been here reading for regarding an hour. I’m an initiate and your success is incredibly a lot of a concept on behalf of me.
angularjs-Training in velachery

angularjs Training in chennai

angularjs-Training in pune

angularjs-Training in chennai

angularjs Training in chennai

angularjs Training in chennai

lekha mathan said...

I have gone through your blog, it was very much useful for me and because of your blog, and also I gained many unknown information, the way you have clearly explained is really fantastic. Kindly post more like this, Thank You.
Air hostess training in Chennai
Air Hostess Training Institute in chennai
air hostess academy in chennai
air hostess course in chennai

siva said...

The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea.
here by i also want to share this.
Java training in Chennai

Java training in Bangalore

Java online training

Java training in Pune

tamilsasi said...


Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you.
Keep update more information..


Selenium training in bangalore
Selenium training in Chennai
Selenium training in Bangalore
Selenium training in Pune
Selenium Online training
Selenium interview questions and answers

jeeva said...

This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.. 
microsoft azure training in bangalore
rpa training in bangalore
best rpa training in bangalore
rpa online training

priya said...

Well you use a hard way for publishing, you could find much easier one!
Microsoft Azure online training
Selenium online training
Java online training
Java Script online training
Share Point online training

Jaweed Khan said...

I Regreat For Sharing The information The InFormation shared Is Very Valuable Please Keep Updating Us Time Just Went On Reading The Article Python Online Training AWS Online Training Hadoop Online Training Data Science Online Training

Tuhin said...

http://radhakrishnasarma.blogspot.com/2009/04/dynamically-generate-parameter-files.html

shri said...

good blogggssss...!
internship in chennai for ece students
internships in chennai for cse students 2019
Inplant training in chennai
internship for eee students
free internship in chennai
eee internship in chennai
internship for ece students in chennai
inplant training in bangalore for cse
inplant training in bangalore
ccna training in chennai


saran said...

"This blog is very nice and the author written way was very good with a brief explanation. Well done...!
.
Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery

"

Anonymous said...

Женские кожаные сумки сумка michael – это образец крутости и стиля. При их производстве используется итальянская кожа и фурнитура, что создает изделиям надежность. Структура удивляет многообразием текстур: кроме глянцевых и матовых присутствуют тканевые варианты, с рисункомм под игуану или перфорированным рисунком. Надежная фурнитура делает изделия красивыми. За стильное оформление отвечает бригада лучших специалистов, которая наблюдает за современными трендами.

Rashika said...

Great information!! Thanks for sharing nice blog.


Digital Marketing Training in Chennai | Certification | SEO Training Course | Digital Marketing Training in Bangalore | Certification | SEO Training Course | Digital Marketing Training in Hyderabad | Certification | SEO Training Course | Digital Marketing Training in Coimbatore | Certification | SEO Training Course | Digital Marketing Online Training | Certification | SEO Online Training Course



Anonymous said...

Номенклатура действий, сориентированных на прогнозирование будущего, называют хиромантия. Загадочны силы и конкретные обстоятельства ворожения наукой не описаны, при том многочисленные люди доверяют подобному. Точное гадание на картах - это обыденный метод поворожить с употреблением всяких объектов и рецептур.

Anonymous said...

Керамическая превосходно подходит к любому интерьеру. Наилучший выбор плитки может прикрасить любую коридор или санузел. Сосредоточьтесь не только на декоративной плитке чужестранного изготовления, а также на российские образцы. Выбирая такой продукт как, например - emigres leed плитка стоит отобрать максимальное число идей.

Anonymous said...

Только ответственные компании осуществляют свои услуги на платформе «Skoro Dengi». Обратившись к площадке займ онлайн с плохой ки, вы сможете самостоятельно отобрать требуемые предложения по кредиту. Получайте кредит на карту на любые цели.

Anonymous said...

Ворожба дает предположить, что вас подстерегает в ближайшем времени. Контакты таролога это способ увидеть будущие события постоянно привлекал человека. Каждый жаждет узнать свою судьбу и представляет конкретные средства хиромантии наиболее действенными.

Anonymous said...

Только лишь на текущей площадке casino x официальный сайт вы имеете возможность окунуться в сказочные приключения. Рулетка – самый-самый популярный игровой аппарат среди азартных игрушек Casino X. Интуитивное меню и мгновенный поиск – это уверенный путь подобрать необходимую виртуальную игру.

Anonymous said...

Сервисная служба в любое время поможет советом и легко решит очень сложную задачу. Увидеть достоверность сайта можно на официальной странице casino-x-oficialniy-sayt.com. Помните, что зеркало сайтов казино икс ни при каких условиях не возьмет деньги за выполнение сеанса верификации пользователей.

Anonymous said...

Кибер Спорт – это следующий этап становления высокопрофессиональных турниров. Люди спорят в тактике, формируют стопроцентные команды и побеждают в турнирах. Посмотрев mr bit отзывы вы попадете в удивительный мир игр по сети. Игры молодого поколения с огромными подарками.

Anonymous said...

Особая твердость обеспечивает износостойкость покрытия на года. Горная порода, как первичный компонент, не подвергается плавлению. При возникновении горения axima budapest не испаряет ядовитых веществ.

Anonymous said...

Плитку возможно использовать в залах организаций общественного питания, в школах и детских садиках. Керамику pamesa essential создают исключительно из естественных исходников. Нетоксический материал, практически безопасен для человека.

Anonymous said...

Любые вариации ворожения называют как астральные науки. Определенный образ хиромантии эксклюзивен и необходим для разных задач. Гадание на беременность на рунах беременна ли я и точность предзнаменований прямолинейно зависит от практики гадающего. Каждый мечтает знать свою судьбу и воспринимает конкретные методы хиромантии по максимуму действенными.

Anonymous said...

Медиапроекторы значатся отличным аналогом плазме, а также предоставляют несколько уникальных преимуществ. Проекторы игровые, указанные на платформе «Проектор 24», – это идеальный вариант, который вы можете себе представить! В новейшем интернет-магазине покупатель может выбрать нужное устройство на любой бюджет.

kavya said...

Hi,
Thanks for sharing, it was informative. We play a small role in upskilling people providing the latest tech courses. Join us to upgradeORACLE APEX ONLINE TRAINING

Asha Kaashiv said...

Nice Blog Looking for Internship Details Click Here MSC Online Internship in Chennai , Internship for MBA Students , Online Internship for MBA Students , Summer Internship for MBA Students

Full stack developer said...

Thank you for the information – it was enlightening. We contribute to upskilling individuals by offering cutting-edge tech courses. Consider joining us to enhance your skills.

PYTHON FULLSTACK TRAINING IN HYDERABAD CERTIFICATION TRAINING

Full stack developer said...

it was enlightening. We contribute to upskilling individuals by offering cutting-edge tech courses. Consider joining us to enhance your skills.

python training in Hyderabad

 
HTML Hit Counters