Migrating On-Premises Data to Oracle BI Cloud Service

Overview

Purpose

In this tutorial, you learn how to migrate on-premises SampleApp aggregated data to Oracle BI Cloud Service.

Time to Complete

Approximately 1 hour

Introduction

If you have an on-premises Oracle Business Intelligence Enterprise Edition (Oracle BI EE) deployment, and you want to migrate to Oracle Business Intelligence Cloud Service (Oracle BI Cloud Service), you can leverage the logical modeling that you performed on your on-premises data. To do this, you can use the Aggregate Persistence Wizard to create data that resembles a logical star schema and then upload the data to Oracle Database Cloud Service using SQL Developer.

In this tutorial, you migrate on-premises SampleApp data to Oracle Cloud by:

  1. Creating aggregates on the source database using the Aggregate Persistence Wizard
  2. Uploading the aggregate tables to Oracle Cloud using Oracle SQL Developer
  3. Building a data model in Oracle BI Cloud Service
  4. Validating the data in Oracle BI Cloud Service

Prerequisites

Before starting this tutorial, you should:

  • Have access to Oracle BI Cloud Service and associated Oracle Database Schema Service.
  • Have SQL Developer installed on a client machine and configured to work with a cloud database.
  • Have access to the Oracle BI Administration Tool. For this tutorial, we used a full installation type of Oracle BI EE version 11.1.1.7.x on a single Windows computer. Oracle Database is installed and connected through the connection pool created in the Oracle BI repository file (RPD).
  • Have the on-premises SampleApp.rpd file configured as the default RPD file.

Creating Aggregates on the Source Database Using the Aggregate Persistence Wizard

In this topic, you create aggregates in the on-premises source database using the Aggregate Persistence Wizard.

Creating the Aggregates

  1. In the Administration Tool, open SampleApp.rpd in online mode.

    alt description here
  2. In drive C, create a folder named bics. You'll use this folder in a later step to store the aggregate specification.

  3. Select Tools > Utilities > Aggregate Persistence, and then click Execute.

    alt description here
  4. On the Select File Location page, specify the complete path and file name where you'll store the aggregate specification (a SQL script).

    1. In the Name field, enter bics.sql.
    2. In the Location field, enter C:\bics.
    3. alt description here
  5. Click Next.

  6. On the Select Business Measures page, select the measures on which you want to aggregate.

    1. For the business model, select 01 - Sample App.
    2. For the measures, select 1 - Revenue, 2 - Billed Quantity, 3 - Discount Amount, and 4 - Paid Amount.
    3. alt description here
  7. Click Next.

  8. On the Select Levels page, specify the level of aggregation to be used for the fact-dimension join.

    1. Click in the Logical Level column for H0Time and select Quarter from the list.
    2. Click in the Logical Level column for H1Products and select Products LOB from the list.
    3. alt description here
  9. Click Next.

  10. On the Select Connection Pool page, specify a location for the aggregate table.

    1. In the Database area, select the database object that you created for your aggregate target database; for example, Orcl.
    2. In the Catalog/Schema area, select the catalog or schema object for the aggregate table; for example, Orcl.
    3. In the Connection Pool area, select the connection pool.
    4. In the Aggregate Table Name area, enter bics.
      alt description here
  11. Click Next.

  12. On the Finish page, click View Script to display the logical SQL script for your review.

    alt description here
  13. Select I am done and then click Next.

    The complete path and file name are displayed on the Finish Script page.

    alt description here
  14. Click Finish.

Here is an example of the generated SQL file:


   create aggregates 
                 
   "bics"
    for "01 - Sample App"."F0 Sales Base Measures"("1- Revenue","2- Billed Quantity","3- Discount Amount","4- Paid Amount")
    at levels ("01 - Sample App"."H0 Time"."Quarter", "01 - Sample App"."H1 Products"."Products LOB")
    using connection pool "Orcl"."Connection Pool"
    in "Orcl";
                

