Performing a Data Load

This chapter describes how to load data from one or more external data sources to your Hyperion Essbase OLAP Server using the Hyperion Essbase 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:

• Prerequisites for Loading Data and Building Dimensions
• Choosing the Data Sources Using the Hyperion Essbase Application Manager
• Choosing the Data Sources Using Windows
• Specifying How to Load Data or Build Dimensions
• Setting the Error Log File
• Starting the Data Load or Dimension Build
• Finishing the Data Load or Dimension Build
• Tips for Loading Data

Use import in MaxL or the LOADDATA or UPDATEFILE commands in ESSCMD to load data without a rules file. See the online Technical Reference in the DOCS directory for information.

Prerequisites for Loading Data and Building Dimensions

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

• A Hyperion Essbase database into which to load the data or build the outline.
• A connection to your server.
• Valid data sources:
  • Microsoft Excel files with the.XLS extension, Version 4.0 and higher. You must load Microsoft Excel files Version 5.0 and higher as client objects or files in the file system.
  • Lotus 1-2-3 files with the.WKS,.WK1,.WK3, or.WK4 extension
  • Spreadsheet audit log files
  • ASCII text files (flat files) from ASCII backups or external sources
  • Hyperion Essbase export files. For information about reloading exported data, see Reloading Exported Data.
  • SQL data sources
• If you are not using a rules file for loading data, a data source correctly formatted for free-form data loading. See Rules for Free-Form Data Sources.
• If you are using a rules file for loading data, a rules file validated for data load. For information about defining data load rules files, see Introducing Data Loading
• If you are performing a dimension build, a rules file validated for dimension build. For more information about defining dimension build rules, see Introducing Dynamic Dimension Building

  Note:

  You must use a rules file to load SQL data or to build dimensions and members dynamically.

Choosing the Data Sources Using the Hyperion Essbase Application Manager

You can select data sources using the Hyperion Essbase 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.

You can use import in MaxL or the LOADDB command in ESSCMD to select a data source and to load data. See the online Technical Reference in the DOCS directory for information.

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 displays.

   Figure 23-1: Data Load Dialog Box

   

4. 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
   • Choosing Text or Spreadsheet Files

Choosing SQL Data Sources

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 this:

   Figure 23-2: SQL Data Load Dialog Box

   

5. 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.
6. 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

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 displays:

   Figure 23-3: Open Server Data File Object Dialog Box

   

6. Make sure the appropriate Hyperion Essbase server, application, and database are selected from their respective lists.
7. Specify the location of the file by clicking either the Server or Client button.

   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 23-3, 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 23-4: Open Client Data Files Dialog Box

   

8. Click OK.
9. 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 Hyperion Essbase 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.

1. Open the Windows File Manager or Explorer. Arrange your windows so that either the Hyperion Essbase Application Manager or its icon is visible.
2. Locate and select the desired data sources.

   To select...	Do...
   One file	Click the file name.
   Several files	Hold the Ctrl key while clicking on the files.
   A range of files	Click the first file, then hold down the Shift key and select the last file in the range.

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.

   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.

Specifying How to Load Data or Build Dimensions

After you select the data sources, specify how Hyperion 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 the Hyperion Essbase Application Manager or Choosing the Data Sources Using Windows.

You can set the following options:

• Modify Outline.
• Load Data

If you are loading data without a rules file, skip to Setting the Error Log File.

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.

1. Select your data sources. If you have not chosen your data sources yet, see Choosing the Data Sources Using the Hyperion Essbase 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.

   Figure 23-5: Open Server Rules Object Dialog Box

   

   Figure 23-6: Open Server Rules Object Dialog Box

4. Make sure the appropriate Hyperion Essbase server, application, and database are selected from the list boxes.
5. Specify the location of the file by clicking either the Server or Client button.

   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.

6. Click OK.
7. Return to the Data Load dialog box.
8. 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.

   Reasons to Stop	Reasons Not to Stop
   To learn immediately that something is wrong with the data source.	To load as much data as possible and then look at errors in the error log.
   To learn immediately that something is wrong with the rules file.

9. 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:

• Modify Outline.

  Hyperion Essbase updates the outline with any new members or dimensions found in the data source. To set up a dimension build rules file, see Introducing Dynamic Dimension Building.

  Note:

  If Modify Outline is not selected, Hyperion Essbase rejects any records containing new members during the data load.

• Interactive.

  Each time a data source fails, Hyperion Essbase tells you which data source failed to load and asks you if you want to continue reading the remaining data sources.

  Figure 23-7: Dataload Error Dialog Box

  

  To continue loading the remaining data sources, click Yes. To stop, click No. Any data sources loaded before you stop are in the database.

  Check the error log to determine why Hyperion Essbase did not load the data source or perform the dimension build operation. See Finishing the Data Load or Dimension Build if you do not know how to do this.

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 MaxL or ESSCMD script. See the online Technical Reference in the DOCS directory, or Performing Interactive and Batch Operations Using ESSCMD for more information.

