How To Create A Data Warehouse In Oracle

Oracle data warehouse design tips

Oracle Database Tips by Donald Burleson

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 :

Oracle Real Application Clusters RAC for Data Warehouse Applications

Tuning & Oracle Data Warehouse Management

Oracle data warehousing

New warehouse features in Oracle 10g data warehousing

New Developments Oracle Data Warehousing

Real-Time Data Warehousing and the Semantic Web

Oracle multiple block size Data Warehousing Architectures

Oracle data warehouse development best practices

Oracle Data Warehouse DBA

Is RAC Suitable for Data Warehousing?

Customer Data Hub and Data Warehousing

Oracle 10g Wins Data Warehouse Benchmarks

Web Services, BPEL Oracle Data Warehousing

Survey says Oracle Data warehouse preferred

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.