Search Results dim_level_value

Overview

The BSC_SYS_FILTERS table is a core data repository within the Oracle E-Business Suite Balanced Scorecard (BSC) application. It serves as the central storage mechanism for filter definitions, which are essential for controlling the scope and dimensionality of data presented in scorecard reports and analyses. This table enables the application to persistently store user-defined or system-defined criteria that limit data based on specific dimension levels, such as organization, time, or product. Its role is critical for implementing focused performance management views, ensuring that users and processes interact only with the relevant subset of strategic data as governed by the configured filters.

Key Information Stored

The table's structure is defined by a composite primary key, which dictates how filter records are uniquely identified and enforced. The key columns are DIM_LEVEL_ID, SOURCE_CODE, SOURCE_TYPE, and DIM_LEVEL_VALUE. The DIM_LEVEL_ID is a foreign key linking to the BSC_SYS_DIM_LEVELS_B table, identifying the specific dimension level (e.g., "Quarter," "Department") to which the filter applies. The SOURCE_CODE and SOURCE_TYPE columns work together to identify the object (such as a specific scorecard, perspective, or KPI) that the filter is associated with. Finally, the DIM_LEVEL_VALUE column holds the actual value used to filter the data at the specified dimension level, such as a specific organization ID or a date range code.

Common Use Cases and Queries

A primary use case is the runtime application of filters when generating a Balanced Scorecard report. The application queries this table to retrieve all active filters for a given scorecard or perspective before querying fact data. For administrative purposes, a common query would list all filters defined for a particular dimension to audit data security and visibility rules. A sample SQL pattern to retrieve filter details for a specific source object would be:

• SELECT dim_level_id, dim_level_value FROM bsc_sys_filters WHERE source_code = 'SCORECARD_100' AND source_type = 'SC';

This query fetches the dimension-level constraints for a scorecard with the code 'SCORECARD_100'. Developers might also query this table to troubleshoot why certain data points are not appearing in a user's scorecard view by validating the filter values against the underlying fact tables.

Related Objects

The BSC_SYS_FILTERS table has a direct and documented foreign key relationship, which is fundamental to its integrity and meaning. The table references the BSC_SYS_DIM_LEVELS_B table via the DIM_LEVEL_ID column. This relationship ensures that every filter is defined against a valid, existing dimension level within the BSC application's dimensional hierarchy. Consequently, any process or report joining these two tables can retrieve not only the filter value but also the descriptive metadata (like the dimension level name) from BSC_SYS_DIM_LEVELS_B. This table is also inherently related to various BSC fact tables and reporting views, which apply the stored filter logic, though those relationships are typically enforced through application logic rather than explicit database constraints.