Results for “bsc_db_tables_cols”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BSC_DB_TABLES_COLS is a metadata repository table within the Oracle E-Business Suite Balanced Scorecard (BSC) module. It stores the column-level definition of data tables registered in the BSC analytical environment. In Oracle EBS 12.1.1 and 12.2.2, the BSC module provides performance management and scorecard functionality, and its underlying architecture relies on a set of registration tables that describe which database tables and columns are available for scorecard definitions, data loads, and validation processing. BSC_DB_TABLES_COLS serves as the child of BSC_DB_TABLES, capturing each column that belongs to a registered data table.

From a Data Vault modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification. The table participates in a one-to-many relationship with BSC_DB_TABLES, meaning it enriches a parent table entity with descriptive column attributes rather than acting as an independent hub or a many-to-many link. This classification is a heuristic suggestion derived from the FK topology, not a documented architectural mandate.

The object resides in the BSC schema and is marked VALID. It is not a transactional table; it is a configuration and metadata table, which means its row count is typically small and stable relative to business data tables, and changes to it are usually driven by setup or customization activity rather than day-to-day transactions.

Key Information Stored

The documented physical schema exposes seven columns: TABLE_NAME, COLUMN_TYPE, COLUMN_NAME, PROJECTION_ID, SOURCE_COLUMN, SOURCE_FORMULA, and SOURCE. The primary key, BSC_DB_TABLES_COLS_PK, is defined on the composite of COLUMN_NAME and TABLE_NAME. This composite key confirms that a column is uniquely identified only within the context of its owning table, which is a natural business-key structure for a table-columns registry.

• TABLE_NAME — Identifies the registered data table to which the column belongs. This column is also the foreign key to BSC_DB_TABLES, anchoring each column row to its parent table definition.
• COLUMN_NAME — The name of the registered column within the data table. Together with TABLE_NAME it forms the primary key.
• COLUMN_TYPE — A classification of the column, used by the BSC engine to distinguish behavioral or functional column roles. It participates in the unique index BSC_DB_TABLES_COLS_U1.
• PROJECTION_ID — A reference identifier linking the column to a projection definition used in scorecard queries or data retrieval.
• SOURCE_COLUMN — The originating physical column from which this registered column is derived, supporting column mapping between source and presentation layers.
• SOURCE_FORMULA — A stored expression or formula used to compute the column value when it is derived rather than directly sourced.
• SOURCE — Indicates the origin or sourcing mechanism for the column definition.

A secondary unique index, BSC_DB_TABLES_COLS_U1 on TABLE_NAME, COLUMN_TYPE, and COLUMN_NAME, serves as an alternate business-key candidate, enforcing uniqueness of a column name per table per column type. No standalone surrogate primary key column is documented; the design relies on natural composite keys.

Common Use Cases and Queries

Typical use cases center on introspection of the BSC data model, validation of registered tables and columns before scorecard deployment, and impact analysis when custom columns are added or modified. Administrators and developers query this table to determine which columns are available for a given registered table, and to trace column derivations through SOURCE_COLUMN and SOURCE_FORMULA.

A representative query retrieves all columns for a specified data table:

• SELECT COLUMN_NAME, COLUMN_TYPE, SOURCE_COLUMN, SOURCE_FORMULA FROM BSC.BSC_DB_TABLES_COLS WHERE TABLE_NAME = :p_table ORDER BY COLUMN_NAME;

To find derived columns defined by formula, filter on the presence of SOURCE_FORMULA. To reconcile registrations against the actual database catalog, join to ALL_TAB_COLUMNS on TABLE_NAME and COLUMN_NAME. Reporting use cases include generating a data dictionary of BSC-registered tables, auditing column-type coverage across tables, and identifying tables referenced by BSC_DB_VALIDATION that depend on specific registered columns.

Related Objects

The most significant related objects are those connected through documented foreign-key relationships.

• BSC_DB_TABLES — The parent table definition registry. Joined via BSC_DB_TABLES_COLS.TABLE_NAME = BSC_DB_TABLES.TABLE_NAME. One parent table has many column rows.
• BSC_DB_VALIDATION — References this table through INPUT_TABLE_NAME and COLUMN_NAME. Validation rules are defined against registered tables and columns, so BSC_DB_VALIDATION.INPUT_TABLE_NAME relates to BSC_DB_TABLES_COLS.TABLE_NAME and BSC_DB_VALIDATION.COLUMN_NAME relates to BSC_DB_TABLES_COLS.COLUMN_NAME.
• BSC_DB_TABLES_COLS_PK — The primary key constraint over COLUMN_NAME and TABLE_NAME.
• BSC_DB_TABLES_COLS_U1 — The unique index over TABLE_NAME, COLUMN_TYPE, and COLUMN_NAME.

Because BSC_DB_VALIDATION both depends on this table and references BSC_DB_TABLES, the three tables together form the core registration and validation metadata layer of the Balanced Scorecard module. Any change to a registered column may cascade into validation logic and, consequently, into scorecard data processing behavior.