Oracle Open World 2017

We are beginning our coverage of Oracle Open World 2017 a on day second with our hit list of topics to cover this year, please send us suggestions on twitter @GreatAnalytics and we’ll add them to our list. This year we will be razor focused on advanced analytics, big data and new analytical and processing capabilities being added to Oracle Financial Services Analytics (OFSAA).

  • Creating custom credit risk models in OFSAA (In Process…)
  • OFSAA and Hadoop, the state of the union (In Process…)
  • OOW2017: Oracle Enterprise R state of the union (In Process…)
  • Oracle Fusion Applications: The evolution of cloud ERP embedded analytics (In Process…)
  • OOW2017: Our updated Big Data Reference Architecture, leveraging Oracle’s full stack of analytical capabilities (In Process…)
  • OOW2017: Oracle Data Integration and Big Data, latest updates in the adoption patterns and features
  • OOW2017: Industry Update – Oracle Higher Education Analytics
  • OOW2017: Industry Update – Oracle Financial Services and Insurance Analytics

Where to download older versions of Java?

I have found myself asking where can I download old versions of Java several times lately. They are generally found on Oracle’s website on a version archive page. To help with direct acess to versions here’s a list with a few versions:

 

Version 64-bit JDK 64-bit JRE 32-bit JDK 32-bit JRE
8u25 (1.8) JDK JRE JDK JRE
7u72 (1.7) JDK JRE JDK JRE
6u45 (1.6) JDK JRE JDK JRE
5.0u22 (1.5) JDK JRE JDK JRE

Issue/Error with ODI Studio right click

As I work with the Oracle Business Intelligence Applications (OBIA) repository in ODI studio I have recently noticed I am no longer able to right click on objects. I have found two solutions, the first one is a work-around:

 

Work Around:

Let’s assume you want to right click on a particular folder or scenario, you notice as you do so the context menu does not come up, go ahead and do the following:

  1. Select the object with a left click
  2. Move your mouse pointer outside the object’s boundary, I prefer a little bit to the right
  3. Right click, the context menu should come up now

This work around works if you are restricted on changing your installation’s settings or using a hosted platform such as Citrix

 

Solution:

In cases where you have access to install software your system then you should look into the compatibility matrix for ODI Studio and the version of Java you are working with. In my case I noticed the hosting provider for my environment has setup JDK 1.7  64-bit, I noticed for some versions of ODI JDK 1.6 was required so I downloaded both 32 and 64 bit versions and pointed my odi.conf file to them version. The 64 bit version did solve my issue, which is great since I can allocate more memory to the client under this bit version.

 

Related:

ODI Tip: How to make sure a “Select distinct” is issued and an ODI interface returns a unique dataset with no duplicates

PROBLEM

 

As a developer I do have a need to make sure that the subset of columns I am mapping through from source to target on my ODI interface is unique, in other words, I want ODI to include a DISTINCT clause on the SELECT statement that will be issued on the source database.

 

SOLUTION

  • Open my interface on the ODI Interface designer
  • Click on the Flow tab on the bottom
  • Click on the Target object
  • On the Property Inspector, click on the “Distinct Rows” checkbox

    image

ETL Tuning in ODI / BI Apps–The #ETL_ANALYZE_WORK_TABLE parameter

One of the first things I do when I run into performance issues with ETL loads is to look at the source and target table statistics. Have they been collected before the current select / insert statement was issued?

It turns out that in Oracle BI Apps the #ETL_ANALYZE_WORK_TABLE parameter is turned off by default when a load plan is generated. This can make doing a high level review of your load plan execution tricky since there will be steps that will seem to be gathering statistics, when in reality, the ODI code generator just puts a placeholder instead of the code for statistics. An example of this is shown below:

 

image

 

image

 

SOLUTION:

Once I realized what the issue was with statistics not being gathered for my work tables I was able to zoom into the ETL_ANALYZE_WORK_TABLE variable by looking at my generated load plan as depicted below, and change its default value to Y. The variable is defined globally so once you change the definition this new default value will apply to any newly generated load plans.

 

image  

 

image

ODI: Purging OLD Sessions

