Results for “bsc_sys_periodicities_u1”

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

Overview

BSC.BSC_SYS_PERIODICITIES is a core configuration table in the Oracle E-Business Suite Balanced Scorecard (BSC) module, introduced to support the Enterprise Performance Foundation and Enterprise Data Warehouse (EDW) integration layers. It stores the definition of each system periodicity that the application supports, including how many periods and sub-periods a periodicity spans, which column names in the underlying fact tables hold period and sub-period values, and whether the periodicity is yearly or EDW-based. Because periodicities drive how Key Performance Indicators, benchmarks, reporting calendars, and database table definitions are aggregated and stored, this table acts as a foundational reference for the BSC schema.

The heuristic Data Vault classification derived from foreign-key analysis is hub-leaning. That classification is a modeling suggestion: PERIODICITY_ID behaves as a stable, business-meaningful key that is referenced by many surrounding tables, which is consistent with treating BSC_SYS_PERIODICITIES as a hub entity in a Data Vault model, with dependent performance and reporting tables acting as satellites or links around it.

Key Information Stored

The table is owned by the BSC schema, resides in the APPS_TS_TX_DATA tablespace, and is documented with 17 columns in ETRM 12.1.1. The most significant columns are:

• PERIODICITY_ID — Surrogate primary key (PK: BSC_SYS_PERIODICITIES_PK) and the single column of unique index BSC_SYS_PERIODICITIES_U1, making it the primary business-key candidate.
• NUM_OF_PERIODS — Number of periods defined for the periodicity option.
• NUM_OF_SUBPERIODS — Number of sub-periods within each period.
• PERIOD_COL_NAME — Name of the period column in the input/fact table.
• SUBPERIOD_COL_NAME — Name of the sub-period column in the input table.
• PERIODICITY_TYPE — Encoded periodicity type: 1=Annually, 2=Semi-annual, 3=Quarterly, 4=Bi-monthly, 5=Monthly, 7=Week, 9=Daily.
• YEARLY_FLAG — Indicates whether the periodicity is a yearly periodicity.
• EDW_FLAG and EDW_PERIODICITY_ID — Flag and identifier linking the periodicity to the Enterprise Data Warehouse.
• CALENDAR_ID — Calendar identifier used when the periodicity is EDW-based; also indexed by BSC_SYS_PERIODICITIES_N1.
• SOURCE — Comma-separated list of all periodicity ID sources.
• CUSTOM_CODE — Custom code: 1 = from daily periodicity, 2 = from predefined periodicity.
• DB_COLUMN_NAME — Column name in the BSC_DB tables.
• SHORT_NAME — Short display name (documented comment reads "Query Text").

Common Use Cases and Queries

Typical usage includes resolving periodicity definitions for KPI aggregation, validating EDW calendar linkage, and driving dynamic SQL that references PERIOD_COL_NAME and SUBPERIOD_COL_NAME when building fact queries. A standard lookup by business key:

• SELECT periodicity_id, num_of_periods, periodicity_type FROM bsc.bsc_sys_periodicities WHERE periodicity_id = :p_id;
• Reporting on EDW-linked periodicities: SELECT * FROM bsc.bsc_sys_periodicities WHERE edw_flag = 1 AND calendar_id IS NOT NULL;
• Joining to dependent tables: SELECT k.kpi_id, p.short_name FROM bsc.bsc_kpis_b k, bsc.bsc_sys_periodicities p WHERE k.periodicity_id = p.periodicity_id;

Related Objects

The following tables reference BSC_SYS_PERIODICITIES through the PERIODICITY_ID foreign key and are the most significant dependents:

• BSC_DB_TABLES (PERIODICITY_ID) — defines database tables whose data is organized by periodicity.
• BSC_KPIS_B (PERIODICITY_ID) — the KPI base table, defining the periodicity at which each KPI is measured.
• BSC_KPI_PERIODICITIES (PERIODICITY_ID) — association table of KPIs to allowable periodicities.
• BSC_SYS_BENCHMARKS_B (PERIODICITY_ID) — benchmark definitions tied to a periodicity.
• BSC_REPORTING_CALENDAR (PERIODICITY_ID) — reporting calendar mappings per periodicity.
• BSC_RPT_KEYS (PERIODICITY_ID) — reporting key definitions referencing the periodicity.

These relationships confirm the hub role of BSC_SYS_PERIODICITIES: PERIODICITY_ID is the shared key through which KPI, benchmark, calendar, and database-table configuration all converge.