A Operations Scripts

Calendar Population Script

The Calendar population scripts consist of a one-time installation script named calendar_population.sql that:

1. Prepares some necessary changes on the schema.

2. Creates the Calendar_Population package that contains following procedures:

   • RUN (the main procedure).

   • 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 ) 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) 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_Time_Views sets up the Time hierarchy reporting layer views, star and hybrid snowflake views.

   • RBIW_Populate_Time_Transform populates the Time transformation tables using the base Time tables or views created above. It populates transformation data for both hierarchies: Business and Calendar.

Executing the Calendar Scripts

To populate calendar data:

1. Go to ORACLE_HOME/ORDM/PDM/Relational/SQL_Scripts/Calendar.

2. Log in to BIA_RTL user compile.

3. Execute the following SQL statements.

   @calendar_population.sql
   exec  Calendar_Population.run(date,num_years);
    
   

   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 'YYYY-MM-DD' (for example, '2005-05-18').

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

Bitmap Index for Fact Tables Script

The gen_bitmap.sh script is a Linux or UNIX Shell script. It generates a script that creates the on fact tables (Base, Derived, and Aggregate).

gen_bitmap.sh is located in the directory ORACLE_HOME/ORDM/PDM/Utilities/Bitmap_Generation.

gen_bitmap.sh includes the function add_bitmap_index. add_bitmap_index takes two parameters: Table Name and Column Name.

gen_bitmap.sh calls add_bitmap_index for all fact tables and columns to create bitmap indexes. Once the script is finished, it creates a SQL script named add_bitmap.sql in the DDL directory.

Partition Append Scripts

You use the partition scripts to append partitions. The partition scripts consist of the gen_script_add_partition.sh which is a shell script that generates two other scripts:

• add_partition_tbs.sql that creates tablespaces

• add_partition.sql that adds partitions

Executing the Partition Append Scripts

Take the following steps:

1. Go to ORACLE_HOME/ORDM/PDM/Utilities/Partition_Generation.

2. Login into BIA_RTL user

3. Invoke the script GEN_SCRIPT_ADD_PARTITION.SH using two parameters where the first parameter is the start year and the second parameter is the end year as shown in the following example.

   [oracle@zeta oracle]$ gen_script_add_partition.sh 1997 2005
   

4. After the Partition Appending scripts are generated, run the SQL script in SQL*Plus to add new partitions to each partitioned table as follows:

   1. Login into BIA_RTL user.

   2. Run the scripts in SQL*Plus as shown in the following code:

      SQL> @add_partition_tbs.sql
      SQL> @add_partition.sql
      

Create Dimensions Script

By default, Oracle Retail Data Model creates three dimensional objects for major dimensions in the BIA_RTL schema: product, time, organization. The dimensional object gives advantages in the query rewrite. Consequently, in the future, reporting tools such as BIEE might import the dimension definition from the database instead of creating them from the beginning each time.

The dimensional object scripts are one-time installation scripts. By default, Oracle Retail Data Model uses three scripts to create hierarchies for three dimensions: organization (ORG_DIM.sql), product (PRO_DIM.sql) and time (TIM_DIM.sql). The script tries first to drop dimensions, and then creates them.

To execute the Dimensional Object Script

To create major dimensions yourself:

1. Go to ORACLE_HOME/ORDM/PDM/Relational/SQL_Scripts/Dimensional_Object

2. Login into BIA_RTL user.

3. Run the Create_Dimensions script by issuing the following SQL*Plus command.

   SQL> @Create_Dimension.sql
   

4. Check the Create_Dimension.spool for errors after the execution.

Foreign Key Manipulation Scripts

The generate_fk_script.sql script, which is generated according to RBIA schema, allows you to enable or drop all foreign keys at the same time. The generate_fk_script.sql script generates two other SQL scripts:

• create_fk_constraint.sql creates of all the Foreign Key Constraints in the BIA_RTL Schema which is convenient when working with the ETL. Together with the drop_fk_constraints.sql script, you can maintain the foreign key; or, if you want to, disable and then enable some of foreign keys (on some tables).

• drop_fk_constraint.sql drops the Foreign Key Constraints in the BIA_RTL Schema. If you choose to drop only some Foreign Key constraints on certain tables (for example, when you did the ETL for a table that failed because of reference integrity), you can search the script by table name and run only the script for that one table.

Executing the Scripts to Create and Drop Foreign Keys

To execute these scripts:

1. Go to ORACLE_HOME/ORDM/PDM/Relational/SQL_Scripts/Create_Drop_FK_Constraint/

2. Login into BIA_RTL user.

3. Run the two scripts in SQL*Plus as shown in the following code.

   SQL> @create_fk_constraints.sql
   SQL> @drop_fk_constraints.sql
   

Lookup Value Population Scripts

The Lookup Value population scripts are one-time installation scripts that populate Seed value for lookup tables:

• insert_LookupOthers_record.sql inserts values into the physical lookup table.

• insert_LookupViews_record.sql inserts values into a master code table, which then provides data for the various Lookup Views.

All lookup tables are divided into two groups:

• Lookup tables with multiple levels. Each of these are implemented as one physical table. The insert_LookupOthers_record.sql script inserts seed values into each of the physical lookup tables.

• Lookup tables with only one level that are used purely for lookup. These are implemented into one master code table. There are views that present the value of each table. The script insert_LookupViews_record.sql inserts seed values into the master code table.

Executing the Lookup Population Scripts

To insert lookup data, take the following steps:

1. Go to ORACLE_HOME/ORDM/PDM/Relational/SQL_Scripts/Lookup_Value_Insert.

2. Log in to the BIA_RTL Schema.

3. Execute the two scripts as shown in the following code.

   SQL> @insert_LookupOthers_record.sql
   SQL> @insert_LookupViews_record.sql
   

4. Check spool files insert_LookupOthers_record.spool and insert_LookupViews_record.spool for errors.

The Out of Stock Script

The Out of Stock scripts are:

• Out_Of_Stock_SQL_Model.SQL which is a script that creates the materialized view that keeps the forecast value for sales quantity and stock on hand.

• STOCK_Modified_SQL_MODEL_Query.SQL which is a version of the model script (which was the first version of the script) that contains comments and modified SQL.

The Out of Stock scripts are located in the 'Out of Stock' folder (ORACLE_HOME/ORDM/PDM/Relational/SQL_Scripts).

Included with the Out of Stock scripts is Out_Of_Stock_SQL_Model.xls which is a spreadsheet that explains the logic of the forecast. In the most general terms, the Out of Stock files use the following calculation model:

1. Get the average SLS_QTY for around 5 days last year: 'SLS_QTY_LP'

2. Get the average SLS_QTY for around 5 days the year before last year: SLS_QTY_LLP.

3. Use SLS_QTY_LP and increment percentage for last year to get the forecast sales quantity.

   FCST_SLS_QTY=SLS_QTY_LP*(SLS_QTY_LP/SLS_QTY_LLP)

4. Get the forecast stock on hand (FCST_SOH_QTY) in the same way

5. Forecast out of stock=FCST_SOH_QTY-FCST_SLS_QTY By now, the data forecast is for two days: 20070101 and 20070102.

As written, the script forecasts data for two days. (in this case, 20070101 and 20070102). To forecast more data, make changes to the script.

RFMP Calculation Script

The RFMP calculation script is a one-time installation script that is integrated into the Installer and the Intra-ETL packages. The script contains the following:

• Pop_rfmp procedure:

  1. Creates two TMP tables for rotated calculation.

  2. Populates tmp_1 with New Month data filtered by Month+Busn_unit

  3. Performs three calculation loops for Recency, Frequency, Monetary. (The fourth calculation loop for Profit is optional.)

  4. Copies the result into DWD_CUST_RFMP_SCR table.

• Following the pop-rfmp procedure are two PLSQL blocks which, in turn:

  1. Populate_DWD_CUST_RFMP_SCR that calls pop_rfmp with all bsns_unit code.

  2. Create a job for the rfmp procedure to run every day.

Executing the RFMP Script

To run the RFMP script separately, take the following steps:

1. Go to ORACLE_HOME/ORDM/PDM/Relational/SQL_Scripts/RFMP.

2. Login to the BIA_RTL Schema.

3. Run the two scripts using the following code:

   SQL> @RFMP_Population.SQL
   SQL> EXEC RFMP_Population.RUN