To load data to text or date measures, follow the same procedure as for loading data to members with numeric measures. The input data should contain formatted date values, or text values corresponding to the text list object that is associated with the text measure.
If you attempt to load text values that are not present in the text list object associated with that member, Essbase issues a warning message.
In aggregate storage databases, values can only be loaded at the input level; this restriction applies equally to text and date measures. In block storage databases, text and date values can be loaded at any level.
Use the following guidelines when loading text and date values into an aggregate storage database. These guidelines will help eliminate invalid aggregations.
Use Replace mode.
Note: | Replace mode is set when committing the buffer. In MaxL, use the override values grammar of the import data statement. In Administration Services Console, use the overwrite existing values option in the Data Load dialog box. In the Essbase API, use the ulCommitType field of EssLoadBufferTerm. |
Use a single load buffer to load all values associated with date/text measures.
Use the aggregate_use_last aggregation method.
Caution! | The aggregate_use_last method has significant performance impact, and is not intended for large data loads. If your data load is larger than one million cells, consider separating the numeric data into a separate data load process (from any typed measure data). The separate data load can use aggregate_sum instead. |
Aggregate_use_last is set when creating the load buffer. In MaxL, see the PROPS terminal that is part of the initialize load_buffer grammar in thealter database statement. In Administration Services Console, select the aggregate use last check box in the Data Load dialog box. In the Essbase API, use the ulDuplicateAggregationMethod field of EssLoadBufferInit.
Avoid loading #MISSING values to text/date measures in incremental data load mode. When a #MISSING value is loaded to a cell with a non-Missing value in incremental load, it is replaced with a zero value. The zero value may not have the same meaning as the #MISSING value for date/text measures. Use full data load if you need to load #MISSING values to date/text measures.
If mixed (numeric and text or date) data are being loaded, either ensure that Replace mode is sufficient for your numeric data, or create a separate data load process for the numeric data.
You can load text or date values with or without rules files. When a rules file is not used, you must distinguish text or date values from member names by enclosing the text values in double quotation marks and prefixing them with the string #Txt:.
Here is an example of a line of data in a free-form data load file:
"100-10" "New York" "Cust Index" #Txt:"Highly Satisfied"
The text value "Highly Satisfied" is pre-fixed with #Txt:to differentiate it from member names such as "New York".
The "#Txt" prefix is also required for date measures when a rules file is not used for data load.
You can clear, lock and send, and export text or date values just as you perform those operations on numeric values.