Data Warehouse Architecture

We all know that for building something from scratch we need an architect that will create a blue print or an architecture of system so that we can execute and implement that blue print to bring into life.

Similarly to create a data warehouse for an organization we need to think of from the prospective of data warehouse architect. There are several phases or steps the operational data need to undergo before being a part of data warehouse. It totally depends how your client want to analyse their data.

Different data warehousing systems have different structures. Some may have an ODS (Operational Data Store), while some may have multiple data marts. Some may have a small number of data sources, while some may have dozens of data sources. In view of this, it is far more reasonable to present the different layers of a data warehouse architecture rather than discussing the specifics of any one system. The picture in header shows the relationships among the different components of the data warehouse architecture.

DataWarehouseArchitecture

In general, all data warehouse systems have the following 9 layers:

  1. Data Source Layer
  2. Data Extraction Layer
  3. Staging Area
  4. ETL Layer
  5. Data Storage Layer
  6. Data Logic Layer
  7. Data Presentation Layer
  8. Metadata Layer
  9. System Operations Layer

Each component is discussed individually below:

1. Data Source Layer

This represents the different data sources that feed data into the data warehouse. The data source can be of any format — plain text file, relational database, other types of database, Excel file, etc., can all act as a data source.

Data Source Types:-

  • Operations — such as sales data, HR data, product data, inventory data, marketing data, systems data.
  • Web server logs with user browsing data.
  • Internal market research data.
  • Third-party data, such as census data, demographics data, or survey data.

All these data sources together form the Data Source Layer.

2. Data Extraction Layer

Data gets pulled from the data source into the data warehouse system. There is likely some minimal data cleansing, but there is unlikely any major data transformation.

3. Staging Area

This is where data sits prior to being scrubbed and transformed into a data warehouse / data mart. Having one common area makes it easier for subsequent data processing / integration.

4. ETL Layer

This is where data gains its “intelligence”, as logic is applied to transform the data from a transnational nature to an analytical nature. This layer is also where data cleansing happens. The ETL design phase is often the most time-consuming phase in a data warehousing project, and an ETL tool is often used in this layer.

5. Data Storage Layer

This is where the transformed and cleansed data sit. Based on scope and functionality, 3 types of entities can be found here: data warehouse, data mart, and operational data store (ODS). In any given system, you may have just one of the three, two of the three, or all three types.

6. Data Logic Layer

This is where business rules are stored. Business rules stored here do not affect the underlying data transformation rules, but do affect what the report looks like.

7. Data Presentation Layer

This refers to the information that reaches the users. This can be in a form of a tabular / graphical report in a browser, an emailed report that gets automatically generated and sent everyday, or an alert that warns users of exceptions, among others. Usually an OLAP tool and/or reporting tool is used in this layer.

8. Metadata Layer

This is where information about the data stored in the data warehouse system is stored. A logical data model would be an example of something that’s in the metadata layer. A metadata tool is often used to manage metadata.

9. System Operations Layer

This layer includes information on how the data warehouse system operates, such as ETL job status, system performance, and user access history.

Hope this article provides you some idea about the life cycle of data before it is an integral part of data warehouse. Looking forward for feedback and suggestion.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s