Import Data (Aggregate Storage)

Click here for non-aggregate storage version

Use this statement to import data from text files or other sources, with or without a rules file.

Minimum permission required: Write.

Syntax

Description of the illustration imp_data_as.gif

Use import data in the following ways to load data into an aggregate storage database:

Keywords

import database <dbs-name> data from...

Specify whether the data import is from a local or server file, and what type of file to import data from.

...using ... rules_file

Import data into the database using a specified rules file.

...<data error spec> (on error...)

Required. Tell Essbase what to do in case of errors during the data load: abort the operation, or write or append to a specified error log.

...<data record spec> from data_string

Load a single data record into the selected database. The string following data_string must be a contiguous line, without newline characters.

...<SQL connect spec> (connect as...)

If you are importing from an SQL source, you must always use a rule file. Provide the appropriate user name and password:

If the network connectivity to the source data is saved in an Essbase connection and Datasource, provide your Essbase credentials in the MaxL statement. For example:

import database Sample.Basic data connect as "Essbaseadmin" identified by "Essbasepa55w0RD" using server rules_file "myrulefile" on error write to 'loadds.err';

Otherwise, provide the user name and password required to connect to the external RDBMS source. For example:

import database Sample.Basic data connect as "RDBMSuser" identified by "RDBMSpa55w0RD" using server rules_file "myrulefile" on error write to 'loadds.err';

When loading SQL data into aggregate storage databases, you can use up to eight rules files to load data in parallel by using the multiple rules_file grammar with the grammar specified in <buffer-block-spec>. Essbase initializes multiple temporary aggregate storage data load buffers (one for each rules file) and, when the data is fully loaded into the buffers, commits the contents of all buffers into the database in one operation.

Each rules file must use the same authentication information (SQL user name and password).

In the following example, SQL data is loaded from two rules files (rule1.rul and rule2.rul):

import database ASOsamp.Basic data 
   connect as TBC identified by 'password' 
   using multiple rules_file 'rule1','rule2' 
   to load_buffer_block starting with buffer_id 100 
   on error write to "error.txt";

In specifying the list of rules files, use a comma-separated string of rules file names (excluding the .rul extension). The file name for rules files must not exceed eight bytes and the rules files must reside on Essbase Server.

In initializing a data load buffer for each rules file, Essbase uses the starting data load buffer ID you specify for the first rules file in the list (for example, ID 100 for rule1) and increments the ID number by one for each subsequent data load buffer (for example, ID 101 for rule2).

The ODBC driver you are using must be configured for parallel SQL connections.

Note:

Performing multiple SQL data loads in parallel to aggregate storage databases is different than using the to load_buffer with buffer_id grammar to load data into a buffer, and then using the from load_buffer with buffer_id grammar to explicitly commit the buffer contents to the database.

...to load_buffer with buffer_id

If you are importing data from multiple data files to an aggregate storage database, you can import to a buffer first, in order to make the data import operation more efficient.

...from load_buffer with buffer_id

If you are importing data from multiple data files to an aggregate storage database, you can import from a data load buffer in order to make the data import operation more efficient.

...from load_buffer with buffer_id...values

Specify whether you want to add to existing values, substract from existing values, or override existing values when committing the contents of the specified data load buffer to the database.

...from load_buffer with buffer_id...create slice

Commit the contents of the specified data load buffer to the database by creating a new data slice.

...from load_buffer with buffer_id override all data

Remove the current contents of the database and replace the database with the contents of the specified data load buffer.

...from load_buffer with buffer_id override incremental data

Remove the current contents of all incremental data slices in the database 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.

Notes

This statement requires that the database is started.
When using the import statement, you must specify what should happen in case of an error.

Example

The following example performs a data load using a data file stored in the shared folder of the Essbase file catalog. No rule file is needed.

import database 'ASOSamp'.'Basic' data from server data_file 'catalog/shared/ASO_Sample_Data' on error write to "asodataload.err";

The following example commits the contents of a specified data load buffer to the ASOsamp.Basic database.

import database ASOsamp.Basic data from load_buffer with buffer_id 1;

The following example commits the contents of multiple data load buffers (buffer_id 1 and buffer_id 2) to the ASOsamp.Basic database.

import database ASOsamp.Basic data from load_buffer with buffer_id 1, 2;

The following example commits the contents of a specified data load buffer to the ASOsamp.Basic database by adding values.

import database ASOsamp.Basic data from load_buffer with buffer_id 1 add values;

The following example commits the contents of the specified data load buffer into a new data slice in the ASOsamp.Basic database.

import database ASOsamp.Basic data from load_buffer with buffer_id 1 override values create slice;

The following example replaces the contents of the ASOsamp.Basic database with the contents of the specified data load buffer.

import database ASOsamp.Basic data from load_buffer with buffer_id 1 override all data;

The following example replaces the contents of all incremental data slices in the ASOsamp.Basic database by creating 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.

import database ASOsamp.Basic data from load_buffer with buffer_id 1 override incremental data;

See Loading Data Using Buffers.