Results for “bim_dimv_event_hier”

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

Overview

BIM_DIMV_EVENT_HIER is a Marketing Intelligence (BIM) dimensional view owned by the APPS schema in Oracle E-Business Suite. Its stated purpose within the ETRM repository is to "show the relationship between events and event offerings." In the context of Oracle Marketing and the Advanced Marketing (AMS) data model, events and event offerings are stored as distinct but hierarchically related entities: an event header represents the parent marketing activity, while one or more event offerings represent specific deliverables, sessions, or registration options attached to that event. This view flattens that relationship into a single denormalized rowset suitable for consumption by Oracle Business Intelligence, Discoverer workbooks, and custom ETL processes feeding a data warehouse fact/dimension model.

The view is marked VALID and conforms to the "DIMV" naming convention used throughout BIM, signaling that it is a dimensional-style view intended to serve as a source for dimension extraction rather than as a transactional interface. It exposes both the event-level and offering-level attributes side by side, allowing downstream reports to pivot on either entity without additional joins.

Underlying Base Objects

The documented metadata lists the following referenced base objects:

• AMS_EVENT_HEADERS_VL (VIEW) — the multilingual event header entity, providing event name, description, type, level, status, and active date ranges.
• AMS_EVENT_OFFERS_VL (VIEW) — the multilingual event offering entity, providing offering name, duration, start/end dates, and registration windows.
• AMS_LOOKUPS (VIEW) — joined on LOOKUP_TYPE = 'AMS_EVENT_TYPE' to translate the event type code into a user-facing meaning.
• FND_LOOKUPS (VIEW) and FND_GLOBAL (PACKAGE) — referenced indirectly by the AMS _VL views for language and security context resolution.

The primary join is EVTH.EVENT_HEADER_ID = EVTO.EVENT_HEADER_ID, establishing the event-to-offering parent/child linkage. A UNION ALL branch injects an unassigned pseudo-row (surrogate key '-999') so that fact records lacking a valid event can still resolve against the dimension.

Key Columns

• EVENT_ID — surrogate string key 'EVEH'||EVENT_HEADER_ID.
• EVENT_NAME, EVENT_DESCRIPTION, EVENT_TYPE_CODE, EVENT_TYPE_NAME — descriptive event attributes; the type name is decoded from AMS_LOOKUPS.
• EVENT_LEVEL, EVENT_STATUS_CODE, EVENT_SOURCE_CODE, EVENT_STANDALONE_FLAG — classification and lifecycle attributes.
• ACTIVE_FROM_DATE, ACTIVE_TO_DATE, EVENT_ACTIVE_FLAG — effective dating for slowly changing dimension handling.
• PARENT_EVENT_HEADER_ID — recursively keyed (again prefixed with 'EVEH') to express event-to-event hierarchy.
• EVENT_OFFER_ID, PARENT_EVENT_OFFER_ID — surrogate keys prefixed with 'EVEO' for the offering entity and its own parent.
• EVENT_OFFER_NAME, EVENT_OFFER_DESCRIPTION, EVENT_OFFER_DURATION — offering descriptive attributes.
• EVENT_OFFER_START_DATE / END_DATE / REG_START_DATE / REG_END_DATE — scheduling and registration windows.
• ORG_ID — explicitly set to NULL in the main branch, reflecting that BIM dimensional views here are not multi-org partitioned at this layer.

Common Use Cases and Queries

Typical uses include populating an Event dimension in a marketing data mart, driving offering-level response analysis, and building hierarchical rollups of events by parent.

SELECT event_name, event_offer_name, event_offer_start_date
FROM   apps.bim_dimv_event_hier
WHERE  event_type_name = 'Conference'
AND    NVL(event_offer_active_flag,'Y') = 'Y';

SELECT event_id, event_name, parent_event_header_id
FROM   apps.bim_dimv_event_hier
WHERE  parent_event_header_id IS NOT NULL;

Analysts frequently filter out the '-999' unassigned row when producing end-user reports, and use EVENT_ACTIVE_FLAG and the ACTIVE_FROM/TO columns to implement type-2 dimension history.