About
Double column support is the ability in the logical layer to associate two columns:
- a descriptor or display column. It contains the display/description values. For example, the description of an item.
- a descriptor ID or code column. It contains code values that uniquely identify display values and are consistent across users or locales, For example, the code column 1 uniquely identify my first item, 2 my second item.
Articles Related
Advantages
The double column feature lets you:
- define language independent filters.
- change display values without breaking existing analyses
- deal with queries that involve LOB data types
Build internationalized analyses
When multilingual columns are based on a lookup function, it is common to specify the non-translated lookup key column as the descriptor ID column of the translated column. Assigning a descriptor ID column enables Double Column Support, a feature which helps in defining language-independent filters. For example, in Answers, users see the display column, but the query filters on the hidden descriptor ID column.
Build internationalized analyses that are automatically filtered by language independent codes. (In previous releases from 11g, filters were not language independent.) For example, you can build one filtered analysis that can be used by both French-language users and English-language users.
Build analyse independent from the display value
The double column support permit you to change the display values without breaking existing analyses.
For example, suppose that you have an analysis that is filtered on the display value Excellent. If it is a double column, then the filtering will be performed on its code column with the code value for “Excellent” rather than the display value of “Excellent.” This means that you can change the display value Excellent to Superb without breaking the existing analysis.
Deal with LOB datatype
The double column support permit you to deal with queries that involve LOB data types such as CLOBs and BLOBs and aggregate functions such as COUNT or SUM. Some data sources do not allow LOB columns to be used in the GROUP BY clause. So, instead of adding the LOB column to the GROUP BY, it is necessary to group by some other column that has a one-to-one relationship with the LOB column and then in join the LOB column after the aggregates have been computed.
Filter
If your repository is configured for double columns, and you are creating the filter on a display column and select the:
- contains all,
- contains any,
- does not contain,
- begins with,
- ends with,
- is LIKE (pattern match),
- or is not LIKE (pattern match)
operator, then filtering will always be done by display values,not code values.
Select by Code Column
If your repository is configured for double columns, and you are creating the filter on a display column, then by default you specify display values. However, if your organization allows the display of code values, then you can specify code values rather than display values, but only if you use one of the following operators:
- is equal to / is in
- is not equal to / is not in
- is between
To specify code values, select the Select by Code Column check box. This box allow code values to be specified in the Value field.
When this box is:
- selected, the list box displays both the code values and the display values (for example, 1 - Beverages) for selection and the Filter by box is selected but disabled.
- deselected, the list box displays only the display values (for example, Beverages).
Filter by Code Column
Available only if your repository is configured for double columns and you selected either the:
- is equal to /is in,
- is not equal to / is not in,
- or is between
operator.
This box specifies whether the column is to be filtered by code values or display values.
When this box is:
- selected, you filter by code values.
- deselected, you filter by display values.
Prompt
Logical SQL
DESCRIPTOR_IDOF
After having setting a descriptor id (for instance for Week Code), when you choose the column, Analytic add the column but also the DESCRIPTOR_IDOF function in the logical SQL.
"Dim Date"."Week Code"
DESCRIPTOR_IDOF("Dim Date"."Week Code")