11G authentication failure - Validate Catalog

When you move repositories between machines and copy the catalog it is possible that there will be conflicting permissions between the details in the catalog and details held in weblogic.

I have previously mentioned how to reset the GUIDS - the glue between weblogic authentication and the catalog.

Sometimes this is not enough - usually when you find that have no permission to change settings on your My Dashboard or you cannot change settings in My Account.

A light touch 'refresh' may be something like:
If you add the following to your instanceconfig.xml file
<Validate>OnStartup</Validate>
<ValidateItems>None</ValidateItems>
<ValidateLinks>None</
ValidateLinks>
<ValidateAccounts>Clean</ValidateAccounts>
<ValidateHomes>None</ValidateHomes>
and open a command line in ../instances/instance1/bin
you can use opmnctl to stop and start the services. This may take some time depending on the size of your catalog.
Remove the Validate section from your instanceconfig.xml and stop/start opmnctl.

To force a 'deep clean' you can opt for something like:

<ps:Validate>OnStartupAndExit</ps:Validate>

<ps:ValidateAccounts>Clean</ps:ValidateAccounts>

<ps:ValidateHomes>Report</ps:ValidateHomes>

<ps:ValidateItems>Clean</ps:ValidateItems>

<ps:ValidateLinks>Clean</ps:ValidateLinks>

As always the documentation is available here from Oracle.

Hiding Catalog and Open links from Users in OBIEE 11.1.1.5

Oracle support have thought of this for us - Useful but limiting and only really applies to 11.1.1.5

How to remove Catalog, Open, Advanced, Help, Home and Search links in OBIEE 11.1.1.5 [ID 1349315.1]

In order to accomplish this following steps need to be performed:

In OBIEE 11.1.1.5 the following new expressions have been added in saw.sessioninfos.xml file. This file is under $ORACLE_HOME/bifoundation/web/msgdb/common/

Edit saw.sessioninfos.xml file, you will see following tags:

<gdexpression id="hdrLinkCatalog" expr="true" />
<gdexpression id="hdrLinkOpen" expr="true" />
<gdexpression id="hdrLinkAdvanced" expr="true" />
<gdexpression id="hdrLinkHelp" expr="true" />
<gdexpression id="hdrLinkHome" expr="true" />
<gdexpression id="hdrLinkGSearch" expr="true" />

Each of these control the display of a UI element in the global header.
By setting the value to "false" instead of "true", the corresponding UI element in the global header can be hidden.

For changes to take effect, please re-start BI Services and clear Browser cache.

IMPORTANT: Finally, note that this change is global, meaning will affect all users. Currently there is an Enhancement Request which has as objective to control these changes via permissions so it can be set at user level, (Bug 10173641).

Upgrade 11.1.1.3 to 11.1.1.5

Only undertake the OBIEE upgrade if you have fully backed up database and OBIEE install. This is an in-place upgrade that is not for the faint hearted and I would recommend setting a good 2-3 hours aside to finish.

Pre-requisites.

The details in this article only really apply if you are:
1. Running 32 bit Windows Server 2003 and OBIEE 11.1.1.3 with Weblogic 10.3.3
2. You have installed OBIEE 11.1.1.3 in D:\OBIEE11G

You have read the upgrade / patch details on the Oracle Website

You have downloaded the Weblogic patch from Oracle

You have downloaded the OBIEE 11.1.1.5 disks from Oracle and unpacked them correctly.

Step 1.


Shut down all beasvc processes, OPMN and the Oracle Weblogic NodeManager. No processes or threads should be running as services for the next steps.

Step 2.

Upgrade Weblogic using the patch (wls1035_upgrade_win32.exe).

Select "Next" and accept all defaults.

Step 3.

Some settings have not set correctly in D:\OBIEE11G\user_projects\domains\bifoundation_domain\bin\setDomainEnv.cmd.

Edit the file and ensure that the correct (new) paths are set for all the JAVA HOMES.

set BEA_JAVA_HOME=D:\OBIEE11G\jrockit_160_24_D1.1.2-4

set SUN_JAVA_HOME=D:\OBIEE11G\jdk160_24

if "%JAVA_VENDOR%"=="Oracle" (
 set JAVA_HOME=%BEA_JAVA_HOME%
) else (
 if "%JAVA_VENDOR%"=="Sun" (
  set JAVA_HOME=%SUN_JAVA_HOME%
 ) else (
  set JAVA_VENDOR=Oracle
  set JAVA_HOME=D:\OBIEE11G\jrockit_160_24_D1.1.2-4
 )
)

Step 4.

Now will perform a software only install of 11.1.1.5. Start the installer and select software only install, the existing middleware home and defaults for all the other options.

Step 5.

Now we upgrade the MDS and BIPLATFORM schemas. Ensure these are backed up before starting.
From the command line we will now upgrade the database schemas

cd D:\OBIEE11G\Oracle_BI1\bin

psa.bat -dbConnectString localhost:1521:dbname -dbaUserName sys -schemaUserName YOUR_BIPLATFORM

You will be prompted for the password for SYS and YOUR_BIPLATFORM. Here is the output.

D:\OBIEE11G\Oracle_BI1\bin>psa.bat -dbConnectString strashtdbs043v:1521:tkitt01
-dbaUserName sys -schemaUserName TEST_BIPLATFORM
Oracle Fusion Middleware Patch Set Assistant 11.1.1.5.0

