Pivot Custmization

 Pivot Tables Changing Column Indent Background Color

 

 

In OTN forum so many developers are asking pivot table indent background color change.

 

Here am going to provide solution.

 

Please follow the steps.

 

1. open your report --> Go to Criteria --> Click Result --> Add Static text view and apply the below html code and click the html option in the static view.

 

 

 

<style>.PTCC {background-color:white;border-top:white;border-bottom:white;border-left:white;border-right:white}</style>

 

 

 

 

 

Save the Dashboard and see the output like below.

 

 

 

 



 

Hope this help's

 

Thanks,

Satya

 





 

Could not create a system user connection to Oracle BI Server during start-up

 Could not create a system user connection to Oracle BI Server during start-up. Please check the error message and try again

Step 1 : Refresh GUID

a)Check if any error message regarding GUID mismatch is seen in the sawlogn located in

 C:\OBI11g\instances\instance3\diagnostics\logs\OracleBIPresentationServicesComponent\coreapplication_obips1 folder

If yes follow these steps to refresh GUID as documented in Oracle Fusion Middleware Administrator's Guide.

Refresh the User GUIDs

1. Open the NQSConfig.INI file for editing. For more information, see "Where are Configuration Files Located?" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

2. Locate the setting FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = NO and change its value to YES.

3. Modify the instanceconfig.xml file to instruct Oracle BI Presentation Server to refresh GUIDs on restart. Edit the file to add the last line in the following instruction.

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

 <ps:UpgradeAndExit>false</ps:UpgradeAndExit>

<ps:UpdateAccountGUIDs>UpdateAndExit<ps:UpdateAccountGUIDs>

4. From a terminal window, stop and restart the managed processes using the opmnctl parameters stopall and startall. You can use the parameter status to verify process status throughout.

The following components are involved: Oracle BI Presentation Server, Oracle BI Server, Oracle BI Scheduler, Oracle BI Cluster Controller, and Oracle BI JavaHost.

5. Edit the NQSConfig.INI file to reset the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES to NO and restart the Oracle BI Servers.

6. Remove, set to none, or comment out the line (see UpgradeAndExit in the following example) added to the instanceconfig.xml file (that instructs Oracle BI Presentation Server to refresh GUIDs on restart).

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

7. Restart the Presentation Server for the instanceconfig.xml file that was updated.

8. Make sure Oracle WebLogic Server and the system components are also running, if they are not running, restart them.

b) Relogin as the problematic user to verify if you still see the “Unable to Sign In” error

Step 2 : Delete user from rpd if present

a)Launch admin tool and open rpd in OFFLINE Mode

b)Click Manage > Identity > Users tab to verify if you see this user present

Note : Normally you will only see list of users when you open rpd in ONLINE mode. Unless 

the users were created manually in rpd, no users should be visible in OFFLINE mode

c)If the user is present, delete this user entry from rpd

d)save rpd and deploy this changed rpd using EM

e)restart OBI Server component

f)relogin as the problematic user to see if you still see “Unable to Sign In” error

Step 3 : Delete the cacheduserinfo file from webcatalog

If Step1 and Step2 does not work, then do the following

Note : In the example below, catalog name is SampleApp and the user who gets “invalid Login” error is Administrator user.

a)Take a backup of your webcatalog

b)Navigate to

 C:\OBIEE11G\instances\instance2\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleApp\root\users\Administrator\_prefs
c)Delete the files name cacheduserinfo and cacheduserinfo.atr
d)Launch OBIEE 11.1.1.3 and test by logging in as the specific user.

Hope this help's

Thanks,

Satya

Failes To start Manged Process

OPMNCTL Failed to Start Managed Process After Maximum Retry Limit on Solaris 10, OBIEE 11g 

If you've happened to glance at Oracle Technical Network, it appears that alot of practitioners are encountering an issue when upgrading their 11g platform to the newest 11.1.1.6.4 version if they're using a Solaris or Linux distribution.

After upgrading to 11.1.1.6.4 some users encounter the following error(s) when trying to start their BI services using opmnctl (Oracle Process Manager and Notification Server) with the 'opmnctl startall' command:

=====================================================================
Response: 1 of 5 processes started.

