Search Results bsc_db_tables

Overview

The BSC_DB_TABLES table is a core metadata repository within the now-obsolete Balanced Scorecard (BSC) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It functioned as a central registry for defining and managing the physical database tables used to store performance measurement data, such as Key Performance Indicator (KPI) values. This table enabled the BSC application to abstract and govern the relationships between its logical data models and the underlying physical database structures, facilitating data loading, calculation, and reporting processes. The ETRM metadata explicitly notes this table as "Not implemented in this database," indicating it may not be present in a standard installation or may have been deprecated in favor of other structures in later releases of the obsolete module.

Key Information Stored

Based on the provided ETRM documentation, the primary data element stored in BSC_DB_TABLES is the TABLE_NAME, which serves as the table's primary key. This column would hold the name of a registered physical database table. A critical foreign key relationship is defined via the PERIODICITY_ID column, which links to the BSC_SYS_PERIODICITIES table. This relationship associates each data table with a specific time frequency (e.g., monthly, quarterly), which is fundamental for time-series analysis in a scorecard. While other column specifics are not detailed in the excerpt, the table's role suggests it likely contained metadata such as a description, refresh flags, or status indicators to manage the data lifecycle.

Common Use Cases and Queries

The primary use case for BSC_DB_TABLES was administrative and operational within the BSC module. System administrators or batch processes would query this table to identify all configured data sources for scorecard metrics. A typical query would retrieve a list of tables associated with a specific reporting periodicity to control data loading or aggregation jobs. For example:

• SELECT TABLE_NAME FROM BSC_DB_TABLES WHERE PERIODICITY_ID = (SELECT PERIODICITY_ID FROM BSC_SYS_PERIODICITIES WHERE NAME = 'MONTHLY');

This would return all tables containing monthly data. Another common pattern involved joining with BSC_DB_TABLES_COLS to get a full view of a table's structure for validation or dynamic SQL generation during report execution or data integration tasks.

Related Objects

The BSC_DB_TABLES table sits at the center of a network of related metadata tables, as evidenced by its numerous foreign key relationships. Key dependent objects include:

• BSC_DB_TABLES_COLS: Stores column-level metadata for each table registered in BSC_DB_TABLES.
• BSC_DB_CALCULATIONS & BSC_DB_TABLES_RELS: Define calculation logic and source-to-target table relationships, both dependent on the base table definition.
• BSC_DB_LOADER_CONTROL: References the table as a source (INPUT_TABLE_NAME) for data loading processes.
• BSC_KPI_DATA_TABLES: Links specific Key Performance Indicators to their underlying data tables.
• BSC_SYS_PERIODICITIES: The referenced parent table that provides the valid list of time frequencies for the PERIODICITY_ID foreign key.