1. Database Administrator's Guide for Oracle Essbase
  2. Performing and Debugging Data Loads or Dimension Builds
  3. Debugging Data Loads and Dimension Builds

Debugging Data Loads and Dimension Builds

Problems with Essbase data loads and dimension builds include incorrect loads or failed processes. The source may contain unknown members or missing / invalid fields, the load rule may be invalid, or there is no connection to the server or the source data. Error logs can help you debug.

If a data source does not correctly load into Essbase Server, ensure that you are connected to the appropriate application and database and that you are loading the correct data source.

If you still encounter problems, see these topics:

After you correct the problems, you can reload the records that did not load by reloading the error log.

Verifying that Essbase Server Is Available

To help identify that the problem is with Essbase and not with the server or network, try to access the server without using Essbase. Check whether:

  • The server is running.

    Try connecting to the server without using Essbase. If you cannot, check with your system administrator.

  • Essbase Server is running.

    Check with your Essbase administrator.

  • The client is connected to the server.

    Try connecting to the server from the client without using Essbase.

Verifying that the Data Source Is Available

If Essbase cannot open the data source that you want to load, ensure that the following conditions are true:

  • The data source is open (for example, is someone editing the data source?).

    Essbase can load only data sources that are not locked by another user or application.

  • The data source has the correct file extension.

    Text files must have a .txt extension; rules files must have an .rul extension.

  • The data source name and the path name are correct.

    Check for misspellings.

  • The data source is in the specified location.

    Ensure that no one has moved or deleted the data source.

  • If you are using a SQL data source:

    • The connection information (such as the user name, password, and database name) is correct.

    • You can connect to the SQL data source without using Essbase.

Checking Error Logs

If a data load or dimension build fails, the error log can be a valuable debugging tool.

If there is no error log, check whether the following conditions exist:

If the error log exists but is empty, Essbase does not think that an error occurred during loading. Check whether the following conditions exist:

Resolving Problems with Data Loaded Incorrectly

If the data source loads without error, but the data in the database is wrong, check whether the following conditions exist:

  • You loaded the correct data source.

    If so, check the data source again to make sure that it contains the correct values.

  • The data source is formatted correctly.

    • All ranges are set up properly.

    • The data is clean. For example, as it processes the data source, Essbase recognizes member names and knows the dimensions they belong to. If a data source record inadvertently includes a member from a dimension for which there is a member named in the header record, the new member name replaces the header record member for that dimension. In the following example data source, Essbase recognizes Florida as a member of the Market dimension. The values in the last four records are interpreted as Florida values instead of Texas values. 

      Jan  Actual  Texas  Sales
   "100-10"   51.7
   "100-20"   102.5
   "100-20"   335.0
   Florida    96.7
   "200-20"   276.0
   "200-20"   113.1
   "200-10"   167.0

  • You added incoming data to existing data instead of replacing incoming data with existing data.

    See Adding to and Subtracting from Existing Values.

  • You selected or rejected any records that you did not intend to select or reject.

    See Select and Reject Records.

  • The sign is reversed (for example, a minus sign instead of a plus sign) and whether you performed sign flips on any UDAs.

    See Flipping Field Signs.

  • You cleared data combinations that you did not intend to clear.

    See Clearing Existing Data Values.

  • You scaled the incoming values incorrectly.

    See Scaling Data Values.

  • All member and alias names are fewer than 79 characters long.

To check data in Smart View, see the Working with Oracle Smart View for Office.

Creating Rejection Criteria for End of File Markers

A SQL data source may have an end of file marker made up of special characters that cause a data load or dimension build to fail. To fix this problem, define a rejection criterion to reject the problem record.

To define rejection criteria:

  1. Find the end of file marker in the SQL data source.
  2. Determine how to search for the end of file marker using the Essbase search command.

    This task may be difficult, because the end of file marker may be composed of one or more special characters.

  3. Define a rejection criterion that rejects the end of file marker.

Understanding How Essbase Processes a Rules File

Sometimes, you can track down problems with dimension builds by understanding how Essbase initializes the rules file and processes the data source.

Essbase performs the following steps to initialize a rules file:

  1. Validates the rules file against the associated outline.

  2. Validates the dimensions. This process includes ensuring that the build method and field types are compatible and that each dimension name is unique. Member names must be either unique or shared.

  3. Adds new dimensions defined in the rules file to the outline.

  4. Reads header records specified in the data source.

Then Essbase performs the following operations on each record of the data source during a data load or dimension build:

  1. Sets the file delimiters for all records.

  2. Applies field operations to the data in the order in which the operations are defined in the rules file.

    Field operations include joins, moves, splits, and creating fields using text and joins. To see the order in which field operations are defined in the rules file, see Perform Operations on Fields.

  3. Essbase applies all properties for each field, applying all properties to field1 before proceeding to field2. Essbase applies field properties in the following order:

    1. Ignores fields set to be ignored during data load

    2. Ignores fields set to be ignored during dimension build

    3. Flags the data field

    4. Applies field names

    5. Applies field generations

    6. Performs all replaces in the order in which they are defined in the rules file

    7. Drops leading and trailing spaces

    8. Converts spaces to underscores

    9. Applies suffix and prefix operations

    10. Scales data values

    11. Converts text to lowercase

    12. Converts text to uppercase

  4. Adds members, or member information, or both, to the outline

  5. If you chose to skip lines, Essbase skips the number of lines that you specified; otherwise, Essbase proceeds to the first record.

  6. Essbase performs selection or rejection criteria in the order in which the criteria are defined in the rules file. Essbase loads or rejects individual records of the data source based on the specified criteria.

Understanding How Essbase Processes Missing or Invalid Fields During a Data Load

See these topics to understand how Essbase processes invalid fields during a data load.

Missing Dimension or Member Fields

If a dimension or member field is missing, Essbase uses the value that it used previously for that dimension or member field. If there is no previous value, Essbase aborts the data load.

For example, when you load the following file into the Sample.Basic database, Essbase maps the Ohio member field into the Market dimension for all records, including the records that have Root Beer and Diet Cola in the Product dimension. 

Jan  Sales  Actual  Ohio
                    Cola          25
                    "Root Beer"   50
                    "Diet Cola"   19

Essbase stops the data load if no prior record contains a value for the missing member field. For example, if you try to load the following file into the Sample.Basic database, the data load stops, because the Market dimension (Ohio, in the previous example) is not specified. 

Jan  Sales  Actual
            Cola          25
           "Root Beer"    50
           "Diet Cola"    19

Unknown Member Fields

If you are performing a data load and an unknown member name is encountered, the entire record is rejected. If there is a prior record with a member name for the missing member field, Essbase continues to the next record. If there is no prior record, the data load stops. For example, when you load the following file into the Sample.Basic database, the record containing Ginger Ale is rejected because it is not a valid member name. The records containing Cola, Root Beer, and Cream Soda are loaded. If Ginger Ale were in the first record, however, the data load would stop. 

Jan, Sales, Actual
Ohio   Cola          2
       "Root Beer"   12
       "Ginger Ale"  15
       "Cream Soda"  11

Note:

If you are performing a dimension build, you can add the new member to the database. See Performing Data Loads or Dimension Builds.

Invalid Data Fields

If you are performing a data load, the data load stops if an invalid data field is encountered. All fields that are read before the invalid field are loaded into the cube, resulting in a partial data load. For example, in the following file, the data load stops when it encounters the 15- data value. The Jan and Feb Sales records are loaded but not the Mar and Apr Sales records.

East   Cola   Actual
Sales         Jan    $10
              Feb    $21
              Mar    $15-
              Apr    $16