Search Results edw_level_table_atts_md

Overview

The EDW_LEVEL_TABLE_ATTS_MD table is a metadata repository object owned by the BIS (Business Intelligence System / Applications BIS) schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. Its name follows the Enterprise Data Warehouse (EDW) metadata naming convention, and the table stores descriptive attributes that define how dimensional level tables are exposed within the EBS analytics and reporting layer. In practice, this table acts as a configuration and mapping service for level-based dimension structures, associating each dimensional level with its physical level table, the columns that comprise it, and the key definitions applied to those columns.

Based on the heuristic Data Vault classification mined from the foreign key structure, this table is best modeled as a standalone reference or configuration table rather than a hub, link, or satellite. It carries a single documented foreign key to MSD_LEVELS via LEVEL_ID, which anchors each attribute row to a defined level in the multidimensional schema. Because it is standalone in the mined classification, it should be treated as a descriptive metadata store that supports the EDW abstraction rather than as a transactional entity participating in a star or Data Vault integration model.

Key Information Stored

The documented 12.1.1 schema for BIS.EDW_LEVEL_TABLE_ATTS_MD contains 14 columns. The most significant are summarized below.

• DIM_ID / DIM_NAME — Identify the dimension to which the level table attribute belongs, linking the metadata to a specific warehouse dimension.
• LEVEL_TABLE_ID / LEVEL_TABLE_NAME — The surrogate and business identifiers for the physical level table being described.
• LEVEL_PREFIX — The naming prefix applied to columns or objects generated from this level, used in dynamic SQL and column resolution logic.
• LEVEL_TABLE_COL_ID / LEVEL_TABLE_COL_NAME / LEVEL_TABLE_COL_LONG_NAME — Describe the specific column of the level table, including its short name and long descriptive name used in user-facing metadata.
• KEY_TYPE — Classifies the key role of the column (for example, primary, foreign, or surrogate key semantics) within the level table.
• KEY_ID / KEY_NAME — Identify the key definition and its business name, enabling the reporting layer to recognize key columns.
• LEVEL_ID / LEVEL_NAME / LEVEL_LONG_NAME — Describe the dimensional level itself. LEVEL_ID is the documented foreign key candidate to MSD_LEVELS, making it the principal business-key candidate for joining to level definitions.

The surrogate identifier for this metadata record is represented by the combination of the level table and column identifiers (LEVEL_TABLE_COL_ID in conjunction with LEVEL_TABLE_ID). No standalone single-column primary key is documented, so uniqueness is effectively enforced by the composite of the level table, column, and dimension identifiers.

Common Use Cases and Queries

This table is typically queried when building or debugging EDW abstraction layers, generating dynamic SQL against level tables, or auditing dimension metadata for consistency. A representative join back to the level definition master is shown below.

• Metadata discovery: listing all columns defined for a given level table.
  SELECT LEVEL_TABLE_NAME, LEVEL_TABLE_COL_NAME, KEY_TYPE FROM BIS.EDW_LEVEL_TABLE_ATTS_MD WHERE DIM_NAME = :dim;
• Level-to-column resolution: joining to MSD_LEVELS.
  SELECT a.LEVEL_NAME, a.LEVEL_TABLE_COL_NAME FROM BIS.EDW_LEVEL_TABLE_ATTS_MD a, MSD_LEVELS l WHERE a.LEVEL_ID = l.LEVEL_ID;
• Key enumeration: identifying which columns are treated as keys for a level.
  SELECT LEVEL_TABLE_NAME, KEY_NAME, KEY_TYPE FROM BIS.EDW_LEVEL_TABLE_ATTS_MD WHERE KEY_TYPE IS NOT NULL;

Typical reporting consumers include EDW load and abstraction programs, dimension build utilities, and BI metadata browsers that must translate logical level definitions into physical column mappings.

Related Objects

• MSD_LEVELS — Referenced through EDW_LEVEL_TABLE_ATTS_MD.LEVEL_ID → MSD_LEVELS.LEVEL_ID; provides the authoritative level definition for each attribute row.
• EDW_LEVEL_TABLES_MD — The companion level-table metadata repository, related by LEVEL_TABLE_ID and DIM_ID.
• EDW_DIMENSIONS_MD — Dimension-level metadata joined through DIM_ID and DIM_NAME.
• EDW_LEVEL_TABLE_COLS_MD — Column-level metadata aligned through LEVEL_TABLE_COL_ID.
• MSD_DIMENSIONS — Dimension master that contextualizes the DIM_ID values used here.

Collectively these objects form the metadata backbone that allows the BIS analytics layer to present EBS data through a governed, level-based dimensional model.