OBIEE Follow the sun / Time driven connection

One of our customers wanted a 'follow the sun' solution to connect to there international databases. Translated to the practice this means that they wanted to set a connectionpool based on the the local time. To achieve this we made a repository initationblock called "REP_IB_TNS" and a repository variable called "LOCAL_TNS_TO_USE".

In the datasource block we made a very simple query which determines which TNS to uses based on the time of the day:

We forced a refresh every 10 minutes by setting the "lifespan" of the block:

In the connection pool you make a reference by using the VALUEOF method:

n theory it takes a maximum of 15 minutes (10 minutes for "REP_IB_TNS", 5 minutes for the connection pool) to refresh all connections

Till Next Time

OBIEE Blocking request based on Criteria

The basis can be found in Oracle Business Intelligence Presentation Services Administration Guide (B31766.pdf)

“ When a user attempts to execute a request that your code blocks, you can display an error message, and the request will not be executed. The answerstemplates.xml file includes a message named kuiCriteriaBlockingScript that can be overridden to either define or include JavaScript that defines a validateAnalysisCriteria function. By default, this message contains a function that always returns True.
Answers calls your validateAnalysisCriteria function when the user tries to execute the request. The function can return True if the request is not blocked, or False or a message if the request is blocked. If a message or a value other than False is returned, the message is displayed in a popup window. In either case, the query is blocked.”

Since the documentation on this subject is guiding you in the wrong direction, I toke upon myself to make a step by step instruction.

Step 1: in .. \OracleBI\web\msgdb create a customMessages folder if it not already there:


Step 2: in \OracleBI\web\msgdb\customMessages create an XML file named : qbCriteria.xml (The name is arbitrary, the content isn’t !!!)


Step 3:
Enter the following XML into the file

{ ?xml version="1.0" encoding="utf-8"?}
{ WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem"}
{ WebMessageTable system="QueryBlocking" table="Messages"}
{ WebMessage name="kuiCriteriaBlockingScript" translate="no"}
{ HTML}
{ script language="javascript" src="fmap:myblocking.js" /}
{ /HTML}
{ /WebMessage}
{ /WebMessageTable}
{ /WebMessageTables}

Replace the { and }
Save the file!

In …OracleBI\web\app\res\b_mozilla (this is wrong in the OBIEE documentation) make a new text file called myblocking.js




Enter the following Script:

// This is a blocking function. It makes sure users pick what I want them to.
function validateAnalysisCriteria(analysisXml)
{
// Create the helper object
var tValidator = new CriteriaValidator(analysisXml);
// Validation Logic
if (tValidator.getSubjectArea() != "Paint")
return "Why don't you try Paint?";
if (!tValidator.dependentColumnExists("Markets","Region","Markets","District"))
{
// If validation script notifies user, then return false
alert("Region and District go so well together, don't you think?");
return false;
}
if (!tValidator.dependentColumnExists("Sales Measures","","Periods","Year"))
return "You picked a measure so pick Year!";
if (!tValidator.filterExists("Sales Measures","Dollars"))
return "Why don't you filter on Dollars?";
if (!tValidator.dependentFilterExists("Markets","Market","Markets"))
return "Since you're showing specific Markets, please filter the markets.";
var n = tValidator.filterCount("Markets","Region");
if ((n <= 0) (n > 3))
return "Please select 3 or fewer specific Regions";
return true;
}

save the file:

the name myblocking.js is seemingly hard coded is OBIEE somewhere, I wasn’t able to change it……..

Restart the whole BI-server and play around in the Paint and Paint Exec subject area.

In Paint Exec:

In Paint:



Till Next Time

OBIEE Excel Import, prepping the data

One of our customers has an application which generates an Excell with combined data.

They wanted this to be imported into OBIEE, but couldn’t get OBIEE to see the data. They got the connection oke, but didn’t "see" the tables. This problem was caused by the fact that the generating application didn’t prep the data. It didn’t set the named ranges for the three tables.


Here is how you do it by hand:
Select a range:

Enter a range name and press enter!

Do the same for the two other tables:


Here is how you do it by VBA code:

Columns("A:B").Select ActiveWorkbook.Names.Add Name:="MY_TABLE_RANGE",
RefersToR1C1:= _ "=Sheet1!C1:C2"

When you now try to import the data into OBIEE you will see the three tables:

Till Next Time

Note: don't forget to disable the count distinct feature:

http://obiee101.blogspot.com/2008/11/obiee-excell-count-distinct-error.html

OBIEE "Private" output type for a chart

Sometimes you want the OBIEE chart in a higher graphical definition. This can be done by altering the instanceconfig.xml. Problem with this is that this will change the setting for the whole presentation server.


At one of our customers we had the need only to change this for the scatter chart and leave the rest on default (Flash)


This is how we did it:

Warning: Remember PNG and JPEG graphical types are not down drillable!

Step 1: Altering imagetypes.cxml

First locate your Chart support directory, usually found in …OracleBI\web\app\res\s_oracle10\chartsupport

Make a copy of “imagetypes.cxml” in the same directory, give it a name which refers to the chart type you want to use (eg: “imagetypesScatter.cxml” )

Open the renamed file (e.g. with Notepad):


Changed preferred to the image type you want (e.g.: “png”)






! Save the file

Step 2: Altering charttype.cxml

Now locate the charts directory usually found ….\OracleBI\web\app\res\s_oracle10\charts


Open the .CXML file for the chart type (e.g.: “scatter.cxml”)

Alter the
{?xml:namespace prefix = xi /}{xi:include parse="xml" href="fmap://chartSupport/imagetypes.cxml"}{/xi:include}{?xml:namespace prefix = xi /}{xi:include parse="xml" href="fmap://chartSupport/imagetypes.cxml"}{/xi:include}

(of course use the correct tag sign)

Till Next Time

OBIEE Directing a user to a default dashboard

One of our customers wanted the OBIEE users after login directed to a default dashboard based on the department they work in. First we created a view containing P_USER, DEPARTMENT and PORTALPATH.

In the variable manager we created an initialization block


We connected this to a session variable called PORTALPATH. This is a OBIEE reserved word so will get a warning, just ignore it.

You need to restart the BI-server and the presentationserver!
Let’s try it:
USER1

USER2

USER3

Till Next Time

OBIEE Flexible Dashboards

One of our customers wanted a “flexible”dashboard. They wanted to select a x number of trucks and have an individual graph for each truck. The number of selected truck varies between 0 and 5.
We made this possible by using the RANK function in OBIEE. We made a simple report for the “licence plate” dimension:



Have a look at SQL going to the database:

If we put a filter (fi: 3) on the RANK we get:

You see that the filtering is done after getting the RANK number from the db.
Now save this report 5 times were you put a different number in each rank filter (SEL1, SEL2, SEL3, SEL4 and SEL5) .
To test this make a dashboard page with a dimension prompt and the 5 SEL reports:

Select 1 truck:

Select 3 trucks:

Now make your “real” reports and filter them “Filter based on results of another request.


Set your NO RESULTS view do a simple ‘-’

Do this also 5 times every time on a different SEL report. (RES1, RES2, RES3, RES4 and RES5)
Put the 5 RES reports to your dashboard instead of the SEL reports.

Check if it works:

Till Next Time

(this text is also published on http://knowledge.ciber.nl/)

NOTE: in some cases using RCOUNT instead of RANK works better, there is a known issue of OBIEE RANK function when numbers are strings.