Essbase Troubleshooting Data Load

When Hyperion Essbase cannot load a record, it writes the record to the file called DATALOAD.ERR on the client.

There is a limit to the number of records that an error log can contain. The default limit is 1000 records, but you can set the limit to be higher or lower than 1000 by setting DATAERRORLIMIT in the ESSBASE.CFG file.

When Hyperion Essbase writes the maximum allowed number of records in the error log file, it does not log any other errors it encounters.

Error Messages

1) Error “datalimit error reached 1000, rejected records will not be logged transaction aborted – data load elapsed time 3.03 seconds..…..”

Causes: Was hitting the default size limitation for the .err file

Fix: The size limitation for the .err file can be changed by using the dataerrorlimit .cfg setting.

2) Error : the error file is invalid

Fix: Essbase won’t create the dataload.err. The default error file will be created on c:\essbase\client\dataload.err, user need to go to the file system to create a file and named it essbase.err.
Note: Normally, dataload.err is automatically created by default. This is something you can try if you see this error message.

3) Error : error #1003001, duplicate members from same dimension in data record when doing a data load

Cause: Essbase won’t allow you to use duplicate member names in database.

For example, The data file includes One of the member from one dimension is 011 and another member from another dimension is 11.

Fix: Edit the data file

_____________________________________________________________________________________________

4) Error: Invalid Number in data column

Check:

  1. Verify that the rules file validates.
  2. Too many columns in the rules files. For example; if 3 columns are from the same dimension, then Essbase expects 3 data points. There can be one and only one data column from each dimension.
  3. Member names with numbers need to be surrounded by quotes. (i.e. 1100Member)
  4. All dimensions must be encountered before the data. In other words, the DATA field must be the last field.
  5. Need to use dimension name instead of member name in the field attributes
  6. Check the error file to find out which records are rejected and see if the member (in those records) exist in the outline.
  7. Must be identifying each dimension in the database using data source or the rule file. If the data source doesn’t identify each dimension in the database, you must identify the missing dimension in a header record.
  8. The field attribute for a column has ‘Replace: 0.00″ with “#MI, you need to check the box ” Match Whole Word”.
  9. Contain one generation column and an alias column.
  10. Two dimensions used member name in headings. Tried to load 2 columns in same dimensions but to different members. so the user need to recreate the data load file so that members would be loaded separately.
  11. If above fails, re-create the data load file so that members load separately to help isolate the problem. Sometimes, the rule files might be got corrupted, or you can get their outline, rule files and sample data file to replicate it.

5) Error: Incorrect access to store record

Cause: User logged in (doing the dataload) doesn’t have write access.

Cause: Error in trying to load data to a target database with partitioning.

Fix: This will happen attempting to load data to a target database and “The target partition can be updated” box is not checked in the settings of the partition wizard. To load data to a target database using partitions, this box needs to be checked.

6) Error: data value encountered before all dimensions are specified

Cause: This commonly occurs when a customer is typing in member names in Excel and forgets to place a ‘ before a numeric member name and the member is mistaken for a data value rather than text by Essbase. Essbase must find a reference to each dimension before it can load data.

Cause: Had added a new dimension to outline and was using an old data file that did not have the new dimension, therefore did not have all the dimensions referenced in the rules file also.

7) Error code 3307 incorrect parent, and 3315 attribute changes for members not allowed

Fix: To check the “allow attribute changes” box (Options| Dimension build settings) and load it through File| Outline update.

Fix: To check for member in the outline to see if member exists. Examine the dimension build text file for duplicate member names.

8) Too many values in a row

Fix: change the field name from the member name to the dimension name.

9) Spreadsheet extractor internal error invalid input spreadsheet table doing lock and send from the spreadsheet

Fix: Make sure all dimensions in your outline are represented in the spreadsheet
Fix: Check for any unknown members in the spreadsheet
Fix: If using members in spreadsheet that are numeric, put a single quote in front of those members.
Fix: If using numeric members, be sure those cells in the spreadsheet are formatted as General, not Text

What if the error log exists but empty, it means that there is no error occurred during the dataload.

Check: Does the rules file reject every record in the data source?

Is the rule file correct?

Does the rules file validate properly?

Data Loaded Incorrectly

Cause: Have a blank field in the data source.

To replace a blank field with #MI an #MISSING, using the option of “Replacing an Empty Field with Text”.

Cause: Switching (~) to (+) sign for the member, the member becomes implicitly shared member.

Cause: Scale the incoming values incorrectly

Cause: Have an incorrect data source

Cause: Add incoming data to existing data instead of replacing it using a rule file

Cause: Selected or rejected the incorrect record

Cause: Doesn’t clear existing data value

Cannot open the Data Source

Cause: Be sure that another user or application did not lock the file.

Cause: Does not have the correct file extension

Cause: Incorrect Path or location

Cause: Have an incorrect connection information if using an SQL data source

More Tips

1. Doing a dataload, getting the error message that Essbase does not support long file names.
It is not a problem with the file name itself, but with the path. If the path is too long, it will give this error message.

2. Unable to see data in database after doing lock and send.

Make sure that you are looking at the proper member combinations in Excel for the data that you loaded. If loading data at level zero, be certain to calculate to be able to see data values at upper levels

3.Abort on error during data load in the Data Load response window is disabled but data load is aborting upon error.

The general rule when performing a data load using a rules file and “Abort on Error” is not selected, and Essbase hits a record with errors, the data load will continue and load all subsequent errorless records in the data file. An example of this is when Essbase rejects a record due to an “unknown member” in one of the data records and the “Abort on Error” box is not selected. Essbase will reject this record but will load all other records that do not contain errors.

An exception to this rule is when the error “duplicate members from the same dimension” is hit during a data load. Even if the “Abort on Error” box is not selected, the dataload will be aborted and no subsequent records will be loaded.

  1. Rule file can be validated even though it missed the header definition.
  1. Loading the data file from the server, the files need to be ended in .TXT, but from the client, the file can have any extension.
  1. If the data source from SQL, End of file Markers might cause the failing of dataload.