The first steps: creating a connection between Oracle Database 19c and OBIEE 12c

Published in

Geek Culture

5 min readOct 12, 2021

So you finally finished installing OBIEE and Oracle Database 19c on your on-premises server — and just as you are about to pull out the champagne and start celebrating, you realize one major problem: despite the fact that you needed to have an Oracle Database up-and-running to properly install OBIEE, for some reason, there still seems to be no communication between the two. In other words, the data inside your Oracle Database is not visible in OBIEE. All you can see is the Sample Sales Lite and Sample Targets Lite subject areas and no data files nor connections…This is a major problem. We are going to fix this.

Before I continue, this blogpost assumes that you already have Oracle Database 19c up-and-running on your Windows Server and that you have unlocked the HR dataset. If you have not already done this, you can checkout my blogpost on how to install and configure Oracle Database 19c on Windows here. This blogpost also assumes that you have OBIEE 12c up-and-running on your Windows Server. If you have not already done this, you can checkout my blogpost on how to install and configure OBIEE 12c here. Let’s get started.

Setting the TNS_ADMIN environmental variable

We first need to set the TNS_ADMIN environmental variable. This variable essentially defines the directory where the tnsnames.ora file is stored. First, click on the start menu in the bottom left-hand corner of your screen. Then, open your settings app by clicking on the gear icon, circled in red.

Your screen should now look like this. Click-on “System.”

Your screen should now looks like this.

Inside the “Find a setting” search bar, search for the following.

Edit the system environment variables

Select the only option below.

A new window should pop-up that now looks like this. Click on “Environment Variables,” which I have circled in red below.

We are now going to define the environment variable, TNS_ADMIN. In your Environment Variables screen, click the “New” button, circled in red below.

A new window should pop-up that looks like this.

Set “Variable name” as follows.

TNS_ADMIN

Set the “Variable value” to the directory to where your tnsnames.ora file is located for your database. In my instance, it is as follows.

C:\App\db_home\network\admin

The window should now look like this. Click “OK.”

Creating a connection between OBIEE and the database

We are now going to access the HR dataset in OBIEE inside the OBIEE admin tools. In your Windows File Explorer, navigate to the following directory.

C:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\bitools\bin

Your Windows File Explorer should look like this. Right-click on the admintool application, which I have circled in red below.

In the right-click menu, click “Create shortcut” and then drag this shortcut to your desktop. This will just make the application more accessible for future use. Now, double-click on the shortcut to open the application. A window should pop-up that looks like this.

On your keyboard, press the “Ctrl”+”N” buttons together to create a new repository. A new wizard-like window should pop-up. Let’s run through it.

Step 1: Repository Information

Fill-out the window with the following inputs:

Name: HR-OBIEE
Location: Leave this as-is
Import Metadata: Check the “Yes” bubble
Repository Password: [input_any_password]

Your window should now look like this. Click “Next.”

Step 2: Select Data Source

Your window should now look like this

Set the “Connection Type” to “OCI 10g/11g.” Your window should now look like this.

For the “Data Source Name,” you need to use the easy connect syntax to define your data source, which is as follows.

//database_host[:port]/service_name

In my instance, my string will look like this.

//localhost:1521/orclpdb

For the username, input hr, and for the password, also input hr. Your window should now look like this. Click “Next.”

Step 3: Select Metadata Types

Your window should now look like this. Leave everything “as-is.” Click “Next.”

Step 4: Select Metadata Objects

Your window should now look like this. First, select the HR schema underneath the “Data source view” window, circled in red below. Then, click the blue arrow, also circled in red below to input the HR schema into the Repository View.

Click the Blue Arrow, Circled in Red Above

Two windows will pop-up after you do this — first, you will get a window that looks like this.

Drag this window out of the way and ignore it for now. The other window that will pop-up will look like this. Change the “Timeout” to infinite. Then, click “OK.”