OLAP Basic Concept

OLAP stores data in multi-dimension; it helps the user to view the data in different point of view. OLAP database is not necessarily required to be as large as data warehouse since we use only a part of data in the data warehouse to work on the analysis. OLAP can be defined as a decision support system that allows user to quickly analyze information that has been summarized into multi-dimensional views and hierarchies.

There are three main features of OLAP system :

ü Multidimensional Viewing – OLAP supports multidimensional model which consists of facts and dimensions also called as Star Schema.

ü Calculation Intensive Capabilities – Due to data is stored in facts and dimensions tables, it enables users to analyze data without much calculations.

ü Time Series analysis – Enables users to analyze data across time.

*This is just a basic idea, read more details if required.

ETL Basic Concepts

ETL stands for Extract Transform & Load; this is the foundation of Data Integration Systems. The concept which we are talking here is very simple and self explanatory. Here you go…

E – Extract:

Extracting data from different sources, where each source contains different types of data and in different formats.

T – Transform:

Transform the data into a required unified format.

L – Load:

Load the data into the required system. Normally it will be a Data Warehouse or any other applications.

What is done during processing the data in the ETL?

Below is the processing we normally do in ETL.

1. Aggregating Data

2. Cleansing Data

3. Deriving Data

4. Filtering Data

5. Integrating Data

6. Validating Data

Data Warehouse Basic Definition

Data Warehouse is a just like a database which stores large amount of data usually the data stored in the data warehouse are old data not current data. Organizations use these data stored over years in the data warehouse to analyze the trend and to forecast the future. This analysis is very helpful to find some unknown patterns in the Business which might turn the profits higher.

Data warehouse mainly has 3 functional layers: Staging Layer, Integration Layer and Access Layer

In Staging Layer, the raw data is stored.

In Integration Layer, some data are integrated together as per the requirement and are stored in this layer.

In Access Layer, data are ready to be accessed in the format which will be helpful for generating the reports.

What is DataStage?

The very first question which arises in people’s mind is what is it? Here is the definition by wikipedia.com, “IBM InfoSphere DataStage is an ETL tool and part of the IBM Information Platforms Solutions suite and IBM InfoSphere. It uses a graphical notation to construct data integration solutions and is available in various versions such as the Server Edition and the Enterprise Edition.”

IBM is the company which has developed this product; this product comes under a product line called InfoSphere. InfoSphere contains any many products (you can check out the products using the link http://www-01.ibm.com/software/data/infosphere/ )

ETL stands for Extract, Transform & Load, if you don’t know ETL concept I feel its better you get some brief knowledge about it and then continue reading.

ETL concept is used with the Data Warehouse, because we are going to handle billions or trillions or even more number of records in Data Warehouse and it will be dealt differently not like the way we deal with few thousands of records.

I suggest you to read about Data Warehouse, OLAP, OLTP and difference between Data Warehouse and Data Base before continuing reading on DataStage.