12 Replies Latest reply: Apr 18, 2013 4:38 AM by user1116922 RSS

    Importing OCDM Intra ETL into Workflow.

    user1116922
      Hi,

      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.
        • 1. Re: Importing OCDM Intra ETL into Workflow.
          User8653986-Oracle
          Hi,

          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.

          Thanks,
          Uday
          • 2. Re: Importing OCDM Intra ETL into Workflow.
            user1116922
            Hi,

            I have installed the OCDM in an UNIX server. Hence the OCDM was not installed via OUI installer.

            Thanks.
            • 3. Re: Importing OCDM Intra ETL into Workflow.
              user1116922
              Hi,

              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.
              Thanks.
              • 4. Re: Importing OCDM Intra ETL into Workflow.
                User8653986-Oracle
                Hi,

                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.

                Thanks,
                Uday
                • 5. Re: Importing OCDM Intra ETL into Workflow.
                  user1116922
                  Hi Uday,

                  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.

                  -OCDM_INTRA_ETL

                  -Databases

                  -Oracle

                  -OCDM_INTRA_ETL
                  -Mappings
                  -OLAP__MAP
                  -Transformations
                  -Packages
                  -DWD_ACCT_DEBT_DAY_PKG
                  -..
                  -..
                  -Tables
                  -DWC_OLAP_ETL_PARAMETER

                  -OCDM_MINING
                  -Transformations
                  -Packages
                  -PKG_OCDM_MINING

                  Below is the tree structure under Process Flows of OCDM_INTRA_ETL project.

                  -OCDM_INTRA_ETL

                  -Process Flow Modules

                  -OCDM_PFLW

                  -ETL_FLW
                  -AGGR_FLW
                  -DRVD_FLW
                  -MINING-FLW

                  -TOP_FLW
                  -INTRA_ETL_FLW

                  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
                  • 6. Re: Importing OCDM Intra ETL into Workflow.
                    user1116922
                    Hi Uday,

                    Kindly let me know your suggestions to proceed further on the INTRA ETL.

                    Thanks.
                    • 7. Re: Importing OCDM Intra ETL into Workflow.
                      User8653986-Oracle
                      Hi,

                      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))

                      Click Submit.


                      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:

                      OCDM_MINING_LOCATION
                      OCDM_SYS_LOCATION
                      OWB_REPOSITORY_LOCATION

                      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.


                      Thanks,
                      Uday
                      • 8. Re: Importing OCDM Intra ETL into Workflow.
                        User8653986-Oracle
                        Hi,

                        Few corrections:

                        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.



                        HTH,
                        Uday
                        • 9. Re: Importing OCDM Intra ETL into Workflow.
                          user1116922
                          Hi Uday,

                          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.
                          DWD_INVC_ADJ_PKG
                          DWD_CMISN_DAY_PKG
                          DWD_ACCT_PYMT_DAY_PKG
                          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.
                          Thanks.
                          • 10. Re: Importing OCDM Intra ETL into Workflow.
                            User8653986-Oracle
                            Hi,

                            You have to grant execute on CTX_DDL to ocdm_mining. For the other error, I think that is a view. Please check the ddl for that table/view if its already not there in ocdm_sys schema.

                            Thanks,
                            Uday
                            • 11. Re: Importing OCDM Intra ETL into Workflow.
                              user1116922
                              Hi Uday,

                              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;
                              Where:
                              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.
                              Thanks.
                              • 12. Re: Importing OCDM Intra ETL into Workflow.
                                user1116922
                                Hi Uday,

                                The only issue left out is because of the view dwv_cnrt_acct_sbrp_prod being missed from the OCDM_SYS schema. It is possible to get the view definition.

                                Thanks.