Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter describes how to load data or members from one or more external data sources to an Analytic Server. You can load data without updating the outline, you can update the outline without loading data, or you can load data and build dimensions simultaneously. For information about setting up data sources and rules files, see Understanding Data Loading and Dimension Building and Creating Rules Files.
This chapter contains the following sections:
Before you start to load data or build dimensions, make sure that you have the following items in place:
When you start to load data or build dimensions, you must first select one or more valid data sources that contain the data to load or dimensions to build. For a list of types of valid data sources, see Supported Data Sources. Make sure you are connected to the Analytic Server before you specify the data sources. For a comprehensive discussion of how to optimize a data load, see Optimizing Data Loads.
When you use Administration Services to perform a data load or dimension build for a block storage database, you can execute the load or build in the background so that you can continue working as the load or build processes. You can then check the status of the background process to see when the load or build has completed. For more information, see "Performing a Data Load or Dimension Build" in Essbase Administration Services Online Help.
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 any of the following methods:
Tool |
Topic |
Location |
---|---|---|
You can stop a data load or dimension build before it completes. You should not stop a data load or dimension build unless you are very sure that stopping is necessary. If a data load or dimension build process is terminated, Analytic Services 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 some time before the client responds to the termination request. Because Analytic Services reads the source file until all source data is read, the amount of time depends on the size of the file and the amount of source data that Analytic Services has processed. If the process is terminated from the machine that initiated it, the 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. For a description of the operation of each isolation level setting, see Understanding Isolation Levels. If you stop a data load that is adding to or subtracting from data values, see Recovering from an Analytic Server Crash to identify the recovery procedure.
To stop a data load or dimension build before it completes, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
This section lists tips for data loading and dimension building. It contains the following sections
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, be aware of the following:
To set the consolidation, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
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. For a discussion of how Analytic Services calculates #MISSING values, 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.
Skip this section if you are building dimensions.
If you use a spreadsheet to load data, see the Essbase Administration Services Online Help and search for "spreadsheet" in the index.
Each record in the data source must have the same number of fields to perform a data load or dimension build. If fields are missing, the data load or dimension build processes incorrectly. For example, the file in Figure 108 is invalid, because there is no value under Apr. To fix the file, insert #MISSING or #MI into the missing field. For instructions, see Replacing an Empty Field with Text.
Actual Ohio Sales Cola Jan Feb Mar Apr 10 15 20
Figure 109 is valid because #MI
replaces the missing field.
Figure 109: Valid Missing Fields
Actual Ohio Sales Cola Jan Feb Mar Apr 10 15 20 #MI
If a rules file has extra blank fields, join the empty fields with the field next to them. For a brief discussion, see Joining Fields.
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.
For a brief discussion, see Ignoring Fields.
For example, reject all records for which the ignored column is less than 250 or greater than 500. For a brief discussion, see Rejecting Records.
Note: You cannot reject more records than the error log can hold. By default, the limit is 1000, but you can change it by setting DATAERRORLIMIT in the essbase.cfg
file. See the Technical Reference for more information.
If you try to load a data source into Analytic Server, but it does not load correctly, check the following:
If you can answer both of the above questions with a "yes," something is probably wrong. Use the following sections to determine what the problem is and to correct the problem.
When you correct the problems, you can reload the records that did not load by reloading the error log. For more information, see Loading Dimension Build and Data Load Error Logs.
To help identify if the problem is with Analytic Services and not with the server or network, try to access the server without using Analytic Services. Check the following:
If Analytic Services cannot open the data source that you want to load, check the following:
.TXT
. All rules files must have a file extension of .RUL
. 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 in for more information about error logs.
If there is no error log, check the following:
If the error log exists but is empty, Analytic Services does not think that an error occurred during loading. Check the following:
If the server crashes while you are loading data, Analytic Services sends you a time-out error. The recovery procedures that you need to perform depend on the type of load you are performing and the Isolation Level setting:
For a description of Isolation Level settings, see Understanding Isolation Levels.
If the data source loads without error, but the data in the database is wrong, check the following:
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
Analytic Services recognizes Florida to be a member of the Market dimension. The values in the last four records are interpreted as Florida values instead of Texas values.
Note: You can check data by exporting it, by running a report on it, or by using a spreadsheet. If doing exports and reports, see Developing Report Scripts and Using ESSCMD. If using a spreadsheet, see the Essbase Spreadsheet Add-in User's Guide.
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.
This task may be difficult as the end of file marker may be composed of one or more special characters. To ignore all instances of a string, see "Ignoring Fields Based on String Matches" in the Essbase Administration Services Online Help.
See "Rejecting Records" in the Essbase Administration Services Online Help.
Sometimes, you can track down problems with dimension builds by understanding how Analytic Services initializes the rules file and processes the data source.
Analytic Services performs the following steps to initialize a rules file:
Then Analytic Services performs the following operations on each record of the data source during a data load or dimension build:
The following sections describe how Analytic Services processes invalid fields during a data load.
If you are using a rules file for the data load, skip this section. It applies only to data loaded without a rules file.
In a free-form data load, if a dimension or member field is missing, Analytic Services uses the value that it used previously for that dimension or member field. If there is no previous value, Analytic Services aborts the data load.
For example, when you load Figure 110 into the Sample Basic database, Analytic Services 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.
Figure 110: Valid Missing Members
Jan Sales Actual Ohio Cola 25 "Root Beer" 50 "Diet Cola" 19
Analytic Services stops the data load if no prior record contains a value for the missing member field. If you try to load Figure 111 into the Sample Basic database, for example, the data load stops, because the Market dimension (Ohio, in Figure 110) is not specified.
Figure 111: Invalid Missing Members
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 Analytic Services encounters an unknown member name, Analytic Services rejects the entire record. If there is a prior record with a member name for the missing member field, Analytic Services continues to the next record. If there is no prior record, the data load stops. For example, when you load Figure 112 into the Sample Basic database, Analytic Services rejects the record containing Ginger Ale because it is not a valid member name. Analytic Services 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.
If you are performing a data load, when Analytic Services encounters an invalid data field, it stops the data load. Analytic Services loads all fields read before the invalid field into the database, resulting in a partial load of the data. In the following file, for example, Analytic Services stops the data load when it encounters the 15- data value. Analytic Services loads the Jan and Feb Sales records, but not the Mar and Apr Sales records.
Figure 113: Invalid Data Field
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.
![]() |