Friday, 19 September 2014

OBIA – Separate Database for Oracle Business Analytics Warehouse

There is a discussion since very long if is it technically possible to put the Oracle Business Analytics Warehouse in the same database as the transactional database. It is very loud and clear that it is not recommended for performance reasons to have transactional and analytical schemas on same database. The transactional database is structured as an online transaction processing (OLTP) database, whereas the Oracle Business Analytics Warehouse is structured as an online analytical processing (OLAP) database, each optimized for its own purpose.
Why Better Not Together?
The reasons for not combining the two databases are the following:
ETL is configured to maximize hardware resources; and, therefore, the warehouse should not share any resources with any other projects.
The analytical queries interfere with normal use of the transactional database, which is entering and managing individual transactions.
The data in a transactional database is normalized for update efficiency. Transactional queries join several normalized tables and will be slow (as opposed to pre-joined, de-normalized analytical tables).
Historical data cannot be purged from a transactional database, even if not required for current transaction processing, because you need it for analysis. (By contrast, the analytical database is the warehouse for historical as well as current data.) This causes the transactional database to further slow down.
Transactional databases are tuned for one specific application, and it is not productive to use these separate transactional databases for analytical queries that usually span more than one functional application.
The analytical database can be specifically tuned for the analytical queries and Extract-Transform-Load (ETL) processing. These are quite different from transactional database requirements.
This information is copy/pasted from Oracle’s Installation document.

No comments:

Post a Comment