5 Replies Latest reply: Sep 13, 2012 4:17 AM by Oleg RSS

    Finding Obsolete/Unused Mappings

      I recently started working on OWB and "inherited" whatever was left.

      I noticed a few mappings are not connected to anything, but visually clicking through and confirming that they are not contained in any process flows.

      The process flows are a bit large, and is cumbersome to click through.

      Would anyone know of any way to just do a "one click" way of checking for obsolete mappings?

      Thank you
        • 1. Re: Finding Obsolete/Unused Mappings
          you can check inclusion of mappings in processflows with OMB*Plus script like this:
          # list of process flow packages
          set pfpkgList {/ProjName/ProcessFlowModule/ProcessFlowPkg1 /ProjName/ProcessFlowModule/ProcessFlowPkg2}
          # list of Oracle modules with mappings which we want to check
          set moduleList {/ProjName/OracleModule1 /ProjName/OracleModule2}
          set pfAllMappings {}
          # compose list of mappings used in processflow
          foreach pfpkgName $pfpkgList {
              puts "Processing PF package $pfpkgName"
              OMBCC '$pfpkgName'
              set pfList [OMBLIST PROCESS_FLOWS]
              foreach pfName $pfList {
                  set mapActList [OMBRETRIEVE PROCESS_FLOW '$pfName' GET MAPPING ACTIVITIES]
                  foreach mapActName $mapActList {
                      set mapFullPath [OMBRETRIEVE PROCESS_FLOW '$pfName' ACTIVITY '$mapActName' GET REFERENCE]
                      set existFlag [lsearch $pfAllMappings $mapFullPath]
                      if {$mapFullPath=={}} { puts "Empty reference $pfName/$mapActName" }
                      if {$existFlag==-1} {
                          lappend pfAllMappings $mapFullPath
          foreach moduleName $moduleList {
              OMBCC '$moduleName'
              set mapList [OMBLIST MAPPINGS]
              foreach mapName $mapList {
                  set usedFlag [lsearch $pfAllMappings $moduleName/$mapName]
                  if {$usedFlag==-1} {
                      puts "Mapping $mapName is not used"
          Change variable pfpkgList/moduleList assignemnt according your needs

          • 2. Re: Finding Obsolete/Unused Mappings
            Sutirtha Roy
            Hi Oleg,

            Nice to see you back in OWB forum .

            p.s good job for the script .

            • 3. Re: Finding Obsolete/Unused Mappings
              Kurt Geens
              The same information can be retrieved with a query in the design repository schema:
              SELECT c.*
              FROM   all_iv_projects a
                     INNER JOIN all_iv_information_systems b
                       ON ( b.project_id = a.project_id )
                     INNER JOIN all_iv_xform_maps c
                       ON ( c.information_system_id = b.information_system_id )
              WHERE  a.project_name = :project_name
              AND    NOT EXISTS ( SELECT 1
                                  FROM   all_iv_projects d
                                         INNER JOIN all_iv_schemas e
                                           ON ( e.project_id = d.project_id )
                                         INNER JOIN all_iv_packages f
                                           ON ( f.schema_id = e.schema_id )
                                         INNER JOIN all_iv_processes g
                                           ON ( g.package_id = f.package_id )
                                         INNER JOIN all_iv_process_activities h
                                           ON ( h.process_id = g.process_id )
                                  WHERE  d.project_id = a.project_id
                                  AND    h.activity_type = 'MAPPING'
                                  AND    h.bound_object_id = c.map_id )
              ORDER BY c.map_name;
              Performance-wise this query might not be optimal though, I composed it quickly to give a general idea. It might run faster using a MINUS set operation to exclude the used mappings...

              Edited by: Kurt Geens on Aug 28, 2012 4:51 PM
              • 4. Re: Finding Obsolete/Unused Mappings
                Hey Kurt

                For this part
                a.project_name = :project_name
                For ":project_name" did you actually mean to put &project_name?
                Or something to the like?
                I tried running that query and I dont see any results being returned. I am assuming that would imply that all the mappings are connected to the project?
                @Oleg - Thats a wow! I havent touched/used OMB scripts before - where would I go about to run that?
                Thanks for the help so far !                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                • 5. Re: Finding Obsolete/Unused Mappings
                  I tried running that query and I dont see any results being returned.
                  I guess you need to "activate" OWB workspace (with WB_workspace_management.set_workspace procedure) before running SELECT with OWB public views:
                  Public Views for the Runtime Environment (chapter in OWB API and Scripting Reference doc)
                  I havent touched/used OMB scripts before - where would I go about to run that?
                  To run OMB scripts I prefer to use "OMB Plus" console application (you can find it from OWB program group in Windows, usually path to OMBPLus is OWB_HOME\owb\bin\win32\OMBPlus.bat). Before execution OMB scripts you should connect to workspace with command
                  OMBCONNECT <username>/<password>@<host>:<port>:<DB service name> USE REPOS '<workspace name>'