One common administrative task that I find myself doing when I realize that my ODI logs are growing fairly large is purging old sessions from the log. The steps are fairly straightforward as follows:

 

  1. Login to your ODI Studio client
  2. To to the Operator View
  3. On the top right corner of your navigation pane, expand the menu and select purge log…

    image

  4. On the Purge Log screen you can select which old sessions to remove by date, agent, context, status, user and session name

    image

  5. Once you have set parameters as desired click on OK and the ODI session logs will be purged accordingly

 

Related:

How To: Manage your Oracle patch deployment life cycle using Oracle Support Patch Plans

Introduction

 

As part of my writing I often try to document and share best practices I develop on my day to day work, this one relates to formalizing the patch deployment process for your oracle environments. This approach is developed for organizations that have formal release cycles and have established procedures to take patches through test life cycles that; at a minimum, begin in a develop environment, followed by integration testing in a QA and culminate when patches are promoted to production.

I will try to keep this post brief so, at a high level, I have found that the best way to manage patches is to use the Oracle support portal patch & upgrades functionality to create a patch plan for each environment in the life cycle for either each major release or at least each quarter. This process is always initiated by the need to apply a patch so whenever no patches are necessary during a release or quarter no patch plans are created.

The two main benefits of this approach is (1) that it brings transparency into which patches have been approved for each environment, (2) it is a straight forward process that does not carry a lot of overhead. The way patches make it to a patch plan is when a project manager requests a patch to be applied or promoted to each environment in your life cycle, this in turn is monitored using standard project management mechanisms such as issue, task and test management.

 

Implementation

Creating your first patch plan is very simple, just take your first requested patch through the process outlined below.

 

  1. Login to http://support.oracle.com
  2. Click on the Patches & Updates tab
  3. Locate the appropriate version of your patch by specifying a patch number and operating system on the patch search interface

    Locate the appropriate version of your patch by specifying a patch number and operating system on the patch search interface

  4. Locate your patch on the search results screen and click on Add to Plan > Add to new …

    Locate your patch on the search results screen and click on Add to Plan > Add to new ...

  5. Locate the a valid target application server or host name using the search box
  6. Provide a patch plan name using your company’s naming standard and click create plan

    An example naming convention I have used in the past, this particular one allows system administrators to sort by date and to manage patch plans by product:

    – – – approved patches

    Provide a patch plan name using your company's naming standard and click create plan

  7. To add any additional requested patches to your plan go back to Patches & Updatesand select your plan from the Plans list and click on the Add Patch… button.

Having this patching plan makes it easy to manage patch deployment through your environments. As for the actual deployment of each patch, I am a command line geek and like the ability to make sure that each individual patch deployment works correctly by running OPatch for each individual package.

If you find this post useful please or Share our site!

Reference:

As part of my writing I often try to document and share best practices I develop on my day to day work, this one relates to formalizing the patch deployment process for your oracle environments …

Did you run out of time in Oracle Business Intelligence Applications (OBIA)?

There’s nothing worse than this right? I’m with you man (sister)!

All right in all due seriousness this can be an awkward situation where you come in Monday morning and your business stakeholders look deeply angry since none of their reports look right and they need to finish the close of the month/year/quarter.

Anyways, in both OBIA 7.9.x and 11g this is most likely due to the fact that the variables that control the generation of calendar tables are set to a date now in the past.

 

OBIA 11.1.1.7 Solution

  1. Log in to OBIA Configuration Manager (BIACM)
  2. Go to Manage Data Load Parameters
  3. Look for Configure Time Dimension > Gregorian Date Range End
  4. Change the END_DATE parameter to a date far far in the future
  5. Put on a contrite face and let your users know this will be fixed next time your load runs

OBIA 7.9.x Solution

The same general steps would need to be applied on your DAC client to fix this issue in versions of OBIA that use Informatica Power Center

  1. Open your DAC console client
  2. Navitate to Design > Tasks
  3. Look up the SIL_DayDimension task
  4. Look up the Parameters tab on the bottom panel
  5. Change the $$END_DATE parameter to a future date
  6. Same deal, contrite face, deep breath and break in the news that this won’t be fixed until tomorrow morning

 

Related:

Error when importing work repository in ODI Studio (java.lang.OutOfMemoryError: Java heap space)

INTRO

 

I am having an issue with the work repository in one of my environments this week to the point where I had to rebuild it. After dropping and recreating the schema I am running on a java heap space error.

clip_image002

 

SOLUTION

 

In my case the issue went away with the following steps:

  1. Unpack the repository content ZIP file I was importing into an uncompressed folder
  2. Up the MaxPermSize parameter on my ODI\client\odi\bin\odi.conf filefrom 512M to 1024M
    image

 

FULL ERROR MESSAGE

 

clip_image002

java.lang.OutOfMemoryError: Java heap space

                at java.lang.Class.getDeclaredMethods0(Native Method)

                at java.lang.Class.privateGetDeclaredMethods(Class.java:2427)

                at java.lang.Class.getDeclaredMethod(Class.java:1935)

                at com.sunopsis.tools.core.SnpsTools.getMethodFromHierarchy(SnpsTools.java:370)

                at com.sunopsis.tools.core.SnpsTools.getMethodFromHierarchy(SnpsTools.java:392)

                at com.sunopsis.tools.xml.SnpsXmlObjectParser.processValue(SnpsXmlObjectParser.java:611)

                at com.sunopsis.tools.xml.SnpsXmlObjectParser.endElement(SnpsXmlObjectParser.java:270)

                at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1588)

                at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:442)

                at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:388)

                at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:232)

                at com.sunopsis.tools.xml.SnpsXmlObjectParser.parseXmlFile(SnpsXmlObjectParser.java:390)

                at com.sunopsis.tools.xml.SnpsXmlObjectParser.parseXmlFile(SnpsXmlObjectParser.java:337)

                at com.sunopsis.tools.xml.SnpsXmlObjectParser.parseXmlFile(SnpsXmlObjectParser.java:347)

                at com.sunopsis.dwg.DwgObject.doImport(DwgObject.java:6747)

                at com.sunopsis.dwg.DwgObject.doImport(DwgObject.java:6620)

                at com.sunopsis.dwg.DwgObject.doImport(DwgObject.java:6578)

                at com.sunopsis.repository.manager.RepositoryManager.importObjectsUsingDoImport(RepositoryManager.java:5918)

                at com.sunopsis.repository.manager.RepositoryManager.treatObjectListGeneral(RepositoryManager.java:3985)

                at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImport(RepositoryManager.java:4506)

                at com.sunopsis.repository.manager.RepositoryManager.access$7(RepositoryManager.java:4395)

                at com.sunopsis.repository.manager.RepositoryManager$2.doAction(RepositoryManager.java:4369)

                at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

                at oracle.odi.core.persistence.dwgobject.TransactionalDwgObjectTemplate.execute(TransactionalDwgObjectTemplate.java:64)

                at com.sunopsis.repository.manager.RepositoryManager.internalWorkRepositoryImportWithCommit(RepositoryManager.java:4357)

                at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImport(RepositoryManager.java:4661)

                at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImportFromZipFile(RepositoryManager.java:4814)

                at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImportFromZipFileWithCommit(RepositoryManager.java:4884)

                at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImportFromZipFileWithCommit(RepositoryManager.java:4939)

                at com.sunopsis.graphical.dialog.SnpsDialogImportWork$1.run(SnpsDialogImportWork.java:155)

                at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:655)

                at java.lang.Thread.run(Thread.java:662)

 

RELATED

 

Other related issues I found when researching the solution are:

 

 

 

 

Configuring OBIEE to run as a Windows Service

Introduction

One key step to configuring an enterprise deployment of Oracle Business Intelligence is to be setup your services to run in the background as Windows services and start automatically with your server.  By default the installer will create windows services for Oracle Process Manager (OPMN) and the Weblogic Node Manager, this leaves us with the need to configure services for the Weblogic AdminServer and the BI Managed Server.

