I am trying to Install and configure Intra ETL for OCDM. I am done with the Installation of OWB Workflow.
Next steps as per the documentation is to import the OCDM_Intra_ETL.mdl.In the Oracle Installation Guide they have asked to log into Oracle Warehouse Builder to proceed with the import of metadata.
Here which credentials I need to use for login into OWB Designer? Can I create new workspace with any name and import the metadata?
Kindly help me.
Thanks in Advance.
Have you installed OCDM via the OUI installer. If so, it should have a) configured OWB, b) created OWB workspace "ocdmworkspace" with new user "owb_owner" as owner. Please check if "owb_owner" user exists in your database instance and also check if "ocdmworkspace" exists. You have to use OWB repository assistant to check the existence of "ocdmworkspace" OWB workspace. If both "owb_owner" and "ocdmworkspace" exists, OCDM installer should also have imported OCDM_Intra_ETL.mdl and when you log into OWB design center using owb_owner user, you should OCDM_INTRA_ETL project.
I was able to find a user OWB_OWNER in the database. But I was not able to see any workspace owned by this user. Below is the query which I used to find if the user OWB_OWNER owns any workspace.
SQL> select * from OWBSYS.ALL_IV_WORKSPACE_ASSIGNMENTS where user_name='OWB_OWNER';
no rows selected
Kindly let me how to proceed.
Please create "ocdmworkspace" workspace with owb_owner as owner of the workspace. Register ocdm_sys, ocdm_mining, owf_mgr with "ocdmworkspace" workspace. Use repository assistant for those 2 tasks. Log in to design center using owb_owner and import OCDM_Intra_ETL.mdl.
I have followed the steps as mentioned by you and have imported the OCDM_Intra_ETL.mdl into workspace. I could see 2 folders(OCDM_INTRA_ETL, OCDM_MINING) under the Oracle database of OCDM_INTRA_ETL project.
OCDM_INTRA_ETL folder has one mapping, one table and lot of packages.
OCDM_MINING folder has nothing except a PKG_OCDM_MINING package.
I could see the below structure in the OWB design Center.
Below is the tree structure under Process Flows of OCDM_INTRA_ETL project.
-Process Flow Modules
Please help me in executing the intra ETL. What are the steps that I should follow in running the Intra ETL from Control center. I will be running it for the first time.
Do I need to deploy each and every objects before starting the process flow?
Thanks in Advance.
Edited by: user1116922 on Mar 29, 2013 4:01 PM
Have you installed workflow ? If not, do it as follows:
a. Go to $ORACLE_HOME/owb/wf/install
b. Execute wfinstall.csh
The Oracle Workflow Configuration Assistant opens.
c. Enter values for the Workflow account, Workflow, SYS password, and TNS Connect Descriptor.
For TNS Connect Descriptor, use the following syntax where you replace local-host, port-number, and service-name with the appropriate values.
(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=local-host)(PORT = port-number))) (CONNECT_DATA = (SERVICE_NAME = service-name))
In the design center:
1. Go to "Locations" tab, which is next to "Projects" tab. Follow "Locations -> Databases -> Oracle" path, then you should see 3 location parameters:
Open first 2 locations, enter password, and test connection. Ensure that testing connection is successful.
2. In Locations tab, Follow "Locations -> Process Flow and Schedules -> Oracle Workflow", then you should a location parameter, OWF_LOCATION. Open it, enter host, port, service name, OWF user name and password, and test the connection. Ensure that testing is successful.
3. Tools -> Control Center Manager. OCDM_SYS_LOCATION -> OCDM_INTRA_ETL -> Process flow. Deploy top process flow, INTRA_ETL_FLW. Once it is deployed successfully, you can execute the process flow, which in turn executes derived IETL packages, refreshes materialized views, and loads data into cubes. Before executing process flow, make sure that you have inserted right ETL parameters into ETL parameter table, DWC_ETL_PARAMETER table.
I hope that helps. All the best.
1. For workflow install, enter TNS descriptor in the following format:
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port-number>))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = <service-name>)))
replace <hostname>, <port-number>, and <service-name> with your database instance parameters.
2. You have to grant OWB_USER role to OWF_MGR user using repository assistant ($ORACLE_HOME/owb/bin/unix/reposinst.sh)
3. In control center manager, you can find process flows under OWF_LOCATION. Before deploying process flow, deploy OLAP mapping, which you find under OCDM_SYS_LOCATION.
Thanks a lot for your inputs.
I followed the steps mentioned by you and started the INTRA_ETL_FLW.I am facing couple of issues while running the INTRA_ETL_FLW process flow. The process flow fails because of errors in package of OCDM_MINING and OCDM_SYS schema.
In OCDM_MINING schema there is only one package called PKG_OCDM_MINING and while compiling the package body through SQL developer im getting the below errors.
PACKAGE BODY OCDM_MINING.PKG_OCDM_MINING@OCDM_MINING
Error(1798,8): PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
Error(1798,8): PL/SQL: Statement ignored
Error(1801,1): PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be declared
Error(1801,1): PL/SQL: Statement ignored
In OCDM_SYS schema there are 40 packages and i am not able to compile the package body of below 3 packages.
The error is because of the missing table dwv_cnrt_acct_sbrp_prod. I am not sure how this table is missing.
Kindly help me to resolve the errors.
You are correct. The error in OCDM_MINING is because of missing Context in DB.
I followed the below steps to Install Context in Oracle 11g DB.
1. SQL> login as SYSDBA
2. SQL>spool text_install.txt;
3. SQL>@$ORACLE_HOME/ctx/admin/catctx.sql ctxsys SYSAUX TEMP NOLOCK;
ctxsys – is the ctxsys user password
SYSAUX – is the default tablespace for ctxsys
TEMP – is the temporary tablespace for ctxsys
LOCK|NOLOCK – ctxsys user account will be locked or not
The next step is to install appropriate language-specific default preferences – all the available default preferences are in the $ORACLE_HOME/ctx/admin/defaults directory:
4. connect as "CTXSYS"/"CTXSYS"
5. SQL> @$ORACLE_HOME/ctx/admin/defaults/drdefus.sql;
Then I grant execute access for OCDM_MINING to ctx_ddl.
After which the package got compiled successfully.
Now I am left out with the errors in OCDM_SYS schema.