Working with Typed Measures

In This Section:

Working with Text Measures

Working with Date Measures

Performing Database Operations on Text and Date Measures

Working with Format Strings

Typed measures extend the analytical capabilities of Essbase. In addition to numeric values, measures can also be associated with text- or date-typed values.

Text measures are tagged as “text” in whichever dimension measures are represented. They enable cell values to contain one of an enumerated list of text labels. These labels are defined, at the outline level, using a mapping artifact called a text list.

Date measures are tagged as “date” in the dimension where measures are represented. Date measures enable cell values in the form of a formatted date.

The following general guidelines apply to both text and date measures:

Working with Text Measures

Text Measures Overview and Workflow

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.

Use the following workflow to enable and use text measures:

  1. In the outline properties, enable typed measures.

  2. Create a text list object to store the available text values and map each text value to an ordinal number, so that Essbase can work with the text values. Optionally, map Missing and Out of Range to ordinal numbers.

    Note:

    Each numeric value can have only one text value mapped to it.

  3. Create a text measure in the outline (in the Accounts dimension), and in the member properties,

    1. define it as type Text

    2. associate it with the text list object

For more information about working with text measures, see 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 following contents:

Table 30. 

NameID
Missing#MISSING
N/A#OUTOFRANGE
High1
Medium2
Low3

The Name column contains the possible text values for a text measure, and the ID column represents the internal numeric value used by Essbase.

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.

Working with Date Measures

Date Measures Overview

Date measures enable members to be associated with date-type values. The ability to process dates in the measures dimension can be useful for types of analysis that are difficult to represent using the Time dimension.

For example, an application that analyzes asset depreciation may need to track acquisition dates for a series of capital assets. The company has been in business for fifty years, so the acquisition dates span too large a period to allow for feasible Time dimension modeling (the Time dimension only covers five years).

In addition to their ability to represent values spanning large time periods, date measures can also capture date values with smaller granularity than is captured in the Time dimension, such as hours and minutes.

Implementing Date Measures

Date measures are supported for aggregate and block storage databases. You create date measures at the database level.

Use the following workflow to enable and use date measures:

  1. In the outline properties, enable typed measures.

  2. In the outline properties, select a date format (for example, yyyy-mm-dd). All date measures in the outline must use the same format.

  3. Create a date measure in the outline (in the Accounts dimension), and in the member properties, define it as type Date.

For example, in ASOSamp.Sample, you can enable typed measures for the outline, select a date format, and add a measure named IntroDate defined as type Date.

The date values are stored internally as numeric values, although you load them into Essbase as formatted date strings. When queried, date measures are displayed according to the selected date format.

For more information about working with date measures, see Performing Database Operations on Text and Date Measures.

Functions Supporting Date Measures

The following MDX functions are useful for calculations based on date measures.

  • DateDiff

  • DatePart

  • DateRoll

  • FormatDate

  • GetFirstDate

  • GetLastDate

  • ToDate

  • ToDateEx

  • Today

The following calculation functions are useful for calculations based on date measures.

  • @DATEDIFF

  • @DATEPART

  • @DATEROLL

  • @FORMATDATE

  • @TODATEEX

  • @TODAY

The DATEFORMAT Report Writer command

For information about these functions and commands, see the Oracle Essbase Technical Reference.

Performing Database Operations on Text and Date Measures

This section explains how to perform common database operations when using 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 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.

  • Use a single load buffer to load all values associated with date/text measures.

  • Use the Aggregate_use_last aggregation method.

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

Aggregating Text and Date Measures

By default, text measures are assigned the “^” (no aggregation symbol) as the default operator. Text and date measures are not aggregated to higher level members along non Accounts dimensions.

If you tag a text or date measure with an operator other than “^”, it will be aggregated along other dimensions based on its internal numeric value. This is not recommended for aggregate storage databases, because only the + operator is supported, 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 aggregation.

For block storage databases, you can write calculation scripts that aggregate text measures in a custom fashion. You might want to aggregate 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 aggregate 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, Grid API clients, Report Writer, and MDX. The corresponding cells are displayed with the appropriate text values or formatted date values.

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

The MDX function EnumValue and the calculation function @ENUMVALUE are designed specifically for getting the text 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 text value of a member rather than its internally stored numeric value. For more information about these functions, see the Oracle Essbase Technical Reference

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

Working with Format Strings

Overview of Format Strings

Using format strings, you can format the values (cell contents) of Essbase database members in numeric type measures so that they appear, for query purposes, as text, dates, or other types of predefined values. The resultant display value is the cell’s formatted value (FORMATTED_VALUE property in MDX).

The underlying real value is numeric, and this value is unaffected by the associated formatted value. Format strings enable you to display more meaningful values in place of raw numeric values. For example, using a text based formatted value, you might display data cells as “High,” “Medium,” and “Low.”

Text and date type values are additionally supported using the built-in text and date measure types. Format strings add more flexibility to your implementation, in that you can apply format strings to members in multiple dimensions, whereas with text and date measures, you can only apply one or the other to a single measures dimension. You can apply format strings to numeric dimensions; you do not have to type the dimension as text or date.

Format strings can be applied to either aggregate storage or block storage databases.

Format strings can be defined on the following members:

  • All members in Measures dimension

  • Members associated with explicit formula strings on other dimensions

Implementing Format Strings

Format strings are supported for aggregate and block storage databases. You implement format strings at the database level.

Use the following workflow to enable and use format strings:

  1. In the outline properties, enable typed measures.

  2. In the Accounts dimension, create a measure whose members you want to format, and in its member properties, edit the Associate Format String field to create an MDX format directive. For the syntax to create an MDX format directive, see MDX Format Directive.

MDX Format Directive

A format string is defined by the following syntax:

format_string_expression = MdxFormat ( string_value_expression )

where string_value_expression is a valid MDX string value expression as described in the MDX specification documented in theOracle Essbase Technical Reference.

Most MDX expressions can be used to specify format strings; however, format strings cannot contain references to values of data cells other than the current cell value being formatted. The current cell value can be referenced using the MDX CellValue function.

Essbase treats members with invalid format strings as if there is no format string defined. Outlines can be saved with invalid format strings. Essbase generates a warning if a query consists of a member with an invalid format string.

If a member is not associated with a format string, default format rules are applied. The default format rules format a cell based on whether the measure is numeric, text, or date type. For numeric measures, the default formatted value is the text version of that number. For text measures, the default formatted value is the text value based on the associated text list object. For date values, the default format is a date string formatted according the date format string defined in the outline properties.

Functions Supporting Format Strings

The following MDX functions can be useful when applying format strings to a measure. Format strings are applied as MDX expressions, both in aggregate and block storage databases.

  • EnumText, returns the text list label associated with the internal numeric value.

  • EnumValue, returns the internal numeric value for a text list label.

  • CellValue, returns the internal numeric value of the current cell.

  • NumToStr, converts a value to a decimal string.

The @ENUMVALUE calculation function can be useful when writing calculation scripts for a block storage database that has text measures or format strings. This function returns the text list label associated with the internal numeric value.

The MaxL alter session set dml_output statement has a clause set formatted_value on | off. By default, formatted values are displayed in queries, but this statement can be used to turn off the display of formatted values.

The OUTFORMATTEDVALUES Report Writer command returns formatted cell values in a report.

For information about these functions, commands, and statements, see the Oracle Essbase Technical Reference.

Limitations of Format Strings

Format strings are not supported across partitions.

Shared members and implied shared members cannot have separate format strings: they inherit the format string of the original member.