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:
On the Date level add the next higher level (month) in the Preferred Drill Path:
Do the same for Month (quarter), Quarter (year).
Check the result:
Yeah I know it's not perfect .....
Till Next Time
On the OTN forums this picture from the Oracle® Business Intelligence Enterprise Edition Deployment Guide:
It contains the DEFAULT portnumbers used by OBIEE
Till Next Time
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.
Press New:
Enter:
alter session set NLS_SORT=BINARY_CI
alter session set NLS_COMP=LINGUISTIC
Save the repository, reboot the BI-Server
Till Next Time
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")
}
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:
<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....
Till Next Time
I will be on of the speakers at the rittman mead bi forum on may 14th and 15th. Come and join us: http://www.rittmanmead.com/rittman-mead-bi-forum-brighton-may-14th-15th-2009/
See you there!
When your data is stored in ranges instead of individual events:
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:
Join this with a COMPLEX join to your fact table
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.
Check the report:
Till Next Time
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".
One of the possible solution is this:
First Create as truncated date column in your RPD:
using :
CAST( "BM_CP_TEST"."DIM_DATE_F_FACTS"."D_DATE_RANDOM" as DATE)
Next create a prompt:
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:
Till Next Time
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.
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:
Navigate to /system/metadata
Press Edit XML
Press again Edit
Carefully Delete edit the XML !
Press save, close catalog manager, restart presentation service.
Till Next Time
Found this one on the OTN forum http://forums.oracle.com/forums/thread.jspa?threadID=859458&tstart=0
First create a Yes/No Prompt
Put the result in a presentation variable called YesNo
On your report create a filter like:
Pay close attention to the position of the singel quotes!
Put both the prompt and the report on a dashboard page:
Till Next Time
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
