OBIA - Full & Incremental Load Mapping

It’s possible to configure either a Full- or an Incremental Load in Oracle BIA. If you look at the Informatica version of Oracle BIA, there are a few areas you will have to configure.
First you start with the Informatica Mapping. This will be one Mapping. It does not matter whether you run this Mapping Full or Incremental.

Lets take the ‘SDE_ORA_GLJournals’-Mapping as an example. In the Source Qualifier of the Mapping (or Mapplet), you will see a reference to to the $$LAST_EXTRACT_DATE. If you would run the Mapping with these settings, you will run an Incremental Mapping. This means that you only select the data which is created / updated since the last ETL-run.



The $$LAST_EXTRACT_DATE is a Parameter which you configure in the Datawarehouse Administration Console (DAC) and reference in Informatica.



According to the Oracle documentation, the “@DAC_SOURCE_PRUNED_REFRESH_TIMESTAMP. Returns the minimum of the task’s primary or auxiliary source tables last refresh timestamp, minus the prune minutes.”
Make sure this Parameter is available in both the DAC (see above) as well as in the Mapping (or Mapplet).



This way the Parameter can be used in the Extraction Mapping. If you reference a Parameter in the Extraction Mapping Query which isn’t declared, the Workflow will return an error and won’t complete.

So the steps are easy;
1. Declare the $$LAST_EXTRACT_DATE-Parameter in the DAC
2. Declare the $$LAST_EXTRACT_DATE-Parameter in Informatica
3. Reference the $$LAST_EXTRACT_DATE-Parameter in the Source Qualifier
As I said before, the same Mapping is used for the the Incremental- as well as the Full-Load. If you want to run the two different loads, make sure there ar two different Workflows which run the same mapping. The difference is in the mapping of the Workflow. The Full-Workflow uses the $$INITIAL_EXTRACT_DATE whereas the Incremental-Workflow uses the $$LAST_EXTRACT_DATE.



If you edit the task which belongs to the Incremental-Workflow (‘SDE_ORA_GLJournals’), you will find the Source Qualifier with the extraction query and a reference to the $$LAST_EXTRACT_DATE-Parameter.
As you can see, the LAST_UPDATE_DATE is compared to the $$LAST_EXTRACT_DATE-Parameter.

After each ETL-run, the LAST_EXTRACT_DATES (Refresh Date) per table are stored. You can check, update or delete these values as per requirement (see picture below). If you decide to delete the Refresh Date, a Full Load ill be performed the next time.



As stated earlier, the Full-Workflow is almost identical. The only thing is that there is a reference to the $$INITIAL_EXTRACT_DATE. The $$INITIAL_EXTRACT_DATE-Parameter is defined in the DAC. You define a date in the past. Just make sure that this date captures all the data you need.



Make sure this Parameter is available in both the DAC (see above) as well as in the Mapping (or Mapplet).



This way the Parameter can be used in the Extraction Mapping. If you reference a parameter in the Extraction Mapping Query which isn’t declared, the Workflow will return an error and won’t complete.
How do you make sure that the $$INITIAL_EXTRACT_DATE-Parameter will be used when running a Full-Load?



If you edit the task which belongs to the Incremental-Workflow (‘SDE_ORA_GLJournals_Full’), you will find the Source Qualifier with the extraction query and a reference to the $$INITIAL_EXTRACT_DATE-Parameter.
As you can see, the LAST_UPDATE_DATE is compared to the $$INITIAL_EXTRACT_DATE-Parameter.

At this point everything is in place to either run a Full-, or an Incremental Load.



You just have to tell the DAC to either run the ‘SDE_ORA_GLJournals_Full’-Workflow or the ‘SDE_ORA_GLJournals’-Workflow (incremental)



Check the Informatica Session Log when the ETL has a another result than expected. It could be that the Workflows are incorrectly defined. You will see in the Session Log which Parameter is used and what the value of that Parameter is.

OBIEE 11g: How To Check the Log Files

You can check the different log files in the Oracle Business Intelligence (OBIEE) 11.1.1.x version.

The right way to check them is using the Enterprise Manager (EM) Console page but you can also review directly the files in the hard disk.

EM Console

Login to the URL

http://server.domain:7001/em

and navigate to:

Fam_bifoundation_domain-Business Intelligence-coreapplications-Diagnostics-Log Messages

These are all the available files:

Presentation Services Log
Server Log
Scheduler Log
JavaHost Log
Cluster Controller Log
Action Services Log
Security Services Log
Administrator Services Log

*************************************************************************

To check the files directly, you can use the following:

1) Admin Server logs

AdminServer-diagnostic.log

Directory:

$<MW_HOME>/user_projects/domains/bifoundation_domain/servers/AdminServer/logs

or

$DOMAIN_HOME/servers/AdminServer/logs

 

2) Managed Server logs

bi_server1-diagnostic.log

Directory:

$<MW_HOME>/user_projects/domains/bifoundation_domain/servers/bi_server1/logs

or

$DOMAIN_HOME/servers/bi_server1/logs/

 

3) Node Manager logs

Directory: WL_HOME/common/nodemanager

Example:

C:\OBI_11116\wlserver_10.3\common\nodemanager

 

4) BI Components logs

Directory: $<MW_HOME>/instances/instance2/diagnostics

5) OPMN: Oracle Process Manager and Notification Server

All files under the directory:

$<MW_HOME>/instances/instanceX/diagnostics/logs/OPMN/opmn

or

$ORACLE_INSTANCE/diagnostics/logs/OPMN/opmn

 

6) Enterprise Manager Log

emoms.trc

Directory:

$<MW_HOME>/user_projects/domains/bifoundation_domain/servers/sysman/log

or

$DOMAIN_HOME/servers/sysman/log

 

7) Miscellaneous log files

7.1) Installation logs.

7.2) Opatch.

7.3) Repository Creation Utility (RCU).

7.4) Upgrade Assistant (UA).

7.5) Map Viewer log files.

7.6) BI Publisher log file.

7.7) EmbeddedLDAP (can be used in conjunction with the access.logs, as above).

7.8) You can also review the Event Viewer log on Windows and the syslog on Linux or UNIX.

7.9) What Are Diagnostic Log Configuration Files?

7.10) Oracle Business Intelligence Mobile

 

 

---

 

Log files in detail

1. Admin Server logs

AdminServer-diagnostic.log

Directory:
$<MW_HOME>/user_projects/domains/bifoundation_domain/servers/AdminServer/logs
or
$DOMAIN_HOME/servers/AdminServer/logs

Example:
C:\OBIEE_11G\user_projects\domains\bifoundation_domain\servers\AdminServer\logs

 If there is no Managed Server (for instance, you did Simple Install), you should review these log files:

access.log
AdminServer.log

Domain log file:
bifoundation_domain.log

 

2. Managed Server log

bi_server1-diagnostic.log

Directory:
$<MW_HOME>/user_projects/domains/bifoundation_domain/servers/bi_server1/logs
or
$DOMAIN_HOME/servers/bi_server1/logs/

Example:
C:\OBIEE_11g\user_projects\domains\bifoundation_domain\servers\bi_server1\logs

Other files:

access.log
bi_server1.log
bi_server1.out
bi_server1.out00001

 

3. Node Manager log files

WL_HOME/common/nodemanager

or

$<MW_HOME>/wlserver_10.3/common/nodemanager

Example:
C:\OBI_11116\wlserver_10.3\common\nodemanager

4. BI Components logs file are under:

$<MW_HOME>/instances/instance2/diagnostics

In detail:

4.1) BI Server log files.

$<MW_HOME>/instances/instance2/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1

Example:
C:\OBIEE_11g\instances\instance2\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1

To confirm the above path, review the 'component-logs.xml' file, these lines to see if the default path has changed:

<log path="diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/nqquery.log">
 ...
 ...
 <log path="diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/nqserver.log">

The files to review are:

4.1.1) Queries run by BI

nqquery.log

4.1.2) BI Server log file

nqserver.log

4.1.3) Administration Tool

servername_NQSAdminTool.log (example, jjuan4_NQSAdminTool.log)

4.1.4) nQUDMLExec

servername_nQUDMLExec.log (examle, jjuan4_nQUDMLExec.log)

4.1.5) Migration Utility

servername_obieerpdmigrateutil.log (example, jjuan4_obieerpdmigrateutil.log)

4.1.6) Repository log

