Oracle by Example brandingLoad Data Using Rules

section 0Before You Begin

This 10-minute tutorial shows you how to load data using a rule.

Background

You can use rules to extract, transform, and load data values to an Essbase database. The data source values can contain data values, member names and formulas, generation and level names, data storage properties, and attributes.

After you define a rule with the relevant options and preview the results, you run a job to load data using the rule.

What Do You Need?

  • Access to Oracle Analytics Cloud as a Power User or Administrator.
  • Access to the same Sample5 application and Basic database that you previously used.
  • The following data file that you previously downloaded to your computer:

section 1Create the Rule File

Create the rule file to load the data into the Essbase database.

  1. Open the downloaded data_basic_data1.txt file in a formatted text editor. Notice that there is no header row and that the file delimiter is a comma.
  2. Sign into the Oracle Analytics Cloud – Essbase web interface.
  3. On the Applications home page, expand the Sample5 application, and select the Basic database.
  4. In the row for the Basic database, click Actions Actions, and click Inspect.
  5. Select Scripts tab, and then Rules, to see the defined rules in the Rules editor.
    Create rule file
    Description of the illustration create-rule-file.png
  6. Click Create, and select Data Load to begin defining the load data rule.
  7. In the New Rule dialog box, enter Data_basic1 as the name of the rule file.
  8. Enter Measures as the data dimension.
  9. For Preview Data, select File as the flat file input option.
  10. Click the browse icon to the right of File Select File (optional).
  11. In the Preview Data dialog box, rather than using File Browser, select Catalog, select the file you previously uploaded, data_basic_data1.txt, and click Select.
  12. Deselect the Header Row check box, because the first row of the flat file doesn't contain header values.
  13. In the Delimiter drop-down list, select Comma, or verify that it's already selected.
  14. Click Proceed to view the data in the Rules editor, based on the input flat file.

section 2Select Rule Options and View in Preview

On the Rules editor page, you can now set up and edit the rule.

Because the input file has no headers, you must map each column to the appropriate dimensions and members. The order of the entered fields during your mapping must match the order of the data in the rows of the load data file.

All dimensions must be represented in the load data rule before any data can be loaded. If Essbase encounters a data value before all dimensions are specified, an error message is displayed.

  1. On the Rules editor page, to map each rule field, click the Select drop-down boxes, and select the field (column) names as follows:
    • Field 1, Product
    • Field 2, Market
    • Field 3, Year
    • Field 4, Scenario
    • Field 5, Sales, and set Storage Type to Sum
    • Field 6, COGS, and set Storage Type to Sum
    • Field 7, Marketing, and set Storage Type to Sum
    • Field 8 Payroll, and set Storage Type to Sum
    • Field 9, Opening Inventory, and set Storage Type to Sum
    • Field 10, Misc, and set Storage Type to Sum
    • Field 11, Additions, and set Storage Type to Sum
  2. Click Verify in the Global options toolbar to validate the rule syntax, and click Close.
  3. Click Save and Close.
  4. On the Scripts tab, click Refresh, and verify that your created rule Data_basic1 appears in the Rules list.
  5. Click Close, and return to the Applications home page.

section 3Run the Load Data Job

Next, create a job to load the data using the rule file.

  1. On the home page, select Jobs Jobs.
  2. In the New Job drop-down menu, select Load Data.
  3. In the Load Data dialog box, from the Application menu, select Sample5.
  4. In the Database list, select the Basic database. It may take a few moments to load.
  5. In the Script list, select the load data rule file, Data_basic1.
  6. For Load Type, select File.
  7. Select data_basic_data1 file from the Data File list.
  8. Select the Abort on error check box to cause the load to stop if an error occurs.
  9. Click OK.
  10. On the Jobs page, click Refresh refresh to monitor the job status.
  11. When the job ends, click the Actions Actions menu for the executed job.
  12. Select Job Details to view the load data job details. Under Output, notice the values for Result, Records Processed, and Records Rejected.
  13. Click Close.

section 4View the Load Data Results

View the loaded data.

  1. On the Applications home page, expand the Sample5 application, and select the Basic database.
  2. On the row for the Basic database, click Actions Actions, and click Inspect.
  3. Select Statistics tab. On the General page, under Storage, notice that the value for Number of Existing Blocks is now over 300, indicating that the Basic database now contains data. Click Close.
  4. On the Applications home page, expand the Sample5 application, and select the Basic database.
  5. On the row for the Basic database, click Actions Actions.
  6. Select Export to Excel, select the Export Data check box and click OK.
  7. Click OK to view the exported file in Excel.
  8. On the opened Basic Excel worksheet, view the dimension structure in the various worksheet tabs. Scroll to the right to the Data.Basic tab and view the loaded data.
  9. Close Excel and Essbase.

more informationWant to Learn More?