11 Oracle Retail Data Model Utility Scripts

This chapter describes the Oracle Retail Data Model utility script.

This chapter includes the following sections:

Calendar Population
Time Dimension Incremental Load

Calendar Population

The Calendar population scripts consist of two one-time installation packages.

Calendar Population Scripts

The Calendar population scripts include the following packages:

calendar_population_header.sql
calendar_population_body.sql

Running these packages does the following:

Prepares necessary changes for the schema ordm_sys.
Creates the Calendar_Population package that contains the following procedures:
- RUN(in_setup_start_date, in_setup_no_years, in_setup_day_of_week) is the main procedure to populate everything about calendar.
- RBIW_Base_Time_Tables_ddl creates the base table needed to support multiple hierarchies: Business or Calendar.
- RBIW_Populate_Time_Hier_Bsns(in_setup_start_date, in_setup_no_years, in_setup_day_of_week) sets up the data in base table for the Business hierarchy as specified in setup or install section.
- RBIW_Populate_Time_Hier_Clndr(in_setup_start_date, in_setup_no_years, in_setup_day_of_week) sets up the data in base table for the Calendar hierarchy as specified in setup or install section.
- RBIW_Time_hier_Star sets up the Time hierarchy reporting layer tables.
- RBIW_Populate_Time_Transform populates the Time transformation tables using the base Time tables or views. It populates transformation data for the Business Hierarchy alone.

How to Populate Calendar Data

To populate calendar data:

Log in to ORDM_SYS user.
Execute the following SQL statement:
```
exec Calendar_Population.run(date,num_years,dayofweek);
```
where:

