Category Icon
The Treasure Trove Called Informatica Repository

Posted on February 14, 2008 by Thomas
Filed Under Data Warehousing, Informatica, Tips 'n Tricks |

If you’re an Informatica PowerCenter specialist, you’ll agree with me that there is a lot of fun stuff you can do if you’re willing to take a step beyond its comfortable graphical interface. Let’s face it: a tool that has made itself so completely metadata-driven is just asking for some serious tweakage.

Take the PowerCenter Repository, for instance. As you will know, the Repository serves as the central metadata library. It is a collection of database tables that, basically, contain any and all components, settings and variables that make up the grand and complex design of your ETL solution.

Want to know which mappings make use of that source definition you want to change? Repository. Interested in checking if everyone is adding descriptions to their transformations like they promised? Repository. A list of invalid sessions per folder? Total memory reserved per session? Yup, you guessed it.

So, how come it is so hard to find interesting queries that make use of this? Googling for “Powercenter Repository SQL” will get you preciously few interesting hits and asking the question on the appropriate forums only results in stone-walled silence. Either us developers don’t care (which I doubt), don’t know (which I doubt even more), or we are collectively keeping our cards to our chests. Interesting.

Of course, Informatica has been so kind to provide a number of standard queries, the so-called MX Views, to help out, but that’s mostly basic stuff. A tease to spark your interest.

With a little time and effort, SQL statements on your repository will greatly improve your ability to automatically perform standard mapping- and session tests, enable you to determine the impact of change requests and upgrades, and add just a little bit of sunshine to the day of any data warehouse administrator. Leaving your hands free for the stuff that is really interesting.

And for those who were good enough to read the entire article, an encore:

Check for invalid workflows:
select opb_subject.subj_name, opb_task.task_name
from opb_task, opb_subject
where task_type = 71 –workflows
and is_valid = 0 –invalid
and opb_subject.subj_id = opb_task.subject_id
and UPPER(opb_subject.SUBJ_NAME) like UPPER(‘FOLDERNAME’)
order by 1,2

Check if command tasks in a workflow only run if the previous session completed succesfully:
select distinct opb_subject.subj_name, opb_workflows.task_name
, DECODE(opb_sessions.task_name, NULL, ‘Irrelevant, Direct’, opb_sessions.task_name), opb_commands.task_name
from opb_task opb_commands
, opb_task opb_sessions
, opb_task opb_workflows
, opb_subject
, opb_task_attr
, opb_component
, opb_task_inst
where opb_commands.task_type = 58
and opb_commands.subject_id = OPB_SUBJECT.SUBJ_ID
and opb_commands.TASK_ID = opb_task_attr.task_id
and opb_task_attr.attr_id = 1 – the check box
and to_char(opb_task_attr.attr_value) <> ‘1′ – checked = 1, not checked = 0
and opb_commands.task_id = opb_component.ref_obj_id (+)
and opb_component.TASK_ID = opb_sessions.task_id (+)
and DECODE(opb_sessions.task_id, NULL, opb_commands.task_id, opb_sessions.task_id) = opb_task_inst.task_id
and OPB_TASK_INST.WORKFLOW_ID = opb_workflows.task_id
and UPPER(opb_subject.SUBJ_NAME) like UPPER(‘FOLDERNAME’)
order by 1,2,3,4

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Fark
  • Technorati
  • del.icio.us
  • Google
  • StumbleUpon
Email This Post Email This Post

Comments

10 Responses to “The Treasure Trove Called Informatica Repository”

  1. Maralynda on March 24th, 2008 12:41 pm

    thats it, bro

  2. Gilbertacu on April 5th, 2008 10:14 pm

    well done, guy

  3. Chaitanya on April 24th, 2008 6:37 pm

    I didn’t find these tables. Can any one pls tell me where I can find the tables which are in the queries.

    Thanks,
    Chaitanya.

  4. Thomas on April 24th, 2008 8:57 pm

    You will need access to the Informatica repository, which is a separate database created when you installed Powercenter.

    If you’re working on a big project, you will have to sweet-talk your way past the DBA. Messing with the Repository (i.e. executing anything other than innocent read-only queries) means instant death for all ETL involved, so username/pwds can sometimes be jealously guarded secrets.

  5. Chaitanya on April 25th, 2008 7:26 pm

    Thx Thomas for Replying.
    I’am working in a project.
    I tried to find out thr rep tables in the database. May be it’s because of the reason told by you that I didn’t find.Actually I’am trying to develop some add on like how many sessions running, what are those…that kind of stuff. So, for this I need to query rep tables. so, I think as per u I need to contact my admin to view those tables rt!
    Can u pls suggest what kind of extra stuff that I can do in Informatica which I can prove my uniqueness in my project.
    ur help is appreciated.

    Thank you,
    Chaitanya.

  6. Malini on May 6th, 2008 12:25 pm

    Hello.
    That is real good post. Very informative.
    I was trying to find out from Informatica Repository , what are the sessions that have truncate table option checked for target.
    But in vain. :(

    Could any of you please let me know how can i get that?

    Thanks a lot
    Malini

  7. Lewis Kwan on May 9th, 2008 5:15 pm

    Very nicely done and very helpful.

  8. Paul Laman on July 1st, 2008 9:41 am

    I also noticed that it’s hard to find Rep queries, so I wrote some myself and even built a BO universe on top of the PowerCenter repository to create a neat report on a mapping. A few standards are required for this: developers should fill in some description fields and some metadata extensions (for example we store change logs in there). The reports query the repository recursively, starting from the source definitions.

    The major problem with PC is that you can override many things at many levels, which forces you to look in various tables and combine the results. This, together with the need of recursive queries (to my knowledge only supported by Oracle), makes it hard to create a generic solution.

    Also I have not been able to find documentation on the repository internals (data model), so for some functionality I just had to do an intelligent guess.

    To help some out, I’ll see if I can post some of the queries I developed.

  9. LOGASUBRAMANI on August 20th, 2008 6:44 am

    Hi,

    Could you please let me know ,in which repository table or repository view “Fail parent if this task failed” is captured?

    I verified most of the repository views an tables, i dont see this information.

    Please help me in this regard.

    Regards,
    Logasubramani

  10. Ruhi on October 2nd, 2008 3:33 pm

    Hi Everyone!!
    I am new to informatica..going through the learning process. Iam thinking of creating a BO XI Report which will use the information present in the Informatica Repository Tables which are not useful from the stand-alone tables but can give useful information when data from multiple tables is clubbed into one. Can anyone help some me build some sql’s for this..like
    1.identifying a source and target of a session. 2.Identifying schemas of a source and target.

    Please help..
    Thanks

Leave a Reply