Working with Text Measures

Text measures extend the analytical capabilities of Essbase beyond numerical data to text-based content. Storage and analysis of textual content can be useful when a cell needs to have one of a finite list of textual values; for example, a product may be sold in five different colors. The color is a text measure whose value must be one of those five colors. Any color not represented in the finite list would be considered by Essbase to be out of range.

You create text measures at the database level. Text measures are made possible by your mapping of a set of text strings to corresponding numeric IDs. These mappings are contained in database-level text list objects that you create.

• Text Measures Workflow

• Text List Objects and Text List Members

Text Measures Workflow

Use one of the following workflows to implement text measures for a cube. Text measures are enabled by default for an outline, but to use them, you must follow the implementation steps in either the Essbase web interface or the application workbook you use to build a cube.

Text Measures Workflow in Essbase web interface

The following workflow uses the Essbase web interface.

1. Create a measure in the Accounts dimension that will be the textual measure. In the member properties, set its consolidation operator to ^ (no consolidation).

   For example, the following textual measure, Answer, is used in the Facility Rating cube, which you can download from the Files catalog under All Files > Gallery > Applications > Facility Rating.

   
   
   
   
   

2. From the outline properties, go to edit mode and add a text list object to store the available text values for the textual measure. Map each text value to a numeric value, so that Essbase can work with the text values. Also map Missing and Out of Range to numeric values.

   1. Open the cube outline in the Essbase web interface.

   2. Click Edit, and then click Outline Properties.

   3. Click the Textual Measures tab.

   4. Enter a name for a text list object and click Add.

      For example, a text list object named ResponseValues is used in the Facility Rating cube.

      
      
      
      
      

      Essbase automatically adds IDs for handling missing and out of range values.

      
      
      
      
      

   5. Edit the missing and out of range ID and mapping name as needed. Add additional numeric values and map them to text values. Each numeric value can have only one text value mapped to it.

      For example, the text list object named ResponseValue contains numeric values mapped to text values; these are used for survey response answers in the Facility Rating cube.

      
      
      
      
      

      Optionally, you can auto generate the IDs, selecting an increment, as in the example below. There is no need for the IDs to be in any particular order. Don't forget to assign names to each ID.

      
      
      
      
      

   6. Click Apply and Close to save the text list object with the outline.

3. Still in outline editing mode, return to the textual measure in the Accounts dimension (in our example, the member named Answer) and click Inspect. In the member properties, define the measure as type Text, associated with the name of the text list object you created.

   
   
   
   
   
4. Click Apply and Close to save the type setting for the textual measure.
5. Save the outline.

Text Measures Workflow using Application Workbooks

As an alternative to using the Essbase web interface, you can also build a cube with a textual measure implementation from an application workbook.

To do this, include a Cube.TypedMeasures worksheet in the workbook you use to build the cube. In the Cube.TypedMeasures worksheet, fill out what you want to use for the text list name (in this example, ResponseValues), provide the associated textual measure member name (in this case, Answer), and define the text list object (the section that maps IDs to text values).

As an easy way to implement text measures from your application workbook,

1. Download the Facility Rating gallery template workbook, available from the Files catalog under All Files > Gallery > Applications > Facility Rating.

2. Copy the Cube.TypedMeasures worksheet from the template into your own application workbook.

3. Make modifications as needed to the values for List Name, Associated Members, IDs, and their associated text values.

4. Build the cube from the workbook, either by importing it or using Cube Designer.

See also: Performing Database Operations on Text and Date Measures.

Text List Objects and Text List Members

A cell that corresponds to a text measure can have one of a finite list of up to 1024 valid text values. Internally, Essbase needs to store the text values as numbers. Therefore, a mapping of text values to numeric values is required. You define the mapping between the text and numeric values by creating a text list object. A text list object consists of a list of text values and a numeric value that corresponds to each text value.

For example, you can create a text list object called “Customer Satisfaction Level” with the contents shown below. The Name column contains the possible text values for a text measure, and the ID column represents the internal numeric value used by Essbase.

Table 8-1 Example of a Text List Object

Name	ID
Missing	#MISSING
N/A	#OUTOFRANGE
High	1
Medium	2
Low	3

Each text value must map to a unique numeric value. Any text value that does not map to an integer in the text list object is considered by Essbase to be invalid.

The first two IDs, #MISSING and #OUTOFRANGE, are for handling cases where the textual data is invalid or empty. For example, if a user attempted to load an unmapped value such as “Average” to a text measure, the cell value would not be updated, and would display as #MISSING in a subsequent query. If a user loads a numerical cell value which is unmapped, the subsequent query would return N/A.

Aside from #MISSING and #OUTOFRANGE , all of the other IDs must be integers.

The internal numeric value of the "#OutOfRange" ID is the constant -0.000000000000011.