Art of BI: How to Upload OBIEE RPD to Oracle BICS

The cloud is big in the database world today, and its popularity is quickly growing. Oracle has capitalized on this trend with the analytics tool, Oracle Business Intelligence Cloud Service (BICS).

BICS is a solution that bridges the gap between OBIEE‘s enterprise centric and top-down approach to business analytics and reporting, and the need for an on-demand tool that provides flexibility to functional users, department heads, business analysts, and just about any user who needs a short turnaround time for their information and reporting requirements. It provides a multi-tenanted, cost-effective solution.

A useful feature of the BI Cloud Server is the ability to migrate on-premise OBIEE content without manually building the data model in BICS, allowing you to take full advantage of the Admin Tools the cloud solution does not yet offer. Uploading an RPD file to BICS is not difficult, but many do not know how to do it.

Let’s start by reviewing the makeup and purpose of the semantic layer.

The semantic layer

One of the key components of the OBIEE architecture is its semantic layer. The semantic layer is based on the Common Enterprise Information Model (CEIM). The semantic layer has to be specifically created by the user of OBIEE.

The semantic layer consists of the following:

–        Physical layer

–        BMM layer

–        Presentation layer

Why do we need a semantic layer?

The purposes of the semantic layer include the following:

1. It provides a common and consistent view of the data which the users can access.
2. It provides a mechanism of controlling the data that user can access.
3. It abstracts the physical sources of raw data from the user and provides them with readily consumable data from diverse sources that can be directly accessed in their analysis, dashboards, reports, and visualizations.
4. It stores metadata about the data.

What does the semantic layer have to do with RPD? 

In a nutshell, RPD is the name of the file extension that is used by OBIEE to store the details of the semantic model. To expand this further, OBIEE uses this file to store the metadata, physical sources of data, access rules, aggregation rules, and all other relevant information that transitions the physical raw data into meaningful views to the end users.

As discussed earlier, the RPD has to be created within OBIEE. This can be done using the Oracle BI Administration Tool. Here is a resource to get you started on creating the RPD file.

Migrating RPD Files

The RPD files contain metadata — data about data. In order to work with data within BICS we need to first define an RPD file or upload it from an existing on-premise RPD file.

So, let’s discuss how to migrate an existing RPD file to BICS. An existing RPD file can be migrated by using the Lift and Shift migration method. We are taking a little liberty here in by using the catchphrase “Lift and Shift.” This was originally considered to be a brute-force method of moving an entire non-cloud-based application to the cloud first, and implementing changes to make it compatible with the cloud later.

Assumptions

1. An RPD file has already been created in OBIEE Administration Tool and this passes the consistency check
2. THE RPD contains only the subject areas, underlying data sources that are required in BICS
3. Access is available to BICS service console
4. Access is available to a DBaaS instance (Read Caveat No. 2 below)

Steps to upload an on premise RPD onto Oracle BICS

Pre-requisites:

• Have access to Oracle BI Cloud Service and associated Oracle Database Schema Service
• Have SQL Developer installed on your machine and configured to work with a cloud database

– For this you will need to create a new connection have the cloud database Schema Name, URL, port number and Service Name

• Have access to the Oracle BI Administration tool
• Oracle Database is installed and connected through the connection pool created in the Oracle BI repository file (RPD)

Follow these steps to “lift and shift” an on-premise RPD to Oracle BICS:

Step 1: Replicate on-premise data to the DBaaS instance

1.  Create a connection to the on premise database and connect to it

2.  Create a cart where you will add the selected objects that you wish to load into the DBaaS database. Click View and select Cart

3.  Expand your schema and select all the tables you wish to import and right click and choose Add to Cart

4. Check the Data checkbox right next to DDL in order to retrieve the data of the rows in the tables as well as their definitions

5.  Click the Export Cart icon

6.  Make sure to select Dependents, Grants for CREATE and GRANT statements if indexes exist on the table, and Drops for any DROP TABLE statements

7.  Click Apply

8.  Create a connection to the cloud DBaaS and connect to it

9.  Open the export.sql file in SQL developer

10.  Click the Run Script icon and select the Cloud DB connection

11.  Make sure to add a commit statement to commit those insert statements into the Cloud schema.

Step 2: Update on premise RPD to connect to DBaaS instance:

1.  Obtain the IP address, port number and service name of the DBaaS instance from the “My Services” page of the Oracle Database Cloud Service

2.  Open the RPD with the admin tool

3.  Expand each of the physical connection pools. Update the connection pools with the information obtained in a) above.

4.  Save the RPD file

Step 3: Backup the Cloud current system

Save the current system snapshot in BICS by using the “New Snapshot” option in the BICS Service Console (Optional). Please note that though this is marked as optional it is highly recommended as you are backing up the current system state in a snapshot. If things go wrong you always have the option of reverting back to the original state by restoring the snapshot.

1.  Log into your BI Cloud Service and navigate to the console screen

2.  Select Snapshots and Models

3.  Click on New Snapshot and enter the description to remember why and when this snapshot was taken

Step 4: Replace the Data Model

1.  While still in the Snapshot System State screen, click on the Replace Data Model button in BICS service console

2.  Upload the RPD file you had saved previously with Cloud DB connections in the connection pool.

3. Enter the password and click OK on the Replace Data Model dialog. System will display a message confirming that data model was successfully replaced.

Caveats

1. Migration of an RPD file to BICS is unidirectional. The BICS data modeler cannot be used to make changes to the imported RPD.
2. An RPD file imported into BICS is read-only. Any changes to the RPD file can be done only in OBIEE. Once the changes are made the RPD file can be migrated to BICS again.
3. The BICS subscription is bundled with a single-schema service. This is not compatible with the RPD files created in Admin Tool of OBIEE. In order to upload the RPD file created in admin tool of OBIEE, a subscription to a multi-schema DBaaS instance is required.

We hope that this post will help you get started with BICS and leverage the existing data models already created in OBIEE.

To learn more, please contact Datavail today. Datavail is the largest provider of data and database administration (DBA) services in North America with more than 600 DBAs, analysts, developers and consultants. We offer 24×7 managed services and project consulting in database administration, BI/Analytics, data warehousing, and enterprise performance management. Datavail’s expert consulting team specializes in integrations, implementations, and upgrades for applications across the Oracle stack, including Hyperion, OBIEE, OBIA, GoldenGate, Weblogic and more.