Search Results bis_levels_u1

Overview

BIS.BIS_LEVELS is a Business Intelligence System (BIS) configuration table in Oracle E-Business Suite 12.1.1 and 12.2.2 that defines the levels along which data can be grouped within a given analytical dimension. Each row represents one level — for example, "month", "quarter", and "year" within the Time dimension, or "city", "state", and "country" within the Geography dimension. The table acts as the metadata backbone for dimensional partitioning: it tells the BIS engine which source views supply level data, how each level is labelled for end-user navigation, and whether the level is exposed in design-time tools.

From a modeling perspective, the heuristic Data Vault classification is standalone; despite this, the presence of a foreign key from LEVEL_ID to MSD_LEVELS and the strong business keys (LEVEL_ID, SHORT_NAME) suggest the table could alternatively be modelled as a hub surrounded by reference satellites for its descriptive attributes such as SOURCE, COMPARISON_LABEL_CODE, and ENABLED.

Key Information Stored

The most significant columns in BIS_LEVELS are:

• LEVEL_ID (NUMBER) — Surrogate primary key and the column enforced by unique index BIS_LEVELS_U1. The FK relationship to MSD_LEVELS is defined on this column.
• SHORT_NAME (VARCHAR2 30) — Business-key candidate protected by unique index BIS_LEVELS_U2; the user-facing short label for the level.
• DIMENSION_ID (NUMBER) — Identifies the dimension (Time, Geography, Product, etc.) to which this level belongs.
• LEVEL_VALUES_VIEW_NAME (VARCHAR2 30) — The source view that supplies level values; indexed non-uniquely via BIS_LEVELS_N1.
• VIEW_OBJECT_NAME (VARCHAR2 255) — The underlying view object used to resolve the level's data.
• SOURCE (VARCHAR2 30) — Indicates the source system type, such as OLTP or EDW.
• COMPARISON_LABEL_CODE and ATTRIBUTE_CODE (VARCHAR2 30 each) — Codes used for comparison labelling and attribute mapping.
• DEFAULT_VALUES_API, DEFAULT_SEARCH, and LONG_LOV — Control default retrieval behaviour and list-of-values presentation.
• MASTER_LEVEL, ENABLED, HIDE_IN_DESIGN, and DRILL_TO_FORM_FUNCTION — Govern the level's prominence in BI design tools, enablement status, and drill-through navigation.
• APPLICATION_ID (NUMBER) — Owning application identifier.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns referencing FND_USER and FND_LOGINS.

The legacy WHERE_CLAUSE column (VARCHAR2 2000) is no longer used. The table stores in tablespace APPS_TS_TX_DATA with PCTFREE 10.

Common Use Cases and Queries

Typical usage includes BI report enumeration, dimensional partitioning validation, and impact analysis before level changes.

• List enabled levels for a dimension: SELECT level_id, short_name, source FROM bis.bis_levels WHERE dimension_id = :p_dim AND enabled = 'Y' ORDER BY short_name;
• Resolve a level's source view: SELECT short_name, level_values_view_name FROM bis.bis_levels WHERE short_name = :p_name;
• Detect orphaned or duplicate business keys: join on SHORT_NAME and verify the BIS_LEVELS_U2 unique index is intact.
• Drill-through diagnostics: identify levels configured with DRILL_TO_FORM_FUNCTION or MASTER_LEVEL set.

Related Objects

• BIS.MSD_LEVELS — Referenced via BIS.BIS_LEVELS.LEVEL_ID → MSD_LEVELS; the parent level definition object.
• BIS.BIS_DIMENSIONS — Joined by DIMENSION_ID to identify the owning dimension.
• BIS.BIS_LEVEL_VALUES (or the dynamic level-values view named in LEVEL_VALUES_VIEW_NAME) — Supplies actual level member data.
• FND_USER — Referenced by CREATED_BY and LAST_UPDATED_BY.
• FND_LOGINS — Referenced by LAST_UPDATE_LOGIN.
• FND_APPLICATION — Resolved via APPLICATION_ID.
• Level-level design tools read VIEW_OBJECT_NAME and DEFAULT_VALUES_API for runtime rendering.

Because the table is standalone in Data Vault terms, referential enforcement is limited to the LEVEL_ID link into MSD_LEVELS; all other relationships are logical joins driven by DIMENSION_ID, APPLICATION_ID, and the WHO columns.