Search Results edw_dim_sv




Overview
Explore in AI Chat

Overview

EDW_DIM_SV is an Oracle E-Business Suite PL/SQL package owned by the APPS schema. It belongs to the Oracle Enterprise Data Warehouse (EDW) / Daily Business Intelligence (DBI) dimension generation infrastructure introduced in the 11i and 12.1.1 releases. The package declares AUTHID DEFINER, meaning its underlying SQL executes with the privileges of its owner (APPS) rather than the invoking user. This is essential, because generated dimension views must be built against APPS-owned synonyms and metadata tables regardless of caller identity.

The core business function of EDW_DIM_SV is to construct and maintain the SQL views and helper clauses that materialize dimensional access paths for the EDW star schemas. Dimensions in this architecture may be based on a standard, static structure or may be derived dynamically from a key flexfield (flex dimension). Because a dimension is only known at runtime, the package resolves the correct view names, generates wrapper views, and emits the value-set (VS) WHERE-clause fragments required to filter dimension members correctly. The literal dim_name parameter seen throughout the specification is the runtime handle by which every routine identifies which dimension is being processed.

Key Procedures and Functions

  • getViewnameForFlexdim — Resolves and registers the view name that corresponds to a given flex-based dimension.
  • getIndepVSClause — Returns the WHERE-clause fragment for an independent value set applied to a dimension at a specified level.
  • getTableValClause — Produces the clause used when dimension validation is driven by a table-valued value set.
  • getNoneVSClause — Supplies the clause for dimensions where no value-set restriction applies.
  • getDepVSClause — Builds the clause for dependent value sets, whose valid values are constrained by a parent segment.
  • getViewnamesForStdDim — Returns the set of view names associated with a standard (non-flex) dimension at a given level.
  • generateStdDimension — Creates or regenerates the physical view object for a standard dimension.
  • getGeneratedViewnameForStdDim — Reports the name of the generated view for a standard dimension and level.
  • generateViewForDimension — The primary driver routine that builds the view for a dimension, dispatching to the standard or flex paths as appropriate.

Tables Accessed

  • EDW_ATTRIBUTE_MAPPINGS — Defines how source columns map to dimensional attributes; drives generated column lists.
  • EDW_ATTRIBUTE_PROPERTIES — Stores attribute-level behavioral properties consulted when emitting clauses.
  • EDW_FLEX_SEG_MAPPINGS — Maps key flexfield segments to EDW dimensions, enabling flex dimension resolution.
  • EDW_SOURCE_VIEWS — Registry of source view definitions from which dimension views are derived.
  • DBMS_SQL — The Oracle-supplied dynamic SQL package, used to parse and execute generated DDL/DML since view names and column lists are only known at runtime.
  • PLITBLM — The PL/SQL table (index-by-table) utility used to pass and buffer generated SQL text and name lists.

Usage Notes

EDW_DIM_SV is not normally invoked by end users. It is called programmatically during EDW/DBI setup and refresh cycles — principally after flexfield or dimension metadata changes — to regenerate the views that BI reports and ETL queries depend on. Invocation typically originates from an EDW administration concurrent program or from a higher-level package; the metadata records that EDW_DIM_SV is referenced by one other package, consistent with it acting as a low-level service layer. Because it performs DDL through DBMS_SQL, it must run with sufficient APPS privileges and should be executed only in a controlled maintenance window. Custom code may call generateViewForDimension directly when synchronizing a newly defined dimension, but direct calls to the clause functions are intended only as internal building blocks.