Search Results dimension_key




Overview
Explore in AI Chat

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

Overview

The MTH_445_PERIOD_CALENDAR table belongs to the MTH product (Oracle Manufacturing Operations Center) in Oracle E-Business Suite 12.1.1 and 12.2.2. Per the documented ETRM metadata, its stated purpose is to store a 445/544 period calendar. In practical terms, this is a fully denormalized time-dimension reference table: each row represents a single day, and the same row simultaneously carries that day's attributes at the day, fiscal week, fiscal month, fiscal quarter, and fiscal year levels. This design supports the 4-4-5 and 5-4-4 retail/manufacturing fiscal calendars, where periods within a quarter contain four, four, and five weeks respectively, rather than the Gregorian calendar's variable month lengths.

The table is a standalone object; the heuristic Data Vault classification mined from its foreign-key structure is standalone, meaning it neither acts as a hub nor participates in link relationships. From a dimensional-modeling perspective, it is best understood as a conformed date/time dimension sourced by MTH and consumed by fact tables and analytics, rather than as a transactional or master-data entity. The documented physical schema carries 57 columns and a single primary key, MTH_445_P_DIMENSION_KEY_PK, defined on DIMENSION_KEY.

Key Information Stored

The surrogate primary key is DIMENSION_KEY, established by the MTH_445_P_DIMENSION_KEY_PK constraint. No separate unique business-key index is documented in the supplied metadata, but the date-oriented columns serve as natural business-key candidates when combined. The most significant columns fall into these groups:

Common Use Cases and Queries

The principal use case is joining this table to transactional manufacturing facts by date to resolve fiscal period attributes for reporting, planning, and period-close analysis. A representative pattern filters a single fiscal month:

SELECT d.DAY_START_DATE, d.FISCAL_MONTH_NAME, d.FIS_MONTH_NUMBER, d.WEEK_OF_FISCAL_MONTH
FROM MTH.MTH_445_PERIOD_CALENDAR d
WHERE d.FIS_MONTH_NUMBER = 3 AND d.FIS_YEAR_NUMBER = 2024
ORDER BY d.DAY_START_DATE;

A second pattern maps a specific date to its entire fiscal hierarchy using the surrogate key or a natural date column:

SELECT DIMENSION_KEY, FISCAL_YEAR_NAME, FISCAL_QUARTER_NAME, FISCAL_MONTH_NAME, FIS_WEEK_NUMBER
FROM MTH.MTH_445_PERIOD_CALENDAR
WHERE TRUNC(DAY_START_DATE) = TRUNC(SYSDATE);

Additional scenarios include comparing period-over-period performance by joining on FISCAL_YEAR_ID/FIS_YEAR_NUMBER, driving period-based material planning using FIS_MONTH_NUMBER, and validating that every operational date resolves to a row so fiscal hierarchies join without gaps.

Related Objects

The documented FK/PK relationship data classifies this table as standalone, so no foreign keys are declared against other objects. Its relationships are therefore consumed through join columns rather than enforced constraints. The following objects typically reference it:

  • MTH fact and transactional tables in the MTH schema, joined on DIMENSION_KEY to inherit day, week, month, quarter, and year attributes.
  • MTH reporting views and materialized views that resolve fiscal period labels via FIS_YEAR_NUMBER, FIS_MONTH_NUMBER, or FIS_WEEK_NUMBER.
  • Fiscal calendar setup tables maintained alongside the 4-4-5 definition, referenced through FISCAL_YEAR_ID and the FIS_* number columns.
  • GL and inventory period-close queries that align manufacturing results to fiscal periods through shared fiscal year, quarter, and month keys.
  • BI Publisher/DDiscover reports that use MTH_445_PERIOD_CALENDAR as the time dimension for period trending.

Because no enforced foreign keys are documented, referential integrity between this calendar and its consumers depends on application logic and calendar-maintenance processes.