Why DAC 11g(Oracle BI Data Warehouse Administration Console) synchronization fails

 People who are working on DAC(Oracle BI Data Warehouse Administration Console), for the first time face this issue. Below are the exact points which will resolve the problem.

 In this post, you will get an explanation of why DAC 11g(Oracle BI Data Warehouse Administration Console) synchronization fails.

  1) If, PATH environment variable is not set for pmrep and pmcmd.
  2) If DAC installation directory contains spaces.(space character)
  3) Infa_int and Infa_rep connection should be successful.


1) If, PATH environment variable is not set for pmrep and pmcmd.

      

D:\%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Intel\OpenCL SDK\3.0\bin\x86;C:\Program Files (x86)\Intel\OpenCL SDK\3.0\bin\x64;D:\Informatica\9.1.0\server\bin;D:\Oracle11g\app\mvenkata\product\11.2.0\client_1\bin;C:\Program Files (x86)\Lenovo\Access Connections\;C:\Program Files (x86)\Common Files\lenovo\easyplussdk\bin

Symptom: Without the above environment variable, pmcmd and pmrep will not invoke as below.

  So after setting up the above environment variable close the command prompt and open it again. Verify pmcmd and pmrep commands as below. It will invoke as below.

2) If DAC installation directory contains spaces.(space character)

 C:\Program Files (x86)\Oracle Business Intelligence Data Warehouse Administration Console 11g

  If the DAC installation directory contains space character as mentioned above, while doing synchronizing the tasks you will simply get a window saying "success". But the source and target tables will not be added to the tasks.

 But when you debug the issue, you will be stopped here.

 With the error "C:\Program could not be opened for read.", we can say that, DAC is not able to read the file after space found.

 So make sure your installation directory should not contain any spaces.

3) If Infa_int and Infa_rep connections are not successful.

 Make sure the connections are successful that we create "SETUP" tab, "Informatica servers" tab.

Related links:

Unable to synchronize task in DAC(Oracle BI Data Warehouse Administration Console)

DAC task synchronization failed with the error Whitespace required

Full DAC Metadata or Repository tables part 3

Part 3: System Tab related metadata tables:

 In this post you will get more information about Execute tab related metadata tables of DAC(Oracle BI Data Warehouse Administration Console).

1) All execution plans     -    W_ETL_DEFN

    a) Subject areas associated to the EP    -  W_ETL_DEFN_SA
    b) Tables belongs to the EP                 -  W_ETL_DEFN_TBL
    c) All tasks involved in the EP              -   W_ETL_DEFN_STEP 
    d) Connectivity parameters for this EP   - W_ETL_DEFN_PRM

2) Current Run / Run History -   W_ETL_DEFN_RUN

    a) Task belongs to the EP                  -   W_ETL_RUN_STEP
    b) Details for a tasks in EP                -  W_ETL_RUN_SDTL
    c) Audit trial (No.of time EP restarted) -  W_ETL_RUN_AUDIT
    d) Phase summary                            - W_ETL_RUN_STEP
    e) Run type summary                       -   W_ETL_RUN_SDTL

3) EP Schedule information           -  W_ETL_SCHEDULE

Related links: 

DAC Metadata or Repository tables part 2

DAC Metadata or Repository tables part 1

Full DAC Metadata or Repository tables part 2

Part 2: System Tab related metadata tables:

 In this post you will get more information about system tab related metadata tables of DAC(Oracle BI Data Warehouse Administration Console).

1) DAC system properties    -  W_ETL_SYSPROP

2) Informatica servers         -  W_ETL_SERVER

3) All Physical data sources  - W_ETL_DBCONN

  a) Tables associated to Physical data souce and its "Refresh dates" information                       -    W_ETL_REFRESH_DT

4) E-mail recipients           -  W_ETL_EMAIL

5) Working patches           - W_ETL_PATCH

(Patches created information will be stored here. 

   a) Patch content            -  W_ETL_PATCH_DATA
   b) Child patches            -  W_ETL_PATCH_CHILD

6) Applied patches           -   W_ETL_APATCH

   a) Applied patch content           -  W_ETL_APTC_DATA
   b) Applied child patches            -  W_ETL_APTC_CHLD
   c) Deleted objects by this patch - W_ETL_APTC_DEL

Related links:

DAC Metadata or Repository tables part 1

DAC Metadata or Repository tables part 3

Full DAC Metadata or Repository tables part 1

Part 1: Design Tab related metadata tables: 

 In this post you will get more information about design tab related metadata tables of DAC(Oracle BI Data Warehouse Administration Console).

1) All Containers          -  W_ETL_APP
2) All User accounts     -  W_ETL_USER
3) All Subject areas      -  W_ETL_SA
  
   a) All task related to subject area     - W_ETL_SA_STEP
   b) All tables related to subject area   - W_ETL_SA_TABLE

4) All Tables               -  W_ETL_TABLE

   a) All columns for a table                 -  W_ETL_TABLE_COL
   b) All indexes associated for table     -  W_ETL_INDEX
   c) All source/Target for a task           -  W_ETL_STEP_TBL

5) All Indexes             -  W_ETL_INDEX

   a) Columns associates to a index    - W_ETL_INDEX_COL

6) All Task groups       - W_ETL_STEP
  
   a) Associates child tasks - W_ETL_GROUP_STEP

7) All Tasks                -  W_ETL_STEP
   
   a) All Source/Target tables associates to tasks - W_ETL_STEP_TBL
   b) Task associated to a subject areas             -  W_ETL_SA_STEP

8) All Configuration Tags - W_ETL_TAG

   a) All subject areas associated to this CT     -    W_ETL_TAG_SA
   b) All tasks associated to this CT                -    W_ETL_TAG_STEP

9) All source system parameters   -   W_ETL_PARAM

10) All source system folders       -   W_ETL_PARAM

11) Container specific SQL           -   W_ETL_APP_TMPLT

Related links:

DAC Metadata or Repository tables part 2

DAC Metadata or Repository tables part 3

Listing Informatica objects with Query Tool

Issue: List all Informatica objects(Mappings, Sessions, Workflows etc)

Solution: Use inbuilt "Queries" option in Informatica Powercenter Repository Manager tool.

Some of the uses of this "Queries" option.

a) Create list of objects such as Mappings, sessions, workflows etc.
b) Objects with their status( Valid or Invalid).

 In this post I will get the list of workflows in a particular folder using this option. Below are the steps to carryout this activity.

Step 1: Login to "Informatica Powercenter Repository Manager".

Step 2: From the file menu, go to "Tools" and select "Queries".

Step 3: From the file menu, go to "Tools" and select "Queries".

Step 4: Click on the "New" button to create a query and start composing a query like this.

Note: - Use  these button to create a query.

Step 5: Click on the "Execute" button to list the required objects and the output will be displayed as below.

Step 6: Click on the "Save" button to save the list. You can later process this data in to an Excel sheet.

Related links:

Analyzing Informatica objects properties with Excel