Performing and Debugging Data Loads or Dimension Builds Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Performing and Debugging Data Loads or Dimension Builds


This chapter describes how to load data or members from one or more external data sources to an Analytic Server. You can load data without updating the outline, you can update the outline without loading data, or you can load data and build dimensions simultaneously. For information about setting up data sources and rules files, see Understanding Data Loading and Dimension Building and Creating Rules Files.

This chapter contains the following sections:

Prerequisites for Data Loads and Dimension Builds

Before you start to load data or build dimensions, make sure that you have the following items in place:

Performing Data Loads or Dimension Builds

When you start to load data or build dimensions, you must first select one or more valid data sources that contain the data to load or dimensions to build. For a list of types of valid data sources, see Supported Data Sources. Make sure you are connected to the Analytic Server before you specify the data sources. For a comprehensive discussion of how to optimize a data load, see Optimizing Data Loads.

When you use Administration Services to perform a data load or dimension build for a block storage database, you can execute the load or build in the background so that you can continue working as the load or build processes. You can then check the status of the background process to see when the load or build has completed. For more information, see "Performing a Data Load or Dimension Build" in Essbase Administration Services Online Help.

Note: If you are loading data into a transparent partition, follow the same steps as for loading data into a local database.

To load data or build dimensions, use any of the following methods:


Tool
Topic
Location

Administration Services

Performing a Data Load or Dimension Build

Essbase Administration Services Online Help

MaxL

For data loading: import data

For dimension building: import dimensions

Technical Reference

ESSCMD

For data loading: IMPORT

For dimension building: BUILDDIM

Technical Reference



Stopping Data Loads or Dimension Builds

You can stop a data load or dimension build before it completes. You should not stop a data load or dimension build unless you are very sure that stopping is necessary. If a data load or dimension build process is terminated, Analytic Services displays the file name as partially loaded.

If you initiate a data load or dimension build from a client and terminate the data load or dimension build from the server, it could take some time before the client responds to the termination request. Because Analytic Services reads the source file until all source data is read, the amount of time depends on the size of the file and the amount of source data that Analytic Services has processed. If the process is terminated from the machine that initiated it, the termination is immediate.

Note: If you are adding to or subtracting from data values during a data load to a block storage database, use the Committed Isolation Level setting, if possible. If the data load is terminated, this setting rolls the data load back to its previous state. For a description of the operation of each isolation level setting, see Understanding Isolation Levels. If you stop a data load that is adding to or subtracting from data values, see Recovering from an Analytic Server Crash to identify the recovery procedure.

To stop a data load or dimension build before it completes, use any of the following methods:


Tool
Topic
Location

Administration Services

Disconnecting User Sessions and Requests

Essbase Administration Services Online Help

MaxL

alter system kill request

Technical Reference



Reviewing the Tips for Loading Data and Building Dimensions

This section lists tips for data loading and dimension building. It contains the following sections

Determining Where to Load Data

Skip this section if you are building dimensions or working with an aggregate storage database.

If you load data into a parent member, when you calculate the database, the consolidation of the children's data values can overwrite the parent data value. To prevent overwriting, be aware of the following:

To set the consolidation, use any of the following methods:


Tool
Topic
Location

Administration Services

Aggregating Missing Values During Calculation

Essbase Administration Services Online Help

Calculation Script

SET AGGMISSG

Technical Reference

MaxL

alter database

Technical Reference

ESSCMD

SETDBSTATEITEM

Technical Reference