Setting the Error Log File

You can set a file to record errors during the data load or dimension build if you are using a rules file. An error file can be a valuable debugging tool if your data load or dimension build fails. By default, the error log is in the\ESSBASE\CLIENT directory and is named DATALOAD.ERR. To name the error log something else, enter the name in the Error Output File text box in the Data Load dialog box.

CAUTION:

If the Server Output File text box is blank, Hyperion Essbase does not capture 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.

• For data loads, make sure Load Data is selected.
• For dimension builds, make sure Modify Outline is selected.

Click OK.

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

Finishing the Data Load or Dimension Build

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

• Complete load
• Partial load
• No load

Complete Load

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

Figure 23-8: 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. Hyperion Essbase lists all free-form data loads that fail here.

In Figure 23-9, for example, the Act1 text file did not load successfully.

Figure 23-9: Partial Data Load

To fix the data source

1. Open the error log file.

   It is located in\ESSBASE\CLIENT\DATALOAD.ERR for data loads and \ESSBASE\CLIENT\DIMBUILD.ERR for dimension builds. If there is no error log for data load, set one and restart the load. If there is no error log for the dimension build, it means the dimension build was successful. See Setting the Error Log File.

   Note:

   Hyperion Essbase only creates an error log file if you are using a rules file.

2. Browse through the error log file. It contains a list of each of the data sources and records that did not load. Figure 23-10 is the error log that Hyperion Essbase created while trying to load the Act1 file.

   Figure 23-10: Error Log for Partial Data Load

\\ Member  Sales Not Found In Database
California,Caffeine Free Cola, Sales, 145,132,125,110,106,96,87,87,109,109,116,102

\\ Member  COGS Not Found In Database
California,Caffeine Free Cola, COGS, 95,104,109,123,127,141,154,154,122,122,113,127

\\ Member  Marketing Not Found In Database
California,Caffeine Free Cola, Marketing, 30,33,34,39,40,45,49,49,39,39,36,40

\\ Member  Payroll Not Found In Database
California,Caffeine Free Cola, Payroll, 22,22,22,23,23,23,22,22,22,22,22,22

\\ Member  Misc Not Found In Database
California,Caffeine Free Cola, Misc, 0,0,1,1,0,0,0,1,0,0,1,1

1. After you determine what did not load, open the data sources or records that did not load and fix them.

   To fix the data source in Figure 23-10, for example, either edit the data source to remove the invalid members (that is, Sales, COGS, Marketing, Payroll, and Misc) or, if those members are all in the same column, ignore all fields in that column, see Ignoring All Fields in a Column.

   . To view a specific record in a data source from the Data Prep Editor, see Setting the Records Displayed.

2. When you have fixed the problem with the database, you may be able to reload just the records that failed.

   See Loading the Error Log File for more information.

No Load

When no data sources or records were loaded into the database, Hyperion Essbase displays the following dialog box:

Figure 23-11: No Data Loaded

1. Open the error log file for the data load.

   If you did not set an error log, set one and restart the load. See Setting the Error Log File.

2. Browse through the error log file.

   It contains a list of each of the data sources and records that did not load. In Figure 23-11, for example, Data did not load.

3. After you determine what did not load, open the data sources or records that did not load and fix them.

   To jump directly to a record in a data source, see Setting the Records Displayed.

4. When you have fixed the problem with the database, you can reload the records.

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:

• If possible, do not load data directly into a parent.
• If you must load data into the parent member, make sure Hyperion Essbase knows not to aggregate #MISSING values from children into their parents. Set this at the database level through Hyperion Essbase Application Manager or use alter database in MaxL or the SETDBSTATEITEM command in ESSCMD. You can also use the SET AGGMISSG command in calc scripts. See the online Technical Reference in the DOCS directory for more information.

  This only works if the children's values are empty (#MISSING). If the children have data values, those values will still overwrite the values of the parent. See Calculating #MISSING Values for more information.

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.

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 .

   Note:

   You cannot reject more records than the error log file can hold. By default, this is 1000, but you can change it by setting the DATAERRORLIMIT in the ESSBASE.CFG file. See the online 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:

• Hyperion Essbase Spreadsheet Add-in User's Guide for your spreadsheet.
• If you want to use VBA functions, you must also use the Hyperion Essbase Visual Basic API. See ESBIMPORT() in the online API Reference in the DOCS directory. See the Spreadsheet Add-in Online Help for information about using VBA with the Visual Basic API.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.