DAC Full Load vs Incremental Load

I have been getting few queries around what determines DAC to do an incremental load or Full load. Though DAC Guide has it under Refresh Dates section, I thought I will simplify it for the benefit of everybody.

If you ever want to run a Full Load of an execution plan, reset Data Warehouse in DAC by navigating to Tools -> ETL Management -> Reset Data Warehouse. The warning is - "This action will cause all tables that are loaded during next ETL to be truncated". This means that refresh dates in Physical Sources will be set to NULL both on Source and Target tables and if both refresh dates are NULL, ETL truncates the target table and run Full Load command.

Full load is a straight forward exercise but most often, we encounter doing something that is not straight forward such as running full load only on certain tasks but not the whole of the execution plan. Reasons are plenty and this post is limited to provide a reasonable explanation on how DAC determines whether to run an incremental or full load.

The Refresh Dates in Physical Data Sources determine whether to run the command for incremental or full load. I am pasting a graphic that explains what happens when.

Based on what you need, determine what refresh dates are to be populated/modified in Physical data sources.

DAC Synchronize Tasks failing

MESSAGE:::Error while inserting a record!
EXCEPTION CLASS::: com.siebel.etl.gui.core.RecordManipulationException

com.siebel.analytics.etl.client.core.DACMessage.convertToRME(DACMessage.java:31)
com.siebel.analytics.etl.client.data.model.UpdatableDataTableModel.upsertNewRecord(UpdatableDataTableModel.java:141)
com.siebel.analytics.etl.infa.fileParsing.InfaDacWriter.insertTableList(InfaDacWriter.java:459)
com.siebel.analytics.etl.infa.fileParsing.InfaDacWriter.insertNodeTables(InfaDacWriter.java:397)
com.siebel.analytics.etl.infa.fileParsing.InfaDacWriter.insertNodeTables(InfaDacWriter.java:314)
com.siebel.analytics.etl.infa.fileParsing.TaskSync.sync(TaskSync.java:168)
com.siebel.analytics.etl.client.action.TaskSynchronizationAction.doOperation(TaskSynchronizationAction.java:123)
com.siebel.etl.gui.view.dialogs.WaitDialog.doOperation(WaitDialog.java:53)
com.siebel.etl.gui.view.dialogs.WaitDialog$WorkerThread.run(WaitDialog.java:85)
------------------------------------------------------------------------------------
We have a custom mapping that has been tuned to use DB Links and also commented the line that was checking Last Update Date in Oracle EBS tables. The commented line is as below.

-- AND (AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS') OR AP_INVOICES_ALL.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS'))

After this, DAC task is not synchronizing. It is throwing the error - "MESSAGE:::Error while inserting a record". After removing the line, it is just synchronizing fine.

It looks like DAC has problems avoiding the commented line in SQL Query. When I run DAC Client in interactive mode, it throws this error. So, DAC is treating MM/DD/YYYY as a table name for some reason. I can't think what the logic is but definitely wrong.

FROM W_ETL_TABLE A_W_ETL_TABLE
INNER JOIN W_ETL_OBJ_REF OBJ_REF ON OBJ_REF.OBJ_TYPE='W_ETL_TABLE'
AND OBJ_REF.SOFT_DEL_FLG='N'
AND OBJ_REF.APP_WID='PGBU_R12'
AND OBJ_REF.OBJ_WID=A_W_ETL_TABLE.ROW_WID
WHERE
((A_W_ETL_TABLE.NAME=''MM/DD/YYYY'
))
Aug 10, 2009 9:43:32 AM com.siebel.etl.engine.core.ETLUtils logException
SEVERE: MESSAGE:::ORA-00907: missing right parenthesis

EXCEPTION CLASS::: java.sql.SQLException

Synchronize Tasks in DAC

I had this error recently in DAC Client while trying to Synchronize Task.

MESSAGE:::Failed to create folder/workflow/session cache file
EXCEPTION CLASS::: com.siebel.analytics.etl.infa.interaction.PmrepUtilsException

This happens if you have not created/changed INFA_DOMAINS_FILE and PATH variables to point to domains.infa and path to bin directory respectively.

ParameterfileOLTP.txt in DAC

I had many problems with DAC 7.9.4 not picking right values from parameterOLTP.txt file while generating the individual parameter files at the run time. Read the full post here... Dylan Wan talked about an enhancement then and a colleague of mine reminded me today of testing that while we were planning on modifying parameterfileOLTP.txt as we upgrade from 7.9.4 to 7.9.5. It is pretty cool now as we get to define parameters at Task level for different source system containers and requires no restart of DAC, no assemle of subject areas and no build of execution plans required after adding the parameters at task level, which is really cool. If parameterfileOLTP.txt has same parameter, what you define in DAC takes precedence over the definition in parameterfileOLTP.txt.

I was suspicious of the fact that Informatica expects the parameters to be prefixed with mapplet name in case the parameter is a mapplet parameter. DAC is able to generate the parameter as mapplet.parameter at the run time though we do not specify in DAC that it is a Mapplet parameter. I am thinking DAC queries OPB tables (Informatica Repository tables) to identify whether the parameter is Mapplet or mapping.

7.9.4 DAC in fact had same parameters feature at task level in DAC Client and in fact it is documented that we should use parameters at task level instead of attempting to change parameterfileOLTP.txt. It is clear now that I missed to read this piece of the DAC 7.9.4 documentation, while I configured OOB Order Management. To be fair to myself, applications configuration document mentions to change the parameter values in parameterfileOLTP.txt, which should have at least had a line on configuration steps in DAC. I spent lot of time trying to fix the issue with DAC not passing right values from parameterfileOLTP.txt and I ultimately created my own parameters in 7.9.4. Anyway, not bad to realize now than never.

Running DAC Service in Background mode

I have been planning about running DAC Server in background mode in Solaris but did not work with nohup. I first had to change the way the confog.sh and dac_env.sh are called ("source ./config.sh" to ". config.sh") in order to start DAC Server. When I use "nohup startserver.sh &" at command prompt, it errors out immediately. nohup.out file has the error as - LD_LIBRARY_PATH=......... : is not an identifier. I mentioned in one of my earlier posts that it is one issue that I have to fix as doing Ctl+c on the Solaris session does not release the connections DAC made with the database, causing lot many Inactive sessions on the database. Also, having a windows machine just to run DAC Service on the Solaris command prompt is simply not convincing, acceptable and sounds odd.

I found the other day when I had to restart DAC Server multiple times as I was debugging an issue with parameters not getting correctly populated by DAC during the run-time, I figured that the call to run config.sh in startserver.sh is throwing the "invalid identifier" error. I commented config.sh in startserver.sh and tried "nohup startserver.sh &" after running ". config.sh" in foreground at command prompt, it just ran fine and I exited the Solaris session and logged back in to see if the service is still alive. It is indeed alive!! The conclusion is that EXPORT cannot be run in background mode.

Venkat has blogged about how to create this as a Windows service for a oc4j service and I am pretty sure that the same can be implemented for DAC Server. Visit Venkat's blog post for more details, if your DAC Server is hosted on a Windows machine.

Generation of parameter files by DAC at runtime

I was very curious on how actually DAC reads parameterfileOLTP.txt and generates individual parameter files that are passed runtime as a parameter in pmcmd command. I became more curious after I observed that DAC was not taking right values from parameterfileOLTP.txt file. I changed CATEGORY_SET_ID value in different sections to different values as per the documentation but DAC generated one parameter file correctly but the other one was incorrectly populated. It populated one of the parameter file with CATEGORY_SET_ID=27 where as parameterfileOLTP.txt has no such value anywhere in the file.

I am still not very clear on the logic and DAC still maintains default values somewhere thought the value does not exist in parameterfileOLTP.txt. I wonder where. OOB ParameterfileOLTP.txt has additional parameters though the parameters are not defined in the mapping. When I removed those unwanted ones from the file, DAC is passing the correct values. Let me give an example of what I am talking about.

ParameterfileOLTP.txt in \\OracleBI\DAC\Informatica\parameters\input\ has been modified to have $$CATEGORY_SET_ID=1100000023. The entry is pasted here.

[SDE_ORA_InventoryProductDimension_Derive]
$DBConnection_OLAP=PARAM_OLAP
$DBConnection_OLTP=PARAM_OLTP_ORA11I
$$CATEGORY_SET_ID=1100000023

\\informatica\server\SrcFiles has the parameter file is definitely modified (timestamp changed) but $$CATEGORY_SET_ID value is set to ' 27'. DAC is passing the file in \\informatica\server\SrcFiles\SDE_ORA11510_Adaptor.SDE_ORA_InventoryProductDimension_Full.txt in the pmcmd command.

I changed [SDE_ORA_ProductDimension_Full] section to remove mplt_BC_ORA_ProductDimension.$$CATEGORY_SET_ID as the mapping does not a mapplet and the DAC is passing the value that is there in parameterfileOLTP.txt.

I am still searching for the logic but when I remove the ones that are not required, it does work as expected. I will be glad if someone can explain me the logic.