Enter the database administrator password for "sys":
Enter the schema password for schema user "YOUR_BIPLATFORM":
Log file is located at: D:\OBIEE11G\Oracle_BI1\upgrade\logs\psa2011-11-15-13-55-
22PM.log
Updating the Oracle Fusion Middleware Metadata Schema to release 11.1.1.5.0.
Updating schema for component Oracle BI PLATFORM.
The command completed successfully

The we upgrade the MDS schema in a similar fashion.

psa.bat -dbConnectString localhost:1521:dbname-dbaUserName sys -schemaUserName YOUR_MDS



Once again you will be prompted for the SYS and MDS password.

D:\OBIEE11G\Oracle_BI1\bin>psa.bat -dbConnectString strashtdbs043v:1521:tkitt01
-dbaUserName sys -schemaUserName TEST_MDS
Oracle Fusion Middleware Patch Set Assistant 11.1.1.5.0

Enter the database administrator password for "sys":
Enter the schema password for schema user "TEST_MDS":
Log file is located at: D:\OBIEE11G\Oracle_BI1\upgrade\logs\psa2011-11-15-13-57-
17PM.log
Updating the Oracle Fusion Middleware Metadata Schema to release 11.1.1.5.0.
Updating schema for component Oracle Metadata Services.
The command completed successfully


Step 6.

Now we will upgrade the system components. Open a seperate command window and

cd d:\OBIEE11G\user_projects\domains\bifoundation_domain\bin
startWebLogic.cmd


Once Weblogic has started the following commands can be run:

cd d:\OBIEE11G\Oracle_BI1\opmn\bin
set ORACLE_HOME=d:\OBIEE11G\Oracle_BI1
upgradenonj2eeapp.bat -oracleInstance d:\OBIEE11G\instances\instance1 -adminHost localhost -adminPort 7001 -adminUsername weblogic

Replace the "localhost" with your machine name, and you should have an output similar to the following. You will be prompted for the weblogic adminuser password a couple of times as the instance is re-registered.

D:\OBIEE11G\Oracle_BI1\opmn\bin>upgradenonj2eeapp.bat -oracleInstance d:\OBIEE11G\instances\instance1 -adminHost xxxxxxxxxx -adminPort 7001 -adminUsername weblogic
Unregister instance...

Command requires login to weblogic admin server (xxxxxxxxxx):
  Username: weblogic
  Password:

Unregistering instance
Command succeeded.
redeploy NonJ2EEManagement.ear...

Command requires login to weblogic admin server (xxxxxxxxxx):
  Username: weblogic
  Password:

Redeploying NonJ2EEManagement Application...weblogic.Deployer invoked with options:  -adminurl xxxxxxxxxx:7001 -username weblogic -name NonJ2EEManagement -source D:\OBIEE11G\Oracle_BI1\opmn\applications\NonJ2EEManagement.ear -redeploy -upload -noexit
<15-Nov-2011 14:12:31 o'clock GMT> <Info> <J2EE Deployment SPI> <BEA-260121> <Initiating redeploy operation for application, NonJ2EEManagement [archive: D:\OBIEE11G\Oracle_BI1\opmn\applications\NonJ2EEManagement.ear], to configured targets.>
Task 0 initiated: [Deployer:149026]deploy application NonJ2EEManagement [Version=11.1.1] on AdminServer.
Task 0 completed: [Deployer:149026]deploy application NonJ2EEManagement [Version=11.1.1] on AdminServer.
Target state: redeploy completed on Server AdminServer

Done
Command succeeded.
re-register instance...

Command requires login to weblogic admin server (xxxxxxxxxx):
  Username: weblogic
  Password:

Registering instance
Command succeeded.
Successfully upgraded NonJ2EEManagement.ear and the registeration.
D:\OBIEE11G\Oracle_BI1\opmn\bin>
Step 7.

Upgrade Fusion Middleware Shared Libraries, and Security Elements. First we need to shutdown Weblogic

cd d:\OBIEE11G\user_projects\domains\bifoundation_domain\bin

stopWebLogic.cmd

This will close down the Weblogic command window you started previously.

Now we do some Weblogic Scripting to upgrade the

cd d:\OBIEE11G\oracle_common\common\bin

wlst

upgradeJRF('d:/OBIEE11G/user_projects/domains/bifoundation_domain')

upgradeOpss(jpsConfig="d:/OBIEE11G/user_projects/domains/bifoundation_domain/config/fmwconfig/jps-config.xml", jaznData="d:/OBIEE11G/oracle_common/modules/oracle.jps_11.1.1/domain_config/system-jazn-data.xml")

exit()
----- output ------------
D:\OBIEE11G\oracle_common\common\bin>wlst

