1. REST API for Oracle Essbase
  2. Tasks
  3. Aggregate Storage Load Buffers

Create Buffer

post

/essbase/rest/v1/applications/{applicationName}/databases/{databaseName}/asodataload/buffers

Creates an aggregate storage data load buffer with the specified options.

In REST API, the jobType to load data without use of buffers is dataload. The jobTypes you need to load data incrementally to aggregate storage cubes (using buffers) are asoBufferDataLoad and asoBufferCommit.

The workflow in REST API to load data to aggregate storage cubes using buffers is:

  1. Create (initialize) the buffer(s)
  2. (Optional) List existing buffers (List Buffers)
  3. Run jobType asoBufferDataLoad to load data into the buffer(s) (Execute Job)
  4. Run jobType asoBufferCommit to commit data from the buffer(s) to the aggregate storage cube (Execute Job)
  5. (Optional) Destroy initialized load buffer without committing data (Destroy Dataload Buffer)

Request

Supported Media Types
Path Parameters
Body ()
Root Schema : DataLoadBuffer
Type: object
Show Source

  • Unique ID of a single aggregate storage data load buffer. Must be a number between 1 and 4294967296.

  • Allowed Values: [ "ADD", "ASSUME_EQUAL", "USE_LAST" ]

    Select an option to resolve cell conflicts for duplicate cells in the aggregate storage data load buffer.

    • ADD: (Default) Add values when the buffer contains multiple values for the same cell.
    • ASSUME_EQUAL: Treat duplicate values as equal.
    • USE_LAST: Combine duplicate cells by using the value of the cell that was loaded last into the data load buffer.
  • Allowed Values: [ "IGNORE_NONE", "IGNORE_MISSING_VALUES", "IGNORE_ZERO_VALUES", "IGNORE_MISSING_AND_ZERO_VALUES" ]

    Select an option to determine how missing and zero values in the aggregate storage data load buffer should be handled.

    • IGNORE_NONE(0): Do not ignore any values in the incoming data stream
    • IGNORE_MISSING_VALUES(1): Ignore #MI values in the incoming data stream
    • IGNORE_ZERO_VALUES(2): Ignore zero values in the incoming data stream
    • IGNORE_MISSING_AND_ZERO_VALUES(3): Ignore #MI and zero values in the incoming data stream

  • Percentage of the total load buffer resources that the load buffer will be allowed to use; must be within [0, 100], and the value of 0 is interpreted as default, which is currently 100.

Back to Top

Response

Supported Media Types

200 Response

OK

Load buffer created successfully.

400 Response

Bad Request

Failed to create load buffer.

500 Response

Internal Server Error.

Back to Top

Examples

Aggregate storage cubes facilitate analysis of very large dimensions containing up to a million or more members. To support efficient, incremental loading of data values into such large cubes, Essbase:

  • Allows the processing of multiple data sources through temporary aggregate storage data load buffers

  • Allows you to control the percentage of resources a data load buffer uses

  • Allows an aggregate storage database to contain multiple slices of data (a query to the database accesses each slice, collecting all of the data cells)

  • Completes the incremental load process in a length of time proportional to the size of the data

To support loading data incrementally to large cubes, multiple data load buffers can be initialized on an aggregate storage cube. This REST API returns the following information about each existing data load buffer:

  • bufferId -- ID of a data load buffer (a number between 1 and 4294967296).

  • duplicateAggregationMethod -- One of the methods used to handle when multiple values for the same cell are being loaded from the data stream in the buffer:

    • ADD -- Add values when the buffer contains multiple values for the same cell.
    • ASSUME_EQUAL -- Stop loading if there are duplicates with unequal values.
    • USE_LAST -- Combine duplicate cells by using the value of the cell that was loaded last into the load buffer. This is an optimal choice for loading text and date values, to help eliminate invalid aggregations.

  • loadBufferOptions -- How missing and zero values should be handled during the load.

    • IGNORE_NONE -- Ignore neither #MISSING nor zero values in the incoming data stream. To select this option you can pass either this keyword or 0.
    • IGNORE_MISSING_VALUES -- Ignore #MISSING values in the incoming data stream. To select this option you can pass either this keyword or 1.
    • IGNORE_ZERO_VALUES -- Ignore zeros in the incoming data stream. To select this option you can pass either this keyword or 2.
    • IGNORE_MISSING_AND_ZERO_VALUES -- Ignore zero and #MISSING values in the incoming data stream. To select this option you can pass either this keyword or 3.

  • resourceUsage -- Percentage of the total load buffer resources that the load buffer is allowed to use. Must be within [0, 100]. Default is 100, and 0 is interpreted as default.

