Monday, October 2, 2017

Exclude Week Ends in the report.



---->>> Below query will exclude Saturday and Sunday exclusion 
                 
SELECT (TRUNC(SYSDATE)-4) DAY1, (TRUNC(SYSDATE)-1) DAY2,
((TRUNC(SYSDATE)-1) - (TRUNC(SYSDATE)-4))-2*FLOOR(((TRUNC(SYSDATE)-1) - (TRUNC(SYSDATE)-4))/7)-DECODE(SIGN(TO_CHAR((TRUNC(SYSDATE)-1),'D')-
        TO_CHAR((TRUNC(SYSDATE)-4),'D')),-1,2,0)+DECODE(TO_CHAR((TRUNC(SYSDATE)-4),'D'),7,1,0)-
        DECODE(TO_CHAR((TRUNC(SYSDATE)-1),'D'),7,1,0) as Working_Days
FROM DUAL


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

SELECT (TRUNC(SYSDATE)-8) DAY1, (TRUNC(SYSDATE)-1) DAY2,
((TRUNC(SYSDATE)-1) - (TRUNC(SYSDATE)-8))-2*FLOOR(((TRUNC(SYSDATE)-1) - (TRUNC(SYSDATE)-8))/7)-DECODE(SIGN(TO_CHAR((TRUNC(SYSDATE)-1),'D')-
        TO_CHAR((TRUNC(SYSDATE)-8),'D')),-1,2,0)+DECODE(TO_CHAR((TRUNC(SYSDATE)-8),'D'),7,2,0)-
        DECODE(TO_CHAR((TRUNC(SYSDATE)-1),'D'),7,2,0) as Working_Days

FROM DUAL

Friday, November 6, 2015

OBIEE 11G (11.1.1.9) - How to configure a new Skin

About

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
<FileMap>
 
   <!-- The style name should exactly match the name of the folder, including case -->
   <Styles Default="s_blafp">
      <Hierarchy>s_Skyros / s_myName</Hierarchy>
   </Styles>
 
   <!-- The style name should exactly match the name of the folder, including case-->
   <Skins Default="sk_blafp">
      <Hierarchy>sk_Skyros / sk_myName</Hierarchy>
   </Skins>
 
</FileMap>
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.
<SeriesItems>
        <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"/>
</SeriesItems>

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.

instanceconfig.xml

  • backup the instanceconfig.xml file ($fmw_home/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml)
  • and modify it properly:
<URL>
     <CustomerResourcePhysicalPath>/u01/app/oracle/product/fmw/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/res</CustomerResourcePhysicalPath>
     <CustomerResourceVirtualPath>/analyticsRes/res</CustomerResourceVirtualPath>
</URL>
<UI>
     <DefaultStyle>myName</DefaultStyle>
     <DefaultSkin>myName</DefaultSkin>
</UI>

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.

Support

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 DefaultAuthenticatorInit.ldift.org
generate a new admin user and password by

cd D:\OBIEE11g\user_projects\domains\bifoundation_domain\security
java weblogic.security.utils.AdminAccount 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 boot.properties located in D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\security to boot.properties.bak
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 boot.properties. 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.

OBIEE - How to reset the lost Weblogic Admin User password?



You installed OBIEE 11G and you did not write down the Weblogic Domain admin password, or you’ve had it installed for a while and can’t remember what the password is to get into the WebLogic Admin console to make a change. This post will help you do that so you can get on with your business.

1. Make sure all the services are down

2. change directory to <Middleware_Home>\user_projects\domains\bifoundation_domain\bin

cd <Middleware_Home>\user_projects\domains\bifoundation_domain\bin

3. Source the setDomainEnv.cmd(Windows) / setDomainEnv.sh (Linux) script so we can get all the WebLogic variables set correctly

In windows: setDomainEnv.cmd

In Linux: . setDomainEnv.sh

4. Now change the directory to security directory in your instance.

cd <Middleware_Home>\user_projects\domains\bifoundation_domain\security

5. Move the DefaultAuthenticatorInit.ldift to a backup file

In Windows:

mv DefaultAuthenticatorInit.ldift DefaultAuthenticatorInit_old.ldift

In Linux:

mv DefaultAuthenticatorInit.ldift DefaultAuthenticatorInit_old.ldift

6. Run the “java weblogic.security.utils.AdminAccount newuser newpassword .” to generate a newDefaultAuthenticatorInit.ldift with a new user with the assigned password within the security directory

eg: java weblogic.security.utils.AdminAccount weblogic weblogic123 .

Note: replace newuser and newpassword with a user and password of your choosing. Ensure the password is a minimum of 8 characters and contains at least one numeric character. Also, don’t forget the period “.” at the end of the above command, it is required.

7. Change directory to <Middleware_Home>\user_projects\domains\bifoundation_domain\servers\AdminServer\security

cd <Middleware_Home>\user_projects\domains\bifoundation_domain\servers\AdminServer\security\

8. Edit the boot.properties file and replace newpassword and newusername with the user and password you defined in step 6. Do this for all the servers in the domain.

password=newpassword

username=newusername

The boot.properties will get encrypted once the admin server starts up

9. Rename or remove directory <Middleware_Home>\user_projects\domains\bifoundation_domain\servers\AdminServer\data\ldap as it will get recreated once the WebLogic Admin server starts.

e.g:

rename <Middleware_Home>\user_projects\domains\bifoundation_domain\servers\AdminServer\data\ldap <Middleware_Home>\user_projects\domains\bifoundation_domain\servers\AdminServer\data\ldap_OLD

10. Change directory to <Middleware_Home>\user_projects\domains\bifoundation_domain\bin and start the Weblogic Admin Server

In Windows: startWebLogic.cmd

In Linux: ./ startWebLogic.sh

11. Once the weblogic server is up, you’ll be able to login to Console with the new weblogic Admin user and password.

OBIEE 11G: How to Change the default user or weblogic password

If you want to change the default password for the default user. The default user could be named anything at installation time.


For example: weblogic, weblogic_admin, biadmin

Below Steps to change the default password:

Step1: Log in to the Weblogic console
  •   Click on Security in the left pane.
  •   Click on 'My Realm'
  •  Click on 'Users and Groups' tab.
  •   Drill down on any user whose password has to be changed, in this case for example, weblogic user.
  •   Navigate to 'Password' tab.
  •   Enter the password into New Password and Confirm Password text boxes.

Step2:  Navigate to the below locations:
  • D:\OBIEE11g\product\fmw\user_projects\domains\bifoundation_domain\servers\AdminServer\security
  • D:\OBIEE11g\product\fmw\user_projects\domains\bifoundation_domain\servers\bi_server1\security

Rename the boot.properties file to any name or remove the file.

Step3: Stop BI Services & Start BI Services

  • Stop BI Services, it will ask weblogic username and password, provide the username and modified password.
  • Start BI Services.


NOTE: It is recommended that before changing the password, you create a new user ACCOUNT with administration privileges (Administrators, BIAdministrators).