Descriptor IDs for Datasets Created from Local Subject Areas

In Oracle Analytics, for a dataset created from a local subject area, the descriptor ID relationship is defined in the semantic model or deployed RPD.

For example, the semantic model can define PROD_ID as the descriptor ID for the PRODUCT_NAME descriptor column. When you create a dataset from the local subject area, Oracle Analytics exposes this relationship in the dataset. This behavior preserves the double-column capability already available in the semantic model. You don't need to redefine the relationship in the dataset when the semantic model or deployed RPD already provides it. See Add Double Column Support.

To define a descriptor ID for a descriptor column in the Semantic Modeler, see Add Double Column Support.

Example: Filter Unit Cost by Product Name

Suppose that a workbook displays Unit Cost and filters the results by these product names:
  • Aurora Pro Noise-Canceling Headphones
  • Nimbus 14 Ultralight Laptop

The workbook continues to display the product names in the filter. However, when PRODUCT_NAME has PROD_ID as its descriptor ID, Oracle Analytics can filter the cost data directly by product ID.

For example, the generated query can use a predicate similar to:

SELECT PROD_ID, UNIT_COST FROM SH.COSTS WHERE PROD_ID IN (14, 138)

Without a descriptor ID, Oracle Analytics must first identify the product IDs that correspond to the selected product names and then join those results to the cost data. The query can require logic similar to:

SELECT SUM(C.UNIT_COST) FROM SH.COSTS C JOIN SH.PRODUCTS P ON C.PROD_ID = P.PROD_ID WHERE P.PROD_NAME IN ( 'Aurora Pro Noise-Canceling Headphones', 'Nimbus 14 Ultralight Laptop' )

Both queries return Unit Cost for the selected products. The first query can apply the product-ID filter directly to SH.COSTS. The second query accesses SH.PRODUCTS and joins it to SH.COSTS to resolve the displayed product names to product IDs.

Performance Considerations

Using a descriptor ID can improve performance because Oracle Analytics can:
  • Filter source or fact data by a compact identifier instead of a descriptor value.
  • Avoid a dimension-table access and join when the join is required only to resolve a descriptor value to its identifier.
  • Apply selective predicates earlier in query processing.
  • Use the descriptor ID for partitioning or grouping instead of a potentially wide text value.
  • Take advantage of indexes or partitioning on the identifier column when they are available in the data source.

Performance improvement depends on the dataset, data source, and query. The benefit is typically greatest when a workbook filters a large source table by a selective descriptor column, such as a product, customer, or location name.

Key Takeaways

Use a descriptor ID only when the identifier correctly represents the descriptor value.
  • The descriptor ID column must identify descriptor-column values consistently.
  • If the same descriptor value can represent multiple identifiers, ensure that the relationship preserves all valid values.
  • If descriptor values are localized or can change, use a stable identifier column as the descriptor ID. For multilingual data, this enables language-independent filtering while users continue to see translated descriptor values.
  • If you don't specify a descriptor ID, Oracle Analytics continues to filter by the descriptor column and uses the existing query behavior.