Monday, October 2, 2017

Exclude Week Ends in the report.

---->>> Below query will exclude Saturday and Sunday exclusion 
        DECODE(TO_CHAR((TRUNC(SYSDATE)-1),'D'),7,1,0) as Working_Days

------>>> Below query will exclude Friday and Saturday exclusion

        DECODE(TO_CHAR((TRUNC(SYSDATE)-1),'D'),7,2,0) as Working_Days


Friday, November 6, 2015

OBIEE 11G ( - How to configure a new Skin


From the version, there is new ways in order to configure a custom skin.
This article shows you how to it with the Approach 2: Deploying Using Shared Folders

Configuration Steps

Extract the file structure of a new skin

The steps below will extract a new skin and move it to default place of older installation.
mkdir /tmp/bicustom-template
cp $FMW_HOME/Oracle_BI1/bifoundation/jee/bicustom-template.ear  /tmp/bicustom-template
cd  /tmp/bicustom-template
jar -xvf bicustom-template.ear
jar -xvf bicustom.war
mkdir $FMW_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes
mv res $FMW_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/res/
mv WEB-INF/ $FMW_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/WEB-INF/
After the extraction, the target directory structure looks like this:
It's important to keep the /res directory because it's hard coded in some link such as the logo.

Rename the skin name

To rename the skin name, you have to:
  • rename the s_custom directory to s_myName
  • indicate it in the root file /res/filemap.xml
   <!-- The style name should exactly match the name of the folder, including case -->
   <Styles Default="s_blafp">
      <Hierarchy>s_Skyros / s_myName</Hierarchy>
   <!-- The style name should exactly match the name of the folder, including case-->
   <Skins Default="sk_blafp">
      <Hierarchy>sk_Skyros / sk_myName</Hierarchy>
It's not needed to create a skin directory. If OBIEE don't find it, it will use the sk_Skyros skin

Change the skin files

You can update all file from your skin (See the documentation)
Example with the file graph.xml (old name dvt-graph-skin.xml) where you can change the colors of the graph: you can add the below nodes as child of the graph node.
        <Series id="0" color="#a9b2b1" borderColor="#a9b2b1"/>   
 <Series id="1" color="#f47d43" borderColor="#f47d43"/>
 <Series id="2" color="#ffd602" borderColor="#ffd602"/>
 <Series id="3" color="#accbe8" borderColor="#accbe8"/>
 <Series id="4" color="#cdb48c" borderColor="#cdb48c"/>
 <Series id="5" color="#e66665" borderColor="#e66665"/>
 <Series id="6" color="#076bb6" borderColor="#076bb6"/>

Deploy the shared folder as application in Weblogic

The skin folder ($FMW_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes) must be accessible via an URL. It's then needed to deploy it as an web application.
  • In the console, go to deployment
  • Lock and edit the domain (Top-Left button)
  • Stop the old application analyticsRes and suppress it
  • Install a new application with the skin folder path ($FMW_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes)
  • Next > Install this deployment as an application
  • Select bi_cluster as the deployment target.
  • Set the name to analyticsRes.
  • Select I will make the deployment accessible from the following location.
  • Next > Yes, take me to the deployment's configuration screen.
  • Finish
  • Click the Configuration tab.
  • Enter /analyticsRes in the Context Root box.
  • Click Save.
  • Click OK.
  • Click Activate Changes.
  • Click Release Configuration.
  • Start the new application.
  • Click Deployments in the Domain Structure region.
  • Select the analyticsRes checkbox in the Deployments table.
  • Click Start, and then select Servicing all requests.


  • backup the instanceconfig.xml file ($fmw_home/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml)
  • and modify it properly:

Restart the BI Presentation Service

The file are loaded during the startup of the BI Presentation service, restart it if you change any file otherwise you can get unexpected behavior.

Viewing the Modifications

If files are added (new, such as an image file), you must restart Presentation Services.
To view your modifications:
  • Reload the metadata. ( Admin > Reload Files and Metadata link)
  • Clear the browser's cache.
  • Click any link on the global header (for example, Home or Catalog) to see your changes.


OBIEE doesn't see the new skin

Double check the value in the node URL > CustomerResourcePhysicalPath in the instanceconfig.xml file.

Documentation / Reference

Thursday, November 5, 2015

Resetting weblogic OBIEE admin password (creating new OBIEE admin user (forgot weblogic admin password

During the installation of OBIEE 11g on my humble 6 year old Thinkpad laptop, I came across this error where the admin user namely weblogic was not able to login after the installation even though the password was correctly typed.

Tried shutting down the service and restarting it from command line but Admin server (weblogic) refused to start citing authentication problem for the admin “weblogic” user.

The best way to resolve this is to create another admin user and then login with this new user in the Fusion Middleware console and then change the password for the old admin user “weblogic”.

open a cmd prompt and cd to $DOMAIN_HOME\bin e.g. D:\OBIEE11g\user_projects\domains\bifoundation_domain\binand execute setDomainEnv.cmd

in the directory D:\OBIEE11g\user_projects\domains\bifoundation_domain\security rename DefaultAuthenticatorInit.ldift file to
generate a new admin user and password by

cd D:\OBIEE11g\user_projects\domains\bifoundation_domain\security
java rtadmin rtpass .note:
rtadmin is the new admin user
rtpass is the password for the new admin user
.(dot) after password will create a new default authenticator file “DefaultAuthenticatorInit.ldift” in the current directory (which is the “security” directory).

rename located in D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\security to
rename DefaultAuthenticatormyrealmInit.initialized located in D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\data\ldap to DefaultAuthenticatormyrealmInit.initialized.bak
note: it’s a good idea to backup the entire D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\data directory
open a cmd prompt and start the Admin server

D:\OBIEE11g\user_projects\domains\bifoundation_domain\bin>startWebLogic.cmdprovide the new admin username and password: rtadmin/rtpass
the weblogic server should start and you should have access to the weblogic console http://localhost:7001/console/
Login to weblogic console with the new admin user and go to Security Realms –> myrealm. select the users and Groups tab

change the password fro the old admin user “weblogic”
save and logout. Test things work fine by logging in as “weblogic” use with the new password.

in the directory D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\security create the This file contains the username and password of the admin user. when you start the services, the username and password will be encrypted.Note: if you delete or rename the data directory located in D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\data, weblogic will create a new data/ldap directory BUT important users like BISystemUser and OracleSystemUser will no longer be available and your service will not start properly.

OBIEE WebLogic Administration Server Fails to Start Due to Memory Issues

On Windows 32-bit operating systems, the WebLogic Administration Server may fail to start, as it cannot allocate sufficient memory despite the availability of free memory. As a workaround, complete the following steps:
  • Open the setSOADomainEnv.cmd file, located in your <Domain_Home>\bin directory, in a text editor.
  • Update the PORT_MEM_ARGS entry as follows:
  • set PORT_MEM_ARGS=-Xms512m -Xmx1024m
  • Save the file and close.