ias-instance id=instance1
++++++++++++++++++

ias-component/process-type/process-set:
coreapplication_obis1/OracleBIServerComponent/coreapplication_obis1/

Error
--> Process (index=1,uid=1445883997,pid=18522)
failed to start a managed process after the maximum retry limit

Log:
/u01/app/oracle/obiee11g/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/console~coreapplication_obis1~1.log

ias-component/process-type/process-set:
coreapplication_obips1/OracleBIPresentationServicesComponent/coreapplication_obips1/

Error
--> Process (index=1,uid=1445883998,pid=0)
Executable file does not have execute permission
/u01/app/oracle/obiee11g/Oracle_BI1/bifoundation/web/bin/sawserver
failed to start a managed process after the maximum retry limit
Executable file does not have execute permission
/u01/app/oracle/obiee11g/Oracle_BI1/bifoundation/web/bin/sawserver

Log:
none

ias-component/process-type/process-set:
coreapplication_obisch1/OracleBISchedulerComponent/coreapplication_obisch1/

Error
--> Process (index=1,uid=1445884000,pid=18523)
failed to start a managed process after the maximum retry limit
Log:
/u01/app/oracle/obiee11g/instances/instance1/diagnostics/logs/OracleBISchedulerComponent/coreapplication_obisch1/console~coreapplication_obisch1~1.log

ias-component/process-type/process-set:
coreapplication_obiccs1/OracleBIClusterControllerComponent/BIClusterController/

Error
--> Process (index=1,uid=1445884001,pid=0)
Executable file does not have execute permission
/u01/app/oracle/obiee11g/Oracle_BI1/bifoundation/server/bin/nqsclustercontroller
failed to start a managed process after the maximum retry limit 

The important things to note are:
You may encounter all, some, or just 1 of your bi_services which fail to start. In the above example, the following services failed to start:
BI Server
BI Publisher
BI Cluster Controller
BI Scheduler
There are actually two issues:
Managed Processes failed to start after the maximum retry limit
Certain executable files do not have the execute permission enabled

Issue 1: Fix Maximum Retry Limit

This can be easily resolved by setting the ulimit at the user level to : ulimit -d unlimited , as outlined in Oracle's note ID: 1292887.1 . Anyone who has worked in OBIEE 10g should be familiar with this as it is required in 10g's installation process as well.

Issue 2: Enable read access on executable files

If you tried re-running opmnctl startall after resolving issue 1, you'll notice that the BI services still fail to run despite increasing the maximum retry limit. Upon examining the log files as outlined in the above error:

u01/app/oracle/obiee11g/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/console~coreapplication_obis1~1.log
u01/app/oracle/obiee11g/instances/instance1/diagnostics/logs/OracleBISchedulerComponent/coreapplication_obisch1/console~coreapplication_obisch1~1.log

You'll notice that OPMNCTL still fails because it can't execute: nqsserver, nqscheduler and nqsclustercontroller. Below is the log from the above bullet point #1 :

/export/obiee/11g/Oracle_BI1/bifoundation/server/bin/biserver.sh: nqsserver: cannot execute
12/11/06 15:48:06 Start process

/export/obiee/11g/Oracle_BI1/bifoundation/server/bin/biserver.sh: nqsserver: cannot execute

During the 11.1.1.6.4 upgrade on your Solaris or Linux box, these files became read only!

How to resolve this read only issue:

Navigate to your $ORACLE_HOME/bifoundation/server/bin folder and make the the following files executable by using the chmod +x command: nqscheduler, nqsclustercontroller, nqsserver. These files should have read/write privileges as outlined in the image below:





Now when you run opmnctl startall, all services should execute successfully:




Important:
After the upgrade, it is critical that you follow Oracle's recommended start up & shutdown sequence for weblogic & OBIEE 11g

Start up:


$DOMAIN_HOME/bin/startWebLogic.sh (Weblogic)

$WL_HOME/server/bin/startNodeManager.sh (Node Manager)

$DOMAIN_HOME/bin/startManagedWebLogic.sh bi_server1 http://server.domain:7001 (Managed erver)


$ORACLE_INSTANCE/bin/opmnctl startall (OPMN) 

