Friday, February 27, 2009

OBIEE Simulating Drill up

Every now and then I get the question if Drill up is possible within OBIEE. One way of simulating this behavior is using the preferred Drill path option. (One of the lesser documented features is OBIEE).

Chaeck out the basic Calendar Dimension:

image

On the Date level add the next higher level (month) in the Preferred Drill Path:

image

Do the same for Month (quarter), Quarter (year).

Check the result:

image image

image

Yeah I know it's not perfect .....

Till Next Time

Thursday, February 26, 2009

OBIEE Default Portnumbers

On the OTN forums this picture from the Oracle® Business Intelligence Enterprise Edition Deployment Guide:

image

It contains the DEFAULT portnumbers used by OBIEE

  •    25: SMTP
  • 2001: Chart Image Server (Corda) <-> Presentationserver
  • 9700: Prim. Cluster Controler <-> Sec. Cluster Controller
  • 9701: BI Server <-> Cluster Controllers
  • 9703: BI Server <-> Presentation Services
  • 9704: HTTP Web Server
  • 9705: Scheduler <-> Presentationserver
  • 9706: Cluster Controllers <-> Presentationserver
  • 9708: Scheduler <-> : Cluster Controllers
  • 9710: Scheduler <-> Presentation Server <-> J2EE
  • 9810: Javahost <-> Scheduler <-> Presentationserver

Till Next Time

OBIEE Configuring Case Insensitive Search

If you look in the configuration guide you will find that the CASE_SENSITIVE_CHARACTER_COMPARISON parameter in the NQConfig file controls the case sensitive search within OBIEE. Be aware that you might be fooled by your database settings. If you are on an Oracle database 10G+ you can use a connection script in the repository to allow case insensitive searches. Go to the connection pool, click on the connection script tab.

image

Press New: image

Enter:

alter session set NLS_SORT=BINARY_CI
alter session set NLS_COMP=LINGUISTIC

Save the repository, reboot the BI-Server

Till Next Time

Wednesday, February 25, 2009

OBIEE Executing your own JavaScript

Recently I was asked how to asked your own JavaScript from the OBIEE dashboard. I actually fairly simple.

Step 1: Add your script to Common.JS (this usually file is found in the {OracleBI}\web\app\res\b_mozillahere is an example:

// MY Stuff
function obi_popup()
{
    alert("Hello World")
}

image

Step 1b: If you are using OC4J sync the file with the one in {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla

Step 2: restart the presentation server and you webserver.

Step 3: Add the execution code to your dashboard:

image

<input type="button" onclick="obi_popup()" value="Click Me!">

Don't forget to check the Contains HTML Markup!

Step 4: Save your dashboard page and test....

image

Till Next Time

Tuesday, February 24, 2009

Monday, February 23, 2009

OBIEE Reporting on dateranges

When your data is stored in ranges instead of individual events:

image

Graphical representation over a period becomes quit tricky. Best solution: Have the ETL people create a valid entry for each day. Good alternative: Create an entry for each day in the repository.

First create an alias for the calendar table:

image

Join this with a COMPLEX join to your fact table

image

Don't forget to restrict the open ended data!

DIM_DATE.DATEDATE >= AWOL.FROM_DATE AND DIM_DATE.DATEDATE <=  CASE  WHEN AWOL.TO_DATE IS NULL  THEN CURRENT_DATE ELSE AWOL.TO_DATE END

Join this with your regular calendar dimension and set it to outerjoin in the Business model.

image

Check the report:

image

image

Till Next Time

OBIEE Making a distinct date prompt

One of the question I get asked on regurlar basis is: "If have a timestamp column, how do I convert this in a date only prompt".

image 

One of the possible solution is this:

First Create as truncated date column in your RPD:

image

using :

CAST( "BM_CP_TEST"."DIM_DATE_F_FACTS"."D_DATE_RANDOM"  as DATE)

Next create a prompt:

image

on the show dropdown select SQL results.

Next edit the SQL to:

SELECT DISTINCT DIM_DATE_F_FACTS.DATE_TRUNC FROM BM_CP_TEST
ORDER BY DIM_DATE_F_FACTS.DATE_TRUNC

(Don't forget the order by!)

EDIT:

image

Till Next Time

Tuesday, February 17, 2009

OBIEE Editing system wide defaults

Setting a system wide default can be very handy but also completely F@#$K up your system. For instance changes to CSS files don't seem to work. Most catalog administrators forget to retract this privileges until it's to late.

image

Anyway here is how you can undo the damage.

As always be careful when editing directly into the catalog XML.. Backup!

Open the catalog in a off line mode:

image

Navigate to /system/metadata

image

Press Edit XML

image

Press again Edit

image

Carefully Delete edit the XML  !

image

Press save, close catalog manager, restart presentation service.

Till Next Time

Friday, February 13, 2009

OBIEE Switching between filters

Found this one on the OTN forum http://forums.oracle.com/forums/thread.jspa?threadID=859458&tstart=0 

First create a Yes/No Prompt

image

Put the result in a presentation variable called YesNo

On your report create a filter like:

image

Pay close attention to the position of the singel quotes!

Put both the prompt and the report on a dashboard page:

image

image

Till Next Time

Thursday, February 12, 2009

OBIEE "Selling" federated query's

One of the powerful features of OBIEE is the possibility of federated query's. Sadly in most sales pitches we tend to emphases the technical part instead of the practical part. Every now and then there is a BI-manager in the audience who really knows his stuff and starts asking the wright/wrong questions. In this article I want to show you some of the pitfalls when trying to "sell" federated query's.

The most common demo is we get all the data up to last night from our DWH and add today's data from the OLTP system.

Pitfall #1: Creating unstable reports.

Most managers tend to bring paper printouts of there reports to the meeting. This means that the printout for manager 1 at 09:10 will different from the printout for manager 2 at 09:15. This leads to useless discussing of the numbers on the report.

Pitfall #2: Wasted energy

A DWH generally contains data over a long period of time. For strategic or tactical discussions today's unstable data is not significant compared to the amount of historical data. A federated query would be a unnecessary claim of recourses of the OLTP system.

Pitfall #3: Uncleansed data

The ETL process which loads the DWH often cleanses the data from the OLTP system. (FI: Don't count invoices from department '999'). If these rules aren't covered in the RPD it will lead to the wrong numbers.

Does this mean you should use federated query's against an OLTP system at all? Of course not! Just think about the result your are trying to get. Try confining the federated query to a specific level. (FI: Use in only when a day to day comparison is made.)

Till Next Time