This discussion is archived
14 Replies Latest reply: Dec 26, 2012 11:39 PM by Saro RSS

Universal Adapter- CRM load is failing

Saro Explorer
Currently Being Moderated
Hi friends,

Recently had a successful load for the following SA in my DAC

*) Human Resources.
*) Finance.
*) Supply Chain Mgmt.
*) Procurement and Spend.
*) Projects.

Since all the above SA are in the ORA_R1213 Adaptor. After the successful load im in the verge of performing Complete CRM load which is in the Universal Adaptor.

What i did is i added the complete CRM Subject Area to my existing ORA_R1213 Subject Area(nearly i had 23 subject areas for CRM). And generated the parameters for the subject areas of the following container(ORA_R1213 and Universal) in my subject area like the following.
Name:DB_Connection_OLAP-->Value: DataWarehouse-->SourceSystem: Universal
Name:DB_Connection_OLAP-->Value: DataWarehouse-->SourceSystem: Oracle R12.1.3
Name:DB_Connection_OLTP-->Value: ORA_R1213-->SourceSystem: Universal
Name:DB_Connection_OLTP-->Value: ORA_R1213-->SourceSystem: Oracle R12.1.3
Name:FlatFileConnection-->Value: ORA_R1213_Flatfile-->SourceSystem: Universal
Name:FlatFileConnection-->Value: ORA_R1213_Flatfile-->SourceSystem: Oracle R12.1.3
Name:PLP-->Value: PLP-->SourceSystem: Oracle R12.1.3
Name:PLP-->Value: PLP-->SourceSystem: Universal
Name:SDE_ORA_R1213 Adaptor-->Value: SDE_ORA_R1213 Adaptor-->SourceSystem: Oracle R12.1.3
Name:SDE Universal Adaptor-->Value: SDE Universal  Adaptor-->SourceSystem: Universal
Name:SILOS-->Value: SILOS-->SourceSystem: Universal
Name:SILOS-->Value: SILOS-->SourceSystem: Oracle R12.1.3
Name:SIL_Vert-->Value: SIL_Vert-->SourceSystem: Universal
As i have Relational connection in informatica Workflow manager for both source and target like

Source-->ORA_R1213
Target-->DataWarehouse

After generated parameters i builded it and performed a EP, in which my previous Subject Area will peform incr. load and my new CRM Subject Area will peform Full load in it. As a result i have total number of tasks like 1302, in that task only 200 is of successful task and remaining 1000 task is failing.

When i seen the reason for the failed task, i can see the failed task is from the following universal folder
Name:FlatFileConnection-->Value: ORA_R1213_Flatfile-->SourceSystem: Universal
Im not sure exactly what is the problem as i couldnt find any ORA error behind this Universal Container CRM Failed task.

Is am i missing anywhere else.

Thanks in advance.

Regards,
Saro
  • 1. Re: Universal Adapter- CRM load is failing
    Srini VEERAVALLI Guru
    Currently Being Moderated
    I dont think you can get the CRM source (table name start with S_)schema in both physical connections
    DB_Connection_OLTP-->Value: ORA_R1213-->SourceSystem: Universal
    DB_Connection_OLTP-->Value: ORA_R1213-->SourceSystem: Oracle R12.1.3

    To confirm this, can get the SQ Query from any of the CRM mapping and try to run in your source database or else try to run CRM EP alone.. instead of going with your own custom EP.

    If helps pls mark
  • 2. Re: Universal Adapter- CRM load is failing
    Saro Explorer
    Currently Being Moderated
    Hi Svee,

    Thanks for your reply.
    I dont think you can get the CRM source (table name start with S_)schema in both physical connections
    DB_Connection_OLTP-->Value: ORA_R1213-->SourceSystem: Universal
    DB_Connection_OLTP-->Value: ORA_R1213-->SourceSystem: Oracle R12.1.3
    Is it So!!!
    To confirm this, can get the SQ Query from any of the CRM mapping and try to run in your source database
    Will check and confirm you whether i can execute the SQL Query from the CRM Mapping in source.
    try to run CRM EP alone.. instead of going with your own custom EP
    Svee, whether u meant of running the CRM subject area belonging to the Universal adaptor as a new EP, instead of adding the CRM subject area to my existing EP. If running so, whether will delete my previous EP load(which includes HRMS, Finance, Projects, Procurement and Spend, SCM) data in datawarehouse.

    Since i had this problem before of overlapping data. That's why im confirming

    What are all the parameters that i need to generate for this EP which includes the Universal Container.

    Thanks

    Brgds,
    Hari
  • 3. Re: Universal Adapter- CRM load is failing
    Srini VEERAVALLI Guru
    Currently Being Moderated
    I dont think so that the new EP will run successfully.. yeah you are right common dimensions may truncate first.
    Try to unit test any of the SDE task from Universal there wont be any harm for existing data for common dimensions.
  • 4. Re: Universal Adapter- CRM load is failing
    Saro Explorer
    Currently Being Moderated
    Hi Svee,
    To confirm this, can get the SQ Query from any of the CRM mapping and try to run in your source database
    Like you said i tried to run any one of the mappings query "SDE_Universal_CodeDimension_Common" in the SDE_Universal_Adaptor

    But i couldnt find any of the query in the above mapping "SDE_Universal_CodeDimension_Common" under the properties tab. Since i also couldnt find any of the sql query under the all the mappings of my universal adaptor.
    Try to unit test any of the SDE task from Universal there wont be any harm for existing data for common dimensions.
    Also im not sure of how to perfom unit test for the SDE task of my Universal Container, can u guide me of doing it.

    Thanks

    Regards,
    Saro
  • 5. Re: Universal Adapter- CRM load is failing
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Open Informatica designer and respective folder and get the query and execute or else you can do a query in your source db like
    select * from all_user_tables where tname like ='S_%'

    To unit test:
    Open DAC->Execute->select your current failed EP and in bottom pane select any of Universal task and hit 'Unit Test'
  • 6. Re: Universal Adapter- CRM load is failing
    Saro Explorer
    Currently Being Moderated
    Hi Svee,

    I quered in my Source DB like you said as below
    select * from all_objects where object_type = 'TABLE' and object_name like 'S_%' and owner = 'APPS'
    I just found 9 tables in my apps schema for the above query which is not @ all related to the CRM module.

    As a part of the unit test i tested for one of the failed task "SDE_Universal_CodeDimension_Currency"

    i got the following error for the failed task like
    All Task Batches
         Informatica Session Batch
              ------------------------------
              INFORMATICA TASK:SDE_Universal_Adaptor:SDE_Universal_CodeDimension_Currency:(Source : FULL Target : INCREMENTAL)
              ------------------------------
              
              
              2012-11-24 21:14:11.788 INFORMATICA TASK:SDE_Universal_Adaptor:SDE_Universal_CodeDimension_Currency:(Source : FULL Target : INCREMENTAL) has started.
              
              
              
              ANOMALY INFO::: Error while executing : INFORMATICA TASK:SDE_Universal_Adaptor:SDE_Universal_CodeDimension_Currency:(Source : FULL Target : INCREMENTAL)
              MESSAGE:::
              Irrecoverable Error
              pmcmd startworkflow -sv Oracle_BI_DW_Base_Integration_Services -d Domain_biserver -u Administrator -p ****  -f SDE_Universal_Adaptor  -paramfile C:\Informatica\9.1.0\server\infa_shared\SrcFiles\\UNIV.DataWarehouse.SDE_Universal_Adaptor.SDE_Universal_CodeDimension_Currency.txt SDE_Universal_CodeDimension_Currency
              Status Desc : Failed
              WorkFlowMessage : 
              =====================================
              STD OUTPUT
              =====================================
              
              Informatica(r) PMCMD, version [9.1.0 HotFix2], build [357.0903], Windows 64-bit
              Copyright (c) Informatica Corporation 1994 - 2011
              All Rights Reserved.
              
              Invoked at Sat Nov 24 21:14:49 2012
              
              Connected to Integration Service: [Oracle_BI_DW_Base_Integration_Services].
              Integration Service status: [Running]
              Integration Service startup time: [Sat Nov 24 19:01:11 2012]
              Integration Service current time: [Sat Nov 24 21:14:50 2012]
              Folder: [SDE_Universal_Adaptor]
              Workflow: [SDE_Universal_CodeDimension_Currency] version [1].
              Workflow run status: [Failed]
              Workflow run error code: [36331]
              Workflow run error message: [WARNING: Session task instance [SDE_Universal_CodeDimension_Currency] failed and its "fail parent if this task fails" setting is turned on.  So, Workflow [SDE_Universal_CodeDimension_Currency] will be failed.]
              Workflow run id [5893].
              Start time: [Sat Nov 24 21:14:28 2012]
              End time: [Sat Nov 24 21:14:44 2012]
              Workflow log file: [C:\Informatica\9.1.0\server\infa_shared\WorkflowLogs\SDE_Universal_CodeDimension_Currency.log]
              Workflow run type: [User request]
              Run workflow as user: [Administrator]
              Run workflow with Impersonated OSProfile in domain: []
              Integration Service: [Oracle_BI_DW_Base_Integration_Services]
              Disconnecting from Integration Service
              
              Completed at Sat Nov 24 21:14:50 2012
              
              =====================================
              ERROR OUTPUT
              =====================================
              
              Error Message : Unknown reason for error code 36331
              ErrorCode : 36331
              EXCEPTION CLASS::: com.siebel.analytics.etl.etltask.IrrecoverableException
              
              com.siebel.analytics.etl.etltask.InformaticaTask.doExecute(InformaticaTask.java:183)
              com.siebel.analytics.etl.etltask.GenericTaskImpl.doExecuteWithRetries(GenericTaskImpl.java:411)
              com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:307)
              com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:214)
              com.siebel.analytics.etl.etltask.GenericTaskImpl.run(GenericTaskImpl.java:586)
              com.siebel.analytics.etl.taskmanager.XCallable.call(XCallable.java:63)
              java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
              java.util.concurrent.FutureTask.run(FutureTask.java:138)
              java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
              java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
              java.util.concurrent.FutureTask.run(FutureTask.java:138)
              java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
              java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
              java.lang.Thread.run(Thread.java:619)
              
              (Number of retries : 1)
              
              pmcmd startworkflow -sv Oracle_BI_DW_Base_Integration_Services -d Domain_biserver -u Administrator -p ****  -f SDE_Universal_Adaptor  -paramfile C:\Informatica\9.1.0\server\infa_shared\SrcFiles\\UNIV.DataWarehouse.SDE_Universal_Adaptor.SDE_Universal_CodeDimension_Currency.txt SDE_Universal_CodeDimension_Currency
              
              2012-11-24 21:15:09.913 INFORMATICA TASK:SDE_Universal_Adaptor:SDE_Universal_CodeDimension_Currency:(Source : FULL Target : INCREMENTAL) has finished execution with Failed status.
              ------------------------------
    What might be the issue svee.

    Regards,
    Saro
  • 7. Re: Universal Adapter- CRM load is failing
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Make sure you have the oob .csv files in your informatica dir at C:\Informatica\9.1.0\server\infa_shared\SrcFiles
    For Universal you have to make sure you have all those files.

    BTW:
    What BIApps module you are trying to config? That would be more helpful to guide you..
  • 8. Re: Universal Adapter- CRM load is failing
    Saro Explorer
    Currently Being Moderated
    Hi svee,

    I searched for any oob.csv file in the location that you said, but i couldnt find it. So due to the absence of this csv file only i couldnt load the universal adapter.

    Inorder to restate that file what all r the steps that i need to do.
    What BIApps module you are trying to config? That would be more helpful to guide you..
    Im trying to load the complete CRM module which includes Marketing, pricing, automotive, public sector, pharma, sales, service, forecasting, marketing, service etc

    Thanks

    Regards,
    Saro
  • 9. Re: Universal Adapter- CRM load is failing
    Srini VEERAVALLI Guru
    Currently Being Moderated
    You got me wrong, I was referring the OOB csv files. Check this doc for CRM Universal
    http://docs.oracle.com/cd/E20490_01/bia.7963/e19039/anyinstadmua.htm#CHDIAAIH

    For rest of the modules you need to have Siebel source Database, without this or using your eBS databasae you cant run the load.
    For Service Analytics check the 11th chapter, you need to have S_ source tables for those modules.

    Is your client using both eBS and Siebel?
  • 10. Re: Universal Adapter- CRM load is failing
    Saro Explorer
    Currently Being Moderated
    Hi svee,
    Is your client using both eBS and Siebel?
    My client is using only EBS R12.1.3 application with 11g database.

    So u meant that inorder to load CRM analytics in BIAPPS we need to have Siebel Source database as a source?

    Is CRM Analytics load is not possible with the EBS R12.1.3 application database??

    Regards,
    Saro
  • 11. Re: Universal Adapter- CRM load is failing
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Yes. You cant do a load without Siebel Source database.
  • 12. Re: Universal Adapter- CRM load is failing
    Saro Explorer
    Currently Being Moderated
    Hi svee,

    Sorry if i asked anything wrong.

    Whether it is possible to load CRM analytics with BIAPPS if my Oracle EBS R12.1.3 source contains the CRM Module(CRM on Demand).

    or else like you said the CRM Analytics in BIAPPS is only possible with the Siebel EBS alone.

    Thanks

    Regards,
    Saro
  • 13. Re: Universal Adapter- CRM load is failing
    Ahsan Shah Expert
    Currently Being Moderated
    I think you need to understand what the purpose of "Universal Adaptor" is before you do anything. UA is intended to integrate NON-ORACLE External systems to the OBIA DW. Currently, OBIA supports Siebel CRM and Several Oracle Modules (Financials, HR, etc)...but I do not believe CRM is one of them (at least not for 7963). "CRM On Demand" sounds like a Oracle cloud based solution..which means..there is no physical DB on site. Either way, you need to do a analysis to see if its possible to use UA to get the Oracle CRM data to the existing data model. Once this is done, you can then configure the CSV files to be used for a UA load. You cannot just "Turn on" UA which is what it looks like you are trying to do. Hope it helps.
  • 14. Re: Universal Adapter- CRM load is failing
    Saro Explorer
    Currently Being Moderated
    Hi Shah,

    Thanks for your information.

    Sorry indeed for the late reply and im marking ur answer
    :D

    Regards,
    Saro

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points