Select Page

Art of BI: Assigning Oracle BI Applications Tablespaces to Indexes

Author: Christian Screen | | December 8, 2016

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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How To Fix Log Shipping Failure Errors In SQL Server

Learn how to fix common Log Shipping Failure errors in SQL Server. Follow Datavail’s step-by-step instructions, screenshots, and software script here!

Eric Russo | August 10, 2016

What Does a DBA Do? 22 DBA Responsibilities You Should Know About: Part I

Not everybody knows what a Database Administrator does. Learn here what DBA job responsibilities are so you can see the depth and breadth of their tasks.

Craig Mullins | November 3, 2016

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.