ETL: Qlik Scripting vs Sql query

[bruno_m_santos]

Hi folks,

today I had a conversation with an Qlik developer about the advantages and disadvantages about build the ETL with Qlik scripting and Sql (Queries, SP).

Unfortunately, we don't achieve a clear conclusion.

I'd like to know your opinion about the issue. What do you think, that is the best approach?

Many Thanks

Bruno

[Gysbert_Wassenaar]

I don't think a clear conclusion is possible, other than perhaps 'use the tool that fits the job best'. There are things that are very easy to do in Qlik{View|Sense} script and hard to do in sql. And there are things that are better done on a database server to make best use of the processing capabilities of the dbms and reduce network traffic to only what's needed. And some things are best done by ETL tools (Powercenter/OWB/PDI), because those can make it easier to manage the etl processes, generating lineage information, data quality issue handling, exception handling, error recovery etc.

[maxgro]

SQL vs. QlikView for Complex Loads

[IAMDV]

Hi Bruno,

There are three things to consider before concluding anything on this topic:

1. Functionality

2. Performance

3. Skill Set

Let's start with functionality: Most of the traditional RDBMS systems are designed for OLTP although this is changing now. Which means there are some missing features or it will be resource intensive queries/procs. So, it's important understand what kind of transformations are required  to use the right tool(s). There are loads of specialized ETL tools which has standard transformations and customize options. Also, you need to consider where you want to store you data, i.e. if you wanted to transform the data and store in data warehouse then it doesn't help to use QlikView (You can still use it but not efficiently!).

Performance: If data load times are key for the project then you need to consider both the options. Using databases, you can partition tables and create optimized indexes for faster data retrieval. If you use the indexes effectively, where queries/procs performing index seek instead of table scan then your queries will be super fast. Some of the transformations in QV are single threaded in older versions, which means it will take more time to transform in QV compared to the database. However, it all depends on the data load requirements.

Skill Set: If you have more resources who are specialized in writing SQL queries/procs compared to QV then it makes sense to use the existing resources. However, most of the QV scripting is similar to ANSI SQL programming. So, gauge the skill set of the team members and make call.

I hope this gives some direction.

Good luck!

Cheers,

DV

www.QlikShare.com

[DropTable]

Hi,

Actually I have been using Qlik Sense for 2 months but I guess I have enough knowledge to make some comparsion,

let me know if you disagree with something so I can learn new things...

1- Unlikely RDBMS, you have to manually manage the logical objects in your memory, which means, you design your way of the execution plan. This is especially very obvious when there is no subquery logic in qlik scripting. You have to create your own table and threat it as a subquery, on the contrast this is done automatically by rdbms with very efficient ways using inbuild algorithms.

2-Debugger of qlik is very weak.   

3- Qlik has no validation process before execution, which means it has to execute entire script until it finds you have a typo. RDBMS on the other hand, deals with it, not only for typos but different aspects as well (security, permissions) before even attempting to use the data. 

4- Qlik is not practical, most of the time when I use SQL for db object creations, I usually start with select statement to see my logic is correct. You can not do this with Qlik, you need to create everything including data visuals to check how you are doing. Its such a pain if you have complicated data model.  

So for me Qlik is not an ETL tool and its not as efficient as the other top ETL tools. Its just all in one BI tool. Which is cool.