Labelling Time axes in Excel

Excel may send chills down the spine of us when we hear users talking about its [ab]use, but it has its place in the toolset. For my money, it is a very good tool for knocking out graphs which look decent. Of course, rrdtool is my geek tool of choice for dynamic long-term graphing, but when doing scratch PoC work, I normally fall back to Excel.

One thing which has frustrated me over time is, well, time, and Excel’s handling thereof. How many times (these puns are getting tiresome already) have you seen an axis like this and gnashed your teeth?

Excel's default labelling of time axes sucks

Looking at the axis options shows some decimals, with no apparent bearing on the times shown on the axis:

By virtue of using Excel for quick ‘n dirty graphing, I normally don’t have the time to figure this out properly. I finally cracked, and I’m glad I did. The answer is very simple.

Time’s in Excel are based on fractions of 1 day. So, with a number of 1 = 24 hours, we can work backwards:

• 1/2 is going to be half a day, twelve hours = 0.5
• 1/24 = one hour = 0.04166666666667
• 1/24/2 = Half an hour = 0.02083333333333
• 1/24/2/2 = Quarter of an hour = 0.010416666666666667
• 1/24/60 = One minute = 0.000694444444

This is a good example of understandable backend functionality (storing times as a plain number) ought to be shielded from the end user, and the interface design has fallen one step short. Excel knows the data is Time, and good interface would at the very least offer the option to define axes in terms of time, if not hide the decimals entirely.

Still, making use of what we have, we can still get Excel to behave, it’s just a bit labourious:
You want a graph with a maximum of 1 hour, major lines every fifteen minutes, and minor at 5 minutes? No problem.

Sensible time axis labelling in Excel

So next time you’re graphing a time series in Excel, fire up Calc and work out your fractions, for some proper time axis labelling.

Undocumented nqcmd parameters

I noticed on Nico’s wiki (which is amazing by the way, it has so much information in it) a bunch of additional parameters for nqcmd other than those which are displayed in the default helptext (nqcmd -h).

These are the additional ones:

-b<super batch file name>
-w<# wait seconds>
-c<# cancel interval seconds>
-n<# number of loops>
-r<# number of requests per shared session>
-t<# number of threads>
-T (a flag to turn on time statistics)
-SmartDiff (a flag to enable SmartDiff tags in output)
-P<the percent of statements to disable cache hit>
-impersonate <the impersonate username>
-runas <the runas username>

Most parameters don’t appear to work in default call of nqcmd in 10g and 11g, throwing a Argument error near: error.

-b<super batch file name>
-w<# wait seconds>
-c<# cancel interval seconds>
-n<# number of loops>
-r<# number of requests per shared session>
-t<# number of threads>
-P<the percent of statements to disable cache hit>
-SmartDiff (a flag to enable SmartDiff tags in output)

I wonder if there’s an Open Sesame type flag that needs to be used to enable these parameters by support. Or maybe they don’t even exist.

This leaves this handful of additional parameters which do work (/don’t throw an error) in the default invocation of nqcmd:

-T (a flag to turn on time statistics)
-impersonate <the impersonate username>
-runas <the runas username>

Oracle Support directed me to the documentation (Table 14-1), but this covers the standard parameters, not these extra ones.

Oracle Support also pointed out that undocumented parameters are not supported except under direct instruction

The -T flag looks very useful for performance testing purposes, as it appends this information to the output from nqcmd:

Clock time: batch start: 15:44:32.000 Query from: 15:44:32.000 to: 15:44:59.000 Row count: 0
 total: 27 prepare:  1 execute: 26 fetch:  0
Cumulative time(seconds): Batch elapsed: 26 Query total: 27 prepare:  1, execute: 26, fetch:  0, query count:  1, cumulative rows:  0

I’m intrigued to know where Nico got his list from (he couldn’t remember when I asked him :-)). Has anyone else come across these and/or know what they do and how to invoke them? Stuff like SmartDiff sounds tantalisingly interesting.

Changing LDAP settings in an OBIEE RPD with UDML

A chap called Kevin posted a comment on a previous posting of mine asking

did you ever come across anything that could be used to change the LDAP server settings from a command line (admintool.exe, UDML, or otherwise)?

I did a quick play around with some UDML and it appears that you can.

Set up the initial LDAP server definition in the RPD

First I added a dummy LDAP server to samplesales.rpd:

A LDAP server added to samplesales.rpd

Then save and close the RPD.

Export the RPD to UDML format, and isolate the LDAP server UDML definition

Next open up a command prompt and run the following, which will export the UDML for the whole RPD:

c:\oraclebi\server\bin\NQUDMLGen.exe -U Administrator -P Administrator -R c:\oraclebi\server\repository\samplesales.rpd -O c:\scratch\udml.txt

Running the export of UDML

Open up the generated UDML in your favourite text editor. In the above example, it will have been written to c:\scratch\udml.txt.

Do a search for the name of your LDAP server, and you should hopefully find a line like this:

DECLARE LDAP SERVER "My LDAP server" AS "My LDAP server" UPGRADE ID 80295

What you do now is remove all the rest of the RPD UDML, so cut from the beginning of the file up to the DECLARE LDAP SERVER, through to the next DECLARE statement. You should end up with something like this:

Example UDML for the LDAP server definition

Make the required LDAP server change in the UDML

On a copy of the UDML extracted above, make the required changes to the LDAP server definition.
For this example, let’s imagine we’re moving the RPD to use a pre-production LDAP server.
In a copy of the original udml.txt file, now called ldap_preprod.udml, I’ve simply amended the HOST NAME field:

HOST NAME 'ldap.preprod.server.com'

Save the changed file (ldap_preprod.udml in my example).

Apply the LDAP server change to the RPD

Back at the command line, and this time NQUDMLExec

c:\OracleBI\server\Bin\nQUDMLExec.exe -U Administrator -P Administrator -I c:\scratch\ldap_preprod.udml -B c:\OracleBI\server\Repository\samplesales.rpd -O c:\OracleBI\server\Repository\samplesales.preprod.rpd

This applies the UDML in the file specified by “-I” (c:\scratch\ldap_preprod.udml) to be applied to “-B” base repository file (c:\OracleBI\server\Repository\samplesales.rpd) and write the output to “-O”, a new repository file (c:\OracleBI\server\Repository\samplesales.preprod.rpd).

Open up the new RPD in Administration Tool and check the results of your handiwork:

LDAP settings showing the change made in the UDML file

Further reading

UDML in OBIEE is nothing new, and there are some very good articles to read if you want to understand more about it:

• Andreas Nobbmann: Scripting OBIEE – Is UDML and XML all you need ?
  • This is Andreas’ presentation from the RittmanMead BI Forum 2009 – and Andreas is speaking again this year. More information here: http://www.rittmanmead.com/biforum2011/
• Dylan Wan: UDML in Oracle BI Server
• Mark Rittman: Scripting Entries in the Oracle BI Repository
• Venkat: UDML to automate repository Updates – Migration of Repositories from Development to Test/Production Environment
• Daan Bakboord: UDML – Scripting
• Andreas Nobbmann: Finding everything in your OBIEE repository – parsing your UDML

Footnote

All this can be done on Unix too, just make sure you have set your OBIEE environment first with sa-init.sh (or sa-init64.sh) before calling nqudmlgen / nqudmlexec

Whether Windows or Unix, make sure you work on a copy of your RPD, because you might corrupt it otherwise. I’m pretty sure some UDML hacking is unsupported, so use this at your own risk. And did I mention, work on a copy of your files and take backups.

From a note that I wrote last year it looks like UDML is on its way out and an XML-based version on its way in for OBIEE 11g.

The code snippets assume that you have OBIEE installed to c:\OracleBI – amend the path as necessary if you have it elsewhere. You’ll always find NQUDMLGen & NQUDMLExec in <wherever you installed OracleBI>/server/Bin (or Bin64).

Adding OBIEE monitoring graphs into OAS

Introduction

This is the third part of three detailed articles making up a mini-series about OBIEE monitoring. It demonstrates how to capture OBIEE performance information, and optionally graph it out and serve it through an auto-updating webpage.

This final article describes how to bolt on to OAS a simple web page hosting the graphs that you created in part 2, plotting data from OBIEE collected in part 1.

The webpage

This is just an old-school basic HTML page, with a meta-refresh tag (which note that Chrome doesn’t work with) and img tags:

<html>
<meta http-equiv="refresh" content="60">
<head>
<title>OBIEE Servers</title>
</head>
<body>
<img src="server01.png"></br>
<img src="server02.png"></br>
</body>
</html>

I shan’t patronise you nor embarrass myself with my rusty HTML skills any further – I’ll leave you how to build your dashboard how you want it.

OAS

This is hack! I am not an expert at Apache, so please don’t take my word for it that this is the best way to do it. It worked for me, but mightn’t for you.

If you’ve got OAS installed for your OBIEE installation, you can tweak it to serve up your new graphs too. If you’re using OC4J, IIS, or another webserver, then you’ll have to figure this bit out yourself.

Assuming that your OBIEE JMX graphs and HTML files are in /tmp/obieejmx, make sure that they’re readable by all:

chmod -R o+rx /tmp/obieejmx

