Data Warehouse Architecture

Data Warehouse Architecture

Published date: 2nd Feb 2015
Categories: Blog Date Warehousing

A Data Warehouse system is the main repository of all data collected by various business divisions and departments of an enterprise. It’s architecture is made up of five main components to provide flexible reporting capabilities to an organisation.

The  five main components of Data Warehouse Architecture are:

    • Internal Data Source / Operational Databases; Information that is procured, reported and consolidated within your organization, for example: purchase orders from sales, transactions from accounting, leads from marketing or information from CRM software.
    • External Data Sources: any information obtained from a source outside of your organization, for example: information collected through census or surveys. 
  1. ETL TOOL:

ETL = Extract – Transform – Load

  • The typical extract-transform-load (ETL)-based data warehouse uses staging, data integration, and access layers.
  • Staging Server: The staging database holds the extracted raw data from the source applications for further processing prior to loading into the data warehouse.  Multiple staging databases can be defined and created and split in terms of specific business areas.
  • Integration Layer: integrates the unrelated data sets by transforming the data from the staging layer and storing this transformed data in an operational data store (ODS) database.
  • The integrated data is then loaded into the data warehouse database.
  • Each step within the extraction, transformation and load process will include error handling to ensure that errors do not make it down to the data warehouse
  1. Data Warehouse Server

The DWH server is the physical storage used to hold the data model for the enterprise data warehouse.

The main risk involves data mapping from the source systems to the data model, which ensures you have sufficient data to meet the reporting requirements.

DWH server configuration optimisation is critical in order to ensure a processing power that can handle multiple querying of data at once; while also storing, managing and securing both new and historical data. The production of reports (in various formats) on several queries, without interrupting ongoing processes, is also important

The goal of the data warehouse is to report on/publish the organization’s data assets to most effectively support decision making.


(OLAP) Online Analytical Processing is the analyses business data for the release of business insight. In order to facilitate the self-service BI requirements and complex analysis and visualisation requirements; the data in the warehouse will conform to a multi-dimensional model in the form of an OLAP cube, where data is organised into hierarchies or dimensions (with multiple levels of detail) for future/advanced business analysis and query purposes.

Using OLAP cubes, an organisation can:

  • Rotate and drill down
  • Create and examine calculated data
  • Determine comparative or relative differences.
  • Perform exception and trend analysis.
  • Perform advanced analytical functions

For in-depth information on OLAP Cube; click HERE

  1. Data Mining / Reporting Tools

Data Mining is the analysis of data sets in order to extract meaning / predict behaviours and trend analysis; and report this information in a readable format fulfilling to business user requirements.

If you need more information on DataWarehousing Solutionor would like to arrange a consultation, please feel free to email or call us on +44 (0) 207 8732 198