date is the start date with which you want to populate calendar data. It is of type CHAR and should be input in the format 'YYYYMMDD' (for example,'20050518' to represent date 18-MAY-2005.

num_years is the number of years to populate calendar data, which should be INTEGER.

dayofweek is the week start day to populate calendar week (should be CHAR type). For example, 'MONDAY'.

Time Dimension Incremental Load

Oracle Retail Data Model lets you extend the Time Dimension for an implementation which was initially set to a certain value. For example for an initial Time dimension of 10 years (from BY 2005 to BY 2014), you can change this to 30 years to support realized forecasting into the future beyond 2014. For example you might like to extend the Time dimension by 20 years.For example, if the initial Time Calendar was setup with the following inputs:

start date = 20050101 (that is, 01-Jan-2005)
week start date = MONDAY
Number of years = 30

First Leap Year = 2007 … NOTE: default value as set in the package=2001. This means that the package was edited and re-run with first leap year = 2007. This setting is present in procedure "RBIW_Populate_Time_Hier_Bsns". Search for " SETUP/INSTALL PARAMETERS" within the package code.

Then, use these five steps to extend the dates and to perform the Time Dimension Incremental Load:

Create new user ordm_tmp and grant default privileges:

As dba user:

create user ordm_tmp identified by ordm_tmp default tablespace USERS temporary tablespace TEMP;

grant
  CONNECT,
  CREATE PROCEDURE,
  CREATE SEQUENCE,
  CREATE SESSION,
  CREATE SYNONYM,
  CREATE VIEW,
  CREATE TABLE,
  CREATE MATERIALIZED VIEW,
  CREATE TYPE,
  CREATE CUBE,
  CREATE CUBE DIMENSION,
  CREATE DIMENSION
to ordm_tmp;

alter user ordm_tmp quota unlimited on USERS;

grant read,write on directory DATA_PUMP_DIR to ordm_tmp;

Import ordm_tmp schema using a dmp file and ddl file.

NOTE: The time tables would be empty to begin with:
- Place the dmp file "ordm_tmp_empty.dmp" located in directory $ORACLE_HOME/ordm/utilities/time_incr in the directory corresponding to Database directory DATA_PUMP_DIR (typically, $ORACLE_BASE/admin/$ORACLE_SID/dpdump)
- Import the dmp into ordm_tmp schema
```
$ impdp ordm_tmp/ordm_tmp dumpfile=ordm_tmp_empty.dmp logfile=impdp_ordm_tmp_empty.log directory=DATA_PUMP_DIR
```
- Run the file time_ddl_install.sql to define (or re-define) 6 views, 11 sequences and 1 package - Calendar_Population in ordm_tmp schema
  
  NOTE: Sequences cant be redefined without dropping them so the script attempts to drop them before attempting to create the sequences. Ignore any errors due to the sequences not existing before being created.
  
  As ordm_tmp user:
```
SQL> @time_ddl_install.sql
```
Using script time_grant_select_tmp.sql, grant privileges on time tables:

As ordm_tmp user:
```
SQL> @time_grant_select_tmp.sql
```
Run the calendar script for 30 years with appropriate inputs:

NOTE 1: It is critical to use the same values as used in Oracle Retail Data Model initial load. The ordm_sys contains time data as per initial settings. The ordm_tmp time dimension should be created with the same time series behavior and should only extend the behavior along time and should not exhibit a different behavior. The behavior of ordm_tmp time dimension would be different if the inputs provided to the Calendar function vary from what was used while setting up time dimension in ordm_sys schema.

NOTE 2: Currently there is no placeholder for storing these inputs in the Oracle Retail Data Model model and expect the customer/Implementor to store, make a note of the values used during Oracle Retail Data Model Installation for future use.

Run the calendar population script with appropriate input parameters:
```
For example, you can use:

start date = 20050101 (that is, 01-Jan-2005)
week start date = MONDAY
Number of years = 30
First Leap Year = 2007
```
NOTE 1: This setting is inside the package. Edit it, if you wish to change the default settings. Please check if you have customized the Calendar package anytime during the initial Oracle Retail Data Model Installation or later. If so, please copy/paste the package code from ordm_sys schema and compile it over in the current temp schema ordm_tmp so that the two schemas are using the same package.

The example scenario used does require you to modify the package as it uses a value which is different from its default settings and so you should edit and recompile the package before using it.

NOTE 2: If the first leap year was 2008 instead of 2007 then you would not have to edit/recompile the package. 2001 and every 7th year (2008, 2015,…) after that would have been treated as "leap year" by default.

As ordm_tmp:
- Edit the package Calendar_Population in ordm_tmp schema. Installed by the dmp and edit it so that the default settings for first leap year is modified from 2001 (default) to 2007 (as in ordm_sys).
- Recompile the package, package body.
As ordm_tmp:
```
set define off
set serverout on size 1000000
set timing on
spool calendar_incr_run.log
begin
   Calendar_Population.Run('20050101',30,'MONDAY');
end;
/
 
spool off
```
Run the script update_clndr_data_for_olap.sql to make the data suitable for loading into olap time dimension:

As ordm_tmp:
```
SQL> @update_clndr_data_for_olap.sql
```
Check: Time dimension in ordm_tmp schema is from BY 2005 to BY 2034 in DWR_BSNS_YR table.

As ordm_tmp:
```
SQL> select bsns_yr_key, yr_desc, yr_nbr from dwr_bsns_yr order by 1;
```
Migrate the data from ordm_tmp into target/original schema ordm_sys.
Copy the entire data for tables - DWR_DAY, DWR_BSNS_WK, and so on, ... incl. DWR_CLNDR_WK, DWR_CLNDR_HLF_MO etc. from BY 2005 onwards up to BY 2034. Ideally this should be a series of inserts into various tables without errors.

Do this in three steps:
1. Disable All FKs relating to the affected list of time tables using script time_fk_disable.sql:
  
  As ordm_sys:
```
SQL> @time_fk_disable.sql
```
2. Move data from ordm_tmp to ordm_sys schema using script time_incr_move_data.sql
  
  NOTE: this runs as a single step or a single pl/sql block. Commit if no errors throughout, rollback otherwise.
  
  As ordm_sys:
```
SQL> @time_incr_move_data.sql
```
3. If step above "time_incr_move_data.sql" is successful (view the log file time_incr_move_data.log to determine outcome), then enable the FKs disabled above using script time_fk_enable.sql
  
  As ordm_sys:
```
SQL> @time_fk_enable.sql
```

 
 
===================================================================
Example log content in log file time_incr_move_data.log created in 2nd task of step 6
====================================================================
 
07:01:19 : Data movement for object: DWL_CLNDR_TYP: Deleted 2 rows.
07:01:19 : Data movement for object: DWL_CLNDR_TYP: Inserted 2 rows.
07:01:19 : Data movement for object: DWL_CLNDR_TYP completed successfully!!
07:01:19 : Data movement for object: DWR_CLNDR: Deleted 0 rows.
07:01:19 : Data movement for object: DWR_CLNDR: Inserted 0 rows.
07:01:19 : Data movement for object: DWR_CLNDR completed successfully!!
07:01:19 : Data movement for object: DWR_WKDAY: Deleted 7 rows.
07:01:20 : Data movement for object: DWR_WKDAY: Inserted 7 rows.
07:01:20 : Data movement for object: DWR_WKDAY completed successfully!!
07:01:20 : Data movement for object: DWR_BASE_DAY: Deleted 7299 rows.
07:01:20 : Data movement for object: DWR_BASE_DAY: Inserted 21905 rows.
07:01:20 : Data movement for object: DWR_BASE_DAY completed successfully!!
07:01:20 : Data movement for object: DWR_TOT_TIME: Deleted 1 rows.
07:01:20 : Data movement for object: DWR_TOT_TIME: Inserted 1 rows.
07:01:20 : Data movement for object: DWR_TOT_TIME completed successfully!!
07:01:20 : Data movement for object: DWR_ADVR_YR: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_ADVR_YR: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_ADVR_YR completed successfully!!
07:01:20 : Data movement for object: DWR_ADVR_QTR: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_ADVR_QTR: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_ADVR_QTR completed successfully!!
07:01:20 : Data movement for object: DWR_ADVR_PERIOD: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_ADVR_PERIOD: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_ADVR_PERIOD completed successfully!!
07:01:20 : Data movement for object: DWR_ADVR_WK: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_ADVR_WK: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_ADVR_WK completed successfully!!
07:01:20 : Data movement for object: DWR_FSCL_YR: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_YR: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_YR completed successfully!!
07:01:20 : Data movement for object: DWR_FSCL_HLF_YR: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_HLF_YR: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_HLF_YR completed successfully!!
07:01:20 : Data movement for object: DWR_FSCL_QTR: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_QTR: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_QTR completed successfully!!
07:01:20 : Data movement for object: DWR_FSCL_MO: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_MO: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_MO completed successfully!!
07:01:20 : Data movement for object: DWR_FSCL_HLF_MO: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_HLF_MO: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_HLF_MO completed successfully!!
07:01:20 : Data movement for object: DWR_FSCL_WK: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_WK: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_FSCL_WK completed successfully!!
07:01:20 : Data movement for object: DWR_PLNG_QTR: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_PLNG_QTR: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_PLNG_QTR completed successfully!!
07:01:20 : Data movement for object: DWR_PLNG_YR: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_PLNG_YR: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_PLNG_YR completed successfully!!
07:01:20 : Data movement for object: DWR_PLNG_PERIOD: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_PLNG_PERIOD: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_PLNG_PERIOD completed successfully!!
07:01:20 : Data movement for object: DWR_PLNG_SEASON: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_PLNG_SEASON: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_PLNG_SEASON completed successfully!!
07:01:20 : Data movement for object: DWR_PLNG_WK: Deleted 0 rows.
07:01:20 : Data movement for object: DWR_PLNG_WK: Inserted 0 rows.
07:01:20 : Data movement for object: DWR_PLNG_WK completed successfully!!
07:01:20 : Data movement for object: DWR_BSNS_YR: Deleted 11 rows.
07:01:20 : Data movement for object: DWR_BSNS_YR: Inserted 31 rows.
07:01:20 : Data movement for object: DWR_BSNS_YR completed successfully!!
07:01:20 : Data movement for object: DWR_BSNS_HLF_YR: Deleted 21 rows.
07:01:20 : Data movement for object: DWR_BSNS_HLF_YR: Inserted 61 rows.
07:01:20 : Data movement for object: DWR_BSNS_HLF_YR completed successfully!!
07:01:20 : Data movement for object: DWR_BSNS_QTR: Deleted 41 rows.
07:01:20 : Data movement for object: DWR_BSNS_QTR: Inserted 121 rows.
07:01:20 : Data movement for object: DWR_BSNS_QTR completed successfully!!
07:01:20 : Data movement for object: DWR_BSNS_MO: Deleted 121 rows.
07:01:20 : Data movement for object: DWR_BSNS_MO: Inserted 361 rows.
07:01:20 : Data movement for object: DWR_BSNS_MO completed successfully!!
07:01:20 : Data movement for object: DWR_BSNS_HLF_MO: Deleted 241 rows.
07:01:20 : Data movement for object: DWR_BSNS_HLF_MO: Inserted 721 rows.
07:01:20 : Data movement for object: DWR_BSNS_HLF_MO completed successfully!!
07:01:21 : Data movement for object: DWR_BSNS_WK: Deleted 522 rows.
07:01:21 : Data movement for object: DWR_BSNS_WK: Inserted 1565 rows.
07:01:21 : Data movement for object: DWR_BSNS_WK completed successfully!!
07:01:21 : Data movement for object: DWR_CLNDR_YR: Deleted 11 rows.
07:01:21 : Data movement for object: DWR_CLNDR_YR: Inserted 31 rows.
07:01:21 : Data movement for object: DWR_CLNDR_YR completed successfully!!
07:01:21 : Data movement for object: DWR_CLNDR_HLF_YR: Deleted 21 rows.
07:01:21 : Data movement for object: DWR_CLNDR_HLF_YR: Inserted 61 rows.
07:01:21 : Data movement for object: DWR_CLNDR_HLF_YR completed successfully!!
07:01:21 : Data movement for object: DWR_CLNDR_QTR: Deleted 41 rows.
07:01:21 : Data movement for object: DWR_CLNDR_QTR: Inserted 121 rows.
07:01:21 : Data movement for object: DWR_CLNDR_QTR completed successfully!!
07:01:21 : Data movement for object: DWR_CLNDR_MO: Deleted 121 rows.
07:01:21 : Data movement for object: DWR_CLNDR_MO: Inserted 361 rows.
07:01:21 : Data movement for object: DWR_CLNDR_MO completed successfully!!
07:01:21 : Data movement for object: DWR_CLNDR_HLF_MO: Deleted 241 rows.
07:01:21 : Data movement for object: DWR_CLNDR_HLF_MO: Inserted 721 rows.
07:01:21 : Data movement for object: DWR_CLNDR_HLF_MO completed successfully!!
07:01:21 : Data movement for object: DWR_CLNDR_WK: Deleted 523 rows.
07:01:21 : Data movement for object: DWR_CLNDR_WK: Inserted 1566 rows.
07:01:21 : Data movement for object: DWR_CLNDR_WK completed successfully!!
07:01:21 : Data movement for object: DWR_DAY: Deleted 3652 rows.
07:01:23 : Data movement for object: DWR_DAY: Inserted 10957 rows.
07:01:23 : Data movement for object: DWR_DAY completed successfully!!
07:01:23 : Data movement for object: DWR_HLF_YR_TODATE_TRANS: Deleted 30 rows.
07:01:23 : Data movement for object: DWR_HLF_YR_TODATE_TRANS: Inserted 90 rows.
07:01:23 : Data movement for object: DWR_HLF_YR_TODATE_TRANS completed successfully!!
07:01:23 : Data movement for object: DWR_QTR_TODATE_TRANS: Deleted 100 rows.
07:01:23 : Data movement for object: DWR_QTR_TODATE_TRANS: Inserted 300 rows.
07:01:23 : Data movement for object: DWR_QTR_TODATE_TRANS completed successfully!!
07:01:23 : Data movement for object: DWR_MO_TODATE_TRANS: Deleted 900 rows.
07:01:23 : Data movement for object: DWR_MO_TODATE_TRANS: Inserted 2700 rows.
07:01:23 : Data movement for object: DWR_MO_TODATE_TRANS completed successfully!!
07:01:23 : Data movement for object: DWR_HLF_MO_TODATE_TRANS: Deleted 3000 rows.
07:01:23 : Data movement for object: DWR_HLF_MO_TODATE_TRANS: Inserted 9000 rows.
07:01:23 : Data movement for object: DWR_HLF_MO_TODATE_TRANS completed successfully!!
07:01:24 : Data movement for object: DWR_WK_TODATE_TRANS: Deleted 13833 rows.
07:01:25 : Data movement for object: DWR_WK_TODATE_TRANS: Inserted 41552 rows.
07:01:25 : Data movement for object: DWR_WK_TODATE_TRANS completed successfully!!
07:01:47 : Data movement for object: DWR_DAY_TODATE_TRANS: Deleted 666876 rows.
07:01:59 : Data movement for object: DWR_DAY_TODATE_TRANS: Inserted 2003204 rows.
07:01:59 : Data movement for object: DWR_DAY_TODATE_TRANS completed successfully!!
07:01:59 : Data movement for object: DWR_YR_TRANS: Deleted 9 rows.
07:01:59 : Data movement for object: DWR_YR_TRANS: Inserted 29 rows.
07:01:59 : Data movement for object: DWR_YR_TRANS completed successfully!!
07:01:59 : Data movement for object: DWR_QTR_TRANS: Deleted 39 rows.
07:01:59 : Data movement for object: DWR_QTR_TRANS: Inserted 119 rows.
07:01:59 : Data movement for object: DWR_QTR_TRANS completed successfully!!
07:01:59 : Data movement for object: DWR_MO_TRANS: Deleted 120 rows.
07:01:59 : Data movement for object: DWR_MO_TRANS: Inserted 360 rows.
07:01:59 : Data movement for object: DWR_MO_TRANS completed successfully!!
07:01:59 : Data movement for object: DWR_WK_TRANS: Deleted 517 rows.
07:01:59 : Data movement for object: DWR_WK_TRANS: Inserted 1560 rows.
07:01:59 : Data movement for object: DWR_WK_TRANS completed successfully!!
07:02:00 : Data movement for object: DWR_DAY_TRANS: Deleted 3283 rows.
07:02:00 : Data movement for object: DWR_DAY_TRANS: Inserted 10584 rows.
07:02:00 : Data movement for object: DWR_DAY_TRANS completed successfully!!
07:02:00 : Data movement successfully completed for all objects!!.. Committing the changes in the database...
07:02:00 : COMMIT complete. Exiting...
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:40.47