Is it common to include warehouse data in a transactional database?

[boxeronboard]

This may be a simple question without a simple answer....

We have a single Oracle database in which we are housing three different types of tables (total 230+ tables). Our DBA says there is no benefit to separating the transaction warehousing data in a database separate from the rolling 60 day processing support tables. Can you share the perferred method of your organizations?

Highlevel (very) overview of the database:

The three types of data are housed in the Oracle database are described below. Each of the three groups of tables are managed by different groups of people within our department.

1. Transactional processing (appx 80 tables)
60 days Realtime transaction processing (requires 24x7 availability) tables: pr_****
EST 400k rows added daily, 400k rows deleted daily

2. Online transaction access (appx 80 tables)
60 days Online access to transaction history (customer service requires prompt access to the data tables: tr_*** (data replicated from replicated from pr_***** tables)
EST 400k rows added daily, 400k rows deleted daily

3. Data warehouse (appx 40 tables)
7 years of transaction data for reporting (both normalized tables and star schemas) hs_***** (and are looking to add 3 fact tables with about 20 dimensions)
(data replicated from replicated from tr_**** tables)
EST 400k rows added daily – no deletes

tr_**** table data is replicated in hs_***** for 7 year retention

 

[LKBrwnDBA]

Depends on the size of the database/data warehouse

Due to performance reasons, the initialization parameters for a DW may have different settings than OLTP database.

If performance is no issue, then I would just separate by schema.






----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[carp]

I would agree. What you will probably wind up with is a database that does warehouse stuff really well but falls short on the OLTP side, or vice versa, or one that does neither really well. But if the whack on performance is acceptable, it probably makes more sense then creating a second database - particularly if both databases would be running on the same server!

 

[boxeronboard]

Thank you for taking time to respond to my inquiry. I appreciate your comments.