Performing and Debugging Data Loads or Dimension Builds

In This Section:

Prerequisites for Data Loads and Dimension Builds

Performing Data Loads or Dimension Builds

Stopping Data Loads or Dimension Builds

Tips for Loading Data and Building Dimensions

Debugging Data Loads and Dimension Builds

Also see:

Prerequisites for Data Loads and Dimension Builds

You can load data or members from one or more external data sources to an Essbase Server. You can load data without updating the outline, update the outline without loading data, or load data and build dimensions simultaneously. Before you load data or build dimensions, ensure that the following items are in place:

Performing Data Loads or Dimension Builds

When you use Administration Services to perform a data load or dimension build, you can execute the load or build in the background so that you can continue working during the load or build. You can then check the status of the background process to see when the load or build has completed. See “Performing a Data Load or Dimension Build” in the Oracle Essbase Administration Services Online Help.

If you are using multiple data sources in a dimension build, to reduce total processing time you can perform a deferred-restructure dimension build. See Performing Deferred-Restructure Dimension Builds.

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 a tool:

Tool

Topic

Location

Administration Services

Performing a Data Load or Dimension Build

Oracle Essbase Administration Services Online Help

MaxL

For data loading: import data

For dimension building: import dimensions

Oracle Essbase Technical Reference

ESSCMD

For data loading: IMPORT

For dimension building: BUILDDIM

