OBIEE Top N Months across

I was asked how to get the Top 10 customers for the year 2007 and put there revenue per month. First make the basic report and filter, put the revenue column in twice:

Open the second revenue column and change the formula to:

SUM("F1 Revenue"."1-01  Revenue  (Sum All)" BY "D1 Customer"."C1  Cust Name")

Create a Top N filter for the second revenue column:

Switch to PIVOT table:

Place the columns in the right places, set the aggregation:

Till Next Time.

OBIEE Making an OR filter

Question I got from the blog, how to make an or filter in OBIEE.

Say you want the revenue for 2008 or the 2007 Q1. First make your report including the filters:

Next click “AND” to change into OR . This way you can make complex filters:

Till Next Time

a generic calendar dimension

Finally had the time to activate on of my other blogs. This one will contain my more generic articles. Here is the first one: http://bizintell101.blogspot.com/2009/10/bi-101-generic-calendar-dimension.html

Feel free to comment.

Till Next Time

OBIEE Multiple repository’s / Multiple presentation servers

Excellent research by RNM:

• http://rnm1978.wordpress.com/2009/08/25/multiple-rpds-on-one-server-part-1-the-bi-server/
• http://rnm1978.wordpress.com/2009/08/25/multiple-rpds-on-one-server-part-2-presentation-services/

Need I say more?

Till Next Time

OBIEE Open a Linux-Hosted OBIEE Repository in Online Mode (repost)

For those who run their OBIEE servers in a Linux environment, it’s possible to open your repository in Online mode from your Windows-based local machine. I just discovered this, so I thought I would pass it along. This assumes that you have the appropriate version of OBIEE installed on your local machine.
Update: Do not use spaces in your System DSN names. You will get an error when trying to connect to that server.

This article has quite a few screenshots, so continue reading by clicking below:
All you need to do is create a new System DSN: Go to Start > Run…, and type odbcad32, then press enter (or open ODBC Data Sources from Administrator Tools).
Click the System DSN tab and click Add:

Choose Oracle BI Server as the Driver:

Give the DSN a Name (no spaces) and optionally a Description, and specify the hostname of your BI server, then click Next:

Type a login ID and Password for your repository, and specify the BI Server Port. Check the option “Connect to Oracle BI Server to obtain default settings…” to make life easier, and click Next:

Accept the defaults if you’re happy with them, and click Finish:

You’ve now added a new System DSN to your local machine and are ready to open the repository in Online mode:

In the Administrator tool, choose the blue folder to open in online mode, and choose the DSN you just created from the list below the login prompt. Enter your login details and click Open to work on your repository in Online mode:

Of course, as the OBIEE documentation recommends, I would only use Online mode for making small changes to your repository. Any significant amount of work should be done in offline mode.
This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html
Till Next Time

OBIEE When the Top N Filter fails… (repost)

If you’ve read my previous post about Using the Top N Filter, this will be a follow up post that covers a trick I discovered when the Top N filter didn’t do what I wanted it to do.
My requirement is to only show data for the past 6 weeks.  I have a base table that has massive amounts of weekly data, designated by a column week_ending.  At first, I thought I could simply add a Top N filter for "Week Ending" in Top 6.  This didn’t work however, since I have multiple rows containing the same date - it only returned data for the latest date in the table (which makes sense, because that date would be the same for each of the Top 6 since it occurs many times).  I also tried using the DISTINCT keyword in various places in the filter formula, as well as in the column definition - still no luck.
What I ended up doing was the following:

1. I decided to turn to SQL to give me the results I need.  I started by building a query that gave me the Top 6 dates in my base table:
   

   SELECT * FROM (SELECT DISTINCT week_ending
   FROM base_table
   ORDER BY week_ending DESC)
   WHERE rownum <= 6;
   
   WEEK_ENDING
   ------------------------- 
   13-JUL-08
   06-JUL-08
   29-JUN-08
   22-JUN-08
   15-JUN-08
   08-JUN-08                 
   
   6 rows selected

   
   
2. 
   
   
   
   Next, I used the query above in the WHERE clause for a view:
   
   

   CREATE OR REPLACE VIEW top_six_weeks_vw AS
   SELECT *
   FROM base_table bt
   WHERE bt.week_ending IN (SELECT * FROM (SELECT DISTINCT week_ending                                          FROM base_table
   ORDER BY week_ending DESC)
   WHERE rownum <= 6);
   
   

   
   
3. 
   
   
   
   Now, simply model this view in the OBIEE Repository, and your Answers reports will only display the latest 6 weeks worth of data.
   
   

This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html









Till Next Time

OBIEE Using the Top N Filter (repost)

Learn to use the Top N filter to filter results in Answers requests. Covers advanced usage of the Top N filter using the by clause, to show the Top N results within a particular grouping (i.e., Top N Customer Revenue amounts by Product):

Suppose you want to see the Top 5 results from an Answers report, such as the Top 5 overall customers by revenue:

This can easily be acheived by using a simple “in Top N” filter:

What if you want to see the Top 5 Customers by Revenue for each Product? Using a simple Top N filter won’t work in this case, as it will produce the following results:

To get OBIEE to show the correct results, you need to take the Top N filter into SQL mode. Starting with a normal Top N filter, click Advanced at the bottom of the filter window and select Convert this filter to SQL:

Next, edit the filter with the by clause enclosed in red - unfortunately you’ll have to type the column name in manually, so be sure to use quotes if the column or presentation table contains spaces. The column used here is the field you want to group by (Product, in this example):

Now, the Top N filter will give the desired results - The Top 5 Customers by Product:

The full request criteria (notice the sorting options used to produce the results above):


This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html
Till Next Time

OBIEE Grand Totals with Calculated Columns (repost)

This is a bit of a specific problem, but I could see it being something that those new to OBIEE could run into. It deals with incorrect Grand Total values being displayed for a Calculated Column when using report filters.
Consider the following report:

In this report, Amount A and Amount B are standard columns in a database table, and Variance is a calculated column in the repository. The calculation for Variance is 100 * (Amount B - Amount A) / Amount A. So, going by the Grand Total amounts in the report above, 100 * (696 - 550) / 550 = 26.55.
Suppose you want to filter the results of this request so that it only shows items having a Variance greater than 30:

Note the Grand Total for Amount A and Amount B are summed correctly for the filter, but the Variance still shows 26.55. Obviously, this is incorrect, so what’s going on here? The filtered amount should be reported as 100 * (220 - 130) / 130 = 69.23.
If you want to show the correct variance for the filtered Grand Total amounts, you need to enable an option in your instanceconfig.xml file. Open the file $OracleBIData/web/config/instanceconfig.xml, and add the following line somewhere inside your <ServerInstance> and </ServerInstance> section:

<ReportAggregateEnabled>true</ReportAggregateEnabled>

Make sure you don’t accidentally put this inside an element nested inside <ServerInstance>, or it won’t work.

Save instanceconfig.xml, and restart your BI Server and Presentation service.
Now, the correct amount should be shown for the filtered Grand Total variance:

This was previously a bug in OBIEE that was addressed by adding the ReportAggregateEnabled option. It’s not really documented anywhere other than on Metalink, so hopefully this will be helpful to someone.
This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html
Till Next Time

OBIEE Tooltip Text in Column Heading

On the forums every now and then the question on how to achieve this:

comes along, and most of the time the conclusion is that it can’t be done without some serious JS hacking. Here is a workaround which takes a little bit less effort:
First create an extra column containing your ToolTip text and column header like:

'<div title="This text explains the contence of the column">1-01  Revenue  (Sum All)</div>'

Set the display format to HTML:

Put the result in a pivot table:

Till Next Time

Edit: Here is an even quicker method:
http://obieepractice.blogspot.com/2008/11/column-headerooltip-mouse-over.html

OBIEE LDAP Problems? Try this! (repost)

