24295 Several enhancements done in Cube Manager 

Module:

Ginesys Web – Admin – Utilities – Cube Manager

Enhancement Summary:

The users of the Cube Manager feature were facing some challenges which have been addressed through the enhancements in this release. 

Scenario:

The challenges faced were as below -

High Data File Usage:

For faster loading of cube tables we were using DIRECT PATH INSERT (refer this link to understand what is direct path insert). However, that filled the data files faster than usual with lot of empty spaces in the data files. These empty spaces could only be retrieved (think de-fragmented) only when a backup and restore was taking place. Since backup-restore operation is not a regular process in most clients, it kept creating lot of data files with substantial fragmentation in between. More cubes, more refresh frequencies, more data files were generated and disk space was consumed.

High Archive Log:

In Absence of direct path INSERT where backup using archive log (RMAN) were active: Since we could not adopt a direct path insert approach for the above, any refresh of the cubes started to generate huge amount of archive logs in customer places where the following was true -

1. Single database configuration was done for OLTP + OLAP
2. Database was running in ARCHIVELOG mode for database backup services (refer this link to understand more about ARCHIVELOG / NOARCHIVELOG mode of database). This was happening since ARCHIVELOG mode was set at the database level and cube refresh was happening in the same database. This configuration was most common and was thus causing problems for many customers in terms of performance

High resource consumption of Production server even in separate MIS Write Database configuration:

A separate MIS / OLAP Write database configuration was proposed in the earlier design to have following performance benefits -

1. Spread Disk IO over multiple physical machines
2. Have the analytical reports access the report data from a different physical server.

These two benefits were addressed in a separate write DB configuration. However, since the database functions / procedures for cube refresh was present in production server, while cube refresh these functions started choking production server resources. Thus cube refreshing became difficult during working hours as production server performance got hampered.