INSTALLATION DOCUMENTS BY RAVI

Wednesday, April 12, 2017

How to manually delete the source system in BI Application Configuration Manager (BIACM) OBIA

To manually delete the source system configured in BIACM console OBIA 11.1.1.x.x:

1. Connect to the application database through a utility such as SQL*Plus or SQL Developer, logged in as the same user configured in the WebLogic data source.

2. Find the source system identifier number of the source system you want to delete by executing the following SQL and looking at the value of column DATASOURCE_NUM_ID:

SELECT DATASOURCE_NAME, DATASOURCE_NUM_ID FROM C_DATA_SOURCE;

3. Go to ODI Studio and delete the physical schema associated with the source system.

4. In ODI Studio go to the respective logical schema.

2.1. Go to flexfield tab

2.2. Mark the checkbox for Data source num id flexfield to use default value.

5. In SQL*Plus or SQL Developer execute the following statements where the bind variable, Bind_DatasourceNumId, is set to the value of the source system identifier. These SQL statements may also be placed in a script, if needed for later use.

DELETE FROM C_DATA_SERVER WHERE DATASOURCE_NUM_ID = :Bind_DatasourceNumId;

DELETE FROM C_DATA_SOURCE_DISABLEDOFFR_REL WHERE DATASOURCE_NUM_ID = :Bind_DatasourceNumId;

DELETE FROM C_EXECUTION_PLAN_FACTGROUP_REL WHERE DATASOURCE_NUM_ID = :Bind_DatasourceNumId;

DELETE FROM C_LOAD_PLAN_GENERATION_STEP WHERE EXECUTION_PLAN_ID IN (SELECT EXECUTION_PLAN_ID FROM C_EXECUTION_PLAN WHERE EXECUTION_PLAN_ID NOT IN (SELECT EXECUTION_PLAN_ID FROM C_EXECUTION_PLAN_FACTGROUP_REL));

DELETE FROM C_EXECUTION_PLAN WHERE EXECUTION_PLAN_ID NOT IN (SELECT EXECUTION_PLAN_ID FROM C_EXECUTION_PLAN_FACTGROUP_REL);

DELETE FROM C_SRC_DOMAIN_MEMBER_TL WHERE DATASOURCE_NUM_ID = :Bind_DatasourceNumId;

DELETE FROM C_SRC_DOMAIN_MEMBER WHERE DATASOURCE_NUM_ID = :Bind_DatasourceNumId;

DELETE FROM C_DOMAIN_MEMBER_MAP WHERE DATASOURCE_NUM_ID = :Bind_DatasourceNumId;

DELETE FROM C_PARAM_DW_VAL WHERE DATASOURCE_NUM_ID = :Bind_DatasourceNumId;

DELETE FROM C_PARAM_DW_VAL_AUDIT WHERE DATASOURCE_NUM_ID = :Bind_DatasourceNumId;

DELETE FROM C_DATA_SOURCE WHERE DATASOURCE_NUM_ID = :Bind_DatasourceNumId;

COMMIT;

No comments:

Post a Comment

SQL query to find whether OdiSendMail is configured or not ODI 12c

In the SQL developer connect to the ODI work repository user (Ex: DEV_ODI_REPO) and run the below query. select SCEN_NAME,TASK_NAME3,DE...