What Is The Basic Setup Of The Changed Data Capture (Journalization) Feature In ODI

What is the basic setup procedure for implementing the CDC (Journalization) feature in Oracle Data Integrator (ODI)?

Details of ODI CDC setup can be found in the documentation library under the “Change Data Capture” topic.

  1. In the Model move to the Journalizing tab and choose the Journalizing Mode.
    • Double-click Model – Journalizing tab – Journalizing Mode, click radio button there for Simple or Consistent Set.
    • Simple Journalizing tracks changes in individual Datastores in a Model by the use of triggers (Simple) and crated views and tables (consistent).
    • Consistent Set Journalizing tracks changes to a group of Model Datastores and takes into account the referential integrity between these Datastores. The group of Datastores journalized in this mode is called a Consistent Set.
  2. Then select the Journalizing Knowledge Module (KM).
    • Double-click Model – Journalizing tab – Journalizing KM – Choose from the drop down list.
    • This Knowledge module must be imported into the Project’s Journalization JKM branch to be available for selection.
  3. The Options window should appear below and presents the options associated with the JKM.
    • Click in the Value section of the Options window to get a drop down menu of values, which can be chosen or left as default.
  4. After Journalization is set up, drill down in the Models panel to the Datastores of the journalized Model.
  5. Add the Datastore(s) to CDC.
    • Right click on the Model, click on Changed Data Capture and then on the Add to CDC.
  6. For Consistent Set Journalization, the order of those Datastores must be set in the Model on the Journalized Tables tab.
  7. Add the subscriber(s) to CDC.
    • Right click on the Model – Changed Data Capture – Subscriber – Subscribe…
    • Add subscriber to the text box on upper right and click on arrow button to add to list click OK.
    • The Execution pop up window shows and click OK button to execute subscriber addition.
    • Check Operator for success in addition of subscriber.
    • Subscribers: CDC uses a publish/subscribe model. Subscribers are the consumers of the data or entities that use the changes tracked on a datastore or on a consistent set. They subscribe to a model’s CDC to have the changes tracked for them. Changes are captured only if there is at least one subscriber to the changes. When all subscribers have consumed the captured changes, these changes are discarded from the journals.
  8. Then from the Model start the Journal.
    • Right click on the Model – Changed Data Capture – Start Journal
    • The Execution pop up window shows and click OK button to execute the journal startup.
    • Check Operator for success of journal startup.
    • This operation creates views for CDC and either triggers and/or the tables for CDC in consistent set.
    • Following are examples of those names created by the Journal.
      • Tables
        • SNP_CDC_*
        • J$
      • Views
      • Triggers
        • DT$
  9. From the Datastore icons, look for the tiny green clock that represents a journalized Datastore, click the refresh button if necessary.
  10. If the clock persists as yellow:
    • Right click on the Model – Changed Data Capture – Stop Journal
    • Determine the issue with the journal and restart journal.
  11. To view changes made, right click on the journalized Datastore – Changed Data Capture – Journal Data.
  12. Restart Changed Data Capture (CDC) processing:
    • The Model / Datastore in question is already journalized, and the ODI journalizing infrastructure still exists in the respective Working schema.
    • One should not re-start the journalization of a Model and/or Datastore if a previous journalization is still active.
    • The re-journalization is not possible without having stopped the already active one.
    • Hence, first stop the existing journalization, then start the new journalization.

Journalizing Status

Datastores in Models or Mappings / Integration Interfaces have an icon marker indicating their journalizing status in Designer’s current context:

  • (green clock icon) OK:Journalizing is active for this Datastore in the current context, and the infrastructure is operational for this Datastore.
  • (yellow clock icon) No Infrastructure:Journalizing is marked as active in the model, but no appropriate journalizing infrastructure was detected in the current context.

    Journals should be started.

    This state may occur if the journalizing mode implemented in the infrastructure does not match the one declared for the Model.

  • (white clock icon) Remnants:Journalizing is marked as inactive in the model, but remnants of the journalizing infrastructure such as the journalizing table have been detected for this datastore in the context.

    This state may occur if the journals were not stopped and the table has been removed from CDC.




Import ODI Objects From Older Versions

Is it possible to export ODI objects from previous release and import them in ODI newer release?

For example: export ODI 10g objects and import them in ODI (or from to etc …)

If so, how can this be performed?

