Search Results edw_levels_md

Overview

EDW_LEVELS_MD is a metadata master table owned by the BIS schema within Oracle EBS Applications BIS. It stores the definition of hierarchical "levels" used by the Enterprise Data Warehouse (EDW) and Oracle Business Intelligence (OBIEE / Daily Business Intelligence) layer. Each row describes a single analytic level — a named, reusable aggregation tier such as a period, region, organization, or account grouping — that downstream warehouse dimensions reference when building level-based hierarchies. The table is therefore a supporting metadata object for the dimensional model rather than a transactional or fact table.

The mined Data Vault classification for EDW_LEVELS_MD is standalone. Treated as a modeling suggestion, this indicates the table behaves as a reference or hub-style entity: it holds a primary key (LEVEL_ID) and descriptive attributes with no upward dependency on other links or satellites in the vault structure. The single foreign key it carries points outward rather than inward, reinforcing its role as a defining lookup source.

Key Information Stored

The table documents nine columns in Oracle EBS 12.1.1. The most significant are:

• LEVEL_ID — The surrogate primary key identifying each analytic level, and the foreign key target of dependent objects (it references MSD_LEVELS, anchoring the EDW level definitions to the Oracle Sales / Marketing foundation level set).
• LEVEL_NAME — The business-facing short name of the level, used as the display label in hierarchy pickers and reports.
• LEVEL_LONG_NAME — The extended, descriptive name of the level for user-facing presentation.
• LEVEL_PREFIX — A short token used to construct or disambiguate level-based column and hierarchy identifiers.
• LEVEL_TABLE_ID — Identifies the physical or logical table that materializes the level's members.
• LEVEL_TABLE_NAME — The name of the corresponding level table, allowing metadata-driven query generation.
• DIM_ID — The surrogate identifier of the dimension to which the level belongs, linking level metadata to the dimension definition.
• DIM_NAME — The dimension name associated with the level, providing a denormalized readability aid.
• DESCRIPTION — Free-text explanation of the level's business meaning and intended use.

The metadata does not document a separate unique business-key index; LEVEL_ID serves as the effective business and technical key, with LEVEL_NAME and DIM_ID acting as the principal qualifying attributes.

Common Use Cases and Queries

The primary use case is metadata-driven reporting: BI tools and custom extract programs read EDW_LEVELS_MD to discover which levels exist, which dimension they belong to, and which physical table backs them. A typical lookup enumerates the levels for a dimension:

SELECT l.level_id, l.level_name, l.level_long_name,
       l.level_table_name, l.dim_name
  FROM   bis.edw_levels_md l
 WHERE  l.dim_id = :p_dim_id
 ORDER  BY l.level_name;

A second pattern joins to the source level foundation to reconcile EBS level definitions with the EDW catalog:

SELECT m.level_id, m.level_name, s.level_id AS msd_level_id
  FROM   bis.edw_levels_md m,
         msd_levels        s
 WHERE  m.level_id = s.level_id;

Additional scenarios include generating dynamic SQL that resolves a level to its backing table via LEVEL_TABLE_NAME, validating that a requested hierarchy level is registered before a report is launched, and auditing which dimensions (DIM_NAME) expose which levels for data governance and lineage reviews.

Related Objects

The FK metadata identifies one direct reference: MSD_LEVELS, joined on EDW_LEVELS_MD.LEVEL_ID = MSD_LEVELS.LEVEL_ID, which supplies the foundational level definition. Beyond this documented relationship, the following objects are significant in practice because they consume level metadata or share the dimension/level model:

• MSD_LEVELS — Parent level-definition table; principal join partner.
• EDW_DIMENSIONS_MD — Dimension metadata joined on DIM_ID / DIM_NAME to resolve level-to-dimension hierarchies.
• EDW_HIERARCHIES_MD — Hierarchy metadata that consumes levels when composing roll-up paths.
• EDW_DIM_MEMBERS_MD — Member metadata resolved against LEVEL_TABLE_NAME for member expansion.
• MSD_DIMENSIONS — Source dimension registry underpinning DIM_ID.
• BIS_EDW_* staging and collection tables — ETL objects that read level definitions when populating the warehouse.

Because EDW_LEVELS_MD is classified as standalone, no dependent link or satellite tables are modeled beneath it; changes to its rows propagate directly to any metadata-driven consumer.