Time Dimension - Process

Process - Initial and Subsequent Load:

Schedule:

• Run once and then checked annually to see if it needs to be run again.
  

Pre-Processing:

• None.
  

Staging:

• None.
  

Validation:

• None.

ODS:

• None.

Warehouse:

• Process name: M_W2W_D_TIME
• Description: Pre-load the Time Dimension with natural and financial calendar data.
• This is based on the Oracle supplied example (found in /u01/app/oracle/10.2.0.1/wb01/owb/misc/time on my server) and is implemented with the use of a Table Function. Table Functions are not directly developed in OWB (at least not is the current release), rather, they use objects independently loaded into the database. These objects are:
• tob_d_time: An object type that describes the parameters to a function that generates the data.
• tta_d_time: A table type based on the tob_d_time object type.
• d_time_s: A sequence used to generate level keys for the Time Dimension.
• f_d_time: A pipelined table function that generates the data.
  

• tob_d_time: This object type consists of the following components:
  

Component	Datatype
DAY_ID	NUMBER
FMN_ID	NUMBER
FQT_ID	NUMBER
FWK_ID	NUMBER
FYR_ID	NUMBER
NMN_ID	NUMBER
NQT_ID	NUMBER
NWK_ID	NUMBER
NYR_ID	NUMBER
DAY_END_DATE	DATE
DAY_JULIAN_DATE	NUMBER
DAY_LAST_DAY_IN_MONTH	VARCHAR2(1)
DAY_LAST_DAY_IN_QUARTER	VARCHAR2(1)
DAY_NAME	VARCHAR2(10)
DAY_SHORT_NAME	VARCHAR2(3)
DAY_START_DATE	DATE
DAY_WEEKDAY	VARCHAR2(1)
FMN_FULL_NAME	VARCHAR2(25)
FMN_MONTH	NUMBER(2)
FMN_NAME	VARCHAR2(10)
FMN_ORDERING	NUMBER(6)
FMN_SHORT_NAME	VARCHAR2(9)
FQT_FULL_NAME	VARCHAR2(25)
FQT_NAME	VARCHAR2(3)
FQT_ORDERING	NUMBER(5)
FQT_QUARTER	NUMBER(1)
FQT_SHORT_NAME	VARCHAR2(11)
FWK_FULL_NAME	VARCHAR2(25)
FWK_NAME	VARCHAR2(3)
FWK_ORDERING	NUMBER(6)
FWK_WEEK	NUMBER(2)
FYR_DAYS_IN_YEAR	NUMBER(3)
FYR_FULL_NAME	VARCHAR2(25)
FYR_NAME	VARCHAR2(9)
FYR_SHORT_NAME	VARCHAR2(20)
FYR_YEAR	NUMBER(4)
NMN_DAYS_IN_MONTH	NUMBER(2)
NMN_MONTH	NUMBER(2)
NMN_NAME	VARCHAR2(10)
NMN_ORDERING	NUMBER(6)
NMN_SHORT_NAME	VARCHAR2(3)
NQT_DAYS_IN_QUARTER	NUMBER(2)
NQT_NAME	VARCHAR2(11)
NQT_ORDERING	NUMBER(5)
NQT_QUARTER	NUMBER(1)
NQT_SHORT_NAME	VARCHAR2(7)
NWK_NAME	VARCHAR2(3)
NWK_ORDERING	NUMBER(6)
NWK_WEEK	NUMBER(2)
NYR_DAYS_IN_YEAR	NUMBER(3)
NYR_NAME	VARCHAR2(4)
NYR_YEAR	NUMBER(4)

• tta_d_time: This table type is a table of tob_d_time.
  

• d_time_s: A sequence created with all clauses allowed to default.
  

• f_d_time: This function has the following specification:
• Parameters:
• p_start_date VARCHAR2
• p_end_date VARCHAR2
  
• Return tob_time_d PIPELINED
• Declaration:
• Declare a record with type tob_time_d and set all components to NULL.
• Declare local variables as required; one for each tob_time_d component and any needed control variables.
• Declare exceptions:
• e_date_span: Raised if passed parameters are insane.
  
• Processing:
• Determine start date, end dates and number of days in range from parameters and check for sanity. Raise e_date_span if there are no days to process.
  
• Calculate calendar week of the start date - 1 day using the Time / Date PL/SQL Library.
• Calculate calendar quarter of the start date - 1 day using the Time / Date PL/SQL Library.
• Calculate financial week of the start date - 1 day using the Time / Date PL/SQL Library.
• Calculate financial month of the start date - 1 day using the Time / Date PL/SQL Library.
• Calculate financial quarter of the start date - 1 day using the Time / Date PL/SQL Library.
• Calculate financial year of the start date - 1 day using the Time / Date PL/SQL Library.
• Loop For each date in range (current_date):
• Calculate the DAY attributes:
• ID: d_time_s.NEXTVAL.
• START_DATE: midnight on the current date.
• END_DATE: 23:59:59 on the current date.
• JULIAN_DATE: use date/time formatting.
• NAME: use date/time formatting.
• SHORT_NAME: use date/time formatting.
• WEEKDAY: 'Y' if Mon-Fri, otherwise 'N'.
• LAST_DAY_IN_QUARTER: 'Y' if 31st Mar, 30th Jun, 30th Sep or 31st Dec, otherwise 'N'.
• LAST_DAY_IN_MONTH: 'Y' if tomorrow is 1st, otherwise 'N'.
• Calculate the NAT_YEA attributes:
• ID: If 1st Jan or first time round loop then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
• YEAR: use date/time formatting.
• NAME: use date/time formatting.
• DAYS_IN_YEAR: If 29th Feb YEAR exists = 366, otherwise - 365.
• Calculate the NAT_QTR attributes:
• ID: If 1st Jan, 1st Apr, 1st Jul, 1st Oct or first time round loop, then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
• QUARTER: If 1st Jan = 1, if 1st Apr = 2, 1st Jul = 3, if 1st Oct = 4.
  
