4 OLAP Installation and Configuration

OLAP and Database Configuration

To set up a working OLAP environment, you must configure the database with the following parameter and configuration settings:

• Set SGA_TARGET to 35% of available memory.

• Set PGA_AGGREGATE_TARGET to 35% of available memory.

• Set OLAP_PAGE_POOL_SIZE=0 to make page pool size dynamic.

• Set _olap_page_pool_hi=30, lower than default of 50.

• Turn off Parallel Update for AW by setting set _olap_parallel_update_threshold and _olap_parallel_update_small_threshold to a high value, such as ~2Gb..2147483647.

The following commands show how to make all of these required changes:

alter system set sga_target=1365M scope=spfile;
alter system set pga_aggregate_target=1365M scope=spfile;
alter system set olap_page_pool_size=0 scope=spfile;
alter system set "_olap_parallel_update_small_threshold"=2147483647 scope=spfile;
alter system set "_olap_page_pool_hi"=30 scope=spfile;
alter system set job_queue_processes=5 scope=spfile;
shutdown immediate;
startup;

OLAP Installation Directory Setup

The files required for the installation of the Oracle Retail Data Model (OLAP Component and also OLAP Sample) on Oracle Database 10g Release 2 are in the following directories:

• ORACLE_HOME/ORDM/PDM/OLAP/Install/10gR2_ALL for the OLAP Component installation

• ORACLE_HOME/ORDM/Sample/OLAP/Install/10gR2_ALL for the OLAP Sample installation

Verify that the following files are present in the directory:

1. rbia_olap_views_star_setup.sql

2. RBIA_OLAP_ETL_AW_LOAD.sql

3. rbia_olap_grants.sql

4. RBIA_OOS_EMPTY.EIF

5. oos_forecast_stock_sales_olap_dml.txt

6. oos_forecast_stock_inv_olap_dml.txt

7. ordm_olap_user_env_install_scr.sql

8. ordm_olap_install_scr.sql

9. ordm_olap_demo_install_scr.sql

10. RBIA_OOS_DEMO.EIF.gz for OLAP Sample only

Edit Forecast Programs for Object Installation

For the OLAP Component installation, edit the forecast programs in the files oos_forecast_stock_sales_olap_dml.txt and oos_forecast_stock_inv_olap_dml.txt.

The section of program to be customized in the two text files is marked by BEGIN/END as follows:

**********BEGIN: Forecast Initialization section **************
...
**********END: Forecast Initialization section **************

By default, the forecast programs run at the Store/SKU Item/Day level using data for two business years (BY 2005 and BY 2006) as input and forecast the data for the third business year (BY 2007).

Change the following lines:

• v_time_yr1_dsc = 'BY 2005'

  This value depends on the year being forecast. The value should be the forecast year minus 2, that is, 2 years before the forecast year. The value must be in the format BY YYYY.

• v_time_yr2_dsc = 'BY 2006'

  This value depends on the year being forecast. The value should be the forecast year minus 1, that is, 1 year before the forecast year. The value must be in the format BY YYYY.

• v_time_yr3_dsc = 'BY 2007'

  This value depends on the year being forecast. The value should be the forecast year. The value must be in the format BY YYYY.

The forecast programs are run as part of the Historical Load. After the programs are run, the Historical load aggregates the Forecast Cubes so that the forecasted information is available across all levels of the cube.

Clean Up an Existing Oracle Retail Data Model OLAP User Schema

If you re-install Oracle Retail Data Model, you must re-create the OLAP user environment again from the beginning. Use the following SQL commands to clean up the OLAP schema before re-running the installation scripts:

SQL> drop user BIA_RTL_OLAP cascade;
SQL> drop tablespace BIA_RTL_OLAP including contents and datafiles;
SQL> drop tablespace BIA_RTL_OLAP_TEMP including contents and datafiles;

OLAP and Database Configuration

To set up a working OLAP environment, you must configure the database with the following parameter and configuration settings:

• Set SGA_TARGET to 35% of available memory.

• Set PGA_AGGREGATE_TARGET to 35% of available memory.

• Set OLAP_PAGE_POOL_SIZE=0 to make page pool size dynamic.

• Set _olap_page_pool_hi=30, lower than default of 50.

• Turn off Parallel Update for AW by setting set _olap_parallel_update_threshold and _olap_parallel_update_small_threshold to a high value, such as ~2Gb..2147483647.

Set memory_target to a value greater than the SGA and PGA setting. This is maximum memory used by the database for both SGA and PGA. The SGA and PGA settings specified are the minimum settings. If you do not set memory_target, instance startup fails. The next time these settings are validated failure occurs if spfile had an older and distinct setting for memory_target.

