Removing the PDF/HTML print option

I saw a post in the OTN forums asking how to disable the pdf option in the print menu for dashboards, if you want to do this for a particular dashboard then you can do this through javascript, here is a simple example:

var dateHeaderDiv = document.getElementById("dateHeader");
var dateHeaderPos = findPos(dateHeaderDiv);
dateHeaderDivYOffset = dateHeaderPos[1];
dateHeaderDivXOffset = dateHeaderPos[0];
 
var menuLink = getElementsByClassName("NQWMenuItem");
  
//Get the appropriate function for getting the HTML print
//output of the current page
for(i = 0; i < menuLink.length; i++)
{
   if(menuLink[i].name == "html")
   {
      var onClickText = menuLink[i].onclick;
   }
}
  
//Find the print icon and make it display the print page instead of the
//default menu for pdf or html print (because pdf print does not work
//properly with javascript narrative views)
var pdfParent = getElementsByClassName("DashboardFormatLinks");
  
for(i = 0; i < pdfParent.length; i++)
{
   for(j=0; j < pdfParent[i].childNodes.length; j++)
   {
      if(pdfParent[i].childNodes[j])
      {
         if(pdfParent[i].childNodes[j].title == "Printer Friendly")
         {
            pdfParent[i].childNodes[j].onclick = onClickText;
         }
      }
   }
}

That acutally removes the PDF option and makes the print icon kick off the HTML print page straight away without showing the menu, it works in 10.1.3.4.1.  You can then add that as a static text item with the HTML option ticked and add that to the dashboard.

If you want to remove the option to print to pdf all across the application then first find the controlmessages.xml file which lives in OracleBI\web\msgdb\messages and copy it to OracleBIData\web\msgdb\customMessages, if it the customMessages folder is not there then create it.  Now open the file, it looks like this:

<?xml version="1.0" encoding="utf-8"?>
<!--DO NOT MODIFY THIS FILE.  THIS FILE IS AUTOMATICALLY GENERATED AND IS REPLACED UPON UPGRADE OR REINSTALL.--><!--Contents of this file are Copyright (C) 2001-2005 by Siebel Systems, inc.--><!--Consult your Siebel Analytics Web documentation for how to override messages.  Note that some or all messages may not be overriden unless your license specifically allows for it.--><WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable lang="en-us" system="ControlMessagesSys" table="Messages">

<WebMessage name="kmsgAdminSysLogConcatenateNew"><HTML><nobr><sawm:param insert="1"/></nobr><br/><nobr><sawm:param insert="2"/></nobr></HTML></WebMessage>

<WebMessage name="kmsgAnswersBannerHeight"/>

<WebMessage name="kmsgAnswersBannerURL"/>

<WebMessage name="kmsgCatalogTabURL"/>

<WebMessage name="kmsgChangePasswordLink"><!--    <HTML><a insert="1"><sawm:messageRef name="kmsgUIChangePassword"/></a></HTML> --></WebMessage>

<WebMessage name="kmsgCustomLink"/>

<WebMessage name="kmsgDashboardAddToBriefbookLink"><HTML><a insert="1"><img align="absbottom" src="fmap:Portal/add2bb.gif" border="none"/></a></HTML><!--    <HTML><a insert="1"><img align="absbottom" src="fmap:Portal/add2bb.gif" border="none"/></a>&nbsp;<a insert="1"><sawm:messageRef name="kmsgPortalAddToBriefbook"/></a></HTML> --></WebMessage>

<WebMessage name="kmsgDashboardAlternateFormats"><HTML><span class="DashboardFormatLinks"><sawm:param insert="1"/></span>&nbsp;<span class="DashboardFormatLinks"><sawm:param insert="2"/></span>&nbsp;<span class="DashboardFormatLinks"><sawm:param insert="3"/></span></HTML></WebMessage>

<WebMessage name="kmsgDashboardPrinterFriendlyLink"><HTML><a href="javascript:void(null)" onclick="return NQWPopupMenu(event,'idDashboardPrintMenu', null, 'top')" title="@{title}"><img align="absbottom" src="fmap:Portal/PrinterFriendly.gif" border="none"/></a>
<div id="idDashboardPrintMenu" class="NQWMenu" onmouseover="NQWMenuMouseOver(event)"><sawm:messageRef name="kuiMenuShadowBegin"/><a class="NQWMenuItem" name="html" href="javascript:void(null)" onclick="return PortalPrint('@{htmlURL}[javaScriptString]', true);"><sawm:messageRef name="kmsgDashboardPrintHTML"/></a><sawm:if name="enablePDF"><a class="NQWMenuItem" name="pdf" href="javascript:void(null)" onclick="return PortalPrint('@{pdfURL}[javaScriptString]',@{bNewWindow});"><sawm:messageRef name="kmsgDashboardPrintPDF"/></a></sawm:if>          
<sawm:messageRef name="kuiMenuShadowEnd"/></div></HTML></WebMessage>

