12/13

7 Guidelines for Initial Load and Utility SQL Scripts

7.1 Guidelines for Initial Load

You must use the following guidelines for initial load:

1. Before the initial load, locate the Oracle DDL script hdwf_fk_index_drop_ddl_6_1.sql (if you are a HDWF only customer) or OHADI_FK_INDEX_DROP_DDL.sql (if you are an OHADI customer) on your Oracle Database 11.2.0.4 and execute it.

2. Gather statistics after initial load as recommended in the Oracle Performance Tuning Guide.

3. For Exadata, after initial load in HDWF, execute hdwf_ddl_6_1_post_initial_exadata.sql. This reverts CELL_FLASH_CACHE to default for a subset of tables that were pinned to flash cache. It also changes compression for all tables that had HCC for Query High to COMPRESS FOR OLTP. For details on pinning, see section 4.5, "Exadata Machine", in the Oracle Healthcare Data Warehouse Foundation Programmer's Guide.

4. After HDWF installation, READ ONLY accounts should be created for accessing the HDWF Schema to load EHA applications. Data modification privileges should not be given to the user running ad-hoc queries on HDWF.

5. Grant update privileges to the users who load data into the HDWF Schema.

6. Review the Introducing Database Security for Application Developers chapter in the Oracle Database Security Guide for more information.

7. While loading data into downstream data marts that extract data from the HDM schema:

   1. Re-create the foreign key indexes using OHADI_FK_INDEX_CREATE_DDL.sql.

      Note:

      Since re-creating the foreign key index is resource intensive, Oracle recommends that you run this script during the environment downtime as the ETLs consume more time to load the HDM schema.

   2. Turn on index monitoring and analyze the usage of these foreign key indexes during the ETL from the HDM schema to the data mart.

   3. Analyze the index usage with real data sets that represent your workload and make the indexes that are still not utilized as invisible.

   4. Delete these invisible indexes if they are still not utilized in the ETLs and turn off index monitoring.

      Note:

      Note down the indexes that you delete as you may have to re-create them when you create a new data mart, expand the existing data mart to include other subject areas, or extract data from new HDM tables or columns.

7.2 HDWF Utility SQL Scripts

7.2.1 Exadata Environment Utility Scripts

The following are Exadata environment utility scripts:

• hdm_hcc_maintenance_6_1_exadata.sql - You can change the compression for all the partitioned tables from OLTP compression to Hybrid Columnar (Query High - HCC) Compression using this script. For more details, see "Exadata compression maintenance script" in the Oracle® Healthcare Data Warehouse Foundation Programmer's Guide.

• upgd_hdwf_ddl_6_1_post_initial_exadata.sql - Use this script if you are upgrading from HDWF 6.0 to 6.1 after data is loaded to new tables introduced in HDWF 6.1. This script changes compression for new tables added in HDWF 6.1 that had compression set to HCC for Query High to OLTP compression mode.

• hdwf_ddl_6_1_post_initial_exadata.sql - Use this script to revert CELL_FLASH_CACHE to default for a subset of tables that were pinned to flash cache. Also, it changes the compression for all tables that had HCC for Query High to COMPRESS FOR OLTP during a fresh installation of HDWF. For more details, see Section 7.1.