Search Results value_order_by




Overview
Explore in AI Chat

Overview

BSC.BSC_SYS_DIM_LEVELS_B is a foundational configuration table within the Oracle EBS Balanced Scorecard (BSC) module, storing metadata that describes the dimensional levels available for analytical reporting, KPI modeling, and graph generation. Each row defines a single dimension level — for example a customer, product, or time level — and captures the physical table and primary key column that materialize that level, along with presentation attributes such as abbreviations, ordering, and key sizing. In Oracle EBS 12.1.1 and 12.2.2 the table resides in the BSC schema, is stored in the APPS_TS_TX_DATA tablespace with PCT Free 10, and holds a status of VALID. It carries the FND Design Data reference BSC.BSC_SYS_DIM_LEVELS_B, confirming it is a registered, supported application object.

From a Data Vault modeling perspective, the mined relationship structure classifies this object as hub-leaning. Its unique primary key on DIM_LEVEL_ID and its role as the referenced parent for numerous dependent tables suggest it functions as a durable business key registry — a natural hub for dimension level identities — rather than a transactional or purely descriptive satellite. This is a modeling suggestion only; the physical implementation is a conventional relational table with standard Who columns.

Key Information Stored

The table's surrogate primary key is DIM_LEVEL_ID, enforced by the unique index BSC_SYS_DIM_LEVELS_B_U1 on the APPS_TS_TX_IDX tablespace. This identifier is also the sole documented business-key candidate. The column that most directly answers the search term comp_order_by is COMP_ORDER_BY, a NUMBER column documented as "Graph item order," which governs the sequence in which graph or chart items are rendered. It is distinct from VALUE_ORDER_BY, documented as "Level values order," which controls the ordering of the level's values themselves.

The remaining significant columns include: LEVEL_TABLE_NAME (the physical dimension level table); LEVEL_PK_COL (its primary key column); TABLE_TYPE (0 = normal, 1 = system table); ABBREVIATION (used to generate dump values); SHORT_NAME and SOURCE (indexed together via BSC_SYS_DIM_LEVELS_B_N2); CUSTOM_GROUP (handling of custom groups); USER_KEY_SIZE and DISP_KEY_SIZE (CODE_USER and NAME lengths); the EDW_FLAG, EDW_DIM_ID, EDW_DIM_LEVEL_ID, and EDW_DIM_LEVEL_NAME columns that mark and identify Enterprise Data Warehouse dimensions; and LEVEL_VIEW_NAME. Standard Who columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) provide audit tracking.

Common Use Cases and Queries

Typical use cases include resolving a dimension level's physical table and key column for dynamic SQL generation, ordering graph elements in Balanced Scorecard dashboards, and filtering levels by EDW status. A representative query to inspect graph ordering is:

  • SELECT dim_level_id, level_table_name, abbreviation, value_order_by, comp_order_by FROM bsc.bsc_sys_dim_levels_b WHERE comp_order_by IS NOT NULL ORDER BY comp_order_by;
  • Joining to the translation table for descriptive names: SELECT b.dim_level_id, t.dim_level_name, b.level_table_name FROM bsc.bsc_sys_dim_levels_b b, bsc.bsc_sys_dim_levels_tl t WHERE b.dim_level_id = t.dim_level_id AND t.language = USERENV('LANG');
  • Identifying EDW-enabled levels: SELECT dim_level_id, edw_dim_level_name FROM bsc.bsc_sys_dim_levels_b WHERE edw_flag = 1;

Related Objects

BSC_SYS_DIM_LEVELS_B is referenced by multiple dependent tables through DIM_LEVEL_ID and, in some cases, PARENT_DIM_LEVEL_ID:

These dependencies confirm the table's role as the central hub for dimension level metadata across the Balanced Scorecard and ETRM analytical stack.