Copy/Paste-able Script with cURL Command to Initialize a Load Buffer

The following example shows how to initialize an aggregate storage data load buffer. In this buffer, Essbase accumulates and sorts data values from multiple sources.

This example uses cURL to call the REST API from a Windows shell script.

The calling user's ID and password are variables whose values are set in properties.bat. 

call properties.bat
curl -X POST "https://192.0.2.1:443/essbase/rest/v1/applications/ASOSamp/databases/Basic/asodataload/buffers" -H  "accept: application/json" -H  "Content-Type: application/json" -d "{  \"bufferId\": 100,  \"duplicateAggregationMethod\": \"ADD\",  \"loadBufferOptions\": \"IGNORE_NONE\",  \"resourceUsage\": .25} -u %User%:%Password%"

Workflow Example for Two Buffers

The following example shows the sequence of request payloads required to load data incrementally using two aggregate storage data load buffers.

Initialize Load Buffer(s)

Create load buffers.

Sample JSON Payloads:

{
 "bufferId": 100,
 "duplicateAggregationMethod": "ADD",
 "loadBufferOptions": "IGNORE_NONE",
 "resourceUsage": 50
}
{
 "bufferId": 101,
 "duplicateAggregationMethod": "ADD",
 "loadBufferOptions": "IGNORE_NONE",
 "resourceUsage": 50
}

Sample cURL:

curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/applications/ASOSamp/databases/Basic/asodataload/buffers" -H Accept:application/json -H  "Content-Type: application/json" -d '{"bufferId": 100,  "duplicateAggregationMethod": "ADD", "loadBufferOptions": "IGNORE_NONE", "resourceUsage": 50}'
curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/applications/ASOSamp/databases/Basic/asodataload/buffers" -H Accept:application/json -H  "Content-Type: application/json" -d '{"bufferId": 101,  "duplicateAggregationMethod": "ADD", "loadBufferOptions": "IGNORE_NONE", "resourceUsage": 50}'

Load Data Into Buffer(s)

Load data into initialized buffers using asoBufferDataLoad job type available in Jobs.

Parameter Description

application

Required. Application name.

db

Required. Cube name.

jobtype

Required. Job type (in this case, asoBufferDataLoad)

bufferId

Required. Unique ID of a single aggregate storage data load buffer. Must be a number between 1 and 4294967296.

rule

Required. Rule file to use for loading into buffer.

file

Required, unless you will connect to an external source (using user+password OR useConnection+connection). Data file to load into buffer.

abortOnError

Required. Error handling choice. If true, data load stops on the first error. If false, data load continues despite errors.

user

Optional. Use if the rule file is configured to load data using connectivity to ODBC, OCI, or DSN, without using the useConnection option. Name of a user authorized to access the external database.

password

Optional, unless you entered a user name for connectivity to an external source. Password of user authorized to access the external database.

useConnection

Optional. true if the data load should use a saved connection that establishes network connectivity between Essbase and an external source of data. false if the data load is directly from a file in the catalog, or if you are providing a username and password to establish connectivity to an external source of data.

connection

Required if useConnection is true. Name of a saved connection that establishes network connectivity between Essbase and an external source of data. Example: conn_name for a global connection (accessible to all applications), or ASOSamp.conn_name for a connection associated only with application ASOSamp.

