The first steps: creating a connection between Oracle Database 19c and OBIEE 12c
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.
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.”
Your original window should now look like this. Click “Finish.”
Your window should now look like this.
Congrats — you have created a connection between OBIEE and the Oracle Database.