0 Replies Latest reply on Jul 24, 2013 4:15 AM by nheinze

    How to: import Metadata Exchange views into PowerCenter Designer

    Guru
      Dear all,
      this is not a question but instead sort of a How-To.My original post about this matter has vanished some time ago.
      In order to enable any interested person in gaining this piece of knowledge here's a re-post.
      If you have any questions, please don't hesitate to ask. That's what we're here for.
      Regards,
      Nico
      -----------------------------------------------------------------------------------
      The PowerCenter Designer does not allow a user to import the  Metadata Exchange views (MX views) from a repository database. This  feature is a safety measure to keep use  from inadvertedly messing up  repository contents.

       

      Sometimes, however, it makes se e to source the MX views because  they contain loads of information useful e.g. for auditing purposes  (which workflow ran from when until when, how may rows have been  processed by session A, and so forth). In such cases one would like to  import one or the other MX view as a Source Definition. However, the  Designer doesn't allow such action ; it filters  out the names of all  database tables and views associated with a PowerCenter repository; this  list is hard-coded, so no user can circumvent this behaviour.

       

      One way to get the MX views into the Designer would be to type in  all attributes together with their data types in the Source Analyzer. Of  cou rse this process is highly error-prone, daunting, and extremely  lengthy.

       

      An alternative is to utilise a feature offered by most modern DBMS,  namely the so-called synonyms (in Oracle) or nicknames (in IBM DB2).  Here's how to go forward in order to retrieve all the MX views as source  definitions :

       

      1. From the respective repository DBMS, retrieve a list of all views  in the repository schema as a text file. Under Oracle, you can get a  list of all these views via a query like SELECT view_name FROM user_views (assuming you're logged on to Oracle as the repository DB user); under DB2, you can use a query like SELECT name FROM sysibm.sysviews WHERE creator = 'PCREPO' (this sample assumes that the repository DB owner is named PCREPO).
      2. Edit this text file such that every line finally reads like in this example (Oracle syntax):
        CREATE PUBLIC SYNONYM mxrep_all_tasks FOR rep_all_tasks;
      3. Of course you can build a small mapping for step #2, you don't have to do that in a text editor.
      4. Add statements to this text file which grant SELECT permission to  all these public synonyms to a DB user ID which you are using to import source  definitions  in the Source Analyzer of the PowerCenter Designer.
      5. Under DB2, do not forget to add the command COMMIT WORK; at the end of the text file.

      6. Execute this text file as a DDL script in the repository database.

      7. Open the PowerCenter Designer and a (preferrably shared) folder in the Source Analyzer.

      8. In the Source Analyzer, start an import from the repository  database. The views named like MX* should all be visible.  Import them all or import as many as you need for your purposes.
      9. If you have imported e.g. 10 MX views, you can now simply edit them  in the Source Analyzer and remove the prefix MX from their names.
      10. If you have imported many or all of the synonyms for MX views, a  faster way to correct their names would be to export them all to XML  files; edit these XML files (e.g. using a PowerCenter mapping with a  flat file source and a flat file target) and remove the prefix MX from  all the view names; and import the changed XML files.
      11. Now remove the source definitions  with names MX* from the repository.

      12. Finally remove the synonyms from the repository database.

       

      Of cours e there is no need to create public synonyms for the MX  views. It is fine to have normal synonyms. Just make sure that the user  ID you use to import objects from the repository DBMS has DB permission  to retrieve the view names / synonym names and to read the system  catalog for the metadata of these synonyms (in particular attribute  names and data types).