The following commands show how to make all of these required changes:

alter system set sga_target=1365M scope=spfile;
alter system set pga_aggregate_target=1365M scope=spfile;
alter system set olap_page_pool_size=0 scope=spfile;
alter system set "_olap_parallel_update_small_threshold"=2147483647 scope=spfile;
alter system set "_olap_page_pool_hi"=30 scope=spfile;
alter system set job_queue_processes=5 scope=spfile;
shutdown immediate;
startup;

OLAP Installation Directory Setup

The files required for the installation of Oracle Retail Data Model (OLAP Component and also OLAP Samples) on Oracle Database 11g Release 1, Oracle Database 11g Release 2 are in the following directories:

• ORACLE_HOME/ORDM/PDM/OLAP/Install/11gR1_ALL for the OLAP Component installation

• ORACLE_HOME/ORDM/Sample/OLAP/Install/11gR1_ALL for the OLAP Sample installation

Verify that the following files are present in the directory:

1. ordm_olap_11g_dml_cmds.txt

2. oos_forecast_stock_sales_olap_cc_dml.txt

3. oos_forecast_stock_inv_olap_cc_dml.txt

4. rbia_olap_11g_views_star_setup.sql

5. rbia_olap_11g_etl_aw_load.sql

6. rbia_olap_11g_sql_main_schema.sql

7. PSLSINV.M10.XML

8. PSLSINV.M11.XML

9. ordm_olap_11g_user_env_install_scr.sql

10. ordm_olap_11g_install_scr.sql

    Note:

    The script file ordm_olap_11g_user_env_install_scr.sql has two separate sections of code: one section for Oracle Database 11g Release 1 (11.1.0.6) and another for Oracle Database 11g Release 1 (11.1.0.7 or higher) as well as Oracle Database 11g Release 2 (11.2.0.1.0). By default, the script works for Oracle Database 11g Release 1 (11.1.0.7 or higher) as well as Oracle Database 11g Release 2 (11.2.0.1.0).

    To install OLAP Component on Oracle Database 11.1.0.6, edit the script as follows:

    1. Search for the string "PROMPT BEGIN Build of AW PSLSINV from Template" in script.

    2. PSLSINV.M10.XML is file to be used for Oracle Database 11.1.0.6 and PSLSINV.M11.XML is the file to be used for Oracle Database 11g Release 1 (11.1.0.7 or higher) as well as Oracle Database 11g Release 2 (11.2.0.1.0).

    3. Uncomment the PL/SQL block for 11.1.0.6. This block is marked by the following comments in script file:

       --====================================================

       - Database version: 11.1.0.6, OLAPI Metadata version: 1.0

       --====================================================

    4. Comment out the PL/SQL block for 11.1.0.7 and higher. This block is marked by the following comments in script file.

       --====================================================

       -- Database version: 11.1.0.7 higher, OLAPI Metadata version: 1.1

       --==================================================

Edit Forecast Programs

For the OLAP Component install, edit the forecast programs in the files oos_forecast_stock_sales_olap_dml.txt and oos_forecast_stock_inv_olap_dml.txt.

The section of program to be customized in the two text files is marked by BEGIN/END as follows:

**********BEGIN: Forecast Initialization section **************
...
**********END: Forecast Initialization section **************

By default, the forecast programs run at the Store/SKU Item/Day level using data for two business years (BY 2005 and BY 2006) as input and forecast the data for the third business year (BY 2007).

Change the following lines:

• v_time_yr1_dsc = 'BY 2005'

  This value depends on the year being forecast. The value should be the forecast year minus 2, that is, 2 years before the forecast year. The value must be in the format BY YYYY.

• v_time_yr2_dsc = 'BY 2006'

  This value depends on the year being forecast. The value should be the forecast year minus 1, that is, 1 year before the forecast year. The value must be in the format BY YYYY.

• v_time_yr3_dsc = 'BY 2007'

  This value depends on the year being forecast. The value should be the forecast year. The value must be in the format BY YYYY.

The forecast programs are run as part of the Historical Load. After the programs are run, the Historical load aggregates the Forecast Cubes so that the forecasted information is available across all levels of the cube.

Clean Up an Existing Oracle Retail Data Model OLAP User Schema

If you re-install Oracle Retail Data Model, you must re-create the OLAP user environment again from the beginning. Use the following SQL commands to clean up the OLAP schema before re-running the installation scripts:

SQL> drop user BIA_RTL_OLAP cascade;
SQL> drop tablespace BIA_RTL_OLAP including contents and datafiles;
SQL> drop tablespace BIA_RTL_OLAP_TEMP including contents and datafiles;