<ORACLE_INSTANCE>/OracleBIServerComponent/coreapplication_obis1/repository

 

4.1.7) System logging level (for BISystem user).

http://docs.oracle.com/cd/E23943_01/bi.1111/e10540/planning.htm#BIEMG398

Using Administration Tool, in Tools->Options->Repository->System Logging Level to set the log level.

This option determines the default query logging level for the internal BISystem user. The BISystem user owns the Oracle BI Server
system processes and is not exposed in any user interface.

A query logging level of 0 (the default) means no logging. Set this logging
level to 2 to enable query logging for internal system processes like event
polling and initialization blocks.

See this link to see all the Query Logging Levels

http://docs.oracle.com/cd/E23943_01/bi.1111/e10541/logging.htm#BIESG181

4.1.8) Usage Tracking (Only if redirected to logs)

NQAcct.yyyymmdd.hhmmss.log

STORAGE_DIRECTORY parameter in the Usage Tracking section of the
NQSConfig.INI file determines the location of usage tracking log files

NQSConfig.ini file in this directory:

$<MW_HOME>/instances/instance2/config/OracleBIServerComponent/coreapplication_obis1


Example:
C:\OBIEE_11G\instances\instance2\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository\SampleAppLite.rpd.Log

4.2) Presentation Server log files

$<MW_HOME>/instances/instance2/diagnostics/logs/OracleBIPresentationServicesComponent/coreapplication_obips1

Example:
C:\OBIEE_11g\instances\instance2\diagnostics\logs\OracleBIPresentationServicesComponent\coreapplication_obips1

File to check:
sawlog0.log

4.2.1) Java Host

$<MW_HOME>/instances/instance2/diagnostics/logs/OracleBIJavaHostComponent/coreapplication_obijh1

Example:
C:\OBIEE_11g\instances\instance2\diagnostics\logs\OracleBIJavaHostComponent\coreapplication_obijh1

Files to check:

jh.log
jh-1.log
jh-2.log

 

4.3) Web Catalog Upgrade.

$<MW_HOME>/instances/instance2/diagnostics/logs/OracleBIPresentationServicesComponent/coreapplication_obips1

Example:
C:\OBIEE_11g\instances\instance2\diagnostics\logs\OracleBIPresentationServicesComponent\coreapplication_obips1

File to check:
webcatupgrade0.log

http://aseng-wiki.us.oracle.com/asengwiki/display/~mikhail_shmulyan/BIPS+Logging+Configuration

 

4.4) Scheduler log

$<MW_HOME>/instances/instance2/diagnostics/logs/OracleBISchedulerComponent/coreapplication_obisch1

Example:
C:\OBIEE_11g\instances\instance2\diagnostics\logs\OracleBISchedulerComponent\coreapplication_obisch1

File to check:
nqscheduler.log
console~coreapplication_obisch1~1.log

4.4.1) iBot log files.

- You can review the log files using the Windows client tool Job Manager

- You can also review the following log files:

Agent-1-1.err
Agent-1-1.log

Review the instanceconfig.xml file under

$<MW_HOME>/instances/instance2/config/OracleBISchedulerComponent/coreapplication_obisch1

Example:
C:\OBIEE_11g\instances\instance2\config\OracleBISchedulerComponent\coreapplication_obisch1


to see the path where the iBot log files are created:

iBots>
            <Log_Dir>C:\OBIEE_11g\instances\instance2\diagnostics\logs\OracleBISchedulerComponent\coreapplication_obisch1</Log_Dir>
            <!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--><Web_Server>
            jjuan2-es.es.oracle.com:9710</Web_Server>
        <

By default, it is the same path as the Scheduler log but you can modify it.

The path is:

$<MW_HOME>/instances/instance2/diagnostics/logs/OracleBISchedulerComponent/coreapplication_obisch1

Example:
C:\OBIEE_11g\instances\instance2\diagnostics\logs\OracleBISchedulerComponent\coreapplication_obisch1

4.5) Cluster Controller

$<MW_HOME>/instances/instance2/diagnostics/logs/OracleBIClusterControllerComponent/coreapplication_obiccs1

Example:
C:\OBIEE_11g\instances\instance2\diagnostics\logs\OracleBIClusterControllerComponent\coreapplication_obiccs1