Sample JSON Payload Using Data File:

{
 "application": "ASOSamp",
 "db": "Basic",
 "jobtype": "asoBufferDataLoad",
 "parameters": {
  "bufferId": 100,
  "rule": "data.rul",
  "file": "Basic.txt",
  "abortOnError": "false"
  }
}

Sample JSON Payload Using Application-level Connection:

{
 "application": "ASOSamp",
 "db": "Basic",
 "jobtype": "asoBufferDataLoad",
 "parameters": {
  "bufferId": 101,
  "rule": "data.rul",
  "useConnection": "true",
  "connection": ASOSamp.conn_name,
  "abortOnError":"false"
  }
}

Sample cURL:

curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/jobs" -H "accept: application/json" -H "Content-Type: application/json" -d '{"application":"ASOsamp_Data", "db":"Sample", "jobtype":"asoBufferDataLoad", "parameters":{"bufferId": 101, "rule": "data.rul", "useConnection": true, "connection": "ASOsamp_Data.app_conn", "abortOnError":"true"}}'

Commit Buffer(s)

Move data from buffers to the aggregate storage cube, using asoBufferCommit job type available in Jobs.

Parameter Description

application

Application name.

db

Cube name.

jobtype

Job type (in this case, asoBufferCommit)

bufferId OR bufferIds

bufferId: Unique ID of a single aggregate storage data load buffer. Must be a number between 1 and 4294967296.

OR

bufferIds: Comma-separated list of more than one bufferId

commitOption

Load buffer commit options to use when committing the contents of the data load buffer to the cube.

Options:

  • STORE_DATA (or 0) -- Store data in the load buffer

  • ADD_DATA (or 1) -- Add values in the load buffer to existing stored data values

  • SUBTRACT_DATA (or 2) -- Subtract values in the load buffer from existing stored data values

  • OVERRIDE_ALL_DATA (or 3) -- Store incoming data instead of the existing stored data values.

  • OVERRIDE_INCREMENTAL_DATA (or 4) -- Reset incremental data (stored in incremental slices). In other words, remove the current contents of all incremental data slices in the cube and create a new data slice with the contents of the specified data load buffer. The new data is created with the data load property add values (aggregate_sum). If there are duplicate cells between the new data and the primary slice, their values are added together when you query for them.

actionType

What to do if there is an error committing the load buffer.

Options:

  • COMMIT (or 1) -- Commit anyway

  • ABORT (or 2) -- Terminate the operation

termOption

Final options for committing data slices to the cube from the data load buffer.

Options:

  • INCR_TO_MAIN_SLICE (or 0) -- Commit the contents of all incremental data slices as a primary (main) slice.

  • INCR_TO_NEW_SLICE (or 1) -- Replace the contents of all incremental data slices with a new slice.

  • INCR_TO_NEW_SLICE_LIGHTWEIGHT (or 2) -- Write the data currently stored in the buffer to a new slice in the cube, as a lightweight operation. This option is intended only for very small data loads of up to 1,000s of cells that occur concurrently (for example, grid client data-update operations).

Sample JSON Payload:


{
 "application":"ASOSamp",
 "db":"Basic",
 "jobtype":"asoBufferCommit",
 "parameters":{
  "bufferIds":[100,101],
  "commitOption":"ADD_DATA",
  "actionType":"COMMIT",
  "termOption":"INCR_TO_NEW_SLICE"
  }
}

Sample cURL:

curl -X POST "https://myserver.example.com:9001/essbase/rest/v1/jobs" -H "accept: application/json" -H "Content-Type: application/json" -d '{"application":"ASOsamp_Data", "db":"Sample", "jobtype":"asoBufferCommit", "parameters":{"bufferIds": [100,101], "commitOption": "ADD_DATA", "actionType": "COMMIT", "termOption":"INCR_TO_NEW_SLICE"}}'
Back to Top