What is Etl & Datawarehouse?

You probably heard about datawarehouses & analysis tools in the past in database related discussions or in job meetings.

The fact is that everyday more and more companies are starting to think (and build) data warehouses or other data analysis and statistical tools.

It is no more a large-companies-only matter; today with the right knowledge and expertise (or a little education) small companies can also make analysis and get valuable information to boost sales or revenues.

So what is a datawarehouse?

In simple words, a Datawarehouse is a common repository (a database, for simplicity) of information about a company’s activities and operations.

This means, all your company’s transactions such as sales, payments or acquisitions end up in the data warehouse.

This “database” is a technical product or platform that allows us to “ask” real life business questions such as “which branch sold more products this month?” or “who is my top performing salesman?”

You may be thinking “I currently HAVE a database which records sales/transactions/movements of my company everyday”.

Yes, of course you have one of those. But that is a transactional database. This means that this database is heavily used everyday to store our company’s operations, and because of this we cannot use it for analysis.

Also, this database holds data, not information.

A typical example for a record would be “Qty:1 Product_code: Shoes A Code_Branch: South …”. This is not meaningful for a report containing information, not only raw data that can not answer complex business questions which would allow us to make a manager decision.

These and a few other points are the key reasons to build a datawarehouse to do analysis.

So how I move or copy the data from my everyday transactional database to my datawarehouse?

Here is where ETL comes to play.

ETL is the process for Extracting, Transforming and Loading data from one database to another.

There are several ways for doing this, from coding your own processes to the more often used way of implementing ETL tools.

These ETL tools can do the job very well, and if chosen wisely can save you a lot of coding efforts and money, since you can graphically build processes and in most cases without knowing how to program for databases.

There are a lot of ETL tools in the market right now. As and advice, I suggest you to invest some research time (and testing if possible) before choosing the one that suits your company’s needs.

They can range from open source free tools to high price commercial tools. Neither of them is perfect in every situation, and you will have to take into account your data volumes, the analysis and answers you want from your datawarehouse, and the periodicity needed of those answers, among other aspects.

More info: http://www.etlreviews.com