Running the Aggregate Specification Against the Oracle BI Server

  1. In Windows Explorer, go to ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup.

  2. Double-click bi-init.cmd to display a command prompt that is initialized to the Oracle instance.

    alt description here
  3. Use the following syntax to run the nqcmd utility:

    nqcmd -d my_dsn -u my_username -p my_password -s sql_input_file -o my_result_file

    Where:

    • my_dsn is the ODBC data source name for the Oracle BI Server to which you want to connect. Look for the data source name at the top of the Administration Tool when you open SampleApp.rpd in online mode.
    • my_username is the user name for Oracle BI EE.
    • my_password is the password for the Oracle BI EE user.
    • sql_input_file is the full path to and the name of the aggregate script.
    • my_result_file is the full path to and the name of a file to which the utility writes the aggregate script results.

    For example, nqcmd -d coreapplication_OH255337996 -u Administrator -p Admin123 -s "C:\bics\bics.sql".

    The following figure shows an example of the output:

    alt description here
  4. Open SampleApp.rpd in online mode and check the RPD file for the aggregate metadata in the target database.

    alt description here
  5. In the Administration Tool, verify that the data was returned without errors.

    1. Right-click the bics table and select View Data.
    2. alt description here

      The View Data from Table dialog box is displayed.

      alt description here
    3. If there are any errors, debug the issues using the logs.
    4. Repeat steps a and b for the SA_Product00002E9E table.
    5. Repeat steps a and b for the SA_Quarter00002E85 table.

Uploading the Aggregate Tables to Oracle Cloud Using SQL Developer

In this topic, you add a cloud connection and a connection to the source database, and then upload the aggregate tables.

Adding a Cloud Connection from SQL Developer to Oracle Database Cloud Service

In this topic, you configure a cloud connection to connect SQL Developer to Oracle Database Cloud Service.

  1. Run SQL Developer locally.

    The SQL Developer Home page is displayed.

    alt description here
  2. Under Connections, right-click Cloud Connections.

    The Cloud Connection context menu is displayed.

    alt description here
  3. Select New Cloud Connection.

    The New Cloud Connection dialog box is displayed.

    alt description here
  4. Sign in to My Services in Oracle Cloud.

    The My Services page is displayed.

    alt description here
  5. Click Platform Services.

    The Platform Services page is displayed.

    alt description here
  6. Click the name of the Oracle Database Cloud Service.

    The Service Detail page is displayed.

    alt description here
  7. Click the service SFTP user name.

    alt description here

    The SFTP Users tab is displayed.

    alt description here
  8. Reset the password.

    1. Click the action icon (link) for the service SFTP user name that you located on the Service Detail page, and then select Reset Password.
    2. alt description here
    3. In the Reset Password dialog box, enter a new password, reenter the password, and click Save.
    4. alt description here
    5. In the Reset Password confirmation dialog box, click Save to confirm the change.
    6. alt description here
  9. In the New Cloud Connection dialog box in SQL Developer, enter the following information:

    • Connection Name: Enter the name for this cloud connection; for example, MyCloudConnection.
    • Database

    • Username: Enter the user name required during sign-on when accessing Oracle Database Cloud Service.
    • URL: Enter the service instance URL from the Service Detail page.
    • SFTP

    • Username: Enter the service SFTP user name from the Service Detail page.
    • Password: Enter the service SFTP password that you reset.
    • Hostname: Enter the service SFTP host name from the Service Detail page.
    • Port: Enter 22.
  10. Click OK.

  11. Under Cloud Connections in the Connections pane, open the new cloud connection.

    alt description here

    If you connected successfully, the tables and other objects from Oracle Database Cloud Service are displayed.

Adding a Connection to the Source Database

  1. Under Connections in SQL Developer, right-click Connections and select New Connection.

    The New / Select Database Connection dialog box is displayed.

    alt description here
  2. Enter or select the appropriate settings for connecting to the source database.

  3. Click Save and close the dialog box.

    The connection is displayed in the Connections pane.

    alt description here

Uploading the Aggregate Tables

  1. In SQL Developer, click View and select Cart.

    The Cart window is displayed.

    alt description here
  2. In the left pane, drag the Oracle Database objects generated by the Aggregate Persistence Wizard to the Cart window. For example, drag and drop the following objects:

    • SA_Product00002E9E
    • SA_Quarter00002E85
    • BICS
    alt description here
  3. (Optional) To include data with the cart deployment, click the Data check box in the Cart window.

    alt description here
  4. Click Deploy Cloud.

    alt description here

    The Deploy Objects to Cloud dialog box is displayed.

    alt description here
  5. Complete the following fields:

    • Title: Enter a 15-character title for this deployment. Allowed alphanumeric characters are a-z, A-Z, 0-9, underscore (_), and dash (-). Special characters, such as spaces, aren't allowed.
    • File: Enter a name for the file that you want to deploy. This zip file is generated by SQL Developer and contains all objects and optional data included in the cart.
  6. Click Apply.

    The Exporting dialog box is displayed.

    alt description here
  7. Verify that the deployment was successful.

    1. Click Deployments under the cloud connection.
    2. The list of deployments is displayed.

      alt description here
    3. Click the deployment.
    4. alt description here

      The Details tab is displayed.

      alt description here
    5. Make sure that the value for the status detail is Processed.
  8. Click Refresh.

    alt description here

    You can now view the deployed objects.

    alt description here

Building the Data Model in Oracle BI Cloud Service

In this topic, you build the data model in Oracle BI Cloud Service.

  1. Sign in to Oracle BI Cloud Service.

    alt description here
  2. On the Home page, click Model.

    alt description here

    The Data Modeler is displayed.

  3. Click Lock to Edit to lock the data model.

    alt description here
  4. Click the Model Actions icon (link), and then select Delete Model to delete any previously existing models.

    alt description here
  5. Drag the following tables from the Database pane:

    • Drag SA_Product00002E9E to the Dimension Tables area.
    • Drag SA_Quarter00002E85 to the Dimension Tables area.
    • Drag BICS to the Fact Tables area.
    alt description here
  6. Rename the Dimension tables.

    1. Right-click SA_Product00002E9E and select Inspect.
    2. alt description here

      The Overview tab is displayed.

      alt description here
    3. In the Name field, change SA_Product00002E9E to SA_PRODUCT.
    4. Click Done.
    5. Right-click SA_Quarter00002E85 and select Inspect.
    6. In the Name field, change SA_Quarter00002E85 to SA_QUARTER.
    7. Click Done.
  7. Rename the columns in the fact and dimension tables.

    1. Click the fact or dimension table to display the Overview tab.
    2. Click Edit All.
    3. Rename each column according to the following figure, and then click Done.
    4. alt description here
  8. Create the BICS.P3_LOB = SA_PRODUCT.P13_LOB join.

    1. Click Create Join to add a join row to the Joins area.
    2. alt description here
    3. In the new join row, select the following :
      • Fact Table: BICS
      • Fact Column: P3_LOB
      • Dimension Table: SA_PRODUCT
      • Dimension Column: P13_LOB
    4. Click the checkmark icon to save the join.
    5. Your join should look like the following figure:

      alt description here
  9. Create the BICS.T03_PER_NA = SA_QUARTER.T03_PER_NA join.
    1. Click Create Join.
    2. In the new join row, select the following:
      • Fact Table: BICS
      • Fact Column: T03_PER_NA
      • Dimension Table: SA_QUARTER
      • Dimension Column: T03_PER_NA
    3. Click the checkmark icon to save the join.

      Your join should look like the following figure:

      alt description here
  10. Click Publish Model and select Publish and Unlock to publish the model.

    alt description here

Validating the Data in Oracle BI Cloud Service

In this section, you validate the data in Oracle BI Cloud Service.

  1. Sign in to Oracle BI Cloud Service.

    alt description here
  2. On the Home page, click Create an Analysis.

    alt description here
  3. On the Criteria tab, drag the following columns from the Subject Areas pane to the Selected Columns pane:

    • BICS folder: REVENUE
    • SA_PRODUCT folder: PRODUCT
    • SA_QUARTER folder: QUARTER
    alt description here
  4. Click the Results tab and verify that a view of the data is displayed in the Compound Layout.

    alt description here

Summary

In this tutorial, you learned to:

  • Create aggregates on the source database using the Aggregate Persistence Wizard
  • Upload the aggregate tables to Oracle Cloud using Oracle SQL Developer
  • Build a data model in Oracle BI Cloud Service
  • Validate the data in Oracle BI Cloud Service

Resources

To navigate this Oracle by Example tutorial, note the following:

Topic List:
Click a topic to navigate to that section.
Expand All Topics:
Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:
Click the button to show or hide the screenshots. By default, all images are displayed.
Print:
Click the button to print the content. The content that is currently displayed or hidden is printed.

To navigate to a particular section in this tutorial, select the topic from the list.