QlikView and SAP HANA


    QlikView and SAP HANA

    Data load and calculation comparison

     

    1 Purpose

    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:

    1.00.60.379371

    JDBC connector:

    TIQ Solutions – v 2.0.39.169

    QV installation:

    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:

    ID

    Engine

    Where most calculation were done

    Connector

    Type of connection

    SAP HANA DB Source

    Source table

    Data Model

    On QV side

    1

    QlikView

    ODBC

    Physical table

    Star schema

    2

    QlikView

    JDBC

    Physical table

    Star schema

    3

    HANA

    ODBC

    Physical table

    Star schema

    4

    HANA

    JDBC

    Physical table

    Star schema

    5

    HANA Analytic View

    ODBC

    Analytic view

    One fact table

    6

    HANA Analytic View

    JDBC

    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).

        

    4 Results

    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:

    Rank

    Engine

    Connector

    avg Duration (mm:ss)

    1.

    QV

    ODBC

    04:30

    2.

    HANA_AN_View

    ODBC

    04:45

    3.

    HANA

    ODBC

    06:05

    4.

    QV

    JDBC

    06:53

    5.

    HANA

    JDBC

    08:11

    6.

    HANA_AN_View

    JDBC

    08:44

     

    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).

     

    5 Recommendation

    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.