• NAME: Generate cardinal value using the Number PL/SQL Library || ' Quarter'.
  
• SHORT_NAME: Generate cardinal value using the Number PL/SQL Library || ' Qtr'.
• ORDERING: Concatenate NAT_YEA.YEAR to QUARTER.
• DAYS_IN_QUARTER: Number of days between first and last date of quarter.
• Calculate the NAT_MTH attributes:
• ID: If 1st or first time round loop, then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
• MONTH: use date/time formatting.
  
• NAME: use date/time formatting.
• SHORT_NAME: use date/time formatting.
• ORDERING: Concatenate NAT_YEA.YEAR to MONTH (left padded with zeros to two digits).
• DAYS_IN_MONTH: use date/time formatting.
• Calculate the NAT_WEK attributes:
• ID: If Monday or first time round loop then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
• WEEK:
• If current date is 1st Jan then 1, otherwise;
• If Monday then increment from last iteration, otherwise use last value.
  
• NAME: Generate cardinal value (1st, 2nd, etc.) from WEEK using the Number PL/SQL Library.
  
• ORDERING: Concatenate NAT_YEA.YEAR to WEEK (left padded with zeros to two digits).
• Calculate the FIN_YEA attributes:
• ID: If 1st Jul then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
• YEAR: If 1st Jul then increment from last iteration, otherwise use the local value from last iteration.
• NAME: Concatenate YEAR - 1 || '/' || YEAR
  
• SHORT_NAME: Concatenate NAME || 'Fin yr'
  
• FULL_NAME: Concatenate NAME || 'Financial Year'
  
• DAYS_IN_YEAR: If 29th Feb || YEAR exists = 366, otherwise - 365.
• Calculate the FIN_QTR attributes:
• ID: If 1st Jul, 1st Oct, 1st Jan, 1st Apr or first time round loop, then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
• QUARTER: If 1st Jul = 1, if 1st Oct = 2, if 1st Jan = 3, if 1st Apr = 4.
  
• NAME: Generate cardinal value (1st, 2nd, etc.) from QUARTER using the Number PL/SQL Library.
  
• SHORT_NAME: NAME || ' Fin Qtr'
  
• FULL_NAME: NAME || ' Financial Quarter'
  
• ORDERING: Concatenate FIN_YEA.YEAR to QUARTER.
• Calculate the FIN_MTH attributes:
• ID: If 1st or first time round loop, then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
• MONTH: If 1st then then increment from last iteration. If MONTH = 13 then = 1.
  
• NAME: Generate cardinal value(1st, 2nd, etc.) from MONTH using the Number PL/SQL Library.
• SHORT_NAME: NAME || ' Fin Mth'
  
• FULL_NAME: NAME || ' Financial Month'
  
• ORDERING: Concatenate FIN_YEA.YEAR to MONTH (left padded with zeros to two digits).
• Calculate the FIN_WEK attributes:
• ID: If Monday or first time round loop then get from d_time_s.NEXTVAL, otherwise use the local value from last iteration.
• WEEK:
• If current date is 1st Jul then 1, otherwise;
• If Monday then increment from last iteration, otherwise use last value.
  
• NAME: Generate cardinal value (1st, 2nd, etc.) from WEEK using the Number PL/SQL Library.
  
• FULL_NAME: NAME || ' Financial Week'
  
• ORDERING: Concatenate FIN_YEA.YEAR to WEEK (left padded with zeros to two digits).
• Load the output record with the calculated values and pipe it out.
  


• The M_W2W_D_TIME Mapping:
• Name Structure:
• M: Mapping
• W2W: Internal to whs
• D_TIME: Operates on the Time Dimension.
• Description:
• This mapping loads the Time Dimension (D_TIME).
  
• Input Parameters:
• Start Date: Date of earliest row that will be loaded into the Time Dimension.
• Number of Years: Number of years worth of data, starting at the Start Date that will be loaded into the Time Dimension.
• Pre-Mapping - CTL.PK_CTL_GEN.INITIALISE_MAPPING:
• Inform Control Database of startup.
  
• Get Run Number and Current Run Date.
• Constants:
• Mapping Name: M_W2W_D_TIME
• Stages:
  
• Table Function:
• Mapped to the f_d_time function
• Inputs:
• Start Date
• Number of Years
• Properties:
• In group:
• Input Parameter Type: SCALAR
• Start Date Attribute:
• Position: 1
• Number of Years:
• Position: 2
• Out group:
• Return Table of Scalar: FALSE
• Attributes:
• Type Attribute Name: Name of TOB_D_TIME attribute.
  
• Map the output of the f_d_time Table Function to d_time.
• Connect Run No and Current Run Date to d_time.
  
• Post-Mapping - CTL.PK_CTL_GEN.FINALISE_MAPPING:
• Inform Control Database of completion.

• The PF_D_TIME Process Flow:
• Module: PM_AD_HOC
• Package: AD_HOC
• Name: PF_D_TIME
• Description: Load the D_TIME Time Dimension.
• Type: Wrapper
  

Post-processing:

• None.