Art of BI: Assigning Oracle BI Applications Tablespaces to Indexes

In OBIA 11g, if you don’t spread the storage of the OBIA DW data objects across multiple RDBMS tablespaces, your DBA may come knocking at your door asking why only one tablespaces they created for the OBIA system is consuming a large amount of disk and I/O. You may also notice poor performance in your OBIA full and incremental load times. Either way, completing the steps in this article is the best practice for helping to mitigate both of the above scenarios.

Implementing Oracle BI Applications 11g is a challenge for many organizations. Datavail’s OBIA 11g training course and our professional services help enterprises make sure they are optimizing their implementation in Oracle Analytics.

This post is a snippet from one of our OBIA training exercises where our classroom training helps Oracle customers implement and use OBIA with best practices. To extend the ODI 11g ETL’s storage of index files across multiple tablespaces, one must conduct a configuration post-initial installation and configuration of the OBIA system. Be sure to read our recommendations for scientifically implementing this change in your system at the end of this post prior to conducting this change in a new implementation.

To get started:

• Open the ODI 11g Studio Client to access the OBIA Project and then in the Designer tab expand the Models accordion section.

• Expand the Oracle BI Applications model folder and open the Oracle BI Applications model to reveal the model definition, etc.

• Click on the Flexfields option and you’ll see some pre-developed fields for the respective tablespace allocations.

By default, the Default checkbox is checked which means that the tablespaces automatically for Index objects (indexes, bitmap indexes, etc.) and stage objects (staging tables, etc.) will be loaded to the default tablespace which is the Data Warehouse (DW) tablespace.

For obvious reasons, you’ll want to spread the ETL target data integration, so uncheck the checkbox under the default column for each respective flexfield desired and enter in the value column the name of the tablespace you created when you ran the OBIA RCU to create the appropriate database schemas and tablespaces. If you’ve forgotten the names of the tablespaces created, you can use Oracle SQL Developer’s DBA interface to view all tablespaces for your database as seen in the above image.

Save your work in the ODI Studio client after you’ve made all of your changes. Then you can consider running your data loads but please read our recommendations for considerations below prior making or solidifying your changes.

Our recommendation regarding this option is to:

• Conduct this change only after you have configured OBIA for at least one OBIA module and have run a successful Full Load to completion without errors (having completed also an incremental load without errors in addition to a full load would be optimal).
• Execute a full load prior to making this change to capture a benchmark elapsed time for how long it takes to run a Full Load to the DW using a fairly reflective subset of data. For example, execute it for one or two years of data instead of the full ERP/CRM history if you have lots of years of data.
• Consider making one flexfield change at a time so that you can incrementally understand and document the impact as this will be important to explain to your team and DBA. Start with the OBI Stage Tablespace since staging object issues, if any, are typically encountered earlier in the ETL process and are usually easier to resolve.