Oracle data warehouse design tips Oracle Tips by Burleson Consulting

I am designing an Oracle Data warehouse, and the Data warehouse will be created from an existing OLTP system.  Three questions:

Question 1: My existing OLTP system has 3 heavily summarized tables from which the existing query analysis reports are generated.  Can these tables by itself be called a Data warehouse, or should each of these 3 tables be broken up into subject-oriented fact & dimension tables like a star schema for implementing an Oracle data warehouse?

Answer 1: I have several hundred pages devoted to Oracle data warehouse design optimization in my book "Oracle Tuning: The Definitive Reference", but here is a quick summary.  A traditional data warehouse is all-about providing a vehicle for reporting from summary and aggregate information (using de-normalized tables, summarized tables and materialized views).  Most data warehouse designers replicate the data warehouse summary data onto another instance to avoid contention with the OLTP database, but this depends on the traffic on your system and the ability of your server to handle additional load (i.e. SMP processor capability).

There is no reason that you cannot keep the data warehouse within the same instance as the OLTP systems, but you need to consider several data warehouse design factors:

1 - Data Warehouse Query Performance - A data warehouse pre-summarizes and pre-aggregates the OLTP data so that the queries can fetch the result sets with only a few data block touches.  Make sure that your OLTP server has enough CPU resources to support Oracle parallel query, as you will need it to roll-up your summaries and aggregates:  See Oracle multiple CPU's and parallel query OPQ.

2 - Data warehouse schema design - If your existing summary tables do not require joins into other OLTP tables, then you will not benefit from a star transformation approach.  See my notes on Oracle star schema queries for details.

Question 2: I am aware that there are data warehousing tools available from Oracle like Oracle Warehouse Builder (OWB) for instance.  Instead of buying a new tool, what if i choose the "Data Warehouse" template name in the Step 2 of 8 of oracle Database creation assistant (DBCA)? Will it not generate a DW for me? how to use/explore this option?

Answer 2:  I would not use the DBCA to create a data warehouse instance.  Remember, the DBCA is a crutch for beginners. Once you become comfortable with Oracle, you should experiment with the custom database creation option, and eventually move-on to manual database creation. 

The data warehouse template in DBCA will simply create a one-size-fits-all starting point for a new data warehouse instance, an instance that is configured for large hash joins, large sorts and batch-related processing that is typical of a data warehouse.

For details on using DBCA, see the great book "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master). 

Here are the initialization parameters that are set in the DBCA for a data warehouse.  As we see, these are not correct for everyone, and you need to customize all data warehouse instances to match your specific processing needs.  For details, see my book "Oracle Tuning: The Definitive Reference":

Name Value aq_tm_processes 1 db_block_size 8,192 db_cache_size 16m db_file_multiblock_read_count 32 fast_start_mttr_target 300 hash_area_size 1m hash_join_enabled TRUE java_pool_size 33m job_queue_processes 10 large_pool_size 8m open_cursors 300 pga_aggregate_target 33m processes 150 query_rewrite_enabled TRUE remote_login_passwordfile EXCLUSIVE shared_pool_size 50m sort_area_size 1m star_transformation_enabled TRUE timed_statistics TRUE undo_management AUTO undo_retention 10,800

Question 3:  We have been using the existing OLTP system by creating a database using the "New Database" option...what if we had opted for "Transaction Processing"...would that have made any significant difference?

Answer 3:  No, not really.  See Oracle DBCA templates for database creation to see the differences in parameter values.

Remember, these are only starting points, and you will want to optimize all parameters, based on your workload.

Also see my data warehouse notes in "Oracle Tuning: The Definitive Reference", plus these online supplements: