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.