Now go to your OAS folder, navigate to Apache/Apache/conf, and MAKE A BACKUP of httpd.conf

cd /your/path/to/OAS/here
cd Apache/Apache/conf
cp httpd.conf httpd.conf.bak

Open httpd.conf in vi (or if you’re not a real man then FTP the file to Windows and open it in Notepad 😉 )

1. Search for

   <IfModule mod_alias.c>

2. Add the following beneath it:

       Alias /obieejmx/ "/tmp/obieejmx/"
       <Directory "/obieejmx/">
           AllowOverride None
           Options None
           Order allow,deny
           Allow from all
       </Directory>
   

   • Here’s where you’d change the location of your graphs and HTML file if you needed to
3. Save httpd.conf
4. Restart Apache

   opmnctl restartproc ias-component=HTTP_Server

   or if that doesn’t work restart OAS

   opmnctl shutdown
   opmnctl startall

Assuming you normally access OBIEE through http://myserver:7777/analytics/ then you should now be able to go to http://myserver:7777/obieejmx/ and view the fruits of your hard-earned work.

What next

Obviously, the gnuplot/OAS hack is a bit crude, but for me was the quickest way to get “to market” the power of the OBIEE systems management metric collection by jmx that is possible for anyone with some basic *nix skills and some time to put it together.

The BI Management Pack for Enterprise Manager probably provides some if not all of this functionality but isn’t always available to use (and also has licensing implications).

Whether you collect metrics for day-to-day monitoring of OBIEE, capacity planning, or investigative work, I’ve hopefully demonstrated how easy it is to work with once you’ve got the basics mastered. And the beauty of doing it with shell scripts is that you can customise it to your heart’s content.

There’s a whole bunch of analysis that I’d like to do now, around things like our registered user count vs logged on users vs active users (to determine what actually is our concurrent user rate), as well as profiling BI Server load against database load.

It would also be fun to develop the HTML just a little bit further to create a mock drill-down on the graphs, although if you’re anything like me be aware of “just tweaking for a minute” turning into far too long given then throwaway nature of the solution.

Finally, bear in mind this is now dated technology – some of it may be on the junk heap for OBI11g.

OBIEE monitoring

Those of you who read my blog regularly may have noticed I have a slight obsession with the OBIEE systems management capability which is exposed through JMX. Venkat has blogged this week about JMX in OBI11g, and it’s clearly a technology worth understanding properly.
I’ve recently been tinkering with how to make use of it for monitoring purposes, most recently using JConsole and discussed here. What follows is an extension of this idea, cobbled together with a bit of shell scripting, awk, gnuplot, and sticky backed plastic. It’s built on OBIEE 10g – for OBI11g it may differ (although I understand that Performance MBeans still exist).

Whether you collect metrics for day-to-day monitoring of OBIEE, capacity planning, or investigative work, it’s valuable data (in my humble opinion) that will help you understand the usage of the application by the users that you support.

To whet your appetite, here’s a sample of what you can produce, in realtime:

Performance metrics from a two-server OBIEE cluster

Performance data related to Sessions in BI and Presentation Services

Before you start this, I recommend reading how to secure your jmx agent if you’re working with production systems.

Overview

There are three parts to my monitoring application, and you can pretty much pick and mix as you want. Obviously without any data collected then graphing it will be pretty dull, but you may opt to collect the data and then work with it another way (Excel, OBIEE, etc).
I’ve broken the details down into three separate blog posts:

1. Metric collection from a remote BI Server, using jmxsh
2. Graph rendering of the collected data, using gnuplot
3. Web page serving of the rendered graphs, bolted onto the OAS already in place for Presentation Services.

Scripts to extract information from OBIEE NQQuery.log

Here are a couple of little unix scripts that I wrote whilst developing my performance testing OBIEE method.

They’re nothing particularly special, but may save you the couple of minutes it’d take to write them 🙂

Note that some of this data is available from Usage Tracking and where it is I’d recommend getting it from there, databases generally being easier to reliably and repeatably query than a transient log file.

Extracting Logical SQL from NQQuery.log

First is how to extract logical SQL statements from NQQuery.log. This is useful if you want to build up a set of files to replay as a test load against OBIEE:

1. Use grep to extract just the logical SQL statements

   grep SAW_SRC NQQuery.log > NQQuery.lsql_statements.txt

2. If desired, eliminate duplicates from the file

   	sort -u NQQuery.lsql_statements.txt > NQQuery.lsql_statements.deduped.txt

3. Use split to the lsql statements into separate files:

   split -l 1 NQQuery.lsql_statements.txt replay.

   This creates a set of files with a replay. prefix and xx suffix, eg.

   • replay.aa
   • replay.ab
   • etc

