1. Database Administrator's Guide for Oracle Essbase
  2. Design Rules for Data Load or Dimension Build
  3. Define Load Rules in Essbase Web Interface
  4. Create New Data Load Rule

Create New Data Load Rule

Create and test a data load rule using the rule editor in the Essbase web interface. Load data into an Essbase cube using data from a flat file or from a variety of external sources. This example uses a flat file.

It is very efficient to modify existing rules, such as those that are created for you when you deploy from application workbooks. However, if you are creating a new rule in the Essbase web interface, this topic illustrates a sample flow for creating it, with the goal of loading data for new dimension members added to an Essbase cube.

Prerequisites:

  • Complete the steps in the topic Create New Dimension Build Rule. This exercise assumes you have already built the Sample Basic cube and have added new "500" members to the Product dimension.

  • Create a comma-delimited data file like the following, name it load_sales_cogs_newprod.txt, and upload it to the Sample Basic cube directory 

    "Product","Market","Year","Scenario","Sales","COGS"
"500-10","New York","Jan","Actual","678","271"
"500-10","New York","Jan","Budget","640","260"
"500-10","New York","Feb","Actual","645","258"
"500-10","New York","Feb","Budget","610","240"
"500-10","New York","Mar","Actual","675","270"
"500-10","New York","Mar","Budget","640","250"
"500-10","New York","Apr","Budget","670","270"
"500-10","New York","May","Actual","756","302"
"500-10","New York","May","Budget","710","280"
"500-10","New York","Jun","Actual","890","356"
"500-10","New York","Jun","Budget","840","340"

  1. On the Applications page, expand the application (Sample).

  2. From the Actions menu, to the right of the cube name (Basic), launch the inspector.


    Popup menu with Inspect selected

  3. Select the Scripts tab, and then click Rules.


    Scripts tab selected in the database inspection dialog

  4. Click Create and choose Data Load.


    In the database inspection dialog, create button is pressed and option Data Load is selected

  5. In the New Rule dialog,


    New Rule dialog for data load rule, filled out as per the instructions

    1. Enter a rule name: 500_data.

    2. For Source Type select File, as this example workflow is based on the assumption of using a flat data file. Select the data file you uploaded. For example, click Catalog and navigate to load_sales_cogs_newprod.txt.

      Note:

      Additional options besides File are available to use as the Source Type. Use these when your source data is an external source rather than a flat file.
      Source types you can select for data load rules include File, ODBC (DSN-less), Oracle Call Interface (OCI), Datasource, and SQL Data Sources (DSN).

      When you are using an external source of data, you must complete certain prerequisites, depending on which type of connectivity you have to the source of data.

      • If your connectivity has been predefined using a connection and Datasource established in Essbase by an application manager or service administrator, choose Datasource as the Source Type for the load rule. To ensure your connectivity is ready, see Access External Data Using a Connection and Datasource to understand the prerequisite steps.
      • If your connectivity depends on ODBC drivers configured on the Essbase server, choose ODBC (DSN-less) or SQL Data Sources (DSN) as the Source Type for the load rule. To ensure your connectivity is ready, see Access Data Using ODBC Connectivity to understand the prerequisite steps.
      • If your connectivity depends on a connection string that includes OCI syntax, choose Oracle Call Interface (OCI) as the Source Type for the load rule. To ensure your connectivity is ready, see Access Oracle Database Using Oracle Call Interface to understand the prerequisite steps.

    3. Increment the Data Load Record Number field to 1, because record 0 contains header information you won't load (though it will be used to populate the load rule fields).

    4. As the sample data file is comma delimited, leave the Delimiter value as Comma.

    5. Click Proceed.

    6. The data load rule opens fields mapped to dimensions that were listed in the header record. Preview data (from the text file) populates the grid below the fields.


      New data load rule with fields populated as dimensions from the header record of the text file, and preview data from the text file.

      Each row in the source data is a record, and corresponds with the rows you see in the rule preview data. Fields 1-4 contain metadata (member) information, and Fields 5-6 contain data to load.

  6. Verify the rule, then save and close.

  7. Close the cube inspector, and click Jobs to test your data load rule.

  8. Click New Job and Load Data.

    • For Application, select Sample.
    • For Database, select Basic.
    • For Load Type, specify File.
    • Click Abort on Error.
    • Click Select files from catalog
      Load Data dialog filled out with the options indicated in text instructions

    • Locate the rule file, 500_data.rul. Click it, hold the Shift key, and then find and click the data file name (load_sales_cogs_newprod.txt). Click Select.
    • Click OK.

  9. Click the Refresh icon until the job status is Completed. You have loaded data into the new product.