Oracle Essbase 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 it is necessary. If a data load or dimension build process is terminated, Essbase 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 time before the client responds to the termination request. Because Essbase reads the source file until all source data is read, the amount of time depends on the file size and the amount of source data that Essbase has processed. If the process is terminated from the computer that initiated it, 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. See Understanding Isolation Levels. If you stop a data load that is adding to or subtracting from data values, see Recovering from an Essbase Server Crash.

    *  To stop a data load or dimension build before it completes, use a tool:

    Tool

    Topic

    Location

    Administration Services

    Disconnecting User Sessions and Requests

    Oracle Essbase Administration Services Online Help

    MaxL

    alter system kill request

    Oracle Essbase Technical Reference

      Tips for Loading Data and Building Dimensions

      This section contains topics to help you load data and build dimensions.

      Performing Deferred-Restructure Dimension Builds

      Skip this section if you are loading data only or are using a single data source for a dimension build.

      By default, each time you make changes to an outline, Essbase considers the type of change and restructures the database if needed. Restructuring the database rebuilds the database, which takes time, requires more disk space for its process, and can cause file defragmentation. For information about the types of changes and types of restructuring that can be required, see the following topics:

      Deferred-restructure dimension builds, also known as incremental dimension builds, read multiple data sources for dimension builds and delay restructuring until all data sources have been processed. The following methods for performing deferred-restructure builds use different approaches to specifying the action:

      • Administration Services enables you to list all data sources in a single dialog box.

        When the files listed are all dimension builds, a deferred-restructure dimension build option is available. Selecting this option delays restructuring until all sources have been processed. The outline is validated after each dimension build is processed. See “Performing a Data Load or Dimension Build” in the Oracle Essbase Administration Services Online Help.

      • MaxL enables you to include all of the data sources within one import database statement.

        You can control whether outline validation is performed for each file. You must enable outline validation for the last file. See “import database” and “import dimension” in the MaxL section of the Oracle Essbase Technical Reference.

      • ESSCMD requires several commands:

        • BEGINCBUILDDIM, to indicate that a deferred-restructure dimension build is to be performed

        • INCBUILDDIM for each data source, to indicate that the data sources to be included in the dimension build

        • ENDBUILDDIM, to trigger restructuring, if needed

      In all cases, the data sources are processed in the order in which they are listed.

      Note:

      MaxL and ESSCMD enable you to enforce or suppress outline verification for each file. To ensure a valid outline, ensure that the last build verifies the outline. Deferred-restructure dimension builds in Administration Services verify the outline after each data source is processed.

      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:

      • If possible, do not load data directly into a parent.

      • If you must load data into a parent member, ensure that Essbase knows not to consolidate #MISSING values from the children of the parent into the parent.

      *  To set the consolidation, use a tool:

      Tool

      Topic

      Location

      Administration Services

      Aggregating Missing Values During Calculation

      Oracle Essbase Administration Services Online Help

      Calculation Script

      SET AGGMISSG

      Oracle Essbase Technical Reference

      MaxL

      alter database

      Oracle Essbase Technical Reference

      ESSCMD

      SETDBSTATEITEM

      Oracle Essbase 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. 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.

        Dealing with Missing Fields in a Data Source

        Each record in the data source must have the same number of data value fields to perform a data load. If data values are missing, the data load processes incorrectly.

        For example, the following file is invalid, because there is no value under Apr:

        Actual Ohio Sales Cola
        Jan     Feb    Mar    Apr
        10      15     20

        To fix the file, insert #MISSING or #MI into the missing field:

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

        See Replacing an Empty Field with Text.

        Note:

        If a dimension field or member field is missing, Essbase uses the value that it used previously for that dimension or member field. See Missing Dimension or Member Fields.

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

        In aggregate storage databases, values can be loaded only to level 0 cells. Specifying #MISSING or #MI as a value in the data source removes the associated cell if it is present in the database. For information about data load differences for aggregate storage databases, see Preparing Aggregate Storage Databases.

        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.

          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 fewer than 250 or greater than 500. See Rejecting Records.

          Note:

          You cannot reject more records than the error log can hold. By default, the limit is 1000. You can change the limit by setting DATAERRORLIMIT in the essbase.cfg file. See the Oracle Essbase Technical Reference.

        Saving and Reusing Data Load Options

        If you perform data loads and dimension builds through Administration Services, and you use the same data source files and rules files each time, you can save the source and rules file options specified in the Data Load dialog box and reuse them later.

        By default, the file options are saved in the ARBORPATH directory on the client in an XML file that you name. To use a file of saved options, you can open the file. The saved files and their options are added to the list in the currently open Data Load dialog box.

        Tip:

        You can use the data load options file as a template; after opening a data load options file, you can modify any specifications.

        See “Performing a Data Load or Dimension Build” in the Oracle Essbase Administration Services Online Help.

        Debugging Data Loads and Dimension Builds

        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 the following topics. After you correct the problems, you can reload the records that did not load by reloading the error log. See Loading Dimension Build and Data Load Error Logs.

        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. See Understanding and Viewing Dimension Build and Data Load Error Logs.

        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:

        Recovering from an Essbase Server Crash

        If the server crashes while you are loading data, Essbase sends you a timeout error. The recovery procedures that you must perform depend on the type of load you are performing and the Isolation Level database transaction setting (see Understanding Isolation Levels):

        • If you are overwriting the values of the data source, reload the data source when the server is running again.

        • If you are adding to or subtracting from existing values in the data source and the Isolation Level setting is:

          • Committed, reload the data source when the server is running again.

          • Uncommitted, determine how much data Essbase loaded before the crash:

            1. Compare the values of the data source with the values of the database.

            2. If the values that you are adding to or subtracting from have not changed, reload the data source.

            3. If the values that you are adding to or subtracting from have changed, clear the values that loaded and reload the previous data sources. If, for example, you derive monthly sales figures by adding the sales figures for each week as they are loaded, clear the sales figures from the database and reload the sales figures for each week up to the current week.

        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.

        • There are blank fields in the data source.

          You must insert #MI or #MISSING into a data field that has no value. Otherwise, the data source may not load correctly. To replace a blank field with #MI or #MISSING using a rules file, see Replacing an Empty Field with Text.

        • 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
        • There are any implicitly shared members (when a parent and child share the same data value) of which you were unaware.

          This situation occurs if a parent has only one child or only one child rolls up into the parent.

          See Understanding Implied Sharing.

        • 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 Selecting Records and Rejecting 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.

        Note:

        You can check data by exporting it, by running a report on it, or by using a spreadsheet. If exporting or running reports, see Developing Report Scripts and Using ESSCMD. If using a spreadsheet, see Oracle 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 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.

          See “Ignoring Fields Based on String Matches” in the Oracle Essbase Administration Services Online Help.

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

          See “Rejecting Records” in the Oracle Essbase Administration Services Online Help.

        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 Performing 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

        The following sections describe 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

        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 Essbase encounters an unknown member name, Essbase rejects the entire record. 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, Essbase rejects the record containing Ginger Ale because it is not a valid member name. Essbase loads the records containing Cola, Root Beer, and Cream Soda. 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.

        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 Essbase encounters an invalid data field, it stops the data load. Essbase loads all fields read before the invalid field into the database, resulting in a partial data load. For example, in the following file, Essbase stops the data load when it encounters the 15- data value. Essbase loads the Jan and Feb Sales records but not the Mar and Apr Sales records.

        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.