If you are working as database administrator and having interest to maintain the Oracle data warehouse in your company, you should need to learn special skill sets and extensive tuning of large SQL queries for obtaining superior optimal performance. Well, backup and recovery strategy is another requirement skill that you need to pay concentration to because the data warehousing is generally a large database with large storage.
What is an Oracle Data Warehouse ?
Most of the Business transaction databases are OLTP (Online transaction Processing).
A data warehouse is a special relational database that is designed for specific purpose other than Online transaction and processing (OLTP). Data warehouse is designed for query and analysis of Business Data.
It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.
Oracle Data warehouse database has all components of Online transaction Processing (OLTP) database features, On top of that data warehouse environment includes an extraction, transportation, transformation, and loading (ETL) solution, client analysis tools, online analytical processing (OLAP) engine and other applications that manage the process of gathering data and delivering it to business users.
Data warehouse is a database specially designed for specific data analysis based on business requirement. Data warehouse main focus is around various reporting needs to answer various decision support needs. For example if you are in marketing business, reports such as who was the most valuable customer.
Well, If Data warehouse needs to deliver complex and comprehensive reporting information across all available systems in a business, it needs to be linked to various database systems in a given Business. Thus integrating various parts of the company information is another important part in designing the data warehouse.
Yet another important component of data warehouse design is in maintaining the large amount of data. If you have to generate reports on various permutation and combination possible to support the decision making, you need to have supporting underlying data somewhere in the database. Storing such a large amount of information is a challenge that obviously requires a large amount of storage.
Storage in Data warehouse is normally in Terra bytes depending on type of Business.
When there is huge amount of data stored in database, extracting it through queries to produce required reports becomes a huge challenge. Due to this voluminous data, performance of queries is also important. To support such special requirement Oracle Data warehouse designed completely with a different approach compared to traditional Online transaction Processing (OLTP) relational database.
Because of above mentioned reason, one of the difference between data warehouse is that it not usually in third normal form (3NF), this type of data normalization is usually common in Online transaction Processing (OLTP) environments.
Since Data warehouses and Online transaction Processing (OLTP) systems functionality varies and thus their design requirements.
Here are some examples of differences between typical data warehouses and OLTP systems:
Characteristics of Data warehouse:
In Data warehouse end users do not update data directly. However data warehouse is updated on regular basis with data from other OLTP systems and loaded using bulk data loading techniques. This process is referred as ETL (Extraction, transform and Loading) process.
Due to massive data stored in data warehouse databases, the query optimization is achieved by de-normalized schemas (such as a star schema) and often data is in de-normalized form.
Adhoc query is very basis of data warehouse design, it may not be possible to know the exact workload of your data is advance, so it is important to design the data warehouse to perform well for a wide variety of possible query operations. For instance a typical data warehouse query scans thousands or millions of rows.
Data warehouse being a decision support system which supports historical analysis, it stores may months to many years of data. Due to this massive data, data warehouse requires massive storage requirement.
Characteristics of Online transaction Processing (OLTP):
OLTP systems often use fully standardized schemas to optimize update / insert / delete performance and to ensure data consistency.
OLTP systems typically store data for only a few weeks or months depending on business needs and only store historical data as needed to successfully meet the demands of the current transaction.
Due to the fact that OLTP systems don’t store any historical data, storage requirement on OLTP systems compared to Data warehouse is limited.
As an oracle database administrator on oracle Data Warehouse, you need to understand certain data warehouse concepts.
Oracle data warehousing concepts =>
- Overview of Data warehouse Design
- Partitioning and Partition pruning
- Query response time tuning with Parallel operations
- ETL (Extract, Transform and Load data) in the data warehouse.
- Data warehouse performance enhancement using Materialized views
- Query rewrite using materialized views
- Working with Dimensions
- Tune materialized views for fast refresh and query rewrite using SQL Access Advisor and PL/SQL procedures.