CLASSPATH=D:\OBIEE11G\patch_wls1035\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\patch_oepe1050\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\patch_ocp360\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\JROCKI~1.2-4\lib\tools.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\weblogic_sp.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\weblogic.jar;D:\OBIEE11G\modules\features\weblogic.server.modules_10.3.5.0.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\webservices.jar;D:\OBIEE11G\modules\ORGAPA~1.1/lib/ant-all.jar;D:\OBIEE11G\modules\NETSFA~1.0_1/lib/ant-contrib.jar;;D:\OBIEE11G\ORACLE~1/modules/oracle.jrf_11.1.1/jrf-wlstman.jar;D:\OBIEE11G\ORACLE~1\common\wlst\lib\ADF-SH~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\lib\ADFSCR~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\lib\mdswlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\AUDITW~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\IGFWLS~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\jps-wlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\jrf-wlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OAMAP_~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OAMAUT~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\ossoiap.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OSSOIA~1.JAR;D:\OBIEE11G
\ORACLE~1\common\wlst\RESOUR~1\OVDWLS~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\SSLCON~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\wsm-wlst.jar

PATH=D:\OBIEE11G\patch_wls1035\profiles\default\native;D:\OBIEE11G\patch_oepe1050\profiles\default\native;D:\OBIEE11G\patch_ocp360\profiles\default\native;D:\OBIEE11G\WLSERV~1.3\server\native\win\32;D:\OBIEE11G\WLSERV~1.3\server\bin;D:\OBIEE11G\modules\ORGAPA~1.1\bin;D:\OBIEE11G\JROCKI~1.2-4\jre\bin;D:\OBIEE11G\JROCKI~1.2-4\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;D:\OBIEE11G\Oracle_BI1\bin;D:\OBIEE11G\Oracle_BI1\opmn\bin;D:\OBIEE11G\Oracle_BI1\opmn\lib;D:\OBIEE11G\Oracle_BI1\perl\bin;D:\OBIEE11G\Oracle_BI1\products\Essbase\EssbaseServer\bin;D:\OBIEE11G\WLSERV~1.3\server\native\win\32\oci920_8

Your environment has been set.

CLASSPATH=D:\OBIEE11G\patch_wls1035\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\patch_oepe1050\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\patch_ocp360\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\JROCKI~1.2-4\lib\tools.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\weblogic_sp.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\weblogic.jar;D:\OBIEE11G\modules\features\weblogic.server.modules_10.3.5.0.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\webservices.jar;D:\OBIEE11G\modules\ORGAPA~1.1/lib/ant-all.jar;D:\OBIEE11G\modules\NETSFA~1.0_1/lib/ant-contrib.jar;;D:\OBIEE11G\ORACLE~1/modules/oracle.jrf_11.1.1/jrf-wlstman.jar;D:\OBIEE11G\ORACLE~1\common\wlst\lib\ADF-SH~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\lib\ADFSCR~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\lib\mdswlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\AUDITW~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\IGFWLS~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\jps-wlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\jrf-wlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OAMAP_~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OAMAUT~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\ossoiap.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OSSOIA~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OVDWLS~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\SSLCON~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\wsm-wlst.jar;D:\OBIEE11G\utils\config\10.3\config-launch.jar;D:\OBIEE11G\WLSERV~1.3\common\derby\lib\derbynet.jar;D:\OBIEE11G\WLSERV~1.3\common\derby\lib\derbyclient.jar;D:\OBIEE1
1G\WLSERV~1.3\common\derby\lib\derbytools.jar;;

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline>
wls:/offline> upgradeJRF('d:/OBIEE11G/user_projects/domains/bifoundation_domain'
)
upgradeJRF: BEGIN
Apply patch template
Create Library "adf.oracle.businesseditor#1.0@11.1.1.2.0"
Target Library "adf.oracle.businesseditor#1.0@11.1.1.2.0" to JRF "bi_cluster"
Target Library "adf.oracle.businesseditor#1.0@11.1.1.2.0" to JRF "AdminServer"
Create Library "oracle.adf.desktopintegration.model#1.0@11.1.1.2.0"
Target Library "oracle.adf.desktopintegration.model#1.0@11.1.1.2.0" to JRF "bi_cluster"
Target Library "oracle.adf.desktopintegration.model#1.0@11.1.1.2.0" to JRF "AdminServer"
Create Library "oracle.adf.desktopintegration#1.0@11.1.1.2.0"
Target Library "oracle.adf.desktopintegration#1.0@11.1.1.2.0" to JRF "bi_cluster"
Target Library "oracle.adf.desktopintegration#1.0@11.1.1.2.0" to JRF "AdminServer"
Target Library instances from sub-templates:
Target Library "oracle.bi.adf.model.slib#1.0@11.1.1.2.0" to JRF "bi_cluster"
Target Library "oracle.bi.adf.model.slib#1.0@11.1.1.2.0" to JRF "AdminServer"
Target Library "oracle.bi.adf.view.slib#1.0@11.1.1.2.0" to JRF "bi_cluster"
Target Library "oracle.bi.adf.view.slib#1.0@11.1.1.2.0" to JRF "AdminServer"
Target Library "oracle.bi.adf.webcenter.slib#1.0@11.1.1.2.0" to JRF "bi_cluster"
Target Library "oracle.bi.adf.webcenter.slib#1.0@11.1.1.2.0" to JRF "AdminServer"
Target Library "oracle.bi.jbips#11.1.1@0.1" to JRF "bi_cluster"
Target Library "oracle.bi.jbips#11.1.1@0.1" to JRF "AdminServer"
Target Library "oracle.bi.composer#11.1.1@0.1" to JRF "bi_cluster"
Target Library "oracle.bi.composer#11.1.1@0.1" to JRF "AdminServer"
Create StartupClass "Web Services Startup Class"
Target StartupClass "Web Services Startup Class" to JRF "bi_cluster"
Target StartupClass "Web Services Startup Class" to JRF "AdminServer"
Set DiagnosticContextEnabled for server "bi_server1"
Set DiagnosticContextEnabled for server "AdminServer"
Copy JRF configuration files from D:\OBIEE11G\ORACLE~1/modules to d:/OBIEE11G/user_projects/domains/bifoundation_domain/config/fmwconfig/servers/bi_server1
Copy JRF configuration files from D:\OBIEE11G\ORACLE~1/modules to d:/OBIEE11G/user_projects/domains/bifoundation_domain/config/fmwconfig/servers/AdminServer
upgradeJRF: FINISH
wls:/offline>
wls:/offline>upgradeOpss(jpsConfig="d:/OBIEE11G/user_projects/domains/bifoundation_domain/config/fmwconfig/jps-config.xml", jaznData="d:/OBIEE11G/oracle_common/modules/oracle.jps_11.1.1/domain_config/system-jazn-data.xml")
Starting upgrade of jps configuration and security stores.
WLS ManagedService is not up running. Fall back to use system properties for configuration.
15-Nov-2011 14:23:49 oracle.security.jps.internal.tools.utility.destination.apibased.JpsDstPolicy <init>
WARNING: No identity store associate with policy store found.
Upgrade of jps configuration and security stores is done.
wls:/offline>
wls:/offline>exit()


Exiting WebLogic Scripting Tool.

D:\OBIEE11G\oracle_common\common\bin>

and now we upgrade the OBIEE home.

wlst d:\OBIEE11G\Oracle_BI1\bin\bi-upgrade.py --bioraclehome d:\OBIEE11G\Oracle_BI1 --domainhome d:\OBIEE11G\user_projects\domains\bifoundation_domain


and finally the  policy repository, and for this we need to start up weblogic from a new command window.

cd d:\OBIEE11G\user_projects\domains\bifoundation_domain\bin\
startWebLogic.cmd

once the server is running... replace localhost with your machine name and password with your weblogic adminuser password

cd d:\OBIEE11G\oracle_common\common\bin
wlst
connect ('weblogic','password','t3://localhost:7001')
upgradeWSMPolicyRepository()
exit()


this will give you something like the following output

D:\OBIEE11G\oracle_common\common\bin>wlst

CLASSPATH=D:\OBIEE11G\patch_wls1035\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\patch_oepe1050\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\patch_ocp360\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\JROCKI~1.2-4\lib\tools.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\weblogic_sp.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\weblogic.jar;D:\OBIEE11G\modules\features\weblogic.server.modules_10.3.5.0.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\webservices.jar;D:\OBIEE11G\modules\ORGAPA~1.1/lib/ant-all.jar;D:\OBIEE11G\modules\NETSFA~1.0_1/lib/ant-contrib.jar;;D:\OBIEE11G\ORACLE~1/modules/oracle.jrf_11.1.1/jrf-wlstman.jar;D:\OBIEE11G\ORACLE~1\common\wlst\lib\ADF-SH~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\lib\ADFSCR~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\lib\mdswlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\AUDITW~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\IGFWLS~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\jps-wlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\jrf-wlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OAMAP_~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OAMAUT~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\ossoiap.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OSSOIA~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OVDWLS~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\SSLCON~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\wsm-wlst.jar

PATH=D:\OBIEE11G\patch_wls1035\profiles\default\native;D:\OBIEE11G\patch_oepe1050\profiles\default\native;D:\OBIEE11G\patch_ocp360\profiles\default\native;D:\OBIEE11G\WLSERV~1.3\server\native\win\32;D:\OBIEE11G\WLSERV~1.3\server\bin;D:\OBIEE11G\modules\ORGAPA~1.1\bin;D:\OBIEE11G\JROCKI~1.2-4\jre\bin;D:\OBIEE11G\JROCKI~1.2-4\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;D:\OBIEE11G\Oracle_BI1\bin;D:\OBIEE11G\Oracle_BI1\opmn\bin;D:\OBIEE11G\Oracle_BI1\opmn\lib;D:\OBIEE11G\Oracle_BI1\perl\bin;D:\OBIEE11G\Oracle_BI1\products\Essbase\EssbaseServer\bin;D:\OBIEE11G\WLSERV~1.3\server\native\win\32\oci920_8

Your environment has been set.

CLASSPATH=D:\OBIEE11G\patch_wls1035\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\patch_oepe1050\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\patch_ocp360\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\OBIEE11G\JROCKI~1.2-4\lib\tools.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\weblogic_sp.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\weblogic.jar;D:\OBIEE11G\modules\features\weblogic.server.modules_10.3.5.0.jar;D:\OBIEE11G\WLSERV~1.3\server\lib\webservices.jar;D:\OBIEE11G\modules\ORGAPA~1.1/lib/ant-all.jar;D:\OBIEE11G\modules\NETSFA~1.0_1/lib/ant-contrib.jar;;D:\OBIEE11G\ORACLE~1/modules/oracle.jrf_11.1.1/jrf-wlstman.jar;D:\OBIEE11G\ORACLE~1\common\wlst\lib\ADF-SH~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\lib\ADFSCR~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\lib\mdswlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\AUDITW~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\IGFWLS~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\jps-wlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\jrf-wlst.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OAMAP_~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OAMAUT~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\ossoiap.jar;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OSSOIA~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\OVDWLS~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\SSLCON~1.JAR;D:\OBIEE11G\ORACLE~1\common\wlst\RESOUR~1\wsm-wlst.jar;D:\OBIEE11G\utils\config\10.3\config-launch.jar;D:\OBIEE11G\WLSERV~1.3\common\derby\lib\derbynet.jar;D:\OBIEE11G\WLSERV~1.3\common\derby\lib\derbyclient.jar;D:\OBIEE11G\WLSERV~1.3\common\derby\lib\derbytools.jar;;

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline>

wls:/offline> connect ('weblogic','xxxxxxxx','t3://xxxxxxxxx:7001')
Connecting to t3://xxxxxxxxxx:7001 with userid weblogic ...
Successfully connected to Admin Server 'AdminServer' that belongs to domain 'bif
oundation_domain'.

Warning: An insecure protocol was used to connect to the
server. To ensure on-the-wire security, the SSL port or
Admin port should be used instead.

wls:/bifoundation_domain/serverConfig> upgradeWSMPolicyRepository()
Location changed to domainRuntime tree. This is a read-only tree with DomainMBea
n as the root.
For more help, use help(domainRuntime)


Starting Operation upgradeWSMPolicyRepository ...


The following documents have been added to the repository.

oracle/no_addressing_policy
oracle/no_authentication_client_policy
oracle/no_authentication_service_policy
oracle/no_authorization_component_policy
oracle/no_authorization_service_policy
oracle/no_messageprotection_client_policy
oracle/no_messageprotection_service_policy
oracle/no_mtom_policy
oracle/no_wsrm_policy
oracle/sts_trust_config_client_policy
oracle/sts_trust_config_client_template
oracle/sts_trust_config_service_policy
oracle/sts_trust_config_service_template
oracle/whitelist_authorization_policy
oracle/wss10_saml20_token_client_policy
oracle/wss10_saml20_token_client_template
oracle/wss10_saml20_token_service_policy
oracle/wss10_saml20_token_service_template
oracle/wss10_saml20_token_with_message_protection_client_policy
oracle/wss10_saml20_token_with_message_protection_client_template
oracle/wss10_saml20_token_with_message_protection_service_policy
oracle/wss10_saml20_token_with_message_protection_service_template
oracle/wss11_kerberos_token_with_message_protection_basic128_client_policy
oracle/wss11_kerberos_token_with_message_protection_basic128_service_policy
oracle/wss11_saml20_token_with_message_protection_client_policy
oracle/wss11_saml20_token_with_message_protection_client_template
oracle/wss11_saml20_token_with_message_protection_service_policy
oracle/wss11_saml20_token_with_message_protection_service_template
oracle/wss11_sts_issued_saml_hok_with_message_protection_client_policy
oracle/wss11_sts_issued_saml_hok_with_message_protection_client_template
oracle/wss11_sts_issued_saml_hok_with_message_protection_service_policy
oracle/wss11_sts_issued_saml_hok_with_message_protection_service_template
oracle/wss11_sts_issued_saml_with_message_protection_client_policy
oracle/wss11_sts_issued_saml_with_message_protection_client_template
oracle/wss_saml20_token_bearer_over_ssl_client_policy
oracle/wss_saml20_token_bearer_over_ssl_client_template
oracle/wss_saml20_token_bearer_over_ssl_service_policy
oracle/wss_saml20_token_bearer_over_ssl_service_template
oracle/wss_saml20_token_over_ssl_client_policy
oracle/wss_saml20_token_over_ssl_client_template
oracle/wss_saml20_token_over_ssl_service_policy
oracle/wss_saml20_token_over_ssl_service_template
oracle/wss_saml_or_username_token_service_policy
oracle/wss_sts_issued_saml_bearer_token_over_ssl_client_policy
oracle/wss_sts_issued_saml_bearer_token_over_ssl_client_template
oracle/wss_sts_issued_saml_bearer_token_over_ssl_service_policy
oracle/wss_sts_issued_saml_bearer_token_over_ssl_service_template

The following documents have changed since the last installed version of the product.
 Please import the latest version of the files using Oracle Enterprise Manager.

oracle/binding_authorization_denyall_policy
oracle/binding_authorization_permitall_policy
oracle/binding_permission_authorization_policy
oracle/component_authorization_denyall_policy
oracle/component_authorization_permitall_policy
oracle/component_permission_authorization_policy
oracle/log_policy
oracle/wsaddr_policy
oracle/wsmtom_policy
oracle/wsrm10_policy
oracle/wsrm11_policy
oracle/wss10_message_protection_client_policy
oracle/wss10_message_protection_service_policy
oracle/wss10_saml_hok_token_with_message_protection_client_policy
oracle/wss10_saml_hok_token_with_message_protection_client_template
oracle/wss10_saml_hok_token_with_message_protection_service_policy
oracle/wss10_saml_hok_token_with_message_protection_service_template
oracle/wss10_saml_token_client_policy
oracle/wss10_saml_token_client_template
oracle/wss10_saml_token_service_policy
oracle/wss10_saml_token_service_template
oracle/wss10_saml_token_with_message_integrity_client_policy
oracle/wss10_saml_token_with_message_integrity_service_policy
oracle/wss10_saml_token_with_message_protection_client_policy
oracle/wss10_saml_token_with_message_protection_client_template
oracle/wss10_saml_token_with_message_protection_service_policy
oracle/wss10_saml_token_with_message_protection_service_template
oracle/wss10_saml_token_with_message_protection_ski_basic256_client_policy
oracle/wss10_saml_token_with_message_protection_ski_basic256_service_policy
oracle/wss10_username_id_propagation_with_msg_protection_client_policy
oracle/wss10_username_id_propagation_with_msg_protection_service_policy
oracle/wss10_username_token_with_message_protection_client_policy
oracle/wss10_username_token_with_message_protection_service_policy
oracle/wss10_username_token_with_message_protection_ski_basic256_client_policy
oracle/wss10_username_token_with_message_protection_ski_basic256_service_policy
oracle/wss10_x509_token_with_message_protection_client_policy
oracle/wss10_x509_token_with_message_protection_service_policy
oracle/wss11_kerberos_token_client_policy
oracle/wss11_kerberos_token_service_policy
oracle/wss11_kerberos_token_with_message_protection_client_policy
oracle/wss11_kerberos_token_with_message_protection_service_policy
oracle/wss11_message_protection_client_policy
oracle/wss11_message_protection_service_policy
oracle/wss11_saml_or_username_token_with_message_protection_service_policy
oracle/wss11_saml_token_identity_switch_with_message_protection_client_policy
oracle/wss11_saml_token_with_message_protection_client_policy
oracle/wss11_saml_token_with_message_protection_client_template
oracle/wss11_saml_token_with_message_protection_service_policy
oracle/wss11_saml_token_with_message_protection_service_template
oracle/wss11_username_token_with_message_protection_client_policy
oracle/wss11_username_token_with_message_protection_service_policy
oracle/wss11_x509_token_with_message_protection_client_policy
oracle/wss11_x509_token_with_message_protection_service_policy
oracle/wss_http_token_client_policy
oracle/wss_http_token_over_ssl_client_policy
oracle/wss_http_token_over_ssl_service_policy
oracle/wss_http_token_service_policy
oracle/wss_saml_or_username_token_over_ssl_service_policy
oracle/wss_saml_token_bearer_over_ssl_client_policy
oracle/wss_saml_token_bearer_over_ssl_client_template
oracle/wss_saml_token_bearer_over_ssl_service_policy
oracle/wss_saml_token_bearer_over_ssl_service_template
oracle/wss_saml_token_over_ssl_client_policy
oracle/wss_saml_token_over_ssl_client_template
oracle/wss_saml_token_over_ssl_service_policy
oracle/wss_saml_token_over_ssl_service_template
oracle/wss_username_token_client_policy
oracle/wss_username_token_over_ssl_client_policy
oracle/wss_username_token_over_ssl_service_policy
oracle/wss_username_token_service_policy

upgradeWSMPolicyRepositoryOperation Completed.

wls:/bifoundation_domain/serverConfig>

Step 8

Upgrade the Oracle BI Presentation Server Catalog
With the presentation server down we will do an upgrade of the catalog. First we have to edit the instanceconfig.xml file to force an upgrade.

<ps:Catalog xmlns:ps="oracle.bi.presentation.services/config/v1.1">
<ps:UpgradeAndExit>false</ps:UpgradeAndExit>
</ps:Catalog>

to 

<ps:Catalog xmlns:ps="oracle.bi.presentation.services/config/v1.1">
<ps:UpgradeAndExit>true</ps:UpgradeAndExit>
</ps:Catalog> 

Start the presentation service and then edit the instanceconfig.xml back to its original 'false' setting.

Now reboot to test that all your services come up as expected.

Good luck and let me know how you get on.

Adding a Excel spreadsheet as a datasource in OBIEE

OBIEE can combine Excel spreadsheets as sources of data into your BI solution.

Here's a quick run-through of the stepsyou should take to include the spreadsheet.



1. Open the excel file to the sheet in the workbook you want to use as a datasource.

2. Ensure you have no filters set.

3. Highlight the range of rows and columns you wish to use, including the column headings. The column headings will become the column names. Right click and give your range selection a name or you can go to Formulas-->Define Name and give the scope and range.

4. Each spreadsheet can have multiple named ranges. Each named range will become a "table".

5. Now you need to create a System DSN for the Excel data source. Open the Data Sources (ODBC) from the Control Panel or Administrative Tools menu.  Select the Microsoft Excel Driver from the list of drivers.

6. Give the DSN a name and browse to the spreadsheet with the named range and click on Ok.

7. Open the OBIEE Administraton Tool. For OBIEE 10g go to Administration tools and click on File>Import from Database. Select the DSN which you created for Excel. For OBIEE 11g select File>Import Metadata - the DSN should be in the list of DSN data sources available, select the DSN and continue to import as normal through the wizard.

8. When you click on Import, it will import all the tables present in that excel file.

9. Once you get the tables in physical layer, you can play around with those tables to create subject area.

Paper at Oracle Business Intelligence Conference

I have just presented a paper with my colleague at the recent OBIEE conference held at the London Bridge Hotel on Tuesday 8th November. Nerve racking as we only had a day's notice, but the presentation was well received.

We were demonstrating what we have achieved combining spatial data, namely a road network, with business data, the mechanisms behind the scene and some of the output we produce.

Drop me line if you want a copy of the presentation.

-- Update

Oracle have kindly put the presentation up and you can view it here.

OBIEE instanceconfig.xml

Some of the settings in your instanceconfig.xml file are maintained and set through the enterprise manager - usually available on http://localhost:7001/em.

I have found that sometimes (actually make that usually) the analysts want to consume and display more data than the default settings allow. But be aware that there is always a trade off, if you increase numbers somewhere, there is a knock on somewhere else, maybe a slower response as more rows are analysed by default. There are other side effects on the performance side, but none that need worry us here.

configuration changes in the instanceconfig.xml file

<Views>

<Pivot>

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

<DefaultRowsDisplayedInDownload>250000</DefaultRowsDisplayedInDownload>

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

<DisableAutoPreview>false</DisableAutoPreview>

</Pivot>

<Table>

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

<DefaultRowsDisplayedInDownload>250000</DefaultRowsDisplayedInDownload>

</Table>

<Charts>

<EnableWaterfall>true</EnableWaterfall>

</Charts>

</Views>

Change the instanceconfig.xml file in <biee11g_install>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1 as following

Look for the Views section and edit until the section looks something like:

<Views>
<Pivot>
<MaxCells>1920000</MaxCells>
<MaxVisibleColumns>30</MaxVisibleColumns>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>64000</MaxVisibleRows>
<MaxVisibleSections>25</MaxVisibleSections>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<DefaultRowsDisplayedInDownload>64000</DefaultRowsDisplayedInDownload>
<DisableAutoPreview>false</DisableAutoPreview>

</Pivot>
<Table>
<MaxCells>1920000</MaxCells>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>64000</MaxVisibleRows>
<MaxVisibleSections>25</MaxVisibleSections>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<DefaultRowsDisplayedInDownload>64000</DefaultRowsDisplayedInDownload></Table>
</Views>


Now Restart Presentation Server from EM, then run your request, do excel export to verify.

Remember, OBIEE is not a data extraction tool, and should not be configured to allow very large data extacts. This has some fairly significant performance implications.

How does location impact your business? Do you know, or are you just guessing?

"They" say that 80% of all business data can be mapped to a particular location or at least a geographic area, yet most BI platforms have only limited capabilities for location-based visualization and analysis. Although some have made the argument that location no longer matters in the digital world, I think those who make this argument are missing the point. After all, customers, potential customers, employees, and other corporate resources all exist someWHERE in space. Why is this aspect of business analytics so often ignored in the major platforms?

I will be presenting some of what we've been doing at the OBIEE seminar in London in November

What is a Bridge Table ?

If you want to connect two tables where there is no relationship, you can use a third table, or bridge table for connecting them. This table will have common columns in both tables, this is used in BMM Layer.

You would mainly use a bridge table to perform a many-to-many join without the bridge table being seen as the fact table. The bridge table is then joined in the Business Modelling Layer and 'tagged' as a bridge type or assignment.

Many-to-many relationships can cause nightmares in the modelling and, producting correct and

How to avoid a bridge table?
There are several approaches:

Hide the many-to-many relationship. You can publish two versions of the schema: the full one for use by structured reporting and a handful of power users, and a version that eliminates the many-to-many relationship for use by more casual users.

Eliminate the many-to-many relationship by collapsing or aggregating multiple rows. Add a row to the many-to-many dimension table: “Multiple rows”. The fact table can then link directly with the dimension. As with all design decisions, the IT organization cannot choose this approach without consulting with the user community. For something which has limited information value, this approach may be quite acceptable.

Identify a single primary row (The top 1) It may be possible to identify a primary row, either based on some logic in the transaction system or by way of business rules.
 
Pivot out the many-to-many dimension (see below the Boolean Column Method). If the domain of the multi-choice space is small, you can eliminate the bridge table by creating a dimension table with one column for each choice.

OBIEE SampleApp 11.1.1.5 available on OTN

SampleApp V107 includes a multiple enhancements since previous 11.1.1.3 public release. Many examples showcase OBIEE 11.1.1.5 new features (Mobile, New types of data sources), and numerous additional OBIEE examples were added (Geospatial, Datamining, Functional examples and more).

Get it here.

Performance Metrics for Management

I was on a trawl to get backing for (yet another) an argument at work.

My argument has always been that everyone should understand how the numbers on a dashboard are derived, and its indicative value, is it good, bad, normal, etc. The viewer should also understand what the trend behind this number is, what makes this number better, or less bad.

Logging Level

What is logging level?
Where can you set logging levels?

You can enable logging level for individual users; you cannot configure a logging level for a group.


Typically logging is set to enable some kind of debug or tuning exercise to take place. There is a slight overhead of writing the logging, so disabling this feature in production is probably a good idea.

Set the logging level based on the amount of logging you want to do. In normal operations, logging is generally disabled (the logging level is set to 0). If you decide to enable logging, choose a logging level of 1 or 2. These levels are designed for use by OBIEE administrators.


Level 0

• No logging.

Level 1

• Logs the SQL statement issued from the client application.
• Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing.
• Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query.

Level 2

• Logs everything logged in Level 1.
• Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.

It is possible to set a higher number, but do not do so without the help of Oracle support.

How to Set Logging Level
1. In the Administration Tool, select Manage > Security.
2. The Security Manager dialog box appears.
3. Double-click the user's user ID.
4. The User dialog box appears.
5. Set the logging level by clicking the Up or Down arrows next to the Logging Level field

What is the Purpose of an Alias Table?

An Alias table (Alias) is a physical table with the type of Alias. Physical aliases can be mapped to physical tables, stored procedures, and select statements. An alias table can be a reference to any of these table source types.


Alias Tables can be an important part of designing a physical layer. Here are some of the main reasons to create an alias table:
-To rename physical tables allowing them to be easily identified in the business model layer.
-By renaming you can impose a form of ordering on the tables shown in the physical layer.
-To reuse an existing table more than once in your physical layer (without having to import it several times)
-To set up multiple alias tables, each with different keys, names, or joins
-To help you design sophisticated star or snowflake structures in the business model layer. Alias tables are critical in the process of converting ER Schemas to Dimensional Schemas.

You need to create aliases to
-Eliminate physical joins that cross dimensions.
-Eliminate circular joins.

More on Dates

Do you have timestamp values????

And do you want to......, well join them to a date????

Cast(timestamp_value as DATE)

Does the same as TRUNC(timestamp_value) in Oracle. It removes the time section and you are left with just the date part.

Works in Prompts as well as prompted columns (fortunately).

Which table is selected at runtime

You have many tables under your logical table in OBIEE. Is there any way to guarantee the order in which they are used when multiple paths through the data are available?

Well there is a way to ensure that given tables are ranked lower in the list for selection.

Priority Group

Found on the General Tab of the Logical table source properties.

You can set priority group numbers to determine which logical table source should be used for queries for which there is more than one logical table source that can satisfy the requested set of columns.

For example, you might have a lookup table and another very large table where the data is repeated. Both have the same data, just one has many more rows. Often, scanning a large (many million row table) for distinct values is expensive, while access to a lookup table is “cheap.” In this situation, you can assign a higher priority to the lookup to ensure that all queries are fulfilled using the smaller (more efficient) table if possible.

Note that the priority group of a given logical table source does not always ensure that a particular query will be fulfilled by that source.

Note that the default value for Priority Group is 0 (zero), and changing this value to 1 (one) makes it lower in priority. Zero is therefore highest priority.

OBIEE - AVG

Avg

Calculates the average (mean) value of an expression in a result set. Must take a numeric expression as its argument.

The AVG is calculated as the sum of all rows aggregated divided by the number of rows aggregated. If the function is pushed down to the database, rows with nulls are excluded from the calculation by default. If AVG is done in Answers, then Nulls will cause errors in AVG.

Syntax

Avg(numExpr)

Where:
numExpr - Any expression that evaluates to a numerical value.

AvgDistinct

This function calculates the average (mean) of all distinct values of an expression. It must take a numeric expression as its argument.

Syntax

AVG(DISTINCT numExpr)

Where:
numExpr - is any expression that evaluates to a numeric value.

OBIEE 11G and Flex using crossdomain.xml in Weblogic

For Flex viewer to communicate with a remote data source you need to deploy a small XML file in the root directory of the domain containing the data source.

The crossdomain.xml file has the structure


<?xml version="1.0"?>
<!DOCTYPE cross-domain-policy SYSTEM "http://www.adobe.com/xml/dtds/cross-domain-policy.dtd">
<cross-domain-policy>
<site-control policies="all"></site-control>
<allow-access-from domain="*"></allow-access-from>
</cross-domain-policy>



With OBIEE 10G we had a physical directory.

Now with 11G we are presented with weblogic and no root directory, .......bother.

The solution is to deploy an application for the root directory.

All is explained below

in the %BI Home%\Oracle_BI1\bifoundation\jee directory you create a directory called (in this case) apps
in the apps directory you place your crossdomain.xml file and create two further directories.

META-INF and WEB-INF

in the WEB-INF directory you place two files with the contents detailed below

web.xml



<web-app xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<welcome-file-list>
<welcome-file>crossdomain.xml</welcome-file>
</welcome-file-list>
<mime-mapping>
<extension>xml</extension>
<mime-type>application/xml</mime-type>
</mime-mapping>
</web-app>





weblogic.xml



<?xml version='1.0' encoding='UTF-8'?>
<wls:weblogic-web-app xmlns:wls="http://xmlns.oracle.com/weblogic/weblogic-web-app" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd http://xmlns.oracle.com/weblogic/weblogic-web-app http://xmlns.oracle.com/weblogic/weblogic-web-app/1.1/weblogic-web-app.xsd">
<wls:weblogic-version>10.3.3</wls:weblogic-version>
<wls:context-root>/</wls:context-root>
<wls:container-descriptor>
<wls:default-mime-type>application/xml</wls:default-mime-type>
</wls:container-descriptor>
</wls:weblogic-web-app>

Then you deploy the open directory (apps) as an application on the BI server and accept all defaults.