On my previous project, we had been facing a very strange LDAP-related issue for a while, which prevented us from upgrading to OBIEE 10.1.3.4. The problem was that upon doing an install of 10.1.3.4, users who accessed the server using an LDAP account could no longer log in. Even worse, when someone tried to log in using an LDAP account, the entire BI server would crash and would need to be restarted. Our BI server was hosted on a Red Hat Linux environment, so the resolution of this problem is OS-specific.
After going through many rounds of trying to figure this problem out, we filed a ticket with Oracle Support. About 3-4 weeks later our problem was solved - we needed to set the environment variable LD_PRELOAD. Apparently this was not being set after our attempted upgrades to 10.1.3.4.
LD_PRELOAD is a way of setting the priority of certain libraries, it loads the specified library first. This is called interposing libraries. Suppose you have an application that makes a call to some function that exists in multiple libraries. If you use LD_PRELOAD to load a particular library first, then the application will go to that library and use its functions, instead of some other library. LD_PRELOAD essentially replaces the functionality of another library.
So the actual problem was traced back to a possible symbol clash that occurs between libraries called by the application (OBIEE), and other 3rd party libraries. Our LDAP user logins are in the format of an email address, so it’s possible the mailto:%27@%27 symbol could causing the issue - I’m really not sure.
We ran the command:

export LD_PRELOAD=/path/to/libibmldap.so

Then we restarted the BI Server and Presentation services, and the problem went away!


This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html


Till Next Time

OBIEE a blog lost….

Recently Kevin C. http://oraclebi.soundvoid.net/ mailed me that he is stopping his blogging activities for the time being and clearing his blog. He asked me if I wanted to “save” his most interesting OBIEE articles.
First of all I want to wish Kevin all the best for the future.
In the next days I will repost his OBIEE related articles under the normal labels and add a special KEVIN C tag.
Till Next Time

OBIEE ODBC Client

One of the features you get when doing an OBIEE install on windows is the ODBC Client application.

I never had any use for it until I saw some entries by Nicolas and @lex on some ‘secret’ procedures.

http://gerardnico.com/wiki/dat/obiee/presentation_service/obiee_nqs_drill

http://siebel-essentials.blogspot.com/2009/08/oracle-bi-server-nqs-procedures.html

It appears to by a demo/check tool to test your sql when developing a application which uses a ODBC connection to the BI-server. I couldn’t find any documentation, so tips pointers are welcome.

After starting the application you have to click:

File > Open Database to make a connection:

The usage of the database field is unclear for me at the moment.

From the utility menu you can directly test your BI-server sql:

From the View Schema you get an very unusable info of the catalog:

I couldn’t get the query list to work….

It seems this is a R&D/Q&D tool for the development team which somehow made it’s way into the production release. I you have some more info or usage- for it please let me know.

Till Next Time

OBIEE Patches 10341

Some interesting patches for 10341 are out:

