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.
Tuesday, March 2, 2010
Initialization Parameters in the DBCA
Here are the initialization parameters that are set in the DBCA for a data warehouse. These are not correct for everyone, and you need to customize all data warehouse instances to match your specific processing needs.
| 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 |
Planning for a Data Warehouse
Data Warehousing comes in all shapes and sizes, which bear a direct relationship to cost and time involved. The approach to starting a Data Warehousing project will vary and the steps listed below are summary of some of the points to consider.
1) Get Professional Advice
Data Warehousing makes a great difference is the lead has been there and done that. It will save a huge bundle to get professional advice upfront. Endless meeting times can be saved and the risk of an abandon data warehousing project can be reduced.
2) Plan the Data
Know what metrics you want to measure in the Data Warehouse and ensure that there is the appropriate data to provide for the analysis. If you wish to obtain periodic Key Performance Index (KPI) data for shipping logistics, make sure that the appropriate data is piped into the data warehouse.
3) Who will use the Data Warehouse
The power Data Warehouse consumers are business and financial managers. Data Warehouses are meant to deliver clear indications on how the business is performing. Plot out the expected users for the Data Warehouse in the enterprise. Gauge that they will have the appropriate reports in a format which is quickly understandable. Ensure that planning exercises are conducted in advance to accumulate scenarios on how the Data Warehouse will be used. Always remember that data has to be presented attractively and in a format business managers will feel comfortable. Text files with lines of numbers will not suffice!
4) Intergration to External Applications
Most Data Warehousing projects sink or swim by their ability to extract data from external applications. Enterprises have a slew of applications either developed inhouse or obtain from a vendor. Conceptually, your Data Warehouse will act as the heart to diverse applications running in the enterprise. All important data will flow in or out of the Data Warehouse.
5) Technology, What Technology?
At the bits and bytes level, a Data Warehouse is a souped up database. It will be built from one of the major Relational Database Management System (DBMS) vendors like Oracle, IBM, Microsoft. Open source databases, like mySQL, can also support Data Warehousing with the right support in place.
1) Get Professional Advice
Data Warehousing makes a great difference is the lead has been there and done that. It will save a huge bundle to get professional advice upfront. Endless meeting times can be saved and the risk of an abandon data warehousing project can be reduced.
2) Plan the Data
Know what metrics you want to measure in the Data Warehouse and ensure that there is the appropriate data to provide for the analysis. If you wish to obtain periodic Key Performance Index (KPI) data for shipping logistics, make sure that the appropriate data is piped into the data warehouse.
3) Who will use the Data Warehouse
The power Data Warehouse consumers are business and financial managers. Data Warehouses are meant to deliver clear indications on how the business is performing. Plot out the expected users for the Data Warehouse in the enterprise. Gauge that they will have the appropriate reports in a format which is quickly understandable. Ensure that planning exercises are conducted in advance to accumulate scenarios on how the Data Warehouse will be used. Always remember that data has to be presented attractively and in a format business managers will feel comfortable. Text files with lines of numbers will not suffice!
4) Intergration to External Applications
Most Data Warehousing projects sink or swim by their ability to extract data from external applications. Enterprises have a slew of applications either developed inhouse or obtain from a vendor. Conceptually, your Data Warehouse will act as the heart to diverse applications running in the enterprise. All important data will flow in or out of the Data Warehouse.
5) Technology, What Technology?
At the bits and bytes level, a Data Warehouse is a souped up database. It will be built from one of the major Relational Database Management System (DBMS) vendors like Oracle, IBM, Microsoft. Open source databases, like mySQL, can also support Data Warehousing with the right support in place.
Subscribe to:
Posts (Atom)