File to check:
nqcluster.log

4.6) ODBC

$<MW_HOME>/instances/instance2/diagnostics/logs/OracleBIODBCComponent/coreapplication_obips1

Example:
C:\OBIEE_11g\instances\instance2\diagnostics\logs\OracleBIODBCComponent\coreapplication_obips1

 

5. OPMN: Oracle Process Manager and Notification Server

All files under the below directory:

debug.log
logquery~1.log
opmn.log
opmn.out
service.log

$<MW_HOME>/instances/instanceX/diagnostics/logs/OPMN/opmn
or
$ORACLE_INSTANCE/diagnostics/logs/OPMN/opmn

 

6. Enterprise Manager Log

emoms.trc

Directory:
$<MW_HOME>/user_projects/domains/bifoundation_domain/servers/sysman/log
or
$DOMAIN_HOME/servers/sysman/log

 

7. Miscellaneous log files

7.1) Installation logs

7.1.1) OBIEE Installation and Configuration ($ORACLE_HOME/bin/config.sh):

$<MW_HOME>/cfgtoollogs/oui

Examples:
C:\OBIEE_11g\Oracle_BI1\cfgtoollogs\oui

2011-12-23_02-58-20PM.log
install2011-12-23_02-58-20PM.log
install2011-12-23_02-58-20PM.out
installActions2011-12-23_02-58-20PM.log
installProfile2011-12-23_02-58-20PM.log
oraInstall2011-12-23_02-58-20PM.err
oraInstall2011-12-23_02-58-20PM.out

or
$<MW_HOME>/oracle_common/cfgtoollogs/oui

Examples:
C:\OBIEE_11g\oracle_common\cfgtoollogs\oui

install2011-12-23_02-58-20PM.log
installActions2011-12-23_02-58-20PM.log
installProfile2011-12-23_02-58-20PM.log

 

7.1.2) OraInventory files: Installation, Desinstallation and Patch. OraInventory log files:

                              Windows:
                               C:\Program Files\Oracle\Inventory\logs\

                              Linux and UNIX:
                              USER_HOME/oraInventory/logs/

Examples:

installActions2011-12-15_03-21-41PM.log
oraInstall2011-12-05_02-55-22PM.err
oraInstall2011-12-05_02-55-22PM.out

On Unix/Linux, the location of the oraInventory is defined by the content of oraInst.loc, at:

- /var/opt/oracle/oraInst.loc on Solaris, HP-UX and Tru64
- /etc/oraInst.loc on Linux and AIX

On Windows, the location of the oraInventory is defined by the value of the registry key
HKEY_LOCAL_MACHINE|Software\Oracle\INST_LOC

or if this value is not defined at
C:\Program Files\Oracle\Inventory

7.1.3) Make.log

$ORACLE_HOME/install/make.log

file to check for install/relinking.

7.2) Opatch

Files:
opatch*.log

Directory
$<MW_HOME>/Oracle_BI1/cfgtoollogs/opatch

Example:

OUI location : C:/OBIEE_11G/Oracle_BI1/oui
Log file location : C:/OBIEE_11G/Oracle_BI1/cfgtoollogs/opatch/opatch2010-09-24_16-52-41PM.log
Patch history file: C:/OBIEE_11G/Oracle_BI1/cfgtoollogs/opatch/opatch_history.txt

Other file in the inventory directory. For example, on Windows:
C:/Program Files/Oracle/Inventory/logs

7.3) Repository Creation Utility (RCU)

Files:
biplatform.log
mds.log
rcu.log

If the RCU software is uncompressed in this directory:

D:\Trabajo\Docu\OBIEE\Software\11.1.1.6.0\Windows64

Check the following files, mainly the 'rcu.log':

D:\Trabajo\Docu\OBIEE\Software\11.1.1.6.0\Windows64\rcuHome\rcu\log\logdir.2012-02-23_10-17

biplatform.log
mds.log
rcu.log

7.4) Upgrade Assistant (UA)

$<MW_HOME>/Oracle_BI1/upgrade/logs

Example
C:/OBIEE_11g/Oracle_BI1/upgrade/logs

Files to check:
uaxxxx.log

Example:
ua2010-10-20-14-30-21PM.log

7.5) Map Viewer log files