The methods in this table work only if the child values are empty (#MISSING). If the children have data values, the data values overwrite the data values of the parent. For a discussion of how Analytic Services calculates #MISSING values, see Consolidating #MISSING Values.

Note: You cannot load data into Dynamic Calc, Dynamic Calc and Store, or attribute members. For example, if Year is a Dynamic Calc member, you cannot load data into it. Instead, load data into Qtr1, Qtr2, Qtr3, and Qtr4, which are not Dynamic Calc members.

Loading Data Using a Spreadsheet

Skip this section if you are building dimensions.

If you use a spreadsheet to load data, see the Essbase Administration Services Online Help and search for "spreadsheet" in the index.

Dealing with Missing Fields in a Data Source

Each record in the data source must have the same number of fields to perform a data load or dimension build. If fields are missing, the data load or dimension build processes incorrectly. For example, the file in Figure 108 is invalid, because there is no value under Apr. To fix the file, insert #MISSING or #MI into the missing field. For instructions, see Replacing an Empty Field with Text.

Figure 108: Missing Fields

Actual Ohio Sales Cola
Jan     Feb    Mar    Apr
10      15     20
 
 

Figure 109 is valid because #MI replaces the missing field.

Figure 109: Valid Missing Fields

Actual Ohio Sales Cola
Jan     Feb    Mar    Apr
10      15     20     #MI
 
 

If a rules file has extra blank fields, join the empty fields with the field next to them. For a brief discussion, see Joining Fields.

Loading a Subset of Records from a Data Source

You can load a subset of records in a data source during a data load or a dimension build. For example, you can load records 250 to 500 without loading the other records of the data source.

To load a subset of records:

  1. Using a text editing tool, number the records in the data source.

  2. Set the rules file to ignore the column containing the record number.

    For a brief discussion, see Ignoring Fields.

  3. Define a rejection criterion that rejects all records except those that you want to load.

    For example, reject all records for which the ignored column is less than 250 or greater than 500. For a brief discussion, see Rejecting Records.

    Note: You cannot reject more records than the error log can hold. By default, the limit is 1000, but you can change it by setting DATAERRORLIMIT in the essbase.cfg file. See the Technical Reference for more information.

Debugging Data Loads and Dimension Builds

If you try to load a data source into Analytic Server, but it does not load correctly, check the following:

If you can answer both of the above questions with a "yes," something is probably wrong. Use the following sections to determine what the problem is and to correct the problem.

When you correct the problems, you can reload the records that did not load by reloading the error log. For more information, see Loading Dimension Build and Data Load Error Logs.

Verifying That Analytic Server Is Available

To help identify if the problem is with Analytic Services and not with the server or network, try to access the server without using Analytic Services. Check the following:

Verifying That the Data Source Is Available

If Analytic Services cannot open the data source that you want to load, check the following:

Checking Error Logs

If a data load or dimension build fails, the error log can be a valuable debugging tool. See Understanding and Viewing Dimension Build and Data Load Error Logs in for more information about error logs.

If there is no error log, check the following:

If the error log exists but is empty, Analytic Services does not think that an error occurred during loading. Check the following:

Recovering from an Analytic Server Crash

If the server crashes while you are loading data, Analytic Services sends you a time-out error. The recovery procedures that you need to perform depend on the type of load you are performing and the Isolation Level setting:

For a description of Isolation Level settings, see Understanding Isolation Levels.

Resolving Problems with Data Loaded Incorrectly

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

Note: You can check data by exporting it, by running a report on it, or by using a spreadsheet. If doing exports and reports, see Developing Report Scripts and Using ESSCMD. If using a spreadsheet, see the Essbase Spreadsheet Add-in User's Guide.

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.

  1. Find the end of file marker in the SQL data source.

  2. Determine how to search for it using the Analytic Services search command.

    This task may be difficult as the end of file marker may be composed of one or more special characters. To ignore all instances of a string, see "Ignoring Fields Based on String Matches" in the Essbase Administration Services Online Help.

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

    See "Rejecting Records" in the Essbase Administration Services Online Help.

Understanding How Analytic Services Processes a Rules File

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

Analytic Services 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 Analytic Services 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 that 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 Undoing Field Operations. The dialog box displayed lists all the field operations in order.

  3. Analytic Services applies all properties for each field, applying all properties to field1 before proceeding to field2. Analytic Services 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 that 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, Analytic Services skips the number of lines that you specified; otherwise, Analytic Services proceeds to the first record.

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

Understanding how Analytic Services Processes Invalid Fields During a Data Load

The following sections describe how Analytic Services processes invalid fields during a data load.

Missing Dimension or Member Fields

If you are using a rules file for the data load, skip this section. It applies only to data loaded without a rules file.

In a free-form data load, if a dimension or member field is missing, Analytic Services uses the value that it used previously for that dimension or member field. If there is no previous value, Analytic Services aborts the data load.

For example, when you load Figure 110 into the Sample Basic database, Analytic Services 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.

Figure 110: Valid Missing Members

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

Analytic Services stops the data load if no prior record contains a value for the missing member field. If you try to load Figure 111 into the Sample Basic database, for example, the data load stops, because the Market dimension (Ohio, in Figure 110) is not specified.

Figure 111: Invalid Missing Members

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

For information on restarting the load, see Loading Dimension Build and Data Load Error Logs.

Unknown Member Fields

If you are performing a data load and Analytic Services encounters an unknown member name, Analytic Services rejects the entire record. If there is a prior record with a member name for the missing member field, Analytic Services continues to the next record. If there is no prior record, the data load stops. For example, when you load Figure 112 into the Sample Basic database, Analytic Services rejects the record containing Ginger Ale because it is not a valid member name. Analytic Services loads the records containing Cola, Root Beer, and Cream Soda. If Ginger Ale were in the first record, however, the data load would stop.

Figure 112: Unknown Members

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.

For information on restarting the load, see Loading Dimension Build and Data Load Error Logs.

Invalid Data Fields

If you are performing a data load, when Analytic Services encounters an invalid data field, it stops the data load. Analytic Services loads all fields read before the invalid field into the database, resulting in a partial load of the data. In the following file, for example, Analytic Services stops the data load when it encounters the 15- data value. Analytic Services loads the Jan and Feb Sales records, but not the Mar and Apr Sales records.

Figure 113: Invalid Data Field

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

For information on continuing the load, see Loading Dimension Build and Data Load Error Logs.



Hyperion Solutions Corporation link