Friday, May 8, 2009

What is the difference between ETL tool and OLAP tool?

ETL:
Extract, Transform and Load. This is a product to extract the data from multiple/single source transform the data and load it into a table, flat file or simply a target.
There is a quite a bit competition in the market with regard to the ETL product as well as the OLAP products. These tools would definitely be widely used for data load and data analysis purpose.
ETL tools in market

1 INFORMATICA-- univeral tool good market
2 ABINITO -- fastest loading tool very good market
3 DATASTAGE-- difficult work no good market
4 BODI-- good market
5 ORACLE WAREHOUSE BUILDER-- good market

ETL Tools is for Extracting source data, Transforming as required and Loading into DW. it is first part of DW. Now large fact and dimension tables are in DW. If you have to generate crosstab report from these tables, it will take very long time. So these DW tables are transformed into Cubes and stored in OLAP Server. Any Report Generation, Analysis is done on these... Cubes are having pre aggregated values and hence Report Generation will be much faster.

OLAP:
Its an online analytical processing tool. There are various products available for data analysis. Like Hyperion which is a Business Intelligence (BI) and Business Performance Management (BPM ) Tool like Brio, Business Objects and Cognos etc. Its the market leader in Financial, Operational and Strategic Planning. It has applications for Planning, consolidation, scorecarding, reporting, dashboards, Analysis, Workspace, Master Data Management and Foundation.

The Difference between the two:
There are only 2 ways available to get data from a datawarehouse or a database.one is using sql statement and other method is using an OLAP tool(ex:cognos, business objects.....)So Hyperion is an example for a OLAP tool. ETL is a process of Extracting data from a source systems(a system where client's transactional business information resides) and performing business logic(applying TRANSFORMATION rules) on the collected source information(business logic depends on business requirements), and LOADING the precise data into a warehouse(probably a relational database). OLAP comes into picture after ETL developers design the warehouse........by using olap tools a business user can generate standard reports(without having sql knowledge he can do this)

2 comments:

Anonymous said...

good..nice explanation

Anonymous said...

Excellent explanation with clear details - From Sujatha