Architecture


Tutorial Home >Hardware >Storage and Drives >Data Warehousing >How do I access OLTP data residing in relational databases?
Tutorial Home >Hardware >Storage and Drives >Data Warehousing >What is an ODS? >Architecture

  Step 1:  Concept

Open a Report
An ODS is staging area. Data is "extracted" from an OLTP system, "transformed" into a suitable format, and stored in an ODS, which then "loaded into the Data Warehouse. An ODS does not support reporting needs.
  Step 2:  Model

Typically, ODS is built on relational model. These models support very high CPU utilizations such as updates of huge volume of data. DB2 UDB or Oracle databases support relational model. IMS database supports hierarchical model.
  Step 3:  Access Language

The OLTP data residing in the relational databases such as DB2 UDB or Oracle 8i can be accessed using ANSI standard SQL. Oracle also supports PL/SQL (native to Oracle). For example, SELECT * FROM CUSTOMER_TABLE; is a SQL statement issued to retrieve data form the customer table. QMF, Brio Query, T.O.A.D and Oracle's SQL*Plus are examples of software interfaces that generate SQL or enable SQL execution.
  Step 4:  Example

Open a Report
Typically, every data warehouse needs to have an ODS associated with it order to process the OLTP data into a suitable format.