Installing OBIEE on 64 Bit Redhat Linux 5.5 The Big Picture

Unfortunately, installing OBIEE on 64 bit Redhat Linux 5.5 is neither simple or straightforward. It is a complex multistep process. Usually requiring that you read dozens of documents and webpages, sometimes for one detail alone.

Here is the big picture, or  high level steps on what to do. And some detail, on a few of the gotchas I had.

For more gory details, you can see:

Oracle’s online document:
http://docs.oracle.com/cd/E14571_01/doc.1111/e14770/biee.htm

And, the 120 page installation document from Oracle: e10539.pdf
Oracle Fusion Middleware
Installation Guide for Oracle Business Intelligence
11g Release 1 (11.1.1)
E10539-02

And the many other links referenced below.

—

Prerequisites:

A Linux server, with lots of memory, disk space, and CPU power
Redhat Linux 5.5 installed on the server. Install -every- package on the DVD.

—

Steps:

Read the OBIEE certification matrix
Download the appropriate software
Double check that the Linux installation has the required packages
Install the Oracle database software, if it is not installed already
Install Java JDK, 64 bit
Create a database for the repository
Run the Repository Creation Utility (RCU)
Install WebLogic with generic .jar
Install OBIEE as “software only”
Run configuration assistant to configure OBIEE
Write scripts to start and stop OBIEE

—

First, check the OBIEE certification matrix, and compare it to your environment. This can keep you busy for a while. I used: bi-11gr1certmatrix-166168.xls

Finding All Generations Of An Object’s Children

Say you have an object, such as a table. What are all objects are dependent on it? That is, all generations. Children, Grandchildren. Great-Grandchildren. Great-Great-Grandchildren. Etc. ??

Four Generations

If you drop that object, all generations of its children will become invalid.

The classic case is a table. There are generations of objects that are built referencing them.

Table
  View
    Package
      Function

Table
  Trigger

etc.

This script will be useful for:
– Determining the Order Of Operations when building objects.
– Determining Multiple dependencies
– Assessing the impact of changing a table structure, or dropping a table.

Very Useful Scripts To Find Object Dependencies

For DBAs and Developers.

When you are developing schemas, a lot of objects get created. It can be really confusing to keep track of them all. Especially when you now need to create a few dozen in a new environment.

What is even worse, is when you start to maintain databases and schemas that you didn’t build yourself. If you drop a table or unsuccesfully modify a view, all their children will now become invalid. Suddenly child procedures and packages that reference them will not work.

Here you can find some sqlplus scripts that I have used for years to see the dependencies in schema objects. Run the script, and enter in the object you have questions on. Very fast and effective.

They have been extremely useful to determine things like:
– The order of operations to create objects. Parents first. Then children.
– The impact of dropping a view, package, procedure.
– Causes of object invalidation such as another invalid object.
– All the tables that a package is accessing directly. For those multiple 18 table joins.
– Whether a package, function or procedure calls other prodecures.
– Obscure issues, such as references to variables in packages.

————————————————————
————————————————————
————————————————————

To Find Just The Parents of an Oracle Database Object:

Parent objects must be created first, before creating the child object. If the parent object is dropped, all subsequent child objects will become invalid.

This info is very important in new environments, such as moving from Dev to Test. Or Test to Prod.

Splitting a Table Vertically, AKA, Duplicate Tables!

More Database Design Mistakes to Avoid

There is more than one way to split a 1:1 relationship into multiple tables.  In the first example, the table was split horizontally, and different columns went into different tables.  But the number of rows stayed the same in all of them.

The stranger way is to split the table vertically.

In other words, to have duplicate tables!

MUCK – Massively Unified Code-Key, Generic Three Table Data Model

Another Database Design Mistake To Avoid.   Actually, one of the worst DB design mistakes you can make.

One of the worst things you can do to a database system is to make a generic data model of three tables for the whole system. A variation is to use three tables to combine multiple lookup tables into one.

Instead of standard tables and columns, three tables are used for everything.

One table each for:
entity/table
attribute/column/field
values of the data

Too Few Tables

More Database Design Mistakes To Avoid

One program that I inherited just wasn’t making sense.

The load of this program loaded data into a single table.  It retrieved data into a few fields.  Then did loop de loops, reading these initial fields, and compared them to hard coded values in the program.  Finally updated the remaining fields, based on the hard coded values in the program.

Eventually it dawned on me.  There was a functional dependency, violating 2NF.  The first field, determined the second field.  This of course should have been moved into another table, but it wasn’t.

Combining Identifying Data, Transaction Data, and LOBs in a Single Table

Another Database Design Mistake to Avoid

In another post, I mentioned that as a rule of thumb, if data has a 1:1 relationship, all the fields should be in the same table.

Of course, with many rules, you can go overboard.  Blindly following rules is the mark of a beginner.  Search for:
The Dreyfus Model of Skills Acquisition

Here is a caveat.

Too Many Tables For a 1:1 Relationship

More Database Design Mistakes to Avoid

I’ve seen a few “designs” where a number of 1:1 relationships were actually split up into a number of tables.

Script to List Tables With No Foreign Keys

More on Database Design Mistakes to Avoid

Related to referential integrity, in addition to systems that do not have primary keys, there are database “designs” where there were no foreign keys!  Much like a Cobol system.

Here is an Oracle script to find the tables that don’t have foreign keys.  Tables that are neither a parent table, or a child table.

Redundant Foreign Key

Another Database Design Mistake to Avoid is the Redundant Foreign Key.

I’ve seen a redundant FK directly to the parent, of the parent.

Referential Integrity and Performance

There are three ways to implement Referential Integrity in a database system.  And each method results in different database performance.

1)
Primary and Foreign Keys:
Fastest.  Most structured.  Declarative.  No coding required.

2)
Triggers:
in between.

3)
Application code:
Slowest.  Least Structured.  Procedural.  The most coding required.   If the code is found in applications outside of Oracle, network traffic gets involved and is even slower.

Oracle Script To Find Tables With No Primary Key

Primary keys are so important in databases.  I keep trying to write an effective post about why they are so important.  But I don’t feel I’m doing the subject justice.  So let me start with a related, but shorter post.

At a number of shops, I’ve come across so many tables that did not have a primary key.  Some of these were very important tables in the system!   Bad, bad, bad.

After discovering so many tables that no primary key, at so many shops, I finally wrote an Oracle script to list all the tables that did not have a PK.