Search Results bism_subjects_uk1

Overview

APPLSYS.BISM_SUBJECTS is a foundational table within the Oracle E-Business Suite 12.1.1 and 12.2.2 ETRM (E-Business Suite Technology Reference Model) schema. It belongs to the BISM (Business Intelligence Subject Model or Business Intelligence System Management) family of objects, which governs the storage and organization of subject definitions used by Oracle Business Intelligence and related analytical components embedded in EBS. The table resides in the APPLSYS schema, is marked VALID, and is stored in the APPS_TS_TX_DATA tablespace with a PCT Free of 10.

Functionally, BISM_SUBJECTS serves as the registry of subjects — logical groupings used to classify and control access to BI artifacts, dashboards, and reports. Each row defines a single subject, identified by a surrogate primary key and a unique business name. Its role is administrative and metadata-centric rather than transactional.

Based on the heuristic Data Vault classification derived from the foreign-key structure, this object is best modeled as a hub. It has no foreign keys referencing other tables, yet it is itself referenced by dependent objects, which is characteristic of a hub retaining business keys. It is not a link or satellite, as it stores descriptive attributes of the subject rather than relationships or time-variant detail.

Key Information Stored

Only three columns are documented in the ETRM metadata, though the physical table in 12.2.2 contains additional columns. The most significant documented columns are:

• SUBJECT_ID — RAW(16), the surrogate primary key. It uniquely identifies each subject and is the column enforced by the unique index BISM_SUBJECTS_PK. As a RAW(16) value, it is typically a globally unique identifier or hash rather than a sequentially assigned number.
• SUBJECT_NAME — VARCHAR2(64), the business-key candidate. It is enforced by the unique index BISM_SUBJECTS_UK1, meaning no two subjects may share the same name. This is the human-readable label used in administrative interfaces and lookups.
• SUBJECT_TYPE — VARCHAR2, a classification attribute distinguishing categories of subjects (for example, user-defined versus system-defined). The precise length is not documented in the ETRM excerpt.

Columns such as LAST_MODIFIED_BY, CREATED_BY, GROUP_ID, and USER_ID appear in the relationship metadata as foreign-key columns in dependent or referencing tables, not necessarily as stored columns of BISM_SUBJECTS itself. Their presence in joins reflects audit and ownership tracking applied by related objects.

Common Use Cases and Queries

The table is queried primarily for administrative, security, and reporting purposes. A common pattern resolves a subject name to its identifier for downstream joins:

• Listing all defined subjects: SELECT SUBJECT_ID, SUBJECT_NAME, SUBJECT_TYPE FROM APPLSYS.BISM_SUBJECTS ORDER BY SUBJECT_NAME;
• Looking up a subject by its business key: SELECT SUBJECT_ID, SUBJECT_TYPE FROM APPLSYS.BISM_SUBJECTS WHERE SUBJECT_NAME = :name;
• Joining to permissions to audit access: SELECT s.SUBJECT_NAME, p.SUBJECT_ID FROM APPLSYS.BISM_SUBJECTS s, APPLSYS.BISM_PERMISSIONS p WHERE s.SUBJECT_ID = p.SUBJECT_ID;

Reporting use cases include validating that subject names remain unique, identifying orphaned or unused subjects, and enumerating subjects by type to support security reviews or migration planning.

Related Objects

The following objects reference or depend on BISM_SUBJECTS and are the most significant for join and dependency analysis:

• APPLSYS.BISM_PERMISSIONS — references SUBJECT_ID; defines which principals hold which permissions on a subject.
• APPLSYS.BISM_GROUPS — referenced via GROUP_ID and USER_ID; associates groups and users with subjects.
• APPLSYS.BISM_OBJECTS — referenced via LAST_MODIFIED_BY and CREATED_BY; provides audit linkage to the BI objects that carry subject metadata.
• APPLSYS.BISM_SUBJECTS# — a dependent object, typically a materialized view or synonym, listed as referencing BISM_SUBJECTS.
• APPLSYS.BISM_SUBJECTS_PK / BISM_SUBJECTS_UK1 — the unique indexes enforced on SUBJECT_ID and SUBJECT_NAME respectively, stored in APPS_TS_TX_IDX.

Together these relationships position BISM_SUBJECTS as the central hub for BI subject definition and permission management within the EBS analytical framework.