This article provides an overview of Oracle Data Warehousing implementation. A data warehouse is a database designed to enable Business Intelligence (BI) activities. Data Warehouse is created for query and analysis rather than for transaction processing and usually contains the historical data derived from the transaction data but can include the data from the other sources.
The Data warehouses separate analysis workload from transaction workload and enable an organization to consolidate data from several sources. This helps in:
- Maintaining historical records
- Analyzing data to gain a better understanding of the business and to improve the business
To achieve the goal of enhanced business intelligence, the data warehouse works with the data collected from multiple sources. The source data may come from
- Internally developed systems
- Purchased applications
- Third-party data syndicators and other sources.
It might involve transactions, production, marketing, human resources and more. The Data warehouses are distinct from Online Transaction Processing (OLTP) systems. Using a data warehouse you separate the analysis workload from the transaction workload. Thus data warehouses are very much read-oriented systems.
The data in a data warehouse is loaded through an extraction, transformation, and loading (ETL) process from multiple data sources. The Modern Data warehouses are moving towards an extract, load, transformation architecture in which all or most data transformation is performed on the database that hosts the data warehouse.
Data Warehouse Key Characteristics
The key characteristics of the Data Warehouse (DW) are :
- Data is structured for simplicity of access and for high speed query performance.
- The end users are time sensitive and desire speed of thought response times.
- Large amounts of historical data are used.
- The queries often retrieve large amounts of data, perhaps many thousands of rows.
- Both predefined and ad hoc queries are common.
- The data load involves multiple sources and transformations.
In general, the fast query performance with high data throughput is the key to a successful data warehouse. A common way of introducing Oracle data warehousing is to refer to the characteristics of a data warehouse, they are :
- Subject Oriented
- Time Varient
1. Subject Oriented
The Data Warehouses are designed to help you analyze the data. Eg: To learn more about your company’s sales data, you can build a data warehouse that concentrates on sales. The ability to define a data warehouse by subject matter, in this case sales, makes the data warehouse subject oriented.
Integration is closely related to subject orientation. Data warehouses must put the data from disparate sources into a consistent format. They must resolve such problems as the naming conflicts and the inconsistencies among the units of measure. When this is achieved, they are said to be integrated.
Nonvolatile means, once entered into the data warehouse, data should not change. This is logical cause the purpose of a data warehouse is to enable you to analyze what has occurred.
4. Time Variant
Data warehouses focus change over time, is what is meant by the term time variant. In order to discover the trends and identify hidden patterns and relationships in business, analysts need large amounts of data. This is in contradiction to the online transaction processing (OLTP) systems, where performance requirements demand that historical data be moved to an archive.
Data Warehouse Tasks
As an Oracle data warehousing administrator or a designer, you can expect to be involved in the following tasks:
- Configuring the Oracle database for use as a data warehouse
- Designing data warehouses
- Performing upgrades of the database and Oracle data warehousing software to new releases
- Managing the schema objects, such as tables, indexes, and materialized views
- Managing users and security
- Developing the routines used for extraction, transformation, and loading (ETL) processes
- Creating reports based on data in the data warehouse
- Backing up data warehouse and performing recovery when necessary
- Monitoring data warehouse’s performance and taking preventive or corrective action as required
Data Warehouse Architectures
Data warehouses and their architectures vary depending on the specifics of an organization’s situation. Three common Data Warehouse architectures are:
- Warehouse Architecture with Staging Area
- Warehouse Architecture with Staging Area and Data Marts
Locus IT has a wide knowledge of Data warehousing and has expertise in providing Data warehousing services like implementation, support and training using Oracle products. For more information please contact us.