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