$mapviewer_deploy_home/web.rar/WEB-INF/log

Example:
mapviewer_20.log in the directory

C:/OBIEE_11g/Oracle_BI1/bifoundation/jee/mapviewer.ear/web.rar/WEB-INF/log

Error messages and timestamped:
- Javascript console: Firefox, Chorme, IE with developer tools

7.6) BI Publisher log file

$<MW_HOME>/user_projects/domains/bifoundation_domain/servers/AdminServer/logs/bipublisher

For example:
C:\OBIEE_11G\user_projects\domains\bifoundation_domain\servers\AdminServer\logs\bipublisher\bipublisher.log

Details:
========
$<MW_HOME>/user_projects/domains/bifoundation_domain/servers

You will see entry for AdminServer and managed server. Depending on where bipublisher is
installed, go the

logs/bipublisher

folder.

BI Publisher Trace 32
========================
Go to FMW Control (EM)

http://server:70001/em

Farm_bifoundation_domain
  WebLogic Domain
    bi_cluster
    or
    AdminServer
  
    Right click and select Logs-Log Configuration
  
    In 'Log Levels' tab, search by: oracle.bi.bipssessionscache
  
    View: Runtime Loggers
    Search: All Categories
  
    oracle.bi
  
   oracle.bi.bipssessionscache  TRACE (32) FINEST
   oracle.bi.bipssessionscache.performance  TRACE (32) FINEST

7.7) EmbeddedLDAP (can be used in conjunction with the access.logs, as above).

EmbeddedLDAP.log
EmbeddedLDAPAccess.log

$DOMAIN_HOME/servers/server_name/data/ldap/log/EmbeddedLDAP.log DOMAIN_HOME/servers/server_name/data/ldap/log/

Example:
C:\OBIEE_11G\user_projects\domains\bifoundation_domain\servers\bi_server1\data\ldap\log\EmbeddedLDAP*.log

7.8) Event Viewer log (Windows) and syslog (Linux or UNIX)

You can also review the Event Viewer log on Windows and the syslog on Linux or UNIX. 

7.9) What Are Diagnostic Log Configuration Files?

Diagnostic log configuration files control output to diagnostic log files for Oracle Business Intelligence.
Log configuration files for Oracle Business Intelligence are stored in the following locations:

$ORACLE_INSTANCE/config/component_type/bi_component_name

Example:
logconfig.xml is the same as configuring at

http://server.domain:7001/em

Fam_bifoundation_domain-Business Intelligence-coreapplications-Diagnostics-Log Configuration

 

OBIEE Auto Cache Purge

This post will explain how to clear the BI Server cache using either (1) a script, or (2) the user interface.
You may need to clear your cache to allow the data in the cache to get refreshed.

(1) Clearing the cache using Command Line script …
This example is for Linux/Unix, but can be easily adjusted for Windows.
Create a file called purgecache.txt … and place it at … [FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup
In the file, enter the code “call SAPurgeAllCache()” – (without the quotes) – which is a special BI Server command for clearing the entire cache.
Then create a shell script called purgecache.sh, located in a directory where you store your custom scripts … which includes the following 2 commands …
 (Note: there is a space after the dot in the below command)
. /[FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh  
(Note: the below is ONE long command; not 2 commands)
[FMW_HOME]/Oracle_BI1/bifoundation/server/bin/nqcmd -d AnalyticsWeb -u administrator -p pswd –s [FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/purgecache.txt
Then you may execute the shell script just as you would any other script (for example  ./purgecache.sh ) -or- you may add this new script to your crontab if desired – for example, to run each night before your cache seeding reports run.

(2) Clearing the cache using the Issue SQL page …
Log into OBIEE Analytics using an ID that has the “Administration” link privilege.
http://server:9704/analytics
Click on the “Administration” link located at the top right of the page.

Click on the “Issue SQL” link at the bottom left of the page.

In the Issue SQL window, enter the command:  “call SAPurgeAllCache()” (without the quotes)
Click the “Issue SQL” button to execute the command.

You should see a message indicating that the command was executed successfully (assuming you have caching turned on).
Your cache directory located at …
[FMW_HOME]/instances/instance1/bifoundation/OracleBIServerComponent/ coreapplication_obis1/cache
… should now be empty.


Thanks !!