Search Results is_dim_recursive

Overview

BSC_DBGEN_METADATA_READER is an internal PL/SQL package in the APPS schema that belongs to the Oracle Balanced Scorecard (BSC) database generation subsystem. Its responsibility is to read the metadata definitions that describe the BSC analytical model — facts, measures, dimensions, levels, periodicities, calendars, and dimension sets — and expose them to the runtime engine that materializes and queries the underlying fact and dimension tables. The package therefore acts as the metadata access layer of BSC_DBGEN, translating rows stored in the BSC dictionary tables into strongly typed PL/SQL collections declared in BSC_DBGEN_STD_METADATA (for example tab_clsFact, tab_clsMeasure, tab_clsDimension, tab_clsPeriodicity, clsLevel, and tab_clsLevelRelationship). The user-visible symbol is_target_at_higher_level is one of these metadata interrogation routines.

Key Procedures and Functions

• Initialize — Seeds the package with the metadata source context so subsequent calls resolve against the correct model.
• Get_Facts_To_Process — Returns the set of facts associated with a given process identifier, driving the generation run.
• Get_Measures_For_Fact — Returns the measures defined for a fact within a dimension set, optionally including derived columns.
• Get_Periodicities_For_Fact / Get_Calendar_Id_For_Fact / Get_Fact_Ids_For_Calendar — Resolve the periodicity and calendar context for facts, and map calendars back to the facts they serve.
• Get_Dimensions_For_Fact / Get_Dim_Sets_For_Fact — Return the dimensions and dimension sets assigned to a fact.
• Get_Parents_For_Level / Get_Children_For_Level / Get_Level_Info / Is_Dim_Recursive — Traverse and describe the level hierarchy, including recursive dimension detection and bounds on traversal depth.
• Get_Facts_For_Levels — Returns facts reachable from a supplied list of levels.
• Is_Target_At_Higher_Level — Determines whether the target of a fact/dimension-set combination resides at a higher level of the dimension hierarchy than the source, a condition that governs aggregation direction and level-relative calculation behavior.
• Get_Filter_For_Dim_Level — Produces the predicate text used to filter a fact at a specified level.
• Get_S_Views / Get_B_Table_Measures_For_Fact / Get_Periodicity_For_Table — Expose the staging and base objects, their measures, and periodicity that the generator needs.
• Get_Levels_For_Table / Get_Db_Calendar_Column — Return levels defined for a physical table and the calendar column mapped in the database.

Tables Accessed

The package reads the BSC dictionary through APPS synonyms: BSC_DB_TABLES (physical table registry), BSC_KPI_PERIODICITIES and BSC_SYS_PERIODICITIES (periodicity definitions), BSC_SYS_DIM_LEVELS_B (level definitions), and BSC_SYS_DIM_LEVEL_RELS (parent/child level relationships). It inspects the data dictionary via ALL_TAB_COLUMNS, ALL_PART_TABLES, ALL_TAB_PARTITIONS, and ALL_PART_KEY_COLUMNS to discover columns and partitioning, uses DBMS_SQL and PLITBLM (the dbms_sql varchar2_table and number_table types) for dynamic SQL and collection parameters, reads V$PARAMETER for database configuration, and queries DUAL. No documented writes to application tables are performed; the package is a reader over metadata and dictionary views.

Usage Notes

BSC_DBGEN_METADATA_READER is not intended for direct end-user invocation. It is called by the BSC database-generation packages and concurrent programs that build and refresh the scorecard fact and dimension tables, and it is referenced by twelve other packages in the same subsystem. Calls are typically preceded by Initialize and then driven by a process identifier or fact name. Because return types are BSC_DBGEN_STD_METADATA collections, custom code extending BSC generation must reference those types and honor the level-relationship semantics — in particular the higher-level target determination used to resolve aggregation across dimension levels.