Extracting query metrics from NQQuery.log

The next snippet will parse the end of the NQQuery.log and output query execution details:

• Number of database queries
• How many rows were returned
• How long it took

# get_nq_stats.sh
# https://rnm1978.wordpress.com
#
# Outputs query details of the most recently executed query on BI Server
# Make sure OBIEE_HOME environment variable is set, or update this script to hardcode its location
#
# Usage 
#     get_nq_stats.sh <testref>
#
# Examples:
#   Append to file: 
#     get_nq_stats.sh testrep01 >> nq_stats.csv
#   Output to screen:
#     get_nq_stats.sh testrep01
#
tail -n12 $OBIEE_HOME/server/Log/NQQuery.log|awk -v ref=$1 'BEGIN {physical=""
rows=""
elapsed=""
} {
if ($8=="physical") {gsub(/,/,"",$10) ;physical= $10}
if ($2=="Rows" ) {rows= $6}
if ($2=="Logical") {gsub(/,/,"",$8) ; elapsed= $8}
}
END {
# print "DB Queries,Rows,Elapsed sec"
print ref "," physical "," rows "," elapsed
}
'

The usage for this is on an isolated sequential test environment where you run one BI query, then run this against NQQuery.log, then another query, then this against NQQuery.log etc. Each time you call this script you use a reference (that of the BI Query you’ve just run), and this will be output along with the data from NQQuery.log.
If you call this script and pipe the output to append to a CSV file you can build up a file that looks like this:

Report reference,DB Queries,Rows,Elapsed sec
test_report_001,1,2171,165
test_report_002,1,12,143
test_report_003,2,10,6
test_report_004,1,1890,5
test_report_005,1,615,7
test_report_006,4,893,70
test_report_007,4,1407,77
test_report_008,1,148,126
test_report_009,1,4,48
test_report_011,1,3,152
test_report_012,1,15,430
test_report_013,8,452,141
test_report_014,1,21015,390

OBIEE Replay

These snippets form part of a set of Unix and Oracle scripts that I’ve developed under the title OBIEE Replay. The idea of it is to build a harness through which Logical SQL statements can be run against the BI Server and various metrics collected, all in a repeatable manner.
As and when I get time, I plan to post these scripts up here, so watch this space… 🙂

Syntax for AdminTool.exe command line script

Bringing together in one place all of the script syntax that I’ve found so far for using with OBIEE’s AdminTool.exe /command

Details and examples on usage in the following blogs (where I compiled the commands from):

• Venkat
• Erik Eckhardt (translated from Czech, original here)
• @lex
• Kumar Kambam

---

DON’T TRY THIS AT HOME!

I would only recommend this for read-only purposes such as generating the metadata dictionary or consistency check.

---

* OpenOnline DSN [user [password]]
– Opens the online repository. NB can’t edit properties without checking out objects first, and no way to do that from script.

* Open FileName [user [password]]
– opens the repository offline

* New FileName
– creates new repository offline

* Save
– saves opened repository

* SaveAs FileName
– saves opened repository under new name

* Close
– closes opened repository

* Exit
– closes AdminTool

* SetProperty “Variable” “” Initializer “”
* SetProperty “Connection Pool” “”.”” “User” “”
* SetProperty “Connection Pool” “”.”” “Password” “”
* SetProperty “Connection Pool” “”.”” “DSN” “”

* MessageBox [message]
– displays messagebox with the text, default message is “Siebel Analytics Administration Tool”

* ImportRepository {Online|Offline} {FileName|DSNname} [user [password]]
– initiates import from the other repository

* ImportRepositoryObject {Project|”Presentation Catalog”|User|”Security Group”|Variable} {Name|*} [True|False [True|False]]
– imports object(s) from the other repository

* ImportRepositoryExecute
– executes the repository import defined by previous calls to ImportRepository and ImportRepositoryExecute

* Compare FileName [user [password [outputFile]]]
– compares current repository with another repository

* Merge FileName1 FileName2 [DecisionFile] [user1 [password1 [user2 [password2]]]]
– merges repositories

* ConsistencyCheck [outputFileName]
– global consistency check

* BusinessModelConsistencyCheck businessModelName [outputFileName]
– consistency check for one business model

* CreateSubset NewRepositoryName MasterRepositoryName numberOfProjects project1 [project2 [project3 […]]] [user [password]]
– creates and opens multi-user subset repository

* CheckinSubset ModifiedSubsetRepositoryName LockUserFullName [user [password]]
– checks in ModifiedSubsetRepository into master repository

* DescribeRepository Filename UTF-8
– triggers an export of the rpd metadata to the Filename file in UTF-8 codepage. This is similar to using the Administration Tool utility manually.

