Successful Data warehouses had to achieve three main things in the past. They had to load the data, manage the data and provide access to the data. There are two schools of thought regarding the modeling of the data warehouses.
The first one is popularized by Ralph Kimball called the dimensional approach. Dimensional modeling is achieved by simplifying the data model to facilitate faster access to data using star and snowflake schemas. Dimensional models place emphasis on information access and not on the data management part of data warehousing. This approach simplifies access but may limit the depth of the analysis. In long term, the organizational goals and hierarchies change and might result in complete or partial redesigning.
The second school of thought is a traditional one where the authors Bill Inmon and Barry Devlin argued that the devil was in the detail of data and has to be preserved. 3NF structures place emphasis on the data management of the data warehouse. The detail of the data is stored in normalized structures to avoid redundancy and users typically require solid understanding of the data model in order to navigate through the data model. Further, designing the database with 3NF structures does not mean it is resistant to business change. A change in organizational hierarchy might result in the change of Physical table change for storing the hierarchy which makes “What Was” reporting perspective problematic.
Both the models have their advantages and drawbacks. To be effective, the next generation data warehouses should leverage the strengths of both the models and not just one of them.
To address the next generation data warehousing requirements, Oracle has come up with a Reference Architecture and pre packaged data warehousing applications. The goal of Oracle’s Data warehouse reference architecture is to deliver a high quality integrated system and information at a significantly reduced cost over the long term.
The below picture depicts the design of data warehouse as per the reference architecture.
According to this architecture, the core data warehouse can be divided into three conceptual data layers: the staging data layer, Foundation data layer and Access and Performance data layer.
Staging Data Layer:
The first destination of data that has been extracted from source is the staging data layer. This layer acts as a temporary storage location and is the workshop area for data manipulation. Rejected data after data cleansing is stored in this layer for corrective actions. Data cleansing is still an important part of data warehousing and is carried out in this layer. This layer isolates the rate at which data is received from source to the rate of consumption by the data warehouse. For Example, the changed data can be captured from the source in near real time by using tools like Golden Gate but consumed by data warehouse at intervals dictated by business requirements.
Foundation Layer:
This layer is also referred as Atomic layer. As the name implies, this layer records data at the lowest possible granularity. This layer is the heart of the warehouse and responsible for managing data over long term. This layer is modeled using 3NF for storage efficiency. The data in this layer is recorded in a business neutral fashion to eliminate the risk of restructuring and modifying the data because of business changes. In short, foundation layer is modeled as temporal process neutral 3NF data storage layer.
Access and Performance Layer:
This layer adds the Information access component to our architecture as the foundation layer is difficult to navigate and may be performing poor for aggregated reports. This layer mostly houses the aggregated data that can be queried for quick display of reports, dashboards etc. This layer gives the ability to quickly reconstitute the data into a different representation. Conceptually, this is the subject oriented representation of data and this is where dimensional models and OLAP cubes are built for reporting. This layer is all about quicker access to data and better performance of dashboards, reports etc.
As shown in the picture, data can be accessed for reporting and analysis from all the layers in this architecture. For example, we can build operational reports directly from the source or build detailed reports from foundation layer or build aggregated reports from access and performance.
Oracle has leveraged this architecture to build enterprise application warehouse products like OTDM(Oracle Tax Data Model), OCDM(Oracle Communications Data Model) etc. Businesses can simply buy these data models and plug in their source systems and bring their data warehouses live quickly at a reduced cost.
Let us discuss each component of the warehouse reference architecture in the coming articles.
Happy Reading!!
Apps2Fusion Articles 