Search Results msd_dp_dimensions




Overview
Explore in AI Chat

Overview

MSD_DP_DIMENSIONS is a Demand Planning table owned by the MSD schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It stores the associations between Demand Plan User Defined Dimensions and the Real Dimensions available to a given demand plan. In effect, the table acts as a mapping or cross-reference registry: for each demand plan, it records which user-defined (DP) dimension codes are bound to which underlying real dimension codes. This mapping is fundamental because Demand Planning allows planners to define custom analytical dimensions that are not native to the source transaction schema, and those custom dimensions must ultimately resolve to a physical dimension before planning data can be stored, aggregated, and reported.

From a modeling standpoint, the mined Data Vault classification for this table is satellite-leaning. This is a heuristic suggestion rather than a physical declaration: the table's identity and descriptive attributes are functionally dependent on its owning demand plan, with the DEMAND_PLAN_ID foreign key anchoring the association to its parent entity. Modelers building a Data Vault or dimensional layer over the MSD schema should treat MSD_DP_DIMENSIONS as a dependent descriptive structure attached to the demand plan, not as an independent hub.

Key Information Stored

The documented physical schema comprises 15 columns. The most significant are:

The surrogate primary key is MSD_DP_DIMENSIONS_PK over (DEMAND_PLAN_ID, DIMENSION_CODE, DP_DIMENSION_CODE). A separate unique index, MSD_DP_DIMENSIONS_U1, extends that key with ZD_EDITION_NAME, making it the edition-aware business-key candidate for uniqueness enforcement.

Common Use Cases and Queries

Typical usage centers on resolving how a planner's custom dimension maps to a physical dimension within a specific plan. A common query joins the table to its parent plan:

  • Listing all dimension associations for a plan: SELECT d.dimension_code, d.dp_dimension_code, d.enable_nonseed_flag FROM msd.msd_dp_dimensions d WHERE d.demand_plan_id = :plan_id;
  • Resolving a user-defined dimension to its real dimension: SELECT dimension_code FROM msd.msd_dp_dimensions WHERE demand_plan_id = :plan_id AND dp_dimension_code = :dp_code;
  • Auditing configuration changes through the standard audit columns, for example filtering on last_update_date to detect recently altered mappings.
  • Reporting which plans share a given dimension association, useful during plan cloning or template validation.

Because the table is relatively narrow and key-driven, queries are index-friendly when the demand plan is supplied as a predicate. In R12.2, queries should be edition-aware where the calling session must respect editioning semantics.

Related Objects

The principal related objects are:

  • MSD_DEMAND_PLANS — referenced by MSD_DP_DIMENSIONS.DEMAND_PLAN_ID. This is the mandatory parent join and the primary source of plan-level attributes.
  • MSD_DP_DIMENSIONS_PK — the primary key constraint enforcing row identity on (DEMAND_PLAN_ID, DIMENSION_CODE, DP_DIMENSION_CODE).
  • MSD_DP_DIMENSIONS_U1 — the unique index adding ZD_EDITION_NAME for edition-aware uniqueness.
  • Demand Planning dimension and plan definition views in the MSD schema, which consume the mapping rows to resolve user-defined dimensions at runtime.

Because the documentation exposes only a single foreign key, MSD_DEMAND_PLANS is the only strictly documented dependent relationship; other MSD dimension and plan metadata objects interact with this table logically through the DIMENSION_CODE and DP_DIMENSION_CODE business keys.