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

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 when committing a load buffer.

  To set replace mode when committing a buffer, in MaxL, use the override values grammar of the import data statement. In REST API, use the asoBufferCommit jobType for Execute Job, specifying the termOption as INCR_TO_NEW_SLICE (or 1) when you commit the buffer (see also Create Buffer endpoint in REST API documentation for details). 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.

• Set the duplicate aggregation method to "use last" when initializing the load buffer, to help eliminate invalid aggregations.

  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.

  To specify the "use last" option in MaxL, refer to the PROPS terminal that is part of the initialize load_buffer clause in the alter database statement. In REST API, specify the duplicateAggregationMethod as USE_LAST when you initialize the buffer (see Create Buffer endpoint in REST API documentation). 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.

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].

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

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 and implied shared members inherit the text or date type of the prototype member.