Loading Data with Oracle BI Cloud Service Data Loader


Options



Before You Begin

Purpose

In this tutorial, you learn how to load data in Oracle BI Cloud Service by using Data Loader.

Time to Complete

Approximately 15 minutes

Background

Oracle delivers a broad selection of enterprise-grade cloud solutions, including software as a service (SaaS), platform as a service (PaaS), and infrastructure as a service (IaaS). Oracle BI Cloud Service, a part of PaaS, offers a highly scalable, multi-tenant Oracle BI environment that is integrated with Oracle Cloud store. Oracle BI Cloud Service features Presentation Services to create analyses and dashboards, Oracle Database Cloud Service integration, self-service web-client Data Loader and Data Modeler, simple administration, and integrated Identity Management.

A typical Oracle BI Cloud Service workflow consists of three steps as shown in the following figure. In the first step, you load your data files to a database schema available for use with Oracle BI Cloud Service by using Data Loader. During the second step, you use Data Modeler to organize and secure the uploaded data by building a business data model, and then you publish the model to Oracle BI Cloud Service as a subject area. In the third step, you create analyses and dashboards with Presentation Services using the subject area that is based on the published model.

BICS Flow

Scenario

In this tutorial, you use Data Loader to load data from a denormalized flat file that contains data to build dimension and fact tables for creating the SampleApp model.

What Do You Need?

Before starting this tutorial, you should:

  • Have access to Oracle BI Cloud Service.
  • Have the data source file that contains denormalized data for the Oracle BI Cloud Sample App downloaded from here to your computer.

Launching Data Loader

You launch Data Loader from your Oracle BI Cloud Service Home page.

  1. In the browser, enter a URL to your Oracle Cloud Home page and sign in to Oracle Cloud with your credentials.

    Oracle Cloud Sign In

    The Home page for the Oracle BI Cloud Service is displayed.

    Oracle BI Cloud Service Home Page
  2. In the shortcuts area on the left-hand side, click Load to launch Data Loader.

    The Select Data page of Data Loader wizard is displayed in a separate browser window or tab.

    Data Loader wizard

Loading Data

You can load data from delimited text files, Excel files, or zipped versions of the files. The files can use the following characters as delimiters: comma, tab, or pipe. These files can be personally created, can originate from departmental sources, or can be extracts from an enterprise or Oracle Cloud system. You can also paste data from the clipboard or upload data from a file. You can load up to 50 columns for CSV and TXT files, and up to 49 columns for XLS and XLSX files. You can load up to 750,000 rows. To load larger volumes of data, use SQL Developer. In this topic, you load data from a zipped version of a comma delimited text file.

  1. Download the data file that contains denormalized data for the Oracle BI Cloud Sample App from here to your computer.

  2. In the Data Loader wizard, click Upload, and specify the SampleAppDenorm.zip file for upload.

    Data Loader uploads the file and displays the progress of the upload.

    Upload progress
  3. Review the uploaded data and leave the optional settings at their default values. The following optional settings are available:
    • Ignore the first number lines - Enter the number of the first rows to ignore during the data load (for example, you might want to ignore rows that are related to the title of the file). In this tutorial, you do not ignore any rows.
    • The first line contains header names - Select this option to indicate that the first row of data contains the column header names. The first row of data is the one after the number of rows that you specified to ignore. This line is displayed above the data. In this tutorial, the first line contains header names.
    • Delimiter - Select the type of delimiter (Comma, Pipe, or Tab) that is used to separate the data. In this tutorial, the delimiter is Comma.

    Select Data Page
  4. Click Next.

    The Select Destination page is displayed. You can map your data to an existing table in the database or you can create a new table to store the data.

    Select Destination Page
  5. For Data Destination, select New Table to create a new database table to store the data.

    Data Loader analyzes the data and suggests data types, lengths, and column names for the new table. You can specify the new table name and table column mappings.

    Table data
  6. For the New Table name, replace the generated new table name that is prefixed by "ADL_" with SAMPLE_APP_DENORM.

    New Table Name
  7. Click Next.

    The Review page is displayed showing a preview of the rows to be inserted or updated on the destination table.

    Review Page
  8. Review the data, and click Next.

    The Summary page is displayed showing the results of the data load.

    Summary Page
  9. Click OK.

    The Home page displays a record that indicates the results of the data load.
    Note: If errors were encountered during the data upload, then the number in the Not Loaded Records column is greater than zero, and the cell is highlighted in red. You can display a report of the errors that occurred and correct those errors.

    Home page

Analyzing Data Load Information

After you load data for use with Oracle BI Cloud Service, you can use the Load Details page of Data Loader to view information about data load operations. You can run reports to see rows that were inserted or updated successfully, as well as rows that were not loaded due to errors. You can also save the reports to a CSV file.

Click a link in the list of reports to display details about previous data load operations. For example, click on the number of inserted records to see a report of the rows that were added.

Inserted Rows Report

Want to Learn More?