Performing Database Operations on Text and Date Measures

Loading, Clearing, and Exporting Text and Date Measures

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 cubes, values can only be loaded at the input level; this restriction applies equally to text and date measures. In block storage cubes, text and date values can be loaded at any level.

You can clear, lock and send, and export text or date values just as you perform those operations on numeric values.

Format of Textual Measures in Data Exports and Free-Form Data Imports

You can load text or date values with or without rule files. When a rule file is not used (in a free-form data load), in some cases, you must distinguish text or date values from member names by prefixing them with the string #Txt: and enclosing the prefix and the text values in double quotation marks, for example "#Txt:Highly Satisfied".

Note:

The entire string, including the prefix must be enclosed in the quotation marks. For example, #Txt:"Highly Satisfied" is incorrect and returns an error.

Here is an example of a line of data in a free-form data load file. This is also how an data export file is formatted, when you export data from an aggregate storage cube with textual measures:

"100-10" "New York" "Cust Index" "#Txt:Highly Satisfied"

The text value Highly Satisfied is the text list object name, prefixed with the marker #Txt: to differentiate it from member names such as "New York".

The #Txt: prefix is also applicable when loading date measures, #Missing values, and values that would be #OutOfRange.

Exported textual measures are marked in the export file with #Txt: as a prefix before the text list object name. For example,

"100-10" "New York" "Cust Index" "#Txt:Highly Satisfied"

When re-importing textual measures into the cube using a free-form data load with no rule file, the #Txt: markers are required to distinguish the text list object names from the rest of the data.

Additional Aggregate Storage Guidelines for Loading Text and Date Values

Use the following guidelines when loading text and date values into an aggregate storage cube. These guidelines will help eliminate invalid aggregations.

Load values at the input level.
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 the Essbase Java API, use the commitType parameter of the IEssCube.loadBufferTerm method. In the C API, use the ulCommitType field of the EssLoadBufferTerm function.
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 the alter database statement. In the Essbase Java API, use the duplicateAggregationMethod parameter of the IEssCube.loadBufferInit method. In the C API, use the ulDuplicateAggregationMethod field of the EssLoadBufferInit function.
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.

Consolidating Text and Date Measures

By default, text measures are assigned the ^ operator (never consolidate). Text and date measures are not consolidated to higher level members along non Accounts dimensions.

If you tag a text or date measure with an operator other than ^, it will be consolidated along other dimensions based on its internal numeric value. This is not recommended for aggregate storage cubes, because only the + operator is supported for consolidation, and the aggregated values likely will not have any validity for text or date measures. Additionally, Essbase does not translate out-of-range values to #OUTOFRANGE during consolidation.

For block storage cubes, you can write calculation scripts that consolidate text measures in a custom fashion. You might want to consolidate text measures when they represent ranking measures. For example, consider a text list named "CustomerSatisfaction", which contains mappings such as Excellent=5, Good=4, Fair=3, Poor=2, Bad=1. The values are loaded at level 0. You can consolidate values to parent levels by taking an average of values at child levels. For example, the value of “CustomerSatisfaction” at [Qtr1] is the average of values at [Jan], [Feb], [Mar].

Retrieving Data With Text and Date Measures

Text or date measures can be queried in the same way as numerical measures, using Smart View, MDX, Report Writer, or the Analyze view in the Essbase web interface. The corresponding cells are displayed with the appropriate text values or formatted date values.

For example, the following Smart View grid is from the Facility Rating cube, which you can download from the Files catalog under All Files > Gallery > Applications > Facility Rating. The Answer member is a textual measure within the Measures dimension.

The MDX function EnumValue and the calculation function @ENUMVALUE are designed for getting the numeric value of text measures. These functions can be useful in MDX scripts, calc scripts, or formulas when you need to do operations based on the numeric value of a member rather than its text value.

The following Report Writer commands are designed to work with numeric data, and are not supported for text or date measures:

RESTRICT
TOP
BOTTOM
SORT* commands
CALCULATE COLUMN
CALCULATE ROW

Limitations of Text and Date Measures

An outline restructure does not restructure text lists. If the mapping of numeric to text values in a text list is changed, the change will be reflected in the text data already present in the database for that text list. Therefore, when adding items to a text list, add them to the top or bottom of the list so as to avoid altering the mapping numbers of existing text list items.

Text and date measures are not supported across partitions.

Shared members inherit the text or date type of the prototype member.