In This Section:
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:
See Data Sources.
See Rules Files.
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 “Loading Data and Building Dimensions” 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.
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.
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.
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:
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 “Loading Data and Building Dimensions” in the Oracle Essbase Administration Services Online Help.
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.
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
To set the consolidation, use a tool:
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.
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
Actual Ohio Sales Cola Jan Feb Mar Apr 10 15 20 #MI
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.
To load a subset of records:
See Ignoring Fields.
For example, reject all records for which the ignored column is fewer than 250 or greater than 500. See Rejecting Records.
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.
You can use the data load options file as a template; after opening a data load options file, you can modify any specifications.
A new hashtable implementation optimizes the performance of name lookup and insertion when building dimensions and editing outlines. This hashtable implementation has an increased memory footprint.
To configure the hashtable, use the ESTIMATEDHASHSIZE configuration setting. This setting specifies, in millions, the estimated number of member name and alias name strings that are loaded into memory.
The ESTIMATEDHASHSIZE configuration setting applies to block and aggregate storage applications.
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.
Essbase can load only data sources that are not locked by another user or application.
Text files must have a .txt extension; rules files must have an .rul extension.
Check for misspellings.
Ensure that no one has moved or deleted the 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.
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.
By default, when using a rules file, Essbase creates an error log.
If so, you probably received a timeout error on the client. See 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):
Committed, reload the data source when the server is running again.
Uncommitted, determine how much data Essbase loaded before the crash:
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.
If so, check the data source again to make sure that it contains the correct values.
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 Placing Text in Empty Fields.
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
This situation occurs if a parent has only one child or only one child rolls up into the parent.
See Flipping Field Signs.
See Scaling Data Values.
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.
See “Ignoring Fields By Specifying Tokens” in the Oracle Essbase Administration Services Online Help.
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.
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.
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.
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
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.
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.