Data Warehouse - Overview

Data Warehouse – Overview

A Information Warehouse consists of knowledge from a number of heterogeneous knowledge sources and is used for analytical reporting and resolution making. Information Warehouse is a central place the place knowledge is saved from completely different knowledge sources and purposes.

The time period Information Warehouse was first invented by Invoice Inmom in 1990. A Information Warehouse is at all times saved separate from an Operational Database.

The information in a DW system is loaded from operational transaction programs like −

  • Gross sales
  • Advertising and marketing
  • HR
  • SCM, and so forth.

It could move by operational knowledge retailer or different transformations earlier than it’s loaded to the DW system for data processing.

A Information Warehouse is used for reporting and analyzing of data and shops each historic and present knowledge. The information in DW system is used for Analytical reporting, which is later utilized by Enterprise Analysts, Gross sales Managers or Information employees for decision-making.

Data Warehouse

Within the above picture, you possibly can see that the info is coming from a number of heterogeneous knowledge sources to a Information Warehouse. Widespread knowledge sources for an information warehouse consists of −

  • Operational databases
  • SAP and non-SAP Purposes
  • Flat Information (xls, csv, txt recordsdata)

Information in knowledge warehouse is accessed by BI (Enterprise Intelligence) customers for Analytical Reporting, Information Mining and Evaluation. That is used for resolution making by Enterprise Customers, Gross sales Supervisor, Analysts to outline future technique.

Options of a Information Warehouse

It’s a central knowledge repository the place knowledge is saved from a number of heterogeneous knowledge sources. A DW system shops each present and historic knowledge. Usually a DW system shops 5-10 years of historic knowledge. A DW system is at all times saved separate from an operational transaction system.

The information in a DW system is used for several types of analytical reporting vary from Quarterly to Annual comparability.

Information Warehouse Vs Operational Database

The variations between a Information Warehouse and Operational Database are as follows −

  • An Operational System is designed for recognized workloads and transactions like updating a person file, looking out a file, and so forth. Nonetheless, Information Warehouse transactions are extra advanced and current a normal type of knowledge.
  • An Operational System incorporates the present knowledge of a corporation and Information warehouse usually incorporates the historic knowledge.
  • An Operational Database helps parallel processing of a number of transactions. Concurrency management and restoration mechanisms are required to keep up consistency of the database.
  • An Operational Database question permits to learn and modify operations (insert, delete and Replace) whereas an OLAP question wants solely read-only entry of saved knowledge (Choose assertion).

Structure of Information Warehouse

Information Warehousing entails knowledge cleansing, knowledge integration, and knowledge consolidations. A Information Warehouse has a 3-layer structure −

Information Supply Layer

It defines how the info involves a Information Warehouse. It entails varied knowledge sources and operational transaction programs, flat recordsdata, purposes, and so forth.

Integration Layer

It consists of Operational Information Retailer and Staging space. Staging space is used to carry out knowledge cleaning, knowledge transformation and loading knowledge from completely different sources to an information warehouse. As a number of knowledge sources can be found for extraction at completely different time zones, staging space is used to retailer the info and later to use transformations on knowledge.

Presentation Layer

That is used to carry out BI reporting by finish customers. The information in a DW system is accessed by BI customers and used for reporting and evaluation.

The next illustration exhibits the frequent structure of a Information Warehouse System.

Data Warehouse Architecture

Traits of a Information Warehouse

The next are the important thing traits of a Information Warehouse −

  • Topic Oriented − In a DW system, the info is categorized and saved by a enterprise topic slightly than by utility like fairness plans, shares, loans, and so forth.
  • Built-in − Information from a number of knowledge sources are built-in in a Information Warehouse.
  • Non Unstable − Information in knowledge warehouse is non-volatile. It means when knowledge is loaded in DW system, it’s not altered.
  • Time Variant − A DW system incorporates historic knowledge as in comparison with Transactional system which incorporates solely present knowledge. In a Information warehouse you possibly can see knowledge for Three months, 6 months, 1 yr, 5 years, and so forth.


Firstly, OLTP stands for On-line Transaction Processing, whereas OLAP stands for On-line Analytical Processing

In an OLTP system, there are a lot of brief on-line transactions akin to INSERT, UPDATE, and DELETE.

Whereas, in an OLTP system, an efficient measure is the processing time of brief transactions and may be very much less. It controls knowledge integrity in multi-access environments. For an OLTP system, the variety of transactions per second measures the effectiveness. An OLTP Information Warehouse System incorporates present and detailed knowledge and is maintained within the schemas within the entity mannequin (3NF).

For Instance −

A Day-to-Day transaction system in a retail retailer, the place the client information are inserted, up to date and deleted every day. It gives quicker question processing. OLTP databases comprise detailed and present knowledge. The schema used to retailer OLTP database is the Entity mannequin.

In an OLAP system, there are lesser variety of transactions as in comparison with a transactional system. The queries executed are advanced in nature and entails knowledge aggregations.

What’s an Aggregation?

We save tables with aggregated knowledge like yearly (1 row), quarterly (Four rows), month-to-month (12 rows) or so, if somebody has to do a yr to yr comparability, just one row might be processed. Nonetheless, in an un-aggregated desk it’ll examine all of the rows. That is known as Aggregation.

There are numerous Aggregation features that can be utilized in an OLAP system like Sum, Avg, Max, Min, and so forth.

For Instance −

SELECT Avg(wage)
FROM worker
WHERE title = 'Programmer';

Key Variations

These are the most important variations between an OLAP and an OLTP system.

  • Indexes − An OLTP system has solely few indexes whereas in an OLAP system there are lots of indexes for efficiency optimization.
  • Joins − In an OLTP system, giant variety of joins and knowledge are normalized. Nonetheless, in an OLAP system there are much less joins and are de-normalized.
  • Aggregation − In an OLTP system, knowledge will not be aggregated whereas in an OLAP database extra aggregations are used.
  • Normalization − An OLTP system incorporates normalized knowledge nevertheless knowledge will not be normalized in an OLAP system.


Information Mart Vs Information Warehouse

Information mart focuses on a single useful space and represents the best type of a Information Warehouse. Think about a Information Warehouse that incorporates knowledge for Gross sales, Advertising and marketing, HR, and Finance. A Information mart focuses on a single useful space like Gross sales or Advertising and marketing.

Data Mart Vs Data Warehouse

Within the above picture, you possibly can see the distinction between a Information Warehouse and an information mart.

Reality vs Dimension Desk

A truth desk represents the measures on which evaluation is carried out. It additionally incorporates overseas keys for the dimension keys.

For instance − Each sale is a truth.

Cust Id Prod Id Time Id Qty Offered
1110 25 2 125
1210 28 4 252

The Dimension desk represents the traits of a dimension. A Buyer dimension can have Customer_Name, Phone_No, Intercourse, and so forth.

Cust Id Cust_Name Telephone Intercourse
1110 Sally 1113334444 F
1210 Adam 2225556666 M