Tuesday, March 2, 2010

Data Warehouse verses Data Mart

Definitely spend time upfront thinking about what you're trying to achieve, not just today's requirements.

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.

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

If your existing summary tables do not require joins into other OLTP tables, then you will not benefit from a star transformation approach.

The bottom-up approach to data warehouse design. In this approach, data marts, or "mini data warehouse" data storage facilities are first created to provide reporting and analytical capabilities for specific business processes. Like bricks forming a wall, the data contained within these data marts can eventually be combined to create a more comprehensive data warehouse.

The top-down design method, as it involves a lot of up-front end-in-mind planning before any results can be extracted.

No comments: