QlikView and SAP HANA
Data load and calculation comparison
This document shows result from data load and calculation performance in QlikView that reads data directly from SAP HANA DB tables by using ODBC or JDBC drives.
2 Software installations
There were used 2 ways to fetch data from HANA to QlikView: via ODBC and JDBC connector. Each of them has its own functionality restrictions and features. ODBC connector is free and distributed with HANA Studio installation. JDBC connector is paid solution and provides wider useful functionalities than ODBC connector.
SW & HW preferences:
QlikView desktop client:
11.20.11922 SR2 x64
SAP HANA DB:
TIQ Solutions – v 126.96.36.199
Intel Xeon 2,26GHz, 14 GB RAM, Windows Server 2008 R2 x64
SAP HANA installation:
CPU? , 505 GB RAM, SUSE Linux Enterprise Server 11.1
3 Test parameters
The load performance was tested in QlikView document. Calculations were done either in HANA DB (remote DB, server) or QlikView (local client, server). As mentioned above, data were fetched either by ODBC or JDBC connector. Each combination was executed 4 times (e.g. HANA over JDBC …) and results were averaged.
Data were fetched from physical tables and from simple Analytic model created in SAP Hana Studio. The test set of data from physical tables contained fact table (5 000 000 rows) and 3 dimensions (9-6500 rows). The Analytic model that is stored as table by default under _SYS_BIC schema (Column Views) was also limited to 5 000 000 rows. When loading data from Analytic view the SQL has to contain aggregation function, therefore aggregations on QV side were omitted and were done completely on HANA side.
The sample data in HANA was created based on this article from SCN. Fact table was restricted on SQL level in QV script.
Following table represents the connection types:
Where most calculation were done
Type of connection
SAP HANA DB Source
On QV side
HANA Analytic View
One fact table
HANA Analytic View
One fact table
Main power consumption operations were simple multiplication (UNIT_PRICE * QUANTITY_SOLD as SALES), joins (in HANA: inner, in QlikView: Right join) and aggregations (only on HANA Analytic View).
The difference between first four loading approaches is not too big. The fastest loading approach is when loading physical SAP HANA tables by using ODBC connector with calculations and joins done on QlikView side. The slowest is JDBC connector with calculations done inside HANA DB Analytic view.
Results of 5 000 000 rows sorted by average duration:
avg Duration (mm:ss)
QV application with loading script is attached (note that last two tabs in Script won’t work unless you create the Analytic model with same structure as in script, therefore disable loading of those last 2 tabs).
From the results of this simplified performance test appears that the fastest solution to fetch data from HANA to QlikView is to use ODBC connection over SAP HANA physical tables and do calculations on QlikView side. But in real-life situations there might be another loading approach more suitable and also it depends on hardware configuration where the data are calculated.
Combination of in-memory SAP HANA DB and in-memory QlikView (e.g. with Direct discovery technology – not tested in this test) can be suitable for big data solutions.