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.
To start loading data or building dimensions, you must have:
Note: You must use a rules file to load SQL data or to build dimensions and members dynamically.
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:
Figure 352: Data Load Dialog Box
To load SQL data into an Essbase database:
Figure 353: SQL Data Load Dialog Box
To select text or spreadsheet files:
Figure 354: Open Server Data File Object Dialog Box
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
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.
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.
Task |
Action |
Click the first file, then hold down the Shift key and select the last file in the range. |
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.
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.
Figure 356: Open Server Rules Object Dialog Box
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.
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.
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:
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, Essbase rejects any records containing new members during the data load.
Each time a data source fails, Essbase tells you which data source failed and asks you if you want to continue reading the remaining data sources.
Figure 357: Dataload Error Dialog Box
To continue with the remaining data sources, click Yes. To stop, click No. Any data sources used before you stop are in the database.
Check the error log to determine why 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.
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.
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.
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.
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 |
---|---|---|
import data to load data; import dimensions to build dimensions |
||
LOADDATA or IMPORT to load data; BUILDDIM to build dimensions |
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 |
---|---|---|
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.
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
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.
To fix the data source, see Debugging a Data Load.
When no data sources or records were loaded into the database, the following dialog box is displayed.
To fix the data source, see Debugging a Data Load.
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.
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:
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 Aggregating #MISSING Values for more information.
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.
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.
If you load data using a spreadsheet, see the following documents:
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.
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:
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.
If the data source loads correctly, but the data in the database is wrong, check the following:
Note: You can check data by exporting it, running a report on it, or by using a spreadsheet. To do exports and reports, see Developing Report Scripts and Automating the Production Environment. To use a spreadsheet, see the Essbase Spreadsheet Add-in User's Guide for your particular spreadsheet.
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.
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:
If Essbase cannot open the data source to load, check the following:
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:
For more information on Isolation Level settings, see Isolation Levels.
If you cannot validate your rules file, check to make sure that it is set up correctly:
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.
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |