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

By | In Oracle, Oracle Applications | January 12th, 2017

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.

 

Sherry brings 8 years of technology experience developing and implementing Business Intelligence solutions with Oracle technology, delivering analytical reports, dashboards, custom metadata models, and KPI metrics for international organizations. She has excellent communication and organization skills and coordinates the BI teams she leads to success in even the most complex of client engagements. Having worked for years internationally with data and analytics for multinational corporations, she brings deep industry domain experience and account management skills to the Datavail BI & Analytics team. Sherry is a key contributor as a course creator and course instructor.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

6 thoughts on “Art of BI: How to Upload OBIEE RPD to Oracle BICS”
    1. Question on this part “The BICS subscription is bundled with a single-schema service. This is not compatible with the RPD files created in Admin Tool of OBIEE.”. If not created with the Admin Tool, how would the RPD be created/modified?

      1. Hi Andrew,
        Thank you for your question.
        What is meant by this part is initially BICS did not support uploading an on-premise RPD and only supported a single-schema where you would have to build the data model using the BICS data modeler. Now in order to upload an RPD (created by the Admin tool of course) you need to have a subscription to a multi-schema DBaaS instance.

    2. Thanks for the post. Really helpful.
      can we upload any version of OBIEE RPD to BICS ? if No what is the valid version of RPD and in which version of Admin tool we have to edit it before deploying to BICS ?

      Thanks in advance.

      1. Hi Sri,
        Thanks for reading!
        I actually got stuck on that point during an implementation and of course shared the information about the error faced when uploading the wrong version of OBIEE.
        The version that does seem to work is 12.2.1.0.0 (Repository version 354), which is the version that comes with 12.2.1 of on-premise OBIEE. However, the later versions 12.2.1.1.0 and 12.2.1.2.0 do not work.
        https://www.datavail.com/blog/art-of-bi-oracle-bi-cloud-service-the-data-model-cannot-be-replaced-error/