<WebMessage name="kmsgDashboardRefreshPageLink"><!-- title --><!-- portalPath --><!-- pageName --><HTML>
<A href="javascript:void(null)" onclick="RefreshPage('@{sawCmd}[javaScriptString]&PortalPath=@{portalPath}[javaScriptString]&Page=@{pageName}[javaScriptString]');return false;" title="@{title}"><img align="absbottom" src="fmap:Portal/dash_refresh.gif" border="none"/></A></HTML></WebMessage>

<WebMessage name="kmsgInOuterFrame"/>

<WebMessage name="kmsgJoinGroupLink"><HTML><a insert="1"><sawm:messageRef name="kmsgUIJoinGroup"/></a></HTML></WebMessage>

<WebMessage name="kmsgStaticWebGroups"><TEXT>Analytics Users</TEXT></WebMessage></WebMessageTable><WebMessageTable system="ViewGeneration" table="IQY">

<WebMessage name="kuiIQYContent"><!-- cmdPrefix = http://machine/path/saw.dll? --><!-- path      = /path/to/some/report --><!-- noSSO     = true | false --><TEXT>WEB<sawm:lineBreak/>1<sawm:lineBreak/><sawm:param name="cmdPrefix"/>Download&Format=excel&Extension=.xls&BypassCache=true&PathEncode=IQYEncode&Path=<sawm:param name="path"/><sawm:if name="noSSO">&NQUser=["Oracle BI User"]&NQPassword=["Oracle BI Password"]&SyncOperation=1</sawm:if><sawm:lineBreak/></TEXT></WebMessage></WebMessageTable></WebMessageTables>

The bit we care about in this case is the kmsgDashboardPrinterFriendlyLink in here you can see the bit that says:

<sawm:if name="enablePDF"><a class="NQWMenuItem" name="pdf" href="javascript:void(null)" onclick="return PortalPrint('@{pdfURL}[javaScriptString]',@{bNewWindow});"><sawm:messageRef name="kmsgDashboardPrintPDF"/></a></sawm:if> 

Removing all of that will disable the print to pdf option across the board as the option will no longer appear in the menu.

Restart the OBIEE services and you should see the change applied. 

Using a row wise initialised session variable in a dashboard prompt

I came across a case today where I wanted to use the contents of a row wise intialised variable in a dashboard prompt.  You can't just use the variable directly as the possible list of values for the prompt as you will get an error message like this:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

SQL Issued: VALUEOF(NQ_SESSION.) 

 

So instead you can use the SQL Results option for the "Show" section of the dashboard prompt to bring in the column you are prompting and then add a where clauses referencing the variable:

 

SELECT Organisation.Organisation FROM Masterplan where Organisation.Organisation=VALUEOF(NQ_SESSION.) 

This will work with no errors and the list of possible results in the dashboard prompt will the same as those held in the session variable.

 

If you then want one of these to be selected by default (if you use report defaults then a blank entry will be added to the dropdown and this will be selected by default) then you can use some SQL again:

SELECT MIN(Organisation.Organisation) FROM Masterplan where Organisation.Organisation=VALUEOF(NQ_SESSION.ORGANIZATION_NAME)

 

This will select the first one in the list of results when ordered alphabetically.

OBIEE11g Launch

So, just back from the launch day for OBIEE 11g, lots of good stuff there, although I have to say that after this long in development I was hoping for a bit more, especially for developers.  When one of the people giving a session opened the Admin tool and I saw that it was pretty much exactly the same with new icons my heart did sink a little bit.

Anyway, here are the notes I made, and a few blurry pictures from my iPhone, sorry still getting used to it ...

Visualisation:

- Geospatial support, ie results shown on map without any extra config, probably separately licensed says my sceptical self.
- Scorecards, kpi watchlist , strategy trees etc; these are all new and look very powerful, again the implication was separate licensing.
- Complete integration of bi publisher and
- Complete control of the whole from enterprise manager
- Pivot tables can be re- pivoted in place by the user; huge win in my mind
- Comments against the strategy tree explaining problems etc, collaboration, not so sure about this one.

From insight to action:

- Action links have the traditional navigate etc options but also allow custom actions. These actions can be all sorts of navigation, call web service, java method, browser script or http method. Guided navigation still available on these links.

- Can have saved actions which we then just pick out as action for link. So actions are now first class objects that are saved and shared.

- Can add commentaries to dashboards etc for collaboration, maybe useful if going to sharepoint or something.

Dashboard editor is much prettier.

iBots now called Agents. Other than that not very different for delivers section except for actions which now allows the full list as above and use of the action library. Also have invoke per row so that action is triggered for each row in the report instead of for the whole result set.

Systems management and deployment:

- Centralised performance management.
- Pull logs from all components on all servers to one log and trace an error or query all the way through it.
- Usage tracking and delivers etc schemas are created at point of installation with repository creation utility. 

