Search Results time_dim_key




Overview
Explore in AI Chat

Overview

APPS.MTH_RES_TXN_LT_MV is a materialized view owned by the APPS schema in Oracle E-Business Suite, registered in FND Design Data as FND.MTH_RES_TXN_LT_MV. It resides in the APPS_TS_SUMMARY tablespace, which is the standard repository for aggregated summary and materialized objects within EBS, and is flagged as VALID. Functionally, the object stores pre-aggregated material resource transaction metrics — usage quantities and usage values, along with their associated counts — organized along time and item dimensions. This structure supports period-based cost, utilization, and resource consumption reporting without requiring repeated aggregation of high-volume transactional tables.

The object carries a slight storage over-allocation hint: PCTFREE is set to 10, a setting typically applied to tables subject to updates. For a summary materialized view this usually reflects the standard template applied at creation rather than an expectation of heavy in-place modification. The heuristic Data Vault classification derived from the foreign key structure is standalone. This should be treated as a modeling suggestion rather than a strict characterization: although the table does not reference any database objects directly, its dominant measure-plus-dimensional shape aligns more closely with a satellite-style aggregate than with a hub or a link. Including the object in a Data Vault design would generally be appropriate only in a reporting or derived layer rather than as a core raw vault entity.

Key Information Stored

The table contains fourteen columns. The most significant are the dimensional keys that establish its grain and the numeric measures that carry reportable values.

The unique index I_SNAP$_MTH_RES_TXN_LT_MV on the SYS_OP_MAP_NONNULL transformation of PRODUCT_FK_KEY, CALENDAR_MONTH_ID, LEVEL9_FK_KEY, DAY_ID, and GROUPING_ID is the strongest business-key candidate. Because the transformation maps nulls to a sentinel value, it enforces uniqueness across the full five-column combination even where individual columns are null — a pattern characteristic of snapshot-style materialized views. TIME_DIM_KEY and ITEM_DIM_KEY are indexed non-uniquely via bitmap indexes, indicating a low-cardinality, aggregate-style access path rather than a row-level lookup.

Common Use Cases and Queries

Typical usage centers on aggregate reporting by period and item. Because TIME_DIM_KEY and ITEM_DIM_KEY are bitmap-indexed, queries that filter on both dimensions benefit most from the index structure; the optimizer can combine the two bitmap indexes efficiently.

  • Period-over-period consumption: aggregate USAGE_QTY and USAGE_VAL by CALENDAR_MONTH_ID or by a join to the time dimension through TIME_DIM_KEY.
  • Item-level drill-down: group by ITEM_DIM_KEY, using ITEM_DIM_LEVEL to select an appropriate hierarchy level and avoid double counting across grains.
  • Dimension validation: filter on GROUPING_ID and the *_LEVEL columns to isolate a single summary grain before reporting, since the unique key permits the same product/date combination across multiple grouping sets.
  • Freshness checks: compare the maximum DAY_ID in the view against the source transaction tables to confirm the materialized view refresh is current.

A representative query pattern filters by a time key and item key, then aggregates the measure columns while constraining the level columns to a single aggregation set.

Related Objects

The documented dependency metadata is limited, but the following relationships are material.

  • APPS.HR_DM_TABLE_GROUPINGS — referenced through the GROUPING_ID foreign key; join this table to resolve the aggregation sets present in the view.
  • APPS.MTH_RES_TXN_LT_MV — self-referenced in the dependency listing, indicating internal consistency handling such as a refresh or rebuild mechanism.

The metadata records no outbound table dependencies beyond the GROUPING_ID foreign key and no other objects that reference this materialized view. Consequently, joins to time and item dimension tables via TIME_DIM_KEY and ITEM_DIM_KEY are implied by column naming and index design but are not documented as enforced foreign keys. Implementers should verify dimension table names against the installed EBS release before constructing join logic, and should treat the materialized view as a leaf-level reporting object rather than as a source for downstream referential dependencies.