Oracle Business Intelligence

Unfortunately, I have just joined a new project that is not BI or OBIEE related.  Since I won’t be working with OBIEE daily, I won’t be able to post as often as I’d like.  My passion still lies with BI and OBIEE, so I will continue posting to this blog as I find the time.  I will plan on making at least one post per month if possible.

If anyone has suggestions for topics to write about, or questions about OBIEE or BI, send me an email (hit the Contact Me link at the top of this page), and I’ll try to address them.

As always, thanks for reading!

November 19th, 2008 by Kevin Custer | Category: Blog | Leave a comment

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 ‘@’ 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!

November 19th, 2008 by Kevin Custer | Category: Business Intelligence, OBIEE | Leave a comment

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:

More »

October 14th, 2008 by Kevin Custer | Category: Business Intelligence, OBIEE | Comments (1)

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.

October 3rd, 2008 by Kevin Custer | Category: Business Intelligence, OBIEE | Comments (4)

Oracle has announced Oracle Exadata, a “database machine”, marking the first ever hardware-based solution in Oracle’s product line.  This thing looks pretty impressive.  It’s basically a server rack that has 8 database servers and 14 storage servers and 4 24-port network switches, running Oracle 11g RAC on Oracle Enterprise Linux.  It supports up to 46 TB of user data, managed by Oracle’s Exadata storage software.  The server hardware is from HP, with some impressive specs:          8 x HP Proliant DL360 Database Servers, each with 2 x quad-core Intel Xeon (E5430) Processors 32gb Memory 4 x 146gb SAS hard drives 14 x HP Proliant DL180 G5 Exadata Storage servers, each with 2 x quad-core Intel Xeon (E5430) Processors 8gb Memory 12 x 1TB SATA hard drives (or 12 x 300gb SAS hard drives)

Altogether that’s 176 cores and 368gb total memory (go ahead, drool )  As usual, Oracle is touting performance (reporting 10x faster Data Warehouse performance), scalability (add Database Machines as needed), and reliability.

September 25th, 2008 by Kevin Custer | Category: Database | Leave a comment

I received the following email this morning:

Attention OBIEE Interested Parties

Oracle University would like your opinion.  They are in the very preliminary stages of scoping out the viability of an OBIEE certification path.  Please click the link below to fill out a short survey. 

http://education.oracle.com/pls/web_prod-plq-dad/ou_online_display_survey.display_survey?p_survey_no=300&p_preview=N

It would seem Oracle University is considering updating the certification path for OBIEE.  Currently, the only certification path is for the old version of the product (Siebel Analytics 7.7), which requires that you take the corresponding OU Siebel courses to get certified.  I’m all for an updated certification based on the newer, Oracle branded version of the product, so I encourage everyone to fill out the survey (link) to express your interest.

September 18th, 2008 by Kevin Custer | Category: Business Intelligence, OBIEE | Leave a comment

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.

I’m sure there are many other ways these results could be accomplished, but this was the first thing that came to mind.

September 12th, 2008 by Kevin Custer | Category: OBIEE, SQL | Leave a comment

When working on systems with frequent data loads, the OBIEE cache can often prevent end users from seeing the latest data.  Sometimes it’s appropriate to disable caching on particular tables or reports.  OBIEE has two types of caching mechanisms - Presentation Services caching and individual table caching.

The following link provides a few very good posts from John Minkjan’s blog on how the OBIEE cache works, configuration settings, and how to purge the cache(s).

• OBIEE Cache Management

September 5th, 2008 by Kevin Custer | Category: Business Intelligence, OBIEE | Leave a comment