Performing and Debugging a Data Load

This chapter describes how to load data from one or more external data sources to your OLAP Server using the Application Manager. It shows you how to use free-form or rules file data sources to load data or build dimensions dynamically.

You can load data without updating the outline, you can update the outline without loading data, or you can do both operations simultaneously.

This chapter contains the following sections:

Tip: Use the LOADDATA or UPDATEFILE commands in ESSCMD to load data without a rules file. See the Technical Reference in the docs directory for information about these commands. See Automating the Production Environment for information about ESSCMD.

Prerequisites for Loading Data and Building Dimensions

To start loading data or building dimensions, you must have:

Choosing the Data Sources Using Application Manager

You can select data sources using the Application Manager or Windows. For a list of valid data sources, see Prerequisites for Loading Data and Building Dimensions.

Make sure you are connected to the server before you specify the data sources.

Tip: Use the LOADDB command in ESSCMD to perform this task. See the Technical Reference in the docs directory for information about this command. See Automating the Production Environment for information about ESSCMD.

To select a data source using Application Manager:

  1. Make sure you are connected to a server:
  2. Click the Application Desktop window.
  3. Select Database > Load Data to specify how to load data or build dimensions. The Data Load dialog box is displayed.
  4. Figure 352: Data Load Dialog Box

  5. Click Connect to open the Hyperion Essbase System Login dialog box. Enter the correct values and click OK. Now you are ready to start:

Choosing SQL Data Sources

To load SQL data into an Essbase database:

  1. Click the Application Desktop window.
  2. Select the application and database to load the data into or build dimensions for.
  3. Select Database > Load Data. The Data Load dialog box displays.
  4. To access SQL data, you must first connect to the SQL data source. Select the SQL option. The Data Load dialog box changes to look like the one shown in Figure 353.
  5. Figure 353: SQL Data Load Dialog Box

  6. If your SQL data source requires you to enter your user name and password, enter them. All other connection information is specified in the rules file.
  7. See Using a Rules File with the Data Source to finish, because you must use a rules file to load SQL data sources.

Choosing Text or Spreadsheet Files

To select text or spreadsheet files:

  1. Click the Application Desktop window.
  2. Select the application and database to load the data into or build dimensions for.
  3. Select Database > Load Data. The Data Load dialog box displays.
  4. Click the Data Files option button if it is not already selected.
  5. Click Find to select a text or spreadsheet file to load. The Open Server Data File Object dialog box is displayed.
  6. Figure 354: Open Server Data File Object Dialog Box

  7. Make sure the appropriate server, application, and database are selected from their respective lists.
  8. Specify the location of the file by clicking either the Server or Client button.
  9. If you select Server, the data source to load must reside in the database directory under \essbase\app\application_name\database_name, where application_name and database_name represent the name of your application and database. Type the name of the data source in the Object Name text box or select it from the Objects list box. In Figure 354, for example, you could select ACT1.

    If you select Client, the file may reside in either the application or database directory under \essbase\client or on the drives accessible from the client file system. Click File System to select a file from a standard Open Client Data Files dialog box. Select the file to open, for example, asymm.xls in the \essbase\client\sample directory.

    To select multiple files, hold down the Ctrl key and click the files.

    Note: essbase is the default directory specified during installation. You may have specified a different default directory.

    Load Microsoft Excel files Version 5.0 and higher as client objects or files in the file system, not as server objects.

    Figure 355: Open Client Data Files Dialog Box

  10. Click OK. Return to the Data Load dialog box. Now you can to specify how to load the data or build dimensions.

Choosing the Data Sources Using Windows

You can select the data sources using Application Manager, the Windows File Manager or the Windows Explorer. For a list of valid data sources, see Prerequisites for Loading Data and Building Dimensions.

Make sure you are connected to the server before you select the data sources.

To select a list of files:

  1. Open the Windows File Manager or Explorer. Arrange your windows so that either the Application Manager or its icon is visible.
  2. Locate and select the desired data sources from the Table 32.
  3. Drag the selected files from the File Manager or Explorer window to the Application Manager and release the mouse button. The Data Load dialog box displays. This is the dialog box where you specify how to load data or build dimensions.
  4. Note: If the data source contains blank fields for data values, replace them with #MI or #MISSING. Otherwise, the data will not load correctly. To replace a blank field with #MI or #MISSING, see Replacing an Empty Field with Text.

Table 32: Locating Data Sources

Task
Action

Select one file

Click the file name.

Select several files

Hold the Ctrl key while clicking on the files.

Select a range of files

Click the first file, then hold down the Shift key and select the last file in the range.



Specifying How to Load Data or Build Dimensions

After you select the data sources, specify how Essbase loads those data sources and whether to build dimensions dynamically using the Data Load dialog box. If you have not chosen your data sources yet, see Choosing the Data Sources Using Application Manager or Choosing the Data Sources Using Windows.

You can set the following options:

If you are loading data without a rules file, skip to Setting a Load or Build Error Log.

Using a Rules File with the Data Source

Rules files perform operations on the data as it is loaded, such as moving fields or building new dimensions.

To build dimensions or load SQL data, you must use a rules file.

  1. Select your data sources. If you have not chosen your data sources yet, see Choosing the Data Sources Using Application Manager or Choosing the Data Sources Using Windows.
  2. From the Data Load dialog box, select Use Rules.
  3. Click the Find button to open the Open Server Rules Object dialog box.
  4. Figure 356: Open Server Rules Object Dialog Box

  5. Make sure the appropriate server, application, and database are selected from the list boxes.
  6. Specify the location of the file by clicking either the Server or Client button.
  7. If you select Server, the rules files to use must reside in the database directory under \essbase\app\application_name\database_name, where application_name and database_name represent the name of your application and database. Type the name of the rules source in the Object Name text box or select it from the Objects list box. For example, GENREF.

    If you select Client, the rules file may reside in either the application or database directory under \essbase\client or on the drives accessible from the client file system. Click File System to select a rules file from a standard Open Client Data Files dialog box.

    Note: The \essbase\app and \essbase\client are the default directories specified during installation. You may have set these directories differently.

  8. Click OK. Return to the Data Load dialog box.
  9. Decide if you want to stop the data load or dimension build if an error occurs. This occurs automatically for free-form files, but not for data sources loaded using a rules file.
  10. Stop if you want to learn immediately that something is wrong with the data source or rules file.

    Don't stop if you want to load as much data as possible and then look at errors in the error log.

  11. To stop the data load if an error occurs, select "Abort on error during data load."

Building Dimensions Dynamically by Modifying the Outline

You can modify the outline using a data source and rules file. This lets you change or add new dimensions and members to the database based on data in your data source instead of by using the Outline Editor. You must use a rules file to change the outline.

Caution: Modifying the outline restructures your database.

To change the outline, select the following options in the Data Load dialog box:

Updating the Database Outline in Batch Mode

After you create a dimension build rules file, you may want to automate the process of updating dimensions. You can modify the outline, load data, and calculate databases using a batch job. See Automating the Production Environment, for more information.

Setting a Load or Build Error Log

You can set a file to record errors during the data load or dimension build if you are using a rules file. A data load or dimension build error file can be a valuable debugging tool if your data load or dimension build fails. By default, when performing a dimension build through Outline Editor or using one of the BUILDDIM ESSCMD commands, the error log is named DIMBUILD.ERR. Otherwise, the default name for the error log is DATALOAD.ERR. This error log contains messages from both operations when they are combined using the Application Manager Database > Load data menu command. These logs are located in the \ESSBASE\CLIENT directory.

Caution: If an error output file is not specified, Essbase does not capture data load or dimension build errors.

For more information on errors during data loading or dimension building, see Finishing the Data Load or Dimension Build.

Now you can start loading data or building dimensions.

Starting the Data Load or Dimension Build

After you have set the data load options in the Data Load dialog box, you can start loading the data sources or building dimensions dynamically.

Click OK.

To speed up or optimize a data load, see Optimizing Data Loads.

Tip: You can also load data into Essbase databases using these methods:

Tool
Instructions
For More Information

MaxL

import data to load data; import dimensions to build dimensions

Technical Reference in the docs directory

ESSCMD

LOADDATA or IMPORT to load data; BUILDDIM to build dimensions



Finishing the Data Load or Dimension Build

When the data load or dimension build finishes, Essbase displays a dialog box listing the results. Data loads and dimension builds end in one of the following:

Note: If you are loading data, the state of the load is communicated in the Data Load Completed dialog box.

If you are building dimensions, the state of the build is communicated in the Dimension Build Completed dialog box, which, except for the title, is identical to the Data Load Completed dialog box.

If you are performing a data load and dimension build simultaneously, Essbase displays both dialog boxes.

If a data load process is terminated, Essbase displays the file name as partially loaded. For more information about terminating processes, see the following information sources:

Tool
Instructions
For More Information

Administration Services

Session window

Essbase Administration Services Online Help

MaxL

alter system kill request

Technical Reference in the docs directory



Note: If you initiate a data load from a client and terminate the data load process from the server, depending on the size of the file and how much source data that Essbase has processed, it could take some time before the client responds to the termination request. Essbase reads the entire source file until all source data is read. If the process is terminated from the same machine that initiated it, the termination is immediate.

Complete Load