* GenerateMetadataDictionary Destination_Folder
– run the Metadata Dictionary export

* Hide
– hides AdminTool

* comment line starts with single quote ‘ character

AdminTool.exe /command

There’s an undocumented feature in AdminTool.exe that you can use the /command switch with a text file containing scripted commands to make changes to an RPD file (or create a new one).

It’s undocumented and UNSUPPORTED so be careful using it.

Some good details in these blog posts, especially Erik’s which has a good list of syntax.

• Venkat
• Erik Eckhardt (translated from Czech, original here)
• @lex
• Kumar Kambam

I’m intrigued to know how the original posters figured out the commands available, if it’s undocumented… 🙂

[update]
Just discovered that CAF uses this functionality in order to Consistency Check the altered RPD that it can create:
CAF uses commandline script:
C:\OracleBI\server\bin\AdminTool /command C:\CAF_Training\Target\Consistency.config

Open C:\CAF_Training\Target\paint.rpd Administrator Administrator
Hide
ConsistencyCheck C:\CAF_Training\Target\ConsistencyCheck.log
Exit

[/update]

Multiple RPDs on one server – Part 2 – Presentation Services

Introduction

In this article I plan to get sample and paint repositories hosted on a single server, using one BI Server instance and two Presentation Services instances. This is on both Unix (OEL 4) and Windows, and both OC4J (OBIEE’s “basic installation” option) and OAS (“Advanced Installation”).

Make sure you’ve read and followed part 1 – BI Server first.

Remember that multiple Presentation Services instances on a machine is UNSUPPORTED BY ORACLE!

OBIEE Components

See the deployment guide p.11 for a thorough explanation of the components.

It’s important to understand the components of the OBIEE stack as what we’re doing is unsupported and undocumented in parts, so you need to be able to diagnose and reason through issues you may get:

• BI Server (nqserver) – the Analytics server. Uses the RPD to build queries to send to the database.
• Presentation Services (sawserver) – This takes the submission of queries from Answers/Dashboards and sends them by ODBC to the BI Server. It handles the rendering of the returned data.
• Presentation Services Plug-in (analytics) – This is a J2EE application deployed in on an application server such as Oracle Application Server or OC4J. It handles server-side calls from the Answers or Dashboards webpage.

What we do is deploy a second instance of the Presentation Services Plug-in (analytics) and configure it to talk to a second invocation of Presentation Services (sawserver) which is run with a new configuration.

NB contrary to other posts on this subject that I’ve seen, you don’t need to install a second instance of presentation services – you just fire up your existing one with a different configuration file.

Deploy a second instance of Presentation Services Plug-in (analytics)

This is for OAS and OC4J, with another application server YMMV.

• Login, at http://:/em/ (common ports are 7777 or 9704) (see here for info on resetting login details it if you don’t know the login)
• [OAS only] Assuming you’re now on the “Cluster Topology” screen, click through to the OC4J home (under Members click the link where in the Type column it says OC4J)
• Assuming you’re now on OC4J: home click the Applications link. (You should see one instance of analytics already deployed.)
• Click on the Deploy button
• For the next step, determine where on your server analytics.war is (by default it will be $OracleBI/web/analytics.war).
• On the Deploy: Select Archive page tick “Archive is already present on the server where Application Server Control is running.” and enter the full path to analytics.war. Under Deployment Plan leave “Automatically create a new deployment plan” ticked. Click Next.
• On the Deploy: Application Attributes page change Application Name and Context Root to whatever you want to access the new instance by. For example, if you currently go to http://localhost:7777/analytics you could choose http://localhost:7777/analyticsInstanceB. In this example I’m going to use analyticsRNM. Click Next.
• On the Deploy: Deployment Settings page you shouldn’t need to change anything. Click Deploy.
• Hopefully you’ll get a successful deployment:Take note of the path listed as “Copy the archive to” in the output. This gives you the j2ee home, which you’ll need in a minute. In this example:

  [25-Aug-2009 09:51:33] Copy the archive to /app/oracle/product/10.1.3.1/OracleAS_1/j2ee/home/applications/analyticsRNM.ear

  the J2EE home dir is

  /app/oracle/product/10.1.3.1/OracleAS_1/j2ee/home

• Click Return, and on your OC4J: home you should now have a second analytics listed

Setting up a second Presentation Services

You need to create a second Presentation Services that will have its own web catalog and configuration to use the correct RPD.
In this example I’ll create a new Presenttion Services which will be for samplesales whilst the original default installation will be for paint.

An important note is that we don’t need to install anything new, we simply use the existing installation with separate configurations (instanceconfig.xml) and web catalog. The next steps assume that you already have the web catalog for samplesales and just cover instanceconfig.xml

