|
ETL: Why abandon SQL when it will give you the best performance?
|
A data warehouse appliance uses commodity processors and hardware optimized for BI and combines them with software built specifically for data warehouse-based analytics in a single box. This paper examines the value of Data Warehouse Appliances.
|
|
From manual SQL coding to the traditional ETL tool
As SQL development grows within the corporation, the pressure to acquire an ETL tool keeps increasing. It becomes more and more difficult to maintain all of the generated code and to properly control access to the different machines and databases where developers must connect. Development time needs to be reduced. ETL processes need to be streamlined and better automated. Projects need to be easily documented. Job execution and management needs to be centralized… It’s finally time to make the jump and acquire an ETL tool.
After carefully comparing solutions, you go for one of the traditional ETL offers. Invest in training, motivate your troops, and … start re-developing everything.
Wait…re-developing everything? As a matter of fact, most ETL tools have a proprietary language. You will have to learn this new language, or pay for specialized consultants… Ok, but if it’s for all the good reasons mentioned earlier, it’s probably worth it. So you invest heavily in this new way of processing data.
After the Dreams of Conquest, the Reality
Early on, one thing is clear: transformations will be performed by the engine of the ETL tool,(Figure 1) thus the need to learn a new programming language. But the real cost of these tools is not what you think. It’s not their price tag (though it can be surprisingly high). It’s not the training and consulting required either. The price tag will be all of the new constraints imposed by this new architecture. Because the transformations are performed outside of your databases, many things you were doing seamlessly within your databases when you where coding in SQL become coding nightmares and cause a performance meltdown. Let’s take two common examples: updating data in your target table, and the handling of a star schema.
Figure 1 - Traditional ETL approach
|
|
|
Oracle #1 in Business Analytics According to IDC Research
|
The Business Intelligence Search Engine has all the answers.
|
Find all you need on The Business Intelligence Search Engine.
|
|
|
|
|
|
|
|
Other
Articles by this Author
|
|
|