8599681
Oracle BI Suite EE: Patch
DATE FORMAT ISSUE ON THE DASHBOARD PROMPT
8284585
Oracle BI Suite EE: Patch
NAVIGATION/DRILL DOES NOT WORK WHEN THE COLUMN BEING DRILLED IS IN POSITION 11+
8796912
Oracle BI Suite EE: Patch
DISCONNECTED DOESN'T WORK ON VISTA AS NON-ADMIN USER
8669206
Oracle BI Suite EE: Patch
FIREFOX 3.0 REFRESH FAILURE CAUSES UNEXPECTED BEHAVIOR IN DRILLING/VIEW SELECTOR
8743856
Oracle BI Suite EE: Patch
EXECUTION PLAN DOES NOT UPDATE LAST TASK AS COMPLETED
8685156
Oracle BI Suite EE: Patch
BLR BACKPORT OF BUG 8394579 ON TOP OF 10.1.3.4.1 (BLR #147542)
8650261
Oracle BI Suite EE: Patch
BLR BACKPORT OF BUG 8595693 ON TOP OF 10.1.3.4.1 (BLR #143831)
8685120
Oracle BI Suite EE: Patch
MLR BACKPORT FOR BASE BUGS 8680924 8674235 8608837 8567128

8633968
Oracle BI Suite EE: Patch
MLR BACKPORT FOR BASE BUGS 8331209 8371708 8372436

8616993
Oracle BI Suite EE: Patch
BI OFFICE PATCH
6702999
Oracle BI Suite EE: Patch
REPORT AGGREGATE - RANK HAS DIFFERENT BEHAVIOR VS RANK WITH AGGREGATE
8611209
Oracle BI Suite EE: Patch
MLR BACKPORT FOR BASE BUGS 8332167, 8290868, 8350962
8238481
Oracle BI Suite EE: Patch
NQSERROR14026 OCCURED IRREGULARLY
8439796
Oracle BI Suite EE: Patch
PRIVILEGE ERROR DISPLAYED ON SELECTION OF DELIVERS RECIPIENTS

Yes you need a metalink account to download them. No, I will not download them for you and redistribute them. Ask your local Oracle representative for support.

Till Next Time

Got the flu

For all of you waiting for an answer at the moment: I’m having flu at this moment: (“Dutch” style)

Please be patient or post urgent questions at the OTN forum: http://forums.oracle.com/forums/forum.jspa?forumID=378

EDIT: 19/09/2009 Sort of feeling better now, still got the sniffles. Thanks everybody for the wishes!

Till Next Time

OBIEE Bing Maps

I showed how to integrate Google Maps into OBIEE. Doing the same with Microsoft Bing maps is slightly different. There service is completely asynchronous, which meant a slightly different approach for the narrative view.

Let’s start with the basics. Get your addresses and a extra column for the array counter. This is just a RCOUNT – 1.

Switch to a narrative view. In the prefix part put:

<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<script src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script>
<script>
var map = null;
var count = 0;
var adr = new Array();
var cmt = new Array();
var Center = new VELatLong(52, 6);
var ZoomLevel = 7;
function GetMap()
{

in the narrative put:

adr[@1]="@3";cmt[0]="@2";

In the postfix part put:

map = new VEMap('myMap'); map.LoadMap();
map.SetCenterAndZoom(Center, ZoomLevel);
FindLocation(adr[0]);

}

function FindLocation(searchstr)
{
if (searchstr != '')
{
map.Find(null, searchstr, null, null, null, null, false, null, null, false, AddPin);
}
}

function AddPin(layer, resultsArray, places, hasMore, veErrorMessage)

{
if(places.length > 0)
{
var shape = new VEShape( VEShapeType.Pushpin,places[0].LatLong);
shape.SetTitle(places[0].Name);
//alert(comments[count]);
shape.SetDescription(cmt[count]);
map.AddShape(shape);
map.SetCenterAndZoom(Center, ZoomLevel);
}
count++;


if(count<adr.length)
FindLocation(adr[count]);
}
</script>

</head> <script src="http://code.jquery.com/jquery-latest.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready( function(){GetMap();return false;
}

);

</script>
<body> <div id='myMap' style="position:relative; width:800px; height:600px;"></div> </body>

Don’t forget to set Contains HTML:

Add it to the compound Layout:

I personally find this webservice slower then the one from Google, but maybe some better coding could fix that. If you have any ideas, please let me know.

Till Next Time

OBIEE Config Tags

Updated the Config Tags file and made (hopefully) better readable: OBIEE 10341 Config Tags

Till Next Time

OBIEE Prompt based on groupmembership

Somebody on OTN claimed that you can’t make prompts available based on the security setting of the user (See: http://forums.oracle.com/forums/thread.jspa?threadID=943712). Here is a workaround based on guided navigation:

First create a report which filters results based on the group  (noprompt) membership:

SELECT TIMES.CALENDAR_YEAR saw_0 FROM BM_SALES WHERE LOCATE( 'noprompt',VALUEOF(NQ_SESSION.GROUP)) > 0 ORDER BY saw_0

Based on being a member of nogroup the prompt will be available:

User1:

User2:

Till Next Time