Author: Jennifer Lously

In this article I am providing some guidelines that are useful while designing an oracle data warehouse.

Give time to design and analysis phase:

Don't skimp on the design and analysis phase. You need to understand your source data and specify how the data maps and how you deal with the transformations and cleansing.

Do proper data mapping:

For each mapping produce a specification and test plan on to the developer so that they know, item by item, what bit of data maps on to where and how to test whether their mapping is working ok.

Use powerful developer machines:

Use powerful developer machines. You need at least 1GB of RAM and a 1GHz CPU. Preferably your Design Repository database will be on a server with locally attached disks within your department.

Be careful while keeping data warehouse within the same instance:

You can keep the data warehouse within the same instance as the OLTP systems, but you need to consider several data warehouse design issues. Make sure that your OLTP server has sufficient CPU resources to support Oracle parallel query, as you will need it to roll-up your summaries and aggregates. If your existing summary tables do not need joins into other OLTP tables, then you will not get advantage of a star transformation approach. 

Do not atick to Star Schema Modeling:

Do not stick to star schema modeling. There's a place for normalized modeling in a data warehouse too. Also consider the management benefits of snowflake modeling.

Keep mapping simple:

Keep mappings simple, do one thing at a time. The danger with loading multiple tables, or loading one table after another after another, is that you'll never be able to work out what's gone wrong if the test figures come out wrong, and you'll have the devil of a job tuning the mapping if you've got multiple levels of INSERT INTO .. SELECT in your mapping.

Do not use Oracle Database creation assistant (DBCA):

Prefer not to use DBCA. The DBCA is a support for beginners. Once you become at ease with Oracle, you should experiment with the custom database creation option, and eventually move-on to manual database creation.  There are three templates; general purpose, OLTP and data warehouse. The parameter setting is the most important differences in these DBCA templates. The data warehouse template in DBCA creates a starting point for a new data warehouse instance. Also there are the initialization parameters that are set in the DBCA for a data warehouse.  These parameters are not correct for everyone, and you need to modify all data warehouse instances to match your specific processing requirements.

Use the Flashback technologies:

Use the Flashback technologies in Oracle 9i and 10g to implement mapping and process flow transactions so that your mapping or process flow stores the SCN (System Change Number) at the start of the mapping, and then rolls either the tables within the mapping, or the whole database.

FLASHBACK table:

Use FLASHBACK table to roll back the tables within a process flow if it fails.

FLASHBACK DATABASE:

Use FLASHBACK DATABASE to roll back the entire ETL process. It's surely faster than performing a point-in-time recovery if the whole ETL process goes belly-up and it gives us the ability to pull together a number of mappings and processes into a single atomic package of work which we can reverse out if need be.

Keep daily record of ETL processes:

Keep daily record of the ETL process as at the end of the previous day, and which includes all the changed items in the collection you created yesterday.

Record the run times:

Record and review the run times of your various mappings and process flows. Once a week, list these out in order of run time, longest at the top, and in addition identify those whose run times have varied the most over time.

Tuning:

When tuning a mapping, look for the simple answers first. Assuming that you've designed your mapping properly in the first place, the reason it's running slowly is probably because an index isn't getting used, or you need to increase the size of HASH_AREA_SIZE, or because you're joining too many tables together. Only when you've tried the obvious try techniques such as tracing, the danger with trying the complicated first is that you get drowned in diagnostic data and miss the obvious solution.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Expert Tips on Drop Temporary Tablespace Hangs!!
   Beware of using Oracle RAC with Data Warehouse!!
   Oracle Guide: Recovering accidentally dropped tables!!
   The Secrets of Truly Dropping a Table in Oracle 10g!!