In $OracleBIData/web/config create a copy of instanceconfig.xml for your new instance, eg. instanceconfigRNM.xml. Edit it as follows:

• Set the CatalogPath to the web cat for samplesales
• Set the DSN to the ODBC connection you defined above for samplesales
• Under the <ServerInstance> tag add <Listener port=”9711” />. Set the listener port to port that is not currently in use. Remember what you set it to, as you’ll need to update the Presentation Services plugin with it (see below). In unix you can’t use below 1024 unless you’re root (which you shouldn’t be running OBIEE as!).

Be aware that instanceconfig.xml is CaSe SENsiTIve. Thanks to Merlin128 for discovering this 🙂 This can lead to problems as you won’t always get an error. If you use catalogpath (instead of CatalogPath) you’ll get an error, but if you use Listener Port (capital P, should be lowercase) you won’t get an error but sawserver will ignore it and default to port 9710.

Your modified file should look something like this:

<?xml version="1.0" encoding="utf-8"?>
<WebConfig>
   <ServerInstance>
   <Listener port="9711"/>
   <DSN>AnalyticsWebSampleSales</DSN>
   <CatalogPath>/data/web/catalog/samplesales</CatalogPath>
[...]

(NB: following the instructions in “Changing the BI Presentation Services Listener Port” in the deployment guide p.141, I got an error when I tried to embed the Listener tag within RPC: “The configuration entry ‘RPC/Listener’ is deprecated. Please refer to the admin guide for more information.” followed by an Assertion failure. Putting it just within ServerIntance worked fine)

Testing the second instance of Presentation Services (sawserver)

sawserver can be started with command line parameters, one of which is to specify the config file (which defaults to $OracleBIdata/web/config/instanceconfig.xml). Ultimately we’ll package this up neatly, but to avoid complications it’s best to run it natively from the commandline first to make sure it’s working and not hide any output which may be helpful.

Windows

From the commandline go to $OracleBI\web\bin (eg. C:\OracleBI\web\bin) and enter:
sawserver.exe -c c:\OracleBIData\web\config\instanceconfigRNM.xml
(amend c:\OracleBIData\web\config\instanceconfigRNM.xml as appropriate to point to the new instanceconfig.xml file you created above).
Make sure you get a successful startup:

Type: Information
[…]
Oracle BI Presentation Services 10.1.3.4.1 (Build 090414.1900) are starting up.
—————————————
Type: Warning
[…]
WARNING: The Oracle BI Presentation Server is running on a workstation class machine (Windows 2000 Workstation, Windows XP Professional, etc
.). Number of concurrent users may be severely limited by the operating system.
—————————————
Type: Information
[…]
Oracle BI Presentation Services have started successfully.

Unix

From the shell prompt go to $OracleBI/setup and run

. ./sa-init.sh
sawserver -c /data/web/config/instanceconfigRNM.xml

Points to note:

• common.sh and sa-init.sh are “dot sourced”, i.e. type exactly: dot space dot slash
• If you’re on 64 bit then run sa-init64.sh and sawserver64 instead of sa-init.sh and sawserver respectively
• amend /data/web/config/instanceconfigRNM.xml as appropriate to point to the new instanceconfig.xml file you created above

Make sure you get a successful startup:

Type: Information
[…]
Oracle BI Presentation Services 10.1.3.4.1 (Build 090414.1900) are starting up.
—————————————
Type: Information
[…]
Oracle BI Presentation Services have started successfully.

Configuring the new Presentation Services plugin

You need to configure the new Presentation Services plugin (eg analyticsRNM) so that it can communicate with the second instance of Presentation Services.

• Go to your J2EE home directory (if you didn’t note it down below, in OC4J go logs in the top right corner and then click on View for one of the logs, this should give you the path to j2ee/home). Under applications (not application-deployments) go to whatever you called your new instance (eg analyticsRNM), then analytics, then WEB-INF
  eg.

  /app/oracle/product/10.1.3.1/OracleAS_1/j2ee/home/applications/analyticsRNM/analytics/WEB-INF

  or

  C:\OracleBI\oc4j_bi\j2ee\home\applications\analyticsRNM\analytics\WEB-INF\

• Make a backup copy of web.xml
• Open web.xml in your favourite text editor and search for oracle.bi.presentation.sawserver.Port. On the line below there will be the default port of 9710. Change this to the new value that you defined above in Setting up a second Presentation Services (in that example it was 9711). It’s very important to get this bit right!
• In OC4J Application list restart the new instance (eg analyticsRNM), or restart the whole of OC4J/OAS to make doubly-sure.

Testing the configuration

For good measure, first bounce both BI Server and your web/application server (eg OC4J, OAS) if you haven’t already.