- Oracle Process Management Notification now used for controlling non j2ee comps i.e. BI server etc. All j2ee comps now controlled via weblogic which is included in the installation. EM can be used to configure everything now for 11g.

Security things: pluggable SSO and ID management. SSL everywhere can be configured in one place and then starts working between all components just like that.

3 install options, simple, enterprise (pick ports network install etc) and software only for just lying out software and not doing the configuration.

Very simple 10g to 11g upgrade experience multipass so that you can check what is going to happen etc before doing it. Upgrades data and schema and all pieces eg webcat.

EM is very good now. No more searching for logs etc all in one place no more configuration files either.

Can also see performance metrics for the environment e.g. Number of queries, new logins by time.

6 weeks till GA of OBIEE11G

DAC server as service in 10.1.3.4.1

Today I had to create the DAC server as a service again, this time in a later version of the DAC and found that the libraries have changed.  To get the DAC server working in this version:

I had to use this command (you'd need to change this to point to your location for the jdk and the dac root directory):

javaservice -install "Oracle BI: DAC Service" "E:\Java\jdk1.6.0_13\jre\bin\client\jvm.dll" -Xms256m -Xmx1024m "-Djava.class.path=E:\OracleBI\DAC\bifoundation\dac\lib\msbase.jar;E:\OracleBI\DAC\bifoundation\dac\lib\mssqlserver.jar;E:\OracleBI\DAC\bifoundation\dac\lib\msutil.jar;E:\OracleBI\DAC\bifoundation\dac\lib\sqljdbc.jar;E:\OracleBI\DAC\bifoundation\dac\lib\ojdbc6.jar;E:\OracleBI\DAC\bifoundation\dac\lib\ojdbc5.jar;E:\OracleBI\DAC\bifoundation\dac\lib\ojdbc14.jar;E:\OracleBI\DAC\bifoundation\dac\lib\db2java.zip;E:\OracleBI\DAC\bifoundation\dac\lib\terajdbc4.jar;E:\OracleBI\DAC\bifoundation\dac\lib\log4j.jar;E:\OracleBI\DAC\bifoundation\dac\lib\teradata.jar;E:\OracleBI\DAC\bifoundation\dac\lib\tdgssjava.jar;E:\OracleBI\DAC\bifoundation\dac\lib\tdgssconfig.jar;E:\OracleBI\DAC\bifoundation\dac\DAWSystem.jar;E:\OracleBI\DAC\bifoundation\dac" "-Duser.dir=E:\OracleBI\DAC\bifoundation\dac" -start  com.siebel.etl.net.QServer -description "Oracle BI DAC Server Service" -current "E:\OracleBI\DAC\bifoundation\dac"

As you can see the libraries are completely different compared to my earlier post.

You must include the -current parameter, as otherwise the relative references to files will not work and so features such as integration with Informatica will not work correctly.

SA System Subject Area

I have seen a few posts on various blogs which have info on the SA System area, but none of them seemed complete so here's my attempt :-)

The SA System area allows you to set up delivery devices for users of Siebel Delivers automatically, so that it isn't necessary for each user to fill in My Account with appropriate delivery devices and profiles etc.

The core of the SA System area is a presentation catalog/subject area containing exactly the following tables and columns:

 

As far as I can work out the BMM layer can look pretty much as you want as long as the presentation layer looks like this.  For example, in other examples on blogs I have seen the Group Name on the same logical table as the user name etc; in my BMM I took a leaf out of the OBIA and how the SA System is configured to work with Siebel:

 

So I created a seperate logical table for groups, and then an intersection table to hold the many to many relationship between users and groups, this also mapped to the physical layer:

 

I did this because I wanted each user to be able to have multiple groups, more on this later. 

So, once I had created this rest rpd, I set up my OBIEE server to use it, filled in some data in the DB tables and then started it up.  This is a query showing what I added to the tables:

I then logged in as my test user, went to My Account and got this:

 

Hmm, strange.  So as instructed I  looked at the BI presentation server log:

Authentication Failure.
Odbc driver returned an error (SQLDriverConnectW).
---------------------------------------
Type: Error
Severity: 40
Time: Fri Jan 29 14:20:21 2010
File: project/websubsystems/sasystemsubjectarea.cpp Line: 248
Properties: ThreadID-7256;HttpCommand-UserPreferences;RemoteIP-127.0.0.1;User-Matt
Location:
    saw.httpserver.request
    saw.rpc.server.responder
    saw.rpc.server
    saw.rpc.server.handleConnection
    saw.rpc.server.dispatch
    saw.threadPool
    saw.threads

Error finding  System SA. Authentication Failure.
Error Codes: IHVF6OM7:OPR4ONWY:U9IM8TAC

Odbc driver returned an error (SQLDriverConnectW).
State: 08004.  Code: 10018.  [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.
[nQSError: 43001] Authentication failed for Administrator in repository Star: invalid user/password. (08004)

I had to scratch my head over this one for a while until I wondered if it was something to do with the credential store that Delivers uses to authenticate to the presentation services.  So I decided to try adding Administrator to the store:

C:\OracleBI\web>cd bin

C:\OracleBI\web\bin>cryptotools credstore -add infile c:\OracleBI\web\config\credentialstore.xml
>Credential Store File:
C:\OracleBI\web\bin>cryptotools credstore -add infile c:\OracleBIData\web\config\credentialstore.xml
>Credential Store File:

C:\OracleBI\web\bin>cryptotools credstore -add infile c:\OracleBIData\web\config\credentialstore.xml
>Credential Store File: c:\OracleBIData\web\config\credentialstore.xml
>Credential Alias: admin
>Username: Administrator
>Password: ******
>Do you want to encrypt the password? y/n (y): n
>File "c:\OracleBIData\web\config\credentialstore.xml" exists. Do you want to overwrite it? y/n (y): y

C:\OracleBI\web\bin>

After that I restarted the services and tried again, to my surprise this had worked:

 

Here we can see the devices I added for myself in the database table (the cell phone and pager aren't real examples, they have to be addresses to which texts can be delivered rather than actual phone numbers):

 

  

  

And here is the profile I added:

On playing around with this there appear to be some of the columns which don't do anything:

• Language
• Locale
• Time Zone
• Group Name

As the membership to web groups is handled through the GROUPS session variable, I'm not sure what the Group Name in SA System is supposed to do.  I tested by adding myself to multiple groups but this didn't appear to have any effect anywhere within OBIEE.

Another thing to consider is that a user can still add their own delivery devices and profiles and thus override the SA System ones.  However this can be disabled through the instanceconfig.xml, the tag <IgnoreWebcatDeliveryProfiles> needs to be added inside the Alerts tags and set to true, for example:

<ServerInstance>
...
<Alerts>
...
<IgnoreWebcatDeliveryProfiles>true</IgnoreWebcatDeliveryProfiles>
...
</Alerts>
...
</ServerInstance>

This will mean that users cannot now add their own delivery devices or profiles, the My Account screen looks like this (notice that the links to add new devices and profiles are missing):

By default the user name you log in with and the user name in the database table must exactly match for the SA System area to work.  You can get around this by using the tag <UpperCaseRecipientNames> in the instanceconfig.xml as below:

<ServerInstance>
...
<Alerts>
...
<UpperCaseRecipientNames>true</UpperCaseRecipientNames>
...
</Alerts>
...
</ServerInstance>

If you then ensure that the user names on the database table are in upper case then the user logging in can be in upper, lower or a mix and it will still work.  For example, the username on the database table is 'MATT' but I log in using 'Matt', this will still pick up my delivery devices and profiles if the tag above is set to true.

Finally, it is possible to disable the SA System area completely from the instanceconfig.xml using the tag <SystemSubjectArea> (note that this tag does not go inside Alerts):

<ServerInstance>
...
<SystemSubjectArea>true</SystemSubjectArea>
...
</ServerInstance>

ISAPI Forwarding

At a recent client we were using two web servers, both running IIS and the OBIEE presentation services, over the top of this a load balancer routed connections to one of the two servers based on load.  We used the Oracle provided replication services to make sure that the contents of the two web catalogs running on each presentation server were kept in synch.

However we noticed over time that the replication services weren't working 100% and that some items were not present in both web catalogs.

To get around this we decided instead to use the ISAPI forwarding functionality; this allows the IIS server on one web server to forward it's presentation service connections to another server.  This means that we can have two servers running IIS and accepting incoming connections, but only one running the presentation services, so only one web catalog is required and no replication has to take place.

In versions before the name change to OBIEE (i.e. Siebel Analytics 7.8 etc) this was handled via a registry key entry as below:

Path:

HKEY_LOCAL_MACHINE\Software\Siebel Systems, Inc.\Siebel Analytics\Web\7.8\ISAPI\

String value:

Name = ServerConnectString

Value = sawtcp://[server name]:9710

In OBIEE this has been replaced with a setting in the configuration file:

[Oracle BI Directory]\web\config\isapiconfig.xml

Like this:

<?xml version="1.0" encoding="utf-8"?>
<WebConfig>
   <ServerInstance>
       <ServerConnectInfo address="localhost" port="9710"/>
   </ServerInstance>
</WebConfig>

Just change localhost to the name of the server running the presentation services that you want the IIS connections to be forwarded to.

RPD Groups and Siebel Responsibilities

This post explains how responsibilities map to RPD groups in OBIA/Analytics Apps with Siebel as a source of the apps.

First of all, you must either create responsibilities in Siebel with exactly the same name as groups in RPD, or vice versa.  The important point is that there must be groups in the RPD and responsibilities in Siebel with exactly the same name:

 Groups in RPD

 

Responsibilities in Siebel

When a user logs into OBIEE an initialisation block named "Authorization" runs some SQL against the Siebel database and gets the responsibilities for the current user.  It sets the session variable GROUP to the set of responsibilities returned.

If there are groups in the RPD which have identical names to any of the responsibilities held in the GROUP variable after the initialisation block has run then the user will be added to those groups, and will then inherit all the security (object and data level) for that group.

This example is based on Siebel as a source, but there is no reason why this can't work for any source.  The important point is the session initialisation block which runs and gets the groups to add the user to.  In Siebel this is controlled through responsibilities, in another source system it could be through another mechanism; as long as it is possible to retrieve through SQL then the initialisation block can be changed to use this.

Logical Levels

One of the issues that developers regularly run into with OBIEE (especially when they are new to the application) is the setting of levels against logical fact sources.  The most common error which should instantly have you thinking about levels if you see it is:

Logical dimension table has a source that does not join to any fact source

Beyond the obvious check that all sources do actually join to a fact you should then think about levels.  Logical Levels are set against the logical source of fact tables in the BMM layer:

Levels control how the fact table connects to the dimension table and are used to create level based measures and control the use of aggregation tables.  What many people don't realise is that to make OBIEE work correctly you should always set the levels on fact tables, so that OBIEE knows that it can connect the dimension and the fact at the most detailed level.

The general rules for levels are:

1. If you create a new dimension table, build a dimension hierarchy over it
   
2. Make sure the dimension hierarchy has a detail level at the lowest level of granularity for the dimension table (i.e. integration id, ROW_WID etc)
   
3. If you are adding a join from this new dimension to any fact table then make sure that you set the level for the connected dimension hierarchy against the correct logical source of the fact (the one that physically joins to the dimension), the default level to set is the lowest level (the detail level).
4. If you create a new fact then you need to make sure you set the levels as in 3 for all the dimension hierarchies built over dimensions which this fact joins to.

Sometimes you can get away with not setting levels, but more often than not this will come back and bite you in the long run, the better option is to ensure that you always set them.

Row-wise variables

This is a topic I've seen a few times on the OTN forums, and I'm not surprised because the way it has been built is not exactly intuitive.  With that in mind here are some notes on Row-wise intialisation of varaibles in OBIEE, how it works, how to use them and what they are useful for.

So first of all I am going to set up a table for testing purposes:

CREATE TABLE TEST_ROWWISE(
    ROW_WID NUMBER(10) NOT NULL,
    USER_NAME VARCHAR2(100),
    VAR_VALUE VARCHAR2(100)
);

INSERT INTO TEST_ROWWISE VALUES(1,'matt','Value 1');
INSERT INTO TEST_ROWWISE VALUES(2,'matt','Value 2');
INSERT INTO TEST_ROWWISE VALUES(3,'matt','Value 3');
INSERT INTO TEST_ROWWISE VALUES(4,'sue','Value 1');
INSERT INTO TEST_ROWWISE VALUES(5,'sue','Value 2');

commit;

Then in my repository I created a user matt and a user sue for testing purposes:

Next I created a new session intitialisation block:

With a data source of this:

Then in the data target:

The end result of this is that when a user logs in the Oracle BI server will run the SQL in the initialization block against the database and return the rows from the table TEST_ROWWISE for that user (the parameter :user always passes the username of the currently logged in user).  After it has done that the currently logged in user now has a variable called 'VAR' containing all the values for that user.

I tested it by exposing the TEST_ROWWISE table in an rpd and creating a simple request against that table:

Then to test the variable add a filter to the request:

And the detail of the filter:

When logged on as matt I see:

And when logged in as sue:

So here you can see that the logged in user has a session variable VAR which holds all the values returned from the row-wise initialisation.

One way this technique is used in the Oracle Business Intelligence Applications is to go and fetch the web groups that the user should be added to from the source database.  For instance, if Siebel is the source then an intialisation block gets all the responsibilities for the user from the Siebel DB and assigns these to a variable called GROUPS.  In this way any repsonsiblities that the user is associated to, which have exactly the same name as groups existing in the rpd, will be automatically associated to the user at login.

JavaScript help item

One thing that I have found useful at a few different clients is a small item that can be added to a dashboard which helps the user to understand what the dashboard is supposed to achieve, how to use it etc.  This can be accomplished fairly easily with a static text view on a report that just brings back a very easy result.  However, sometimes this will be even better received if you add a bit of style to it.  It is very easy to make an expanding/collapsing item using some simple javascript and css.  Here is a very simple example:

Initial state of item:

After clicking the "Show Help" link:

Clicking the Hide Help link will then collapse the item again, this adds a bit of style to the help item and saves screen real-estate on the dashboard when the help is not needed.

This can be easily created in a static text item following these steps:

1. Create a very simple report, for example just put the year column from one of the date dimensions in the request and limit it to 2009, something that will return very quickly.  You have to have a query underneath a request, even if you are only exposing a static text view.
   
2. Create a static text view, ensure the "Contains HTML Markup" box is ticked otherwise it won't work properly.
   
3. Put the HTML in the box, here is the HTML for my simple example:

<html>
    <head>
       <meta http-equiv="content-type" content="text/html;charset=ISO-8859-1">
       <style type="text/css">
          <!--
          div.wrapper {
             text-align:left;
             margin:0 auto;
             width:500px;
             border:2px solid #1358A8;
             background: #9ABFDC;
             padding:10px;
          }
          #myvar{
             border:1px solid #1358A8;
             background:#EAEFF5;
             padding:20px;
             font-family:verdana;
          }
          a.link{
             color:#1358A8;
             cursor:pointer;
             font-family:verdana;
             font-weight:bold;
             text-decoration: underline;
          }
          p.custom{
             font-family: verdana;
             color: #586073;
          }
          -->
       </style>
       <script type="text/javascript">
          <!--
          function switchMenu(obj) {
             var el = document.getElementById(obj);
             var linkVar = document.getElementById("showhidelink");
             if ( el.style.display != "none" ) {
                el.style.display = 'none';
                 linkVar.innerHTML = "Show Help";
             }
             else {
                el.style.display = 'block';
                linkVar.innerHTML = "Hide Help";
             }
          }
          //-->
       </script>
    </head>
    <body>
       <div class="wrapper">
          <p align=center><a title="Show/Hide" onclick="switchMenu('myvar');" class="link" id="showhidelink">Show Help</a></p>
          <div id="myvar" style='display:none;'>
             <p class = "custom">Here is some help text to give details on what this dashboard is showing</p>
             <p class = "custom">And another paragraph with some more information</p>
          </div>
       </div>
    </body>
</html>

As you can imagine using some more complex css means you can make this look even better and make it match any company branding guidelines for your client.

DAC server and OC4J as Windows services

As I'm sure you are all aware the DAC server runs as a batch command which lives in the user's session, so as soon as that session ends the command dies and the DAC server stops. This is a real pain for a server side app as at many clients the remote desktop sessions for users are automatically disconnected after a period of time. One way to get around this is to create scheduled server tasks to kick off the DAC server process just before the ETL starts. This is far from ideal however.

So, a while ago I came across this posting on MySupport (or Metalink as it was then) (ID 578174.1) which gave a suggestion for using a free utility called JavaService to install OC4J as a service. The utility is available here:

http://forge.ow2.org/projects/javaservice/

This got me to thinking about whether I could get the DAC server to run as a service, after a lot of playing around I finally got it working properly, you need to create the service using JavaService with this command:

javaservice -install "Oracle BI: DAC Service" "C:\Progra~1\Java\jdk1.5.0_17\jre\bin\client\jvm.dll" -Xms256m -Xmx1024m "-Djava.class.path=D:\OracleBI\DAC\lib\ojdbc14.jar;D:\OracleBI\DAC\DAWSystem.jar;E:\OracleBI\;D:\OracleBI\DAC\lib\ant-1.6.5.jar;D:\OracleBI\DAC\lib\antlr-2.7.6.jar;D:\OracleBI\DAC\lib\ant-antlr-1.6.5.jar;D:\OracleBI\DAC\lib\asm.jar;D:\OracleBI\DAC\lib\asm-attrs.jar;c3p0-0.9.0.jar;D:\OracleBI\DAC\lib\cglib-2.1.3.jar;D:\OracleBI\DAC\lib\checkstyle-all.jar;D:\OracleBI\DAC\lib\cleanimports;D:\OracleBI\DAC\lib\concurrent-1.3.2.jar;D:\OracleBI\DAC\lib\connector.jar;D:\OracleBI\DAC\lib\dom4j-1.6.1.jar;D:\OracleBI\DAC\lib\ehcache-1.2.3.jar;D:\OracleBI\DAC\lib\hibernate3.jar;D:\OracleBI\DAC\lib\jaas.jar;D:\OracleBI\DAC\lib\jacc-1_0-fr.jar;D:\OracleBI\DAC\lib\javassist.jar;D:\OracleBI\DAC\lib\jaxen-1.1-beta-7.jar;D:\OracleBI\DAC\lib\jboss-cache.jar;D:\OracleBI\DAC\lib\jboss-common.jar;D:\OracleBI\DAC\lib\jboss-jmx.jar;D:\OracleBI\DAC\lib\jboss-system.jar;D:\OracleBI\DAC\lib\jdbc2_0-stdext.jar;D:\OracleBI\DAC\lib\jgroups-2.2.8.jar;D:\OracleBI\DAC\lib\jta.jar;D:\OracleBI\DAC\lib\junit-3.8.1.jar;D:\OracleBI\DAC\lib\log4j-1.2.11.jar;D:\OracleBI\DAC\lib\proxool-0.8.3.jar;;D:\OracleBI\DAC\lib\versioncheck.jar;D:\OracleBI\DAC\lib\xerces-2.6.2.jar;D:\OracleBI\DAC\lib\xml-apis.jar;D:\OracleBI\DAC\lib\jsr173_api.jar;D:\OracleBI\DAC\lib\sjsxp.jar" "-Duser.dir=D:\OracleBI\DAC" -start  com.siebel.etl.net.QServer -description "Oracle BI DAC Server Service"

Long I know :-) but the DAC server needs all those jars to work properly.  Obviously you need to change the paths if your OBIEE instance is not installed in D:\OracleBI

This should create a service which you can start and stop as any other Windows service and runs a perfectly functioning DAC server.

To get back to the OC4J document I found on MySupport (ID 578174.1) I tried it and initially it appeared to work but I soon realised that there were problems with the BI Publisher server (which runs in OC4J) which meant that the BI Publisher server could not access the report repository properly.

So I got to work trying to find out why this didn't work and eventually I worked it out, so here are the steps to get an OC4J windows service which allows the BI Publisher to work fully:

1. Copy the JavaService.exe executable to the directory OracleBI\oc4j_bi\bin you can rename it to something else if you want, I named it OC4JService.exe.  It is vitally important that you copy the executable to this location and use this executable to create the service, otherwise you will have problems with the BI Publisher server not being able to access the BI Publisher repository.
2. Using this executable create the Windows service.
3. OC4JJavaservice -install "Oracle BI: OC4J Service" "D:\jre1.5.0_17\bin\client\jvm.dll" -XX:MaxPermSize=128m "-Djava.class.path=D:\OracleBI\oc4j_bi\j2ee\home\oc4j.jar" -start oracle.oc4j.loader.boot.BootStrap -params -out "D:\OracleBI\oc4j_bi\j2ee\home\log\oc4j.log.txt" -err "D:\OracleBI\oc4j_bi\j2ee\home\log\oc4.err.txt" -current "D:\OracleBI\oc4j_bi\bin" -description "Oracle BI Publisher Server"
   

Obviously the first parameter is a personal choice for the service name.  The second parameter is the location of the jre to use.  Leave the third parameter as is.  The fourth parameter is the location of the oc4j.jar, the main jar for OC4J.  The fifth parameter is the class to start from that jar, leave this as is.  The parameters after -params are optional and are just an output log, an error log and a description for the service.

Once the command in number 2 has finished the Windows service should be created and it should start, stop and operate normally.  The BI Publisher running from this service should work correclty and have full access to the report repository.

OBIEE Security

OBIEE security boils down into 2 different types:

• Object level security
• Data level security

Object Level Security
Each object in the RPD can be secured by user group to restrict access. 

 

In this picture everyone has access to this presentation table.  To restrict access you click on the tick to change it to a cross for everyone.  You can then give access only to specific groups by changing the box to a tick for those groups.

Data Level Security
Data level security is also controlled by user group, filters are defined against user groups and these filters then restrict the data returned to each user by manipulating the WHERE clause in the SQL generated by the server.

For instance:

Here you can see the filters that are defined for the user group "Primary Org-Based Security".  If we take one example:

When a user creates a query including the logical table Core."Dim - Opportunity" the OBIEE server will look up the physical column for the logical column Core."Dim - Opportunity".VIS_PR_BU_ID, and add a clause for this column to the WHERE clause of the SQL query generated.  In the case above we are using a session variable called ORGANIZATION, this is generated at login by row-wise initialisation.

So we end up with this on the end of the SQL Query:

WHERE W_OPTY_D.VIS_PR_BU_ID IN ('1-A1233','1-D453G','1-98GT2')

And so the user will only see data from their organizations.  This exact example is based on Analytics Apps 7.9.5 using Siebel as the source OLTP system.  So the ORGANIZATION variable is initialised by getting all the user's orgs from the Siebel DB in a SQL statement; you may need to create something similar for your application yourself.

OBIEE Query Caching

This is something I see come up on the OTN forums every now and then so I thought I would put together a post explaining how OBIEE query caching works and how to administer it.

Introduction

OBIEE includes two levels of caching, one level works at the OBIEE server level and caches the actual data for a query returned from the database in a file, the other level is at the OBIEE Presentation Server.  This post will concentrate on the former.

NQSConfig

Caching is enabled and configured in the NQSConfig.ini file located at \server\Config, look for this section:

###############################################################################
#
#  Query Result Cache Section
#
###############################################################################

[ CACHE ]

ENABLE    =    YES;
// A comma separated list of pair(s)
//   e.g. DATA_STORAGE_PATHS = "d:\OracleBIData\nQSCache" 500 MB;
DATA_STORAGE_PATHS    =    "C:\OracleBIData\cache" 500 MB;
MAX_ROWS_PER_CACHE_ENTRY = 100000;  // 0 is unlimited size
MAX_CACHE_ENTRY_SIZE = 1 MB;
MAX_CACHE_ENTRIES = 1000; 
POPULATE_AGGREGATE_ROLLUP_HITS = NO;
USE_ADVANCED_HIT_DETECTION = NO;

The first setting here ENABLE controls whether caching is enabled on the OBIEE server or not.  If this is set to NO then the OBIEE server will not cache any queries, if it is set to YES then query caching will take place. 

DATA_STORAGE_PATHS is a comma delimited list of paths and data limits used for storing the cache files.  This can include multiple locations for storage i.e.  C:\OracleBIData\cache" 500 MB, C:\OracleBIData\cache2" 200 MB.   Note that although the docs say that the maximum value is 4GB the actual max value is 4294967295.

MAX_ROWS_PER_CACHE_ENTRY is the limit for the number of rows in a single query cache entry, this is designed to stop huge queries consuming all the cache space.

MAX_CACHE_ENTRY_SIZE is the maximum physical size for a single entry in the query cache.  If there are queries you want to enter the cache which are not being stored consider increasing this value to capture those results, Oracle suggest not setting this to more than 10% of the max storage for the query cache.

MAX_CACHE_ENTRIES is  the maximum number of cache entries that can be stored in the query cache, when this limit is reached the entries are replaced using a Least Recently Used (LRU) algorithm.

POPULATE_AGGREGATE_ROLLUP_HITS is whether to populate the cache with a new entry when it has aggregated data from a previous cache entry to fulfil a request.  By default this is NO.


USE_ADVANCED_HIT_DETECTION if you set this to YES then an advanced two-pass algorithm will be used to search for cache entries rather than the normal one-pass.  This means you may get more returns from the cache, but the two-pass algorithm will be computationally more expensive.

Managing cache

 The cache can be managed by opening the Administration Tool and connecting online to the OBIEE server.  Go to Manage -> Cache:

This will open a window showing you the cache as it currently is with all entries:

This contains some very interesting information about each entry such as when it was created, who created it, what logical SQL it contains, how mnay rows are in the entry, how many times the entry has been used since it was created etc.


To purge cache entries select them in the right hand window and right click, choose purge from the context menu:

This will remove those cache entries from the query cache and any request which would previously have been fulfilled by this cache entry will instead go to the database (and create a new cache entry).  This feature is essential if you make a data fix to the underlying data warehouse and need to ensure this fix is displayed in OBIEE as cache entries will then be stale (outdated because the underlying data does not match the data in the cache any more).

Cache Persistence Timing

In addition to the global cache settings you can set the cache persistence individually on each physical table:

The first option is to make the table cacheable or not, if the table is cacheable then there are a number of other options; "Cache never expires" which is self-explanatory and "Cache persistence time" which accepts a number and a period (days, hours, minutes, seconds).  This period and number defines how long an entry for that table will be kept in the query cache, after this time period expires the cache entry for this table will be removed.

Programmatically purging the cache

Oracle provide a number of ODBC calls which you can issue to the OBIEE server over the Oracle provided ODBC driver to purge the cache these are:

SAPurgeCacheByQuery(x) - This will purge the cache of an entry that meets the logical SQL passed to the method.

SAPurgeCacheByTable(x,y,z,m) - This will purge the cache for all entries for the specified physical table where:

x = Database name
y = Catalog name
z = Schema name
m = Table name

SAPurgeAllCache() - This will purge all entries from the query cache.

These ODBC calls can be called by creating a .sql file with the call in it, i.e.:

Call SAPurgeAllCache();

Then create a script to call it, on Windows you can create a .cmd file with the contents:

nqcmd -d "AnalyticsWeb" -u Administrator -p [admin password] -s [path to sql file].sql -o [output log file name]

This will use nqcmd to open an ODBC connection to the ODBC AnalyticsWeb and then run your .sql file which will purge the cache.

I have used this many times as the only step in a custom Informatica WF which is then added as a task in the DAC and set as a following task for an Execution Plan.  This way after the ETL the cache will be purged automatically and there is no risk of stale cache entries. 

Event Polling Tables

Another method for purging the cache selectively is to use event polling tables.  These are tables which the OBIEE server polls regularly, when it finds a row in the table is processes it and purges any cache entries for the table referenced in the row.  Your ETL process could be configured to add rows to an event polling table when it has updated a table and thus ensure the query cache is not stale.  An example event polling table:

create table UET (
   UpdateType Integer not null,
   UpdateTime date DEFAULT SYSDATE not null,
   DBName char(40) null,
   CatalogName varchar(40) null,
   SchemaName varchar(40) null,
   TableName varchar(40) not null,
   Other varchar(80) DEFAULT NULL
); 

Once this table has been created you need to import it into the physical layer and then use the Tools -> Utilities -> Oracle BI Event Tables option to select the table as an event table.

Online Modification of RPD 

If you modify the RPD online, then any changes that affect a Business Model will force a purge of the cache for any entries relating to that Business Model.

Well I hope that is useful, it is by no means a complete document on Query Caching, but it should give a good overview and some ideas on managing the cache in a live environment.