OBIEE Hierarchy Navigation Functions {HNF} Part 1

Since 11g OBIEE has some nice hierarchy navigation function for Parent-Child hierarchies. If you have a 10g background like me you will probably have a natural tendency to work around hierarchy stuff, since it wasn’t available. In this article series I want to show you the functions and how to implement them. There are functions available:

• ISPARENT (Who is my Mother or Father?)
• ISANCESTOR (Who are the {great}(grant)-parent(s)?)
• ISCHILD (Who is my child?)
• ISDESCENDANT (who are the {great}(grant)-children?
• ISLEAF (are there children?)
• ISROOT (Who is the overall boss?)
• ISBROTHER (or sister) (You have to wait until the final part to see the solution)

member_identifier

The functions ISPARENT, ISANCESTER, ISCHILD and ISDENSCENDANT all depend on the member_identifier. This is the column Member Key you identify in your logical table source.

ISPARENT

From the documentation:

The ISPARENT function enables you to find the parents of a member of a parent-child hierarchy, that is, all the members that are one hierarchical level above the specified member.

Presentation Layer Syntax:

ISPARENT(pc_presentation_hierarchy, member_identifier)

Example “Hardcoded Member Identifier”:

Case When ISPARENT("Sales Person"."H5 Sales Rep",'21') Then 'YES' else 'NO' END Example “Session Variable”: Case When ISPARENT("Sales Person"."H5 Sales Rep",VALUEOF(NQ_SESSION.HierarchyUser)) Then 'YES' else 'NO' END

Business Model and Mapping Layer Syntax:

ISPARENT(logical_dimension, member_identifier)

Example “Hardcoded Member Identifier”:

Case When ISPARENT("13 - Hierarchy levels"."H5 Sales Rep",'24' ) Then 'YES' else 'NO' END

Example “SessionVariable”

FILTER("13 - Hierarchy levels"."F0 Sales Base Measures"."1- Revenue" USING   ISPARENT("13 - Hierarchy levels"."H5 Sales Rep",  VALUEOF(NQ_SESSION."HierarchyUser")))

These functions are also available in the LTS and can be used as data access restriction.

Till Next Time

OBIEE mandatory description field

A lot of report builders make META DATA a Friday afternoon project. Recently there was a question on the OTN forum how you could make this mandatory.

You can do this editing the opensavedlg.js, saw.catalog.BrowserDialogViewer.prototype.updateModel part

saw.catalog.BrowserDialogViewer.prototype.updateModel = function () {
        var b = this.model;
		if (this.descEdit.value.length<6)
		{
		  alert("Enter a valid Description");		  
		  this.descEdit.value = "DESC:";
		}
		else
 		{
                   b.description = this.descEdit.value;
		};

After the edit redeploy.

Till Next Time

OBIEE Adding documentation files to the Catalog

Did you know that you can upload your documentation files to your catalog?
First go to the catalog manager in browser:
expand your shared folders:

Click on new > Folder

Give it a meaningful name:

Select the folder:

Press Upload:

Select a file:

Now your documentation is accessible from the catalog:

Put it for instances as a link on your dashboard:
The path is a bit tricky to get used to:
http://<<SERVER_NAME>>: <<port>>/analytics/saw.dll?downloadFile&path=%2FShared%2FDocumentation%2Fdocumentname.ext

Now this might not be the easiest way to publish your documents, but the great advantage is that you can use the OBIEE security model to control access:

Till Next Time

OBIEE Calendar Control

Question from the OTN Forum. (Works in 10 & 11g)

Select a YearMonth, YearWeek and Date from your Calendar dimension, select a measure from your facts.

Add a extra column called “Day Of the Week”

CAST(DAYOFWEEK("Time"."T00 Calendar Date") as varchar(2)) || ' - '||DAYNAME("Time"."T00 Calendar Date")

Alter your fact column to:

'<b>'||cast("Time"."T00 Calendar Date" as varchar(10))||'</b>'||'<br><P STYLE="text-align: right;">'||CAST("Base Facts"."1- Revenue" AS varchar(15))

It concats the date with the fact and adds some HTML Formatting

Alter the column properties [data format] to HTML:

Put it all in a pivot view:

Check the results:

Till Next Time

OBIEE VALUELISTOF(NQ_SESSION.VAR_NAME)

On of the lesser known (and documented) features is the use of VALUELISTOF(NQ_SESSION.VAR_NAME) in the RPD.

As far as I know it’s only available in a session init block.

Let’s assume we have a row wise initiate variable called GeoArea:

This one is populated from our authorisation database and tells the system which areas a user is allowed to see.

Since we can only use single row session variables in the rest of the OBIEE system we have to transform it to a single string.

For this we can use the oracle LISTAGG function (see: http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htm)

We make a new init block with:

SELECT LISTAGG ( COUNTRY_NAME, '; ')  WITHIN GROUP (ORDER BY AREA)
from (select distinct  AREA , COUNTRY_NAME from  SAMP_ADDRESSES_D ) T
WHERE AREA IN (VALUELISTOF(NQ_SESSION.GeoArea))

If we take a peek in the log we see that BI-server is translating this to:

SELECT LISTAGG ( COUNTRY_NAME, '; ')  WITHIN GROUP (ORDER BY AREA)  from (select distinct  AREA , COUNTRY_NAME from  SAMP_ADDRESSES_D ) T

WHERE AREA IN ('West','North','Central','South','South America','Northern','Middle East','East','North Africa','North America','Africa','Europe','Eastern')

The result we put in a session variable called GEO_COUNTRY_LIST.

Be sure to set the order in which the session variables should be loaded:

This variable we can use for a data restriction on the Logical Table Source of our address table.

Till Next Time

OBIEE Killing the firewall on linux

I was trying to connect to the new SampleApp rpd on a remote machine and got this  error nqserror 12008 unable to connect to port 9703. The error says it all, port 9703 is blocked. First I killed the firewall on the linux VM (yep, the  security guy will beat me up tonight )

Open a terminal, make yourself root  > SU

next kill the firewall:

1. Stop the ipchains service. > # service ipchains stop
2. Stop the iptables service. > # service iptables stop
3. Stop the ipchains service from starting when you restart the server. > # chkconfig ipchains off
4. Stop the iptables service from starting when you restart the server. > # chkconfig iptables off

See: http://download.oracle.com/docs/cd/E19140-01/821-0908/p52.html

On all router in between I checked that port 9703 was open.

Now I could make a ODBC for my windows 7 machine to the sample app.

Till Next Time