Then start both versions of Presentation Services (either manully or scripted, see below). Check that they’ve started up correctly by checking sawserver.out.log, and check they’re listening on the correct ports (eg 9710 and 9711):

Note about ports: don’t confuse these Presentation Services ports with your web server ports. You will always connect from your web browser to your web server on the same port, eg. 9704 or 7777. You would never enter the ports 9710 etc in your web browser address bar.

Start BI server if it’s not running, and then navigate to http://%5Bweb server]:[web server port]/analytics (eg http://localhost:7777/analytics) and login and ensure that you get paint (or whatever you’ve left your default instanceconfig.xml pointing to).

Now try http://%5Bserver%5D:%5Bport%5D/%5Bnew analytics] eg. http://localhost:7777/analyticsRNM and login and hopefully you’ll get samplesales (or whatever your new instanceconfig.xml points to).

samplesales.rpd in one window, paint.rpd on the other, both running from one server

Problems you might encounter

When doing this amount of configuration work it never does any harm to throw in cheeky service restart to see if it resolves an error. It’s probably good practice to try and work through an error first, if only for gathering understanding.

500 Internal Server Error

Servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details.
This is a generic message meaning that the Presentation Services plugin (“analytics”) has thrown an error. To find details either get the log file from disc ($J2EE home/application-deployments//home_default_group_1/application.log) or from OC4J: home
From the log file you can get the real error message of what’s going on

Port 9710 is in use on the local system

Check that you’re starting sawserver directly, and not using sawserver.sh.
If you use sawserver.sh then your -c argument will be ignored because sawserver.sh calls the sawserver binary without any arguments.

Doublecheck your customised instanceconfig.xml file, because sawserver won’t necessarily flag an error if it’s invalid, it will just revert to default values including port (9710).

Your new analytics instance shows the same repository as the default one

You analytics is probably connecting to the incorrect Presentation Services, or the Presentation Services it is connecting to is not running with the correct instanceconfig.xml file

• What port is Presentation Services plugin (analytics) looking for Presentation Services (sawserver) on? see $J2EE home/applications/[your analytics]/analytics/WEB-INF/web.xml and check the param-value for oracle.bi.presentation.sawserver.Port
• Shut down all Presentation Server (sawserver) instances that aren’t configured to serve the port in question. Use netstat to verify that the port is state LISTEN
• Try logging in again – if you get the login screen then you’re connecting to Presentation Services correctly, so the problem must be with the configuration there
• Check the instanceconfig file that the Presentation Services is started with, have you updated DSN, CatalogPath and Listener Port as described above?

java.io.EOFException at com.siebel.analytics.web.sawconnect.sawprotocol.SAWProtocol.readInt

09/08/25 14:04:50.46 analytics: Servlet error
java.io.EOFException
at com.siebel.analytics.web.sawconnect.sawprotocol.SAWProtocol.readInt(SAWProtocol.java:167)
at com.siebel.analytics.web.sawconnect.sawprotocol.SAWProtocolInputStreamImpl.readChunkHeader(SAWProtocolInputStreamImpl.java:232)

This means that the Presentation Services plugin (analytics) cannot communicate with Presentation Services (sawserver).
Check:

• What port is analytics configured to use? see $J2EE home/applications/[your analytics]/analytics/WEB-INF/web.xml and check the param-value for oracle.bi.presentation.sawserver.Port
• Is Presentation Services (sawserver) started?
• Is Presentation Services (sawserver) listening on the same port as is configured in analytics’ web.xml? Check the instanceconfig file that sawserver was started with, and use netstat -a to check if it’s state LISTEN or not
• If all of these look correct, try bouncing your application server (OC4J, OAS, etc)

[nQSError: 10058] A general error has occurred. [nQSError: 12008] Unable to connect to port 9703 on machine localhost.

Whoops, you forgot to start your BI server…

Charts aren’t working, I just get a yellow triangle symbol

Is the Javahost service running?

Starting both Presentation Services neatly

Unix

run-saw.sh is the script used in Unix to control Presentation Services. You can examine it in $OracleBI/setup/run-saw.sh and create your own hack based on your requirements (eg if you always want both started, or to control them individually).

Be aware that run-saw.sh checks for an running instance of “sawserver” so you’ll need to cater for that.

One method would be to create a new startup script like this:

#!/bin/sh
#
# Hacky script to run two versions of Presentation Services
#
# https://rnm1978.wordpress.com/
#
# ---------------------------------------------------
# start your default Presentation Services
echo 'Starting default Presentation Services...'
echo ' '
run-saw.sh start
# The above should be "start64" if you're in 64 bit mode

# Now start the additional Presentation Services
echo '----'
echo ' '
echo 'Starting additional Presentation Services...'
echo ' '
. ./common.sh
. ./sa-init.sh
logfile="${SADATADIR}/web/log/sawserverRNM.out.log"
sawserver -c /data/web/config/instanceconfigRNM.xml  >> ${logfile} 2>&1 &
# The above should be "sawserver64" if you're in 64 bit mode
echo 'See '${logfile}' for log'

Stopping Presentation Services is easier, as run-saw.sh is ruthless in its approach and kills all instances of sawserver. If you don’t want this and want to target a specific instance you’ll need to use ps -ef|grep sawserver and kill the required process.

Windows

To add your new Presentation Services as a service in its own right using Microsoft’s sc, follow these steps.
This involves editing the registry! Do so at your own risk!

1. From the commandline enter:

   sc create sawsvc2 binpath= SEARCHFORMEPLEASE displayname= "Oracle BI Presentation Server 2"
   

   (note the spaces after the equals character)

2. Run regedt32 and search for SEARCHFORMEPLEASE
3. Hopefully you’ll find HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\sawsvc2
4. Edit the ImagePath Value to “C:\OracleBI\web\bin\sawserver.exe” /service /c c:\OracleBIData\web\config\instanceconfigRNM.xml (replacing paths where appropriate)
5. Go to Services and you should see Oracle BI Presentation Server 2 which when started should bring up your new Presentation Services.

(I couldn’t get sc to accept the full command hence the SEARCHFORME hack!)

If you don’t want to muck around with services something like this simple script should suffice. It uses PsExec (from the excellent PsTools suite of utilities) to start multiple sawserver instances in the background.

REM runMultiplePS.bat
REM
REM
REM Hacky script to run two versions of Presentation Services
REM
REM https://rnm1978.wordpress.com/
REM
REM Uses psExec, download it from http://technet.microsoft.com/en-us/sysinternals/bb896649.aspx
REM and put it somewhere in your PATH like c:\windows\system32
REM ---------------------------------------------------
REM Default instance. Comment this line out if you're running it from Services instead.
REM (you could include -c c:\OracleBIData\web\config\instanceconfig.xml  if you wanted, same difference)
psexec -d C:\OracleBI\web\bin\sawserver.exe
REM Additional instance:
psexec -d C:\OracleBI\web\bin\sawserver.exe -c c:\OracleBIData\web\config\instanceconfigRNM.xml
REM ---------------------------------------------------
REM paths will be something like C:\OracleBI\web\bin64\sawserver64.exe for 64-bit

Conclusion

Hopefully this article demonstrates clearly and in enough detail how to set up multiple Presentation Services, without overwhelming the reader. It is actually easy to do, and is great practice for understanding the architecture behind the OBIEE stack. Things to consider after this are the other shared resources (like javahost and logconfig.xml) which may want isolating depending on the use.

Remember that multiple Presentation Services instances on a machine is UNSUPPORTED BY ORACLE!

As well as working on multiple RPDs & Web Cats, this method could be used for one RPD but multiple web cats, maybe at different development levels, or as a sandbox for certain users. In that case the instanceconfig for each Presentation Services would specify the same ODBC DSN.

References / sources

• Borkur Steingrimsson’s article on Rittman Mead
• OTN forum topic “can OBIEE run two rpd files at the same time?”
• Changa Reddy’s blog posting
• OTN forum topic “Presentation Server port wont change”
• Giorgio’s post on OTN
• John Minkjan’s 10.1.3.4.1 config tags document
• B.Vellinger’s blog posting
• BI Server Administration Guide
• Presentation Services Administration Guide
• Installation and Configuration guide

sawserver log – short format

I posted a while ago about the sawserver (Presentation Services) log configuration file.
Today I’m doing some work digging around why sawserver’s throwing an error and so increased the log detail. This parameter is really helpful to use:

fmtName=”short”

Consider in these two screenshots, the first is with the default log format and shows about six entries. The second is short log format and is about ten times as much data.

default log format

short log format

Horses for courses, but on a “fishing expedition” through a log I’d say the short format is definitely easier to work with.

To implement it update $OracleBIData/web/config/logconfig.xml and change the Writer definition:

[...]
	<Writers>
		<Writer implementation="CoutWriter" name="Global Output  Logger" writerClassId="2"/>
[...]

[...]
	<Writers>
		<Writer fmtName="short" implementation="CoutWriter" name="Global Output  Logger" writerClassId="2"/>
[...]

and restart Presentation Services. On a tangent, a lazy way to do this on unix whilst leaving time for ports to free up before restarting is:

run-saw.sh stop;sleep 60;run-saw.sh start64