Shutdown:


$ORACLE_INSTANCE/bin/opmnctl stopall (OPMN)


$DOMAIN_HOME/bin/stopManagedWebLogic.sh bi_server1 t3://host.domain:7001 username password (Managed Server)


$DOMAIN_HOME/bin/stopWebLogic.sh username password t3://host.domain:7001 (Admin Server)

Thanks,


Satya Ranki Reddy

Ticker option

How to use Ticker option on  OBIEE 10g / 11G

OBIEE 11G :

If want to add images in the ticker and depending upon the image click it has to navigate to separate reports. I am using <a href> inside the column formula-

'<a href="../analytics/saw.dll?Answers&Action=results&path=Pathofreport1">
<img src="../analytics/res/s_blafp/images/report_account_appl.jpg" width="150" height="40" />
</a>
<BR>
<a href="../analytics/saw.dll?Answers&Action=results&path=Pathofreport2">
<img src="../analytics/res/s_blafp/images/report_activities.jpg" width="150" height="40" />
</a>'

Check the HTML in the Data format.
And if the image still doesnt appear then restart the Services. And blue border should appear around the image.
Then just have the ticker and it works as expected.

Paths for Custom Images in 11g

Drive:\Oracle\Middleware\Oracle_BI1\bifoundation\web\app\res\s_blafp\images

Drive:\Oracle\Middleware\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\s_blafp\images

Drive:\Oracle\Middleware\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\.appmergegen_1291264099332\analytics.ear\ukjjdc\res\s_blafp\images



OBIEE 10g  :

Upload the images to following two folders

D:\ORAHome\j2ee\home\applications\analytics\analytics\res\s_oracle10\images (Oracle application server install)

D:\OracleBI\web\app\res\s_oracle10\images (OBIEE install)
 

After adding images to the folders you need to restart the presentation services

. In answers, edit the title view and add the following

fmap:images/your_namename.gif

In dashboard you can use the same with image or link and fmap:impages/your_gif.gif

Hope this help's

Thanks,

Satya Ranki Reddy

Convert to windows serivice

OBIEE 11.1.1 - How to Install Windows Services For OBIEE 11g AdminServer And BI Managed Server



Following Fusion Middleware 11g components are configured by default as MS Windows Services:
Weblogic Node Manager

(configured as a MS Windows Service if Custom Install route is chosen when installingWeblogic)
FMW 11g Process Manager (OPMN)

(installed as a MS Windows Service if a System Component Instance is configured) 

This article shows how to install the WebLogic AdminServer and BI Managed Server as MS Windows Service for the OBIEE 11g installation.

Pre-requistes: Make sure to define two environment variables below:
1. User Variables
Variable Name : BI_ORACLE_HOME
Variable Value : D:\MIDDLEWAREHOME\Oracle_BI1 (e.g.)

2.System Variables
Variable Name : BI_ORACLE_HOME
Variable Value : D:\MIDDLEWAREHOME\Oracle_BI1 (e.g.)

a. For Administration Server

Note: If you install OBIEE 11g version 11.1.1.5 / 11.1.1.6 with the Simple Install type, then you will only have an AdminServer and not a BI Managed Server; therefore, the Managed Server section would not apply in that specific version and installation type.

1. Configure the boot.properties to create an encrypted method to automatically pass the username and password

Create a text file in the location:

Note: With OBIEE 11.1.1.5 / 11.1.1.6, if you have used the Start BI Services from the menu, then it would have automatically created a boot.properties file the first time it was invoke.


%middleware_home%\user_projects\domains\bifoundation_domain\servers\AdminServer\security\boot.properties
For example, if the middleware_home is c:\BI11g, then the boot properties needs to be here: 

C:\BI11g\user_projects\domains\bifoundation_domain\servers\AdminServer\security\boot.properties

Add the following lines:

username=[the Administrator username created during install, e.g weblogic]
password=[the weblogic username password e.g welcome1]


Note: the above credentials are the same as the one provided when the AdminServeris started from Command line.


Do not use or leave the brackets [ ] in the file. They are only used to show what should be entered.
As soon as you start the Admin Server the username and password values in this file will be encrypted