It is technically possible to import ODI older objects into ODI Repositories or higher version, using:

  • The Smart Export / Smart Import feature, or
  • Export / Import of Topology and Security components in the form of XML export files, or
  • Export / Import of Work Repository versionable components, such as:
    • Models and Model Folders,
    • Projects, and Project Folders
    • Packages
    • Scenarios
    • Integration Interfaces
    • Procedures and Knowledge Modules
    • Sequences
    • User Functions
    • Variables
    • Solutions
    • Load Plans
The full import of a Master Repository though is not supported, and would lead to issues such as the one described in Note 1674134.1 – “This action is forbidden. Master repositories must be upgraded” Signaled When Attempting To Import Components Into ODI Repositories Rather Than Upgrading.

Such operations are supported but not recommended as full update will always bring more benefits such as KM bug fixes.

Also, additional restrictions concern the version of ODI from where objects are exported, and into where they are imported:

To ODI 10g To ODI 11g To ODI 12c
From ODI 10g Yes Yes (1) No
From ODI 11g No Yes Yes (2) (3)
From ODI 12c No No Yes


(1) The import from ODI 10g to ODI 11g requires at minimum ODI to be used.
For moving from ODI and older to ODI 11g, a preliminary/intermediate upgrade/import to ODI (or ODI is required.

(2) The import from ODI 11g to ODI 12c requires at minimum ODI to be used.

(3) Due to the introduction of GLOBAL_IDs, the import from previous version requires an Upgrade Key be specified in order to be able to generate consistent GLOBAL_ID values for the objects as they are upgraded before they are imported.



OBIEE 12c: Oracle Business Intelligence Enterprise 12c (12.2.1) Installation

Proceed with the important information in Table 2-2 before you begin the installation and configuration process. It identifies important tasks and checks to be performed to ensure that your environment is properly prepared for installing and configuring the Oracle Business Intelligence.

  • Verify certification and system requirements
  • Identify a proper installation user
  • Select the installation and configuration directories on your system
  • Install a certified JDK
  • Install and configure a database for mid-tier schemas
  • Hint  only unzip the following zip files:
    • fmw_12.2.1.x.0_infrastructure_Disk1_1of1.zip
    • fmw_12.2.1.x.0_bi_[platform]_Disk1_1of2.zip
    • fmw_12.2.1.x.0_bi_[platform]_Disk1_2of2.zip

      The latter two disks create setup_bi_platform-[platform]-2.zip, this zip file you do *not* want to unzip. Keep it zipped as the installer uses it in the zip format.

  • Example Linux install  (assumes RCU database is previously installed and configured).
    This example is with the base release; however, it also applies to any later patchset (e.g. –,, 12.2.1.x, etc.).

    • copy installation files or create a mount point for your installation files.
    • ensure you have method for graphical user interface (e.g. vnc or local xterm)
    • JDK: install your JDK in your desired location (system or local) and configure it in your PATH and JAVA_HOME

      for example:

      • cp jdk-8uXX-linux-x64.tar.gz ../jdk_location/
      • cd ../jdk_location/
      • tar -xvzf jdk-8uXX-linux-x64.tar.gz
      • export JAVA_HOME=../jdk_location/
      • export PATH=../jdk_location/bin
      • confirm:
        which java
      • confirm:
        java -version
        java version “1.8.0_XX”
        Java(TM) SE Runtime Environment (build 1.8.0_XX-b17)
        Java HotSpot(TM) 64-Bit Server VM (build 25.XX-b01, mixed mode
      • Install Infrastructure (including Weblogic and RCU):
        • java -jar fmw_12.2.1.x.0_infrastructure.jar




          Default: [your path]/Oracle/Middleware/Oracle_Home








      • Install OBIEE 12c Software:
        • ./bi_platform-











      • Configure OBIEE
        •  …/Oracle_Home/bi/bin/config.sh | cmd
        • Note: You may install the RCU schemas prior to installing or configuring OBI via the standalone RCU […/Oracle_Home/oracle_common/bin/rcu ] which has advanced features or during the Configuration Assistant



          Note: It is recommended to install the domain outside the [ORACLE_HOME]. It separates the configuration from the binaries for easier patchset patching.  For Development environments, the default is acceptable.










          bi_config_12The BI Epilogue zips and stores the installation and configuration logs via: [DOMAIN_HOME]/bitools/bin/diagnostic_dump.sh

OBIEE 12c SSL configured environment – Issue with uploadrpd and downloadrpd

Not able to uploadrpd or downloadrpd in SSL configured OBIEE 12c environment


./datamodel.sh uploadrpd -I SampleApp.rpd -W Admin123 -U weblogic -P Welcome1 -SI ssi -S testmachine.us.oracle.com -N 9503 -SSL
Connection failed, the host name, port number or protocol (http/https) may be incorrect.

./datamodel.sh downloadrpd -O SampleApp.rpd -W Admin123 -U weblogic -P Welcome1 -SI ssi -S testmachine,us.oracle.com -N 9503 -SSL
Connection failed, the host name, port number or protocol (http/https) may be incorrect.

The root cause is SSL communication failure.

The command, ./datamodel.sh  needs to be executed with Keystore used for SSL communication.

By default the command uses Demo Keystore for SSL communication

If SSL is configured with Custom Keystore, it needs to be included in the command option for successful SSL handshake.

The following patch 24967379 is needed to get the command options to include the Custom Keystore.

The new command options are [-TS trustStore jks filename location] [-TSP trust store password]


./datamodel.sh uploadrpd -I SampleApp.rpd -W Admin123 -U weblogic -P Welcome1 -SI ssi -S testmachine.us.oracle.com -N 9503 -SSL -TS /refresh/home/ssl/customkeystore.jks -TSP Welcome1

./datamodel.sh downloadrpd -O SampleApp.rpd -W Admin123 -U weblogic -P Welcome1 -SI ssi -S testmachine,us.oracle.com -N 9503 -SSL -TS /refresh/home/ssl/customkeystore.jks -TSP Welcome1

OBIEE 11g: Graphs Displaying A Grey Background using Firefox

Graphs Displaying A Grey Background using Firefox

On : version, Analytics

OBIEE graphs displaying a grey background

All of a sudden all OBIEE graphs began displaying a dark grey background. Even after changing the graph properties to white, the graph is still outlined in this grey border.

OBIEE graphs should display a white background (default).

The issue can be reproduced at will with the following steps:
1. Create or open a report containing a graph.
2. Graph is displayed with a grey background.
3. Even after changing the graph properties to white, the graph is still outlined in this grey border.

This is a known issue with Firefox.

If you’re having problems with Firefox, refreshing it can help.

1. Restart Firefox in safe mode, with all plug-ins disabled.

2. Have the users refresh their browser. This should resolve the grey background.

The only real solution to this would be to contact Firefox to see if they have a permanent solution or use a version of Firefox or other browser that does not cause this problem.

More detail can be found at:



OBIEE 12c : How To Use Session Variable

How to configure a custom dashboard as the default page when users login to Answers in OBIEE 12c?

Make sure ‘Default Dashboard’ is set to ‘Default’ in the ‘My Account’ link.

1. Login as Administrator.
Create a new Dashboard by clicking New> Dashboard from the menu items

2. Enter
Name = Test3
Location = /Shared Folders/_portal/Dashboards

3.Add the required reports and prompts on the dashboard page and save it.
4. Click on Catalog menu. Navigate to Shared Folders > _portal > Test3.
Click More > Properties for the page1 in the right window. Copy the Location value ‘/shared/_portal/Test3’.

5. Click More > Permissions. Set Open/Read permissions for Authenticated User and BI Consumer roles.
6. Open the Admin tool in online node.
7. Click Manage > Variables
8 Create new Session variable initialization block. Name = SET_PORTALPATH
9. Click Edit Data Source
Select Data Source Type = Database
Select Use OBIEE Server
10. Set the Variable Target
Default Initializer = ‘/shared/_portal/Test3’
11. Save and check in changes
12. Login to OBIEE as Administartor.
Navigate to Admin Screen.
13. Click Reload Files and Metadata
14. Test login with other users and make sure the custom page shows up first.

How to Enable Smart View Logging?

Smartview logs we generally require for our audit purpose and some time to raise an issue with Oracle.

Follow below steps.

  1. Office 2003: Point to Hyperion -> Options
    Office 2007 and above: Choose the Options button in the SmartView ribbon
  2. SmartView versions prior to 11.1.2: Select the Display tab
    SmartView 11.1.2 and above: Select Advanced
  3. In the ‘Log Messages’ or ‘Logging’ section, select ‘Route Messages to File’
  4. Enter a valid filename, e.g. C:\SmartViewLog.txt
  5. Unless directed otherwise by Support, select Information. In SmartView 11.1.2 and above, this is in the Log Message Display drop-down menu.
  6. Choose OK.

Now reproduce the problem and any error messages. Close Excel and attach the file (e.g. C:\SmartViewLog.txt) to your SR. Afterwards, either disable logging or change the logging level to ‘Error’ (see step 5 above) to ensure good performance.

Hyperion Financial Management (HFM) Application Not Releasing Database Connections

HFM applications taking too many connections and releasing the connections once the user log off.

This is actually a bug and there is a patch set available to fix.

To implement the solution, please execute the following steps:

1. Download and review the readme Oracle Patch 23527466 .

2. Apply the patch in a test environment.

3. Retest the issue.

4. Migrate the solution as appropriate to other environments.

Hyperion Financial Management (HFM) Application Startup Fails

Generally we copy the database via application backup from and restored in brand new installation and then upgraded using in place upgrade option.

1. It initially failed with errors below:

<Apr 29, 2015 12:49:51 PM PDT> <Error>
<oracle.FM.HSXDATAACCESS.oracle.epm.fm.dal.manager.DALSession> <BEA-000000> <An error occurred in Data Access Layer. Message: Could not rollback as there is no open transaction.>
<Apr 29, 2015 12:49:51 PM PDT> <Error> <oracle.FM.HSX.SERVER.oracle.epm.fm.common.HsxServerConfig> <BEA-000000> <An unexpected error has occurred. Message: An error occurred in Data Access Layer. Message: Could not rollback as there is no open transaction. oracle.epm.fm.dal.exception.DALException: An error occurred in Data Access Layer. Message: Could not rollback as there is no open transaction.

2. After running the Schema upgrade utility again, user was able to open the application and load data, metadata without any problems. The application is running fine. The below error occurs in the HFM application logs frequently.

<Apr 29, 2015 12:49:51 PM PDT> <Warning>
<oracle.FM.HSXDATAACCESS.oracle.epm.fm.dal.dao.BaseDAOImpl> <BEA-000000>
<Apr 29, 2015 12:49:51 PM PDT> <Error>
<BEA-000000> <An error occurred while reading settings from the table : [  ][SQLServer JDBC Driver][SQLServer]Invalid column name ‘UPDATEDON’..
java.sql.SQLSyntaxErrorException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Invalid column name ‘UPDATEDON’.
at weblogic.jdbc.sqlserverbase.ddb_.b(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddb_.a(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddb9.b(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddb9.a(Unknown Source)

3. In addition, the following occurs:

  • Application is in Admin mode and user receives the following error:

EPMHFM-66122: An unexpected error occurred while stopping the admin task for Logistics application.


  • Unknown Error will occur when accessing Settings and Admin tasks menu under Admin tasks.

The root Cause for this issue is XFM_PARAMETERS and XFM_PARAMETERS_DEFAULTS tables are not created as part of inplace upgrade process.


First check whether the XFM_PARAMETERS and XFM_PARAMETERS_DEFAULTS are created in the HFM database.

If not, user needs to execute the below two scripts:

1. <EOH>\products\FinancialManagement\Server\conf\<DBVENDOR>_from_11.

2. <EOH>\products\FinancialManagement\Server\conf\<DBVENDOR>_from_11.


Note: This issue occurs when upgrading to using in-place upgrade option.

NOTE : – Make sure you have a FULL backup before making any changes to the system.




Hyperion Planning “Grid Spreading” vs. “Mass Allocate” Features



Create a scenario that is valid from August to December

Refresh the Essbase database

Create a data form with the following column selection

In Page select the scenarios:

The data form with the correct scenario shows the correct cells for data entry:

The data form does not show the immediate children of the YearTotal member. Since grid spreading spreads from parent to children and so on until the level 0 members, spreading will not work here as quarters (the immediate children of YearTotal) are not displayed on the data form.


Clicking on spread does not spread the value across, as explained above:

Modify the data form to display quarters in order to make spreading work:

Note: The above applies to any dimension used with grid spreading.


When using “Mass Allocate” to do the spreading, the Planning user (even if they are already the application owner) needs to be provisioned with the “Mass Allocation” role in Shared Services.

Enable Mass Allocation on the webform:

Select the cell that will contain the value to be spread:

Note that using “Mass Allocate” in such a situation will spread the value across all children, even those that are read only. This is because Mass Allocate writes directly to Essbase, bypassing existing security filters. This is why a separate Mass Allocation” role must be assigned in Shared Services before you can use it.


Use Grid Spreading if you need to spread values whilst respecting the existing read only/writeable security settings on a form. In order to allow Grid Spreading, you must ensure that all generations of the members you want to spread from exist on the form.