LOADDATA

The LOADDATA command initiates a data load operation on Integration Server. The load environment must be set up properly before you invoke this command. To set up the load environment, issue the SET commands in any order. See SET and LOAD Commands.

LOADDATA takes a few seconds to execute. After executing LOADDATA, use the STATUS command to learn the status of the command execution.

Syntax

LOADDATA "OTL=Metaoutline;APP=Essbase Application;
DBN=Essbase Database;[FLT_ID_DATA=Data Load Filter ID;]
[REPLACE_ZEROS=Replace Zeros with #MISSING;]
[INCUPD=DimID-MbrID,DimID-MbrID,...DimID-MbrID;]
[INCUPD_DATA=Dynamic Restructuring Data Load Options;]
[@@USERS=Username List;][CALC_SCRIPT=Calc Script Name;]
[ESSCMD_SCRIPT=Esscmd Script File;]
[FT_COLUMN=FactTable Column for Incremental Update;]"
ParametersDescription

Metaoutline

The name of the metaoutline. Integration Server uses the specified metaoutline to extract data from the data source to create an Essbase outline.

Essbase Application

The name of the Essbase application on the target Essbase Server computer where the Essbase outline is created.

Essbase Database

The name of the Essbase database on the target Essbase Server computer. Integration Server applies this name to the Essbase outline.

Data Load Filter ID

Optional. You can specify a data load filter ID to use when loading data. If you do not specify a filter ID, the default filter (*DEFAULT) is used. The default filter ID is 1; any additional filters are numbered sequentially, starting with the number 2. For information about creating filters, see the Integration Services Console Help.

Replace Zeros with #MISSING

Optional. The Replace Zeros with #MISSING parameter replaces Essbase account dimension values of zeros with #MISSING. The default is N (No).

This is the format:

REPLACE_ZEROS=[Y|N]

Here are the reasons that you may want to replace zeros with #MISSING:

  • Fewer input blocks are created during the data load.

  • The calculation time required in the Essbase database is decreased significantly.

  • Because of differences in calculator behavior depending on whether a value is zero or missing, faulty data is not generated.

DimID-MbrID, DimID-MbrID,... DimID-MbrID

Optional. Incremental Update. The level at which dimensions are updated during an incremental data load. This is the format:

INCUPD=DimID-MbrID,DimID-MbrID,...DimID-MbrID;[;]

Values for incremental update are as follows:

  • DimID represents the dimension ID of the dimension to which the incremental update applies.

  • MbrID represents the member ID of the level zero member of the hierarchy to which the incremental update applies.

Dynamic Restructuring Data Load Options

Optional. Dynamic restructuring data load options determine how Essbase loads values from a data source to the database. Specify 1, 2, or 3.

Values are as follows:

1 Overwrite. Select to replace the values in the database with the values in the data source. This is the default (INCUPD_DATA=1).

2 Add. Select to add values in the data source to the existing values in the database.

3 Subtract. Select to subtract the values in the data source from the existing values in the database.

Username List

Optional. Lists all user IDs that can access the Essbase application and database into which you are loading the members. Commas separate user IDs. For example, @@USERS="admin","ljones","ksmith".

Calc Script Name

Optional. The name of the calculation script. The calculation script determines how Essbase calculates the data values in the database. If you do not select a calculation script, Integration Server does not perform a calculation.

Note:

If the data storage model property of the metaoutline is aggregate storage, this parameter is not applicable.

Esscmd Script File

Optional. The name of an ESSCMD script. The script that you define instructs Essbase Server to perform specified actions on the Essbase database. These actions might include changing a member tag or loading data from a worksheet into user-defined members after you load the data from the data source. The ESSCMD script must be in the eis/server/esscript directory.

FactTable Column for Incremental Update

Optional. You can specify that a time-based incremental data load be performed based on the date the last data load was performed. The DateTimeColumn Name parameter is the name of the datetime column in the fact table that contains each record’s create date or modified date.

You can perform time-based incremental loads only if the fact table on which a metaoutline is based has a datetime column that contains each record's creation date or modified date.

This is the format:

FT_COLUMN=DataSourceName.TableName.ColumName;

For example:

FT_COLUMN=TBC.Sales.Timestamp;

Example

LOADDATA "OTL=ProductsAnalysis;APP=Products;DBN=Analysis;
FLT_ID_DATA=2;REPLACE_ZEROS=Y;CALC_SCRIPT=#DEFAULT#;
INCUPD=1-2,2-1,3-2,4-2,5-1,5-2,5-3;INCUPD_DATA=3;@@USERS="TBC","CFO";"