Pre-requisites

  • Verify boot.properties files exist for both Weblogic Servers

    AdminServer:
    %mw_home%\user_projects\domains\bifoundation_domain\servers\AdminServer\security\boot.properties

    BI Managed Server:
    %mw_home%\user_projects\domains\bifoundation_domain\servers\bi_server1\security\boot.properties

  • Define the MW_HOME Variable
  • Edit %MW_HOME%\wlserver_10.3\server\bin\installSvc.cmd to redirect standard output to a file and to set the service name prefix to “Oracle_”

    Log Syntax:
    -log:”%MW_HOME%\user_projects\domains\bifoundation_domain\servers\%SERVER_NAME%-stdout.txt”

    Example Customization:

    set MW_HOME=%WL_HOME%\..\

    rem *** Install the service”
    %WL_HOME%\server\bin\beasvc” -install -svcname:”Oracle_%DOMAIN_NAME%_%SERVER_NAME%” -javahome:”%JAVA_HOME%” -execdir:”%USERDOMAIN_HOME%” -maxconnectretries:”%MAX_CONNECT_RETRIES%” -host:”%HOST%” -port:”%PORT%” -extrapath:”%EXTRAPATH%” -password:”%WLS_PW%” -cmdline:%CMDLINE% -log:”%MW_HOME%\user_projects\domains\bifoundation_domain\servers\%SERVER_NAME%-stdout.txt”

    Note: Make sure you replace beasvc and the subsequent space with Oracle_ on the section for -svcname

  • Change the environment script

    %MW_HOME%\user_projects\domains\bifoundation_domain\bin\setOBIDomainEnv.cmd

  • Edit %MW_HOME%\wlserver_10.3\server\bin\installSvc.cmd to ensure the correct Java memory arguments are utilized by your windows service

    Old Code:
    call “%WL_HOME%\common\bin\commEnv.cmd”
    New Code:
    call “%WL_HOME%\..\user_projects\domains\bifoundation_domain\bin\setOBIDomainEnv.cmd”

  • Edit %MW_HOME%\wlserver_10.3\server\bin\installSvc.cmd to implement a workaround for the Windows limitation of the maximum length of the command line being 2KB
    • Locate the two instances where the script sets the value of the CMDLINE variable
    • Add the code below before each instance, this code will output the current value of CLASSPATH to a text file

      REM –
      REM output the class path to text file and change reference to file on CMDLINE variable
      REM this is a workaround to a limit on windows command line to 2KB
      echo %CLASSPATH% > %WL_HOME%\server\bin\classpath.txt

    • Replace the class path variable reference \”%CLASSPATH%\” with@%WL_HOME%\server\bin\classpath.txt as depicted in the example below

      set CMDLINE=”%JAVA_VM% %MEM_ARGS% %JAVA_OPTIONS% -classpath @%WL_HOME%\server\bin\classpath.txt -Dweblogic.Name=%SERVER_NAME% -Dweblogic.management.username=%WLS_USER% -Dweblogic.ProductionModeEnabled=%PRODUCTION_MODE% -Djava.security.policy=\”%WL_HOME%\server\lib\weblogic.policy\” weblogic.Server”

  • Read the Microsoft Support article on specifying the startup order of Windows Services
  • Using regedit, add the one group for each of the OBIEE processes to be started at the end of the list entry at:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ServiceGroupOrder

    Example groups:

    OBI Node Manager
    OBI AdminServer
    OBI Managed Server
    OBI OPMN

    This will sequence the startup of your services based on group

  • Note down the names of the OPMN and Node Manager Services from the registry

    Registry Location:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\

    Sample Service Names:

    OracleProcessManager_instance1
    Oracle WebLogic NodeManager (d_obi_mw_wlserver_10.3)

  • For each of the two services above add a string value (right click the registry folder and follow New > String Value) named Group and provide the corresponding group value for each service (ie. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle WebLogic NodeManager (d_obi_mw_wlserver_10.3)\Group=OBI Node Manager).

    This will work along with the ServiceGroupOrder configuration to ensure the startup order of your services

Implementation


