Search Results msd_cs_defn_dim_dtls




Overview
Explore in AI Chat

Overview

MSD_CS_DEFN_DIM_DTLS is a transactional configuration table owned by the MSD schema, which supports the Demand Planning module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. The table stores the dimension-level detail rows that belong to a Data Stream definition. In Oracle Demand Planning, a Data Stream defines the analytical structure through which demand history, forecasts, and planning data are aggregated and analyzed. Each Data Stream is composed of one or more dimensions (for example, product, customer, organization, or time), and MSD_CS_DEFN_DIM_DTLS holds the metadata describing how each dimension behaves within that stream, including its collection and aggregation characteristics.

The ETRM metadata classifies this table heuristically as standalone within a Data Vault modeling context. This suggests it is best modelled as a satellite or reference table attached to a definition hub (MSD_CS_DEFINITIONS), rather than as an independent hub or link. The table depends on its parent definition but is not a pure intersection of multiple business entities.

Key Information Stored

The table contains 17 documented columns. The most significant include:

  • CS_DEFN_DIM_DTLS_ID — the surrogate primary key, enforced by the MSD_CS_DEFN_DIM_DTLS_PK constraint. It uniquely identifies each dimension detail row.
  • CS_DEFINITION_ID — a foreign key to MSD_CS_DEFINITIONS, linking the dimension detail to its owning Data Stream definition. This is the primary business-key component.
  • DIMENSION_CODE — the identifier of the dimension (such as product or customer) that this row describes. It forms the second component of the business key.
  • COLLECT_FLAG — indicates whether the dimension is collected into the Data Stream.
  • COLLECT_LEVEL_ID — identifies the level at which data for the dimension is gathered.
  • AGGREGATION_TYPE — defines how values are rolled up across the dimension hierarchy.
  • ALLOCATION_TYPE — defines how values are distributed or allocated down the dimension hierarchy.
  • ZD_EDITION_NAME — the editioning column used by Oracle's online patching and edition-based redefinition framework in 12.2.x, which is critical for querying the latest edition of the data.

Audit and concurrency columns include CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE, which record the who, when, and which concurrent program created or modified each row.

Two unique indexes are documented. MSD_CS_DEFN_DIM_DTLS_U1 covers (CS_DEFINITION_ID, DIMENSION_CODE, ZD_EDITION_NAME) and represents the true business-key candidate. MSD_CS_DEFN_DIM_DTLS_U2 covers (CS_DEFN_DIM_DTLS_ID, ZD_EDITION_NAME), reinforcing edition-scoped uniqueness.

Common Use Cases and Queries

Typical use cases involve inspecting or validating how a Data Stream is configured before running a demand plan. Planners and administrators query this table to confirm which dimensions are collected and how aggregation and allocation behave. A representative query joins to the parent definitions table:

  • SELECT d.cs_definition_id, d.dimension_code, d.collect_flag, d.aggregation_type FROM msd.msd_cs_defn_dim_dtls d WHERE d.cs_definition_id = :p_defn_id AND d.zd_edition_name = 'SET1';
  • Audit queries filtering on LAST_UPDATE_DATE or CREATED_BY help trace configuration changes over time.
  • Reports joining to MSD_CS_CLMN_DIM_DTLS reveal the column-to-dimension mappings used beneath each dimension detail.

Always include the ZD_EDITION_NAME predicate on 12.2.x to restrict results to the active edition.

Related Objects

The following objects are the most significant dependencies:

  • MSD_CS_DEFINITIONS — parent table; joined on CS_DEFINITION_ID.
  • MSD_CS_CLMN_DIM_DTLS — child table referencing CS_DEFN_DIM_DTLS_ID; holds column-level dimension details.
  • MSD_CS_DEFN_DIM_DTLS_PK — primary key constraint on CS_DEFN_DIM_DTLS_ID.
  • MSD_CS_DEFN_DIM_DTLS_U1 / U2 — unique indexes enforcing business-key and edition uniqueness.
  • The MSD Demand Planning planning engine and Data Stream administration concurrent programs, which read this configuration during plan execution.