In a complete load, Essbase had no problems loading every specified record in each data source. When data sources load completely, Essbase lists the data sources successfully loaded in the following dialog box. In Figure 358, for example, the Calcdat file loaded successfully.

Figure 358: Data Load Completed Dialog Box

Partial Load

In a partial load, some of the data sources might have loaded and some might not have loaded. The Data Load Completed dialog box lists all files that may have partially loaded in the middle list box. Essbase lists all free-form data loads that fail here.

In Figure 359, for example, the Act1 text file did not load successfully.

Figure 359: Partial Data Load

To fix the data source, see Debugging a Data Load.

No Load

When no data sources or records were loaded into the database, the following dialog box is displayed.

Figure 360: No Data Loaded

To fix the data source, see Debugging a Data Load.

Tips for Loading Data

This section lists tips for data loading. It describes how to load data into a parent instead of its children, how to load a subset of records in a data source, and how to load data using a spreadsheet.

Where to Load Data

If you load data into the parent member, when you calculate your database, the consolidation of the children's values can overwrite the parent's data. To prevent this from happening:

Loading a Range of Records

You can load a range of records from a data source. For example, you could load just the records 250 to 500 without loading the other records in the data source.

To load a range of records:

  1. Number the records in the data source using a text editing tool.
  2. Open the data source and rules file in the Data Prep Editor.
  3. Ignore the column containing the record number. See Ignoring Fields.
  4. Define a rejection criterion to reject all records except those you want to load. For example, reject all records where the ignored column is less than 250 and greater than 500. See Rejecting Records.
  5. Note: You cannot reject more records than the error log can hold. By default, this is 1000, but you can change it by setting the DATAERRORLIMIT in the ESSBASE.CFG file. See the Technical Reference in the docs directory for more information.

Loading Data Using a Spreadsheet

If you load data using a spreadsheet, see the following documents:

Debugging a Data Load

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

If the answer to those questions is yes, then there is probably something wrong. When you have trouble loading a data source, look at the error log generated for that data load. It lists the errors that occurred when Essbase tried to load the data source. The error log is located on the client machine in \essbase\client\dataload.err. For more information about the error log, see Understanding and Using Dimension Build and Data Load Error Logs.

Use these sections to debug a data load:

When you correct the problems in the data load, you can reload the records that didn't load by reloading the error log. For more information, see Loading Dimension Build or Data Load Error Logs.

Debugging Without a Data Load Build Error Log

If there is no error log, check the following:

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

Finding Errors Written to Other Locations

When Essbase cannot load a record, it writes the record to the error log, 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 any value between 1 - 65000 by setting DATAERRORLIMIT in the essbase.cfg file. See the Technical Reference in the docs directory for more information.

When Essbase writes the maximum allowed number of records in the error log, it does not log any other errors it encounters. The data load, however, continues. Any subsequent errors are lost.

Resolving Problems With Data Loaded Incorrectly

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

After you fix the problem with the database or the rules file, you can load just the records that failed by loading the error log. See Loading Dimension Build or Data Load Error Logs.

Verifying that the Server Is Available

Try to access the server without using Essbase to help identify if the problem is with Essbase and not with your server or network. Check the following:

Verifying that the Data Source Is Available

If Essbase cannot open the data source to load, check the following:

Recovering from a Server Crash

If the server crashes while you are loading data, Essbase sends you a time-out error. If you are overwriting the values in the database with the data source, reload the data sources after the server is running again.

If the Isolation Level transaction setting is Committed, you must re-start the data load from the beginning. If the Isolation Level is Uncommitted, and you are adding to or subtracting from the existing values in the database when the server crashes, do the following:

  1. Determine how much data Essbase loaded before the crash. Compare the values in the data source with the values in the database. If the values you are adding to or subtracting from were not changed, restart the data load.
  2. If the values you are adding to or subtracting from were changed, you must clear the values that loaded and reload the previous data sources. If, for example, you derive the monthly sales figures by adding the sales figures for each week as they are loaded, clear the sales figures in the database and re-load the sales figures for each week up to the current week.

For more information on Isolation Level settings, see Isolation Levels.

Resolving Problems With Validating a Rules File

If you cannot validate your rules file, check to make sure that it is set up correctly:

Creating Rejection Criteria for End of File Markers

Some SQL data sources may have end of file markers made up of special characters that can cause a data load or dimension build to fail. To fix this problem, define a rejection criterion to reject that record.

  1. Find the end of file marker in your SQL data source.
  2. Determine how to search for it using the Essbase search command. This may be difficult as the end of file marker may be composed of one or more special characters. See Ignoring Fields Based on String Matching for information on how to do this.
  3. Define a rejection criterion that rejects the end of file marker. See Rejecting Records for information on how to do this.




© 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com