AdminServer Service

  • Create a new script named %MW_HOME%\wlserver_10.3\server\bin\installAdminServer_svc.cmd and using the code below:

    SETLOCAL
    @echo off
    set MW_HOME=d:\obi_mw
    set DOMAIN_NAME=bifoundation_domain
    set USERDOMAIN_HOME=%MW_HOME%\user_projects\domains\%DOMAIN_NAME%
    set SERVER_NAME=AdminServer
    set PRODUCTION_MODE=true
    call “%MW_HOME%\wlserver_10.3\server\bin\installSvc.cmd”
    ENDLOCAL

  • Run the installAdminServer_svc.cmd script
  • Using regedit, verify that a service named Oracle_bifoundation_domain_AdminServer now exists under the following location

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\

  • Right click your service folder and follow the context menus New > String Value to add a new entry in your service folder, call your new string value Group and double click on it to add OBI AdminServer as a value. This will work in tandem with the ServiceGroupOrder configuration to ensure the startup order of your services.
  • Right click your service folder and follow the context menus New > Multi-String Value to add a new entry in your service folder, call your new string value DependOnService and double click on it to add the node manager service as a dependency, the node manager service must match the service listing you noted down as part of the pre-requisite preparation steps (ie. Oracle WebLogic NodeManager (d_obi_mw_wlserver_10.3)). Using this dependency value will cause Windows to verify that dependent services have been started before attempting to start this service.

BI Managed Server Service

  • Create a new script named %MW_HOME%\wlserver_10.3\server\bin\installbi_server1_svc.cmd and using the code below:

    SETLOCAL
    @echo off
    set MW_HOME=d:\obi_mw
    set DOMAIN_NAME=bifoundation_domain
    set USERDOMAIN_HOME=%MW_HOME%\user_projects\domains\%DOMAIN_NAME%
    set SERVER_NAME=bi_server1
    set PRODUCTION_MODE=true
    set ADMIN_URL=http://localhost:7001
    call “%MW_HOME%\wlserver_10.3\server\bin\installSvc.cmd”
    ENDLOCAL

  • Run the installbi_server1_svc.cmd script
  • Using regedit, verify that a service named Oracle_bifoundation_domain_bi_server1 now exists under the following location

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\

  • Right click your service folder and follow the context menus New > String Value to add a new entry in your service folder, call your new string value Group and double click on it to add OBI Managed Server as a value. This will work in tandem with the ServiceGroupOrder configuration to ensure the startup order of your services.
  • Right click your service folder and follow the context menus New > Multi-String Value to add a new entry in your service folder, call your new string value DependOnService and double click on it to add the node manager service and Weblogic AdminServer services as a dependencies (ie.Oracle_bifoundation_domain_AdminServer ). Using this dependency value will cause Windows to verify that dependent services have been started before attempting to start this service.
  • Right click the service folder for OPMN and follow the context menus New > Multi-String Value to add a new entry in your service folder, call your new string value DependOnService and double click on it to add the node manager service and Weblogic AdminServer  and your new BI Managed Server services as a dependencies (ie. Oracle_bifoundation_domain_bi_server1). Using this dependency value will cause Windows to verify that dependent services have been started before attempting to start this service.
  • On the Administration Tools > Services application verify that all of the following services are configured to start automatically and, optionally, configure what actions are taking on failure starting each service.

    Oracle WebLogic NodeManager
    Oracle_bifoundation_domain_AdminServer
    Oracle_bifoundation_domain_bi_server1
    Oracle Process Manager (instance 1)

Setup Validation

  • Restart your windows server and monitor the order in which services are started
  • If you see issues with OPMN starting at the same time as your WebLogic servers you might need to try setting the AdminServer, BI Managed Server and OPMN  services to start manually and using the code below to create batch command file that is executed by a scheduled task each time the computer starts:

    net start Oracle_bifoundation_domain_AdminServer
    timeout 300
    net start Oracle_bifoundation_domain_bi_server1
    timeout 300
    net start OracleProcessManager_instance1

    This script would use the timeout DOS command to institute a five minute wait between each of the OBI services being started.

One key step to configuring an enterprise deployment of Oracle Business Intelligence is to be setup your services to run in the background as Windows services and start automatically with your server.  By default the installer will create windows services for Oracle Process Manager (OPMN) and the Weblogic Node Manager, this leaves us with the need to configure services for the Weblogic AdminServer and the BI Managed Server.