Friday, March 11, 2011

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. 

Thursday, March 10, 2011

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.

Wednesday, July 7, 2010

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



Tuesday, June 1, 2010

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.

Tuesday, February 2, 2010

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>

Thursday, November 26, 2009

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.

Wednesday, October 28, 2009

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.