2. Create a command script called installAdmServer_Service.cmd.

Open a text file and input the following lines [edit for your install]:


echo off
SETLOCAL
set DOMAIN_NAME=bifoundation_domain
set USERDOMAIN_HOME=C:\BI11g\user_projects\domains\bifoundation_domain
set SERVER_NAME=AdminServer
set PRODUCTION_MODE=true
call "C:\BI11g\user_projects\domains\bifoundation_domain\bin\setDomainEnv.cmd"
call "C:\BI11g\wlserver_10.3\server\bin\installSvc.cmd"
ENDLOCAL


Please note that the middleware home values need to be specific to your install


3. For troubleshooting / debugging purposes it is helpful to redirect standard out and error to a text file.


Although most information is captured in the AdminServer server log files, you will not see all standard out and error when the server is started via a MS Windows Service (unlike when you start an AdminServer in console modefrom the command prompt using startWebLogic.cmd). To redirect standard out to a text file, backup and editinstallSvc.cmd file and change the line at the bottom of the file so it includes the -logparameter. For example:


"%WL_HOME%\server\bin\beasvc" -install
-svcname:"%DOMAIN_NAME%_%SERVER_NAME%"
-javahome:"%JAVA_HOME%" -execdir:"%USERDOMAIN_HOME%"
-extrapath:"%WL_HOME%\server\bin" -password:"%WLS_PW%"
-cmdline:%CMDLINE%
-log:"[middleware_home]\user_projects\domains\bifoundation_domain\servers\AdminServer\AdminServer-stdout.txt" 4. Next, run "installAdmServer_Service.cmd" and the service should be installed, it will have a name like "beasvc %DOMAIN_NAME%_%SERVER_NAME%" (e.g. beasvc bifoundation_domain_AdminServer)


The Service "Startup Type" will be 'Automatic'. Just like any other MS Windows Service you can change the 'Startup Type' to 'Manual'.

Start the Service. The Service will come back fairly quickly to say it is started. The actual time taken for AdminServer to start and reach a state of 'RUNNING' will be longer - perhaps two or three minutes. The state of the server can be monitored by reviewing thestdout txt file.

Notes:
An alternative to the boot.properties approach to specifying the Admin Server weblogicusername / password is to add the following environment variables to your wrapper cmdscript - installAdmServer_Service.cmd


set WLS_USER=[user]
set WLS_PW=[password]


Note: Do not use or leave the brackets [ ] in the file. They are only used to show what should be entered.


To quote the documentation, "The beasvc utility encrypts the login credentials and stores them in the Windows registry."
This is one of two possible methods for avoiding the username/password prompt when a server instance starts. The disadvantage to this method is that changing the username or password for the server instance requires you to delete the Windows service and set up a new one with the new username and password. Instead of this method, you can use a boot identity file. With a boot identity file, you can change the login credentials without needing to modify the Windows service.


b. For Managed Server bi_server1


1. Configure the boot.properties to create an encrypted method to automatically pass the username and password

Create a text file in the location:

%middleware_home%\user_projects\domains\bifoundation_domain\servers\bi_server1\security\boot.properties


For example:

C:\BI11g\user_projects\domains\bifoundation_domain\servers\bi_server1

where the BI11g is your middleware home.

Add the following lines:
username=[the Administrator username created during install, e.g weblogic]
password=[the weblogic username password e.g welcome1]


Note: the above credentials are the same as the one provided when the AdminServer is started from Command line.
As soon as you start the BI Managed Server the username and password values in this file will be encrypted.


The alternative approach - like for AdminServer - is to specify WLS_USER and WLS_PW in the wrapper cmd script. See next point


2. Create a command script called installManagedSrvr_Service.cmd which has lines like:


SETLOCAL
set DOMAIN_NAME=bifoundation_domain
set USERDOMAIN_HOME=C:\BI11g\user_projects\domains\bifoundation_domain
set SERVER_NAME=bi_server1
set PRODUCTION_MODE=true
set ADMIN_URL=http://10.220.19.108:7001
call "C:\BI11g\user_projects\domains\bifoundation_domain\bin\setDomainEnv.cmd"
call "C:\BI11g\wlserver_10.3\server\bin\installSvc.cmd"
ENDLOCAL


Notes:


o Before running, change the -log parameter in the installSvc.cmd file to the new location. Fore example:

-log:"[middleware_home]\user_projects\domains\bifoundation_domain\servers\bi_server1\bi_server1-stdout.txt"


o The ADMIN_URL value should reference the AdminServer hostname and listen port


o The SERVER_NAME value is case sensitive. For example, if you are creating a MS Windows service for a different managed server such as 'wls_ods1' then the value needs to match the case of the server name otherwise the startup of the server via the MS Windows service will fail.


o Be careful that there are no trailing spaces after each line in the command file - trailing spaces will cause the managed server to fail at startup. For example a trailing space in the ADMIN_URL value will result in the error

<19-Jan-2010 11:37:58 o'clock GMT> <Error> <EmbeddedLDAP> <BEA-171524> <Cannot determine the Listen address for the Admin server


3. Now run "installManagedSrvr_Service.cmd". The Service should be installed, it will have a name like "beasvc %DOMAIN_NAME%_%SERVER_NAME%" (e.g. - beasvc bifoundation_domain_bi_server1)

The Service "Startup Type" will be 'Automatic'. Just like any other MS Windows Service you can change the 'Startup Type' to 'Manual'.

Start the Service. The Service will come back fairly quickly to say it is started. The actual time taken for the managed server to start and reach a state of 'RUNNING' will be longer - perhaps two or three minutes. The state of the server can be monitored by reviewing thestdout txt file.


4. Ensure you make the managed Server to a manual startup so this service can be started after the AdminServer is started.


5. You may choose to start the managed server from the AdminServer instead of from creating the MS Windows service which is the recommended practice.


6. First start the Node Manager, Admin Server and the Managed Server in this Order.


You can start the BI Server components either from FMW Control or from the OPMN Command line.


Note: If you start the servers as a service, then any parameters, such as JVM parameters need to be modified in the registry at:
If you want to view these parameters, open up the MS Windows Registry Editor (regedit) and navigate to:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\beasvc <your domain>_<managed server name>\Parameters

Be sure and double-check the parameter settings such as the the path for the JVM, the hostname and the port; otherwise the service may not start properly

Otherwise, the parameters can be modified in the command shell scripts

See the WebLogic documentation for installing the NodeManager as a service.


Important Note: If you get ":java.lang.OutOfMemoryError: PermGen space" error in log while starting the service then try to increase the following heap sizes in registry:

CmdLine the “-server -Xms1024m -Xmx1024m -XX:MaxPermSize=512m ……..”

Thanks,

Satya Ranki Reddy

Null value replace in Prompt

How to Replace the Null Value in Prompt on OBIEE 11g

I have seen several posts in OTN for how to replace null values in prompt.

There are four options to resolve this issue:

1.- Validate if NULL values are relevant for the business analysis (for some reason in the data warehouse NULL values were stored). If they are not, you have to delete them in Dimensions and Fact tables.

2.- Change the physical table type in your rpd in all tables you have NULL values and use a SELECT like this:

SELECT LOGIN, RESP
FROM NQ_LOGIN_GROUP
WHERE NOT LOGIN IS NULL

Doing this, you only have NON-NULL values in your Multi-select prompt.

3.- Replace the NULL value with a new name like UNSPECIFIED in ALL tables you which are related.

4.- Log an enhancement request to review that multi-select prompts works with NULL values.

5.  This is a knows issue. Check to see if your prompt list has a 'Null' value at the bottom. If Null value is selected in the prompt, it will wipe out the selection.

Instead of using "<<", try selecting all products using control + select and use "<" icon.

Thanks,

Satya Ranki Reddy

Home Page Customization

OBIEE 11g Home page option customization 

I have seen several post for control or limit access to OBIEE Home page and Header menu options.  we don’t have any easy process to address this in OBIEE. So end up with changing the saw.headers.xml file to just hide/display. Again it is System-wide setting.

In  OBI 11.1.1.6.0 we have flexibility to limit the access to either User/Group/Application role. And also we can do this from OBIEE 11G Presentation service Administration User interface itself.

Thanks,

Satya Ranki Reddy