Using Qlik Sense with Kognitio

This tutorial will show you how to create a simple Qlik Sense dashboard to access and visualize your data from Kognitio.

For information on how to connect Qlik Sense to Kognitio see Qlik Sense configuration.

This tutorial assumes you have Qlik Sense server installed. For more information about this refer to the deployment section of Qlik’s documentation.

Data

This tutorial uses Kognitio’s retail data available for download. For more information how to load this data and set it up in memory follow our getting started tutorial or run our retail demo scripts.

Importing data into Qlik Sense

There’s two ways to import data into Qlik Sense; visually via the GUI or using the load editor. The GUI is more suited for users who don’t like using coding interfaces or are not proficient in SQL.

In this tutorial we will step through both the GUI and load editor with a small data set to demonstrate functionality. For advanced analytics using larger data sets it is necessary to use the load editor. For more details see Using Kognito to empower Qlik Sense on-demand app generation (ODAG),

Creating visuals from data loaded via the GUI

  1. Open the Qlik Sense hub. Create a new app and name it “Sales-retail”.

  2. At the data loading screen select the Add data from files and other sources box which brings up the connection screen.

  3. From the choice of connections, click on ODBC.

  4. Select the DSN that points to your Kognitio system. Enter your login credentials and name the connection.

  5. In the Owner dropdown we can see the schemas available in Kognitio. Select the one with the retail data in it.

  6. The Tables list below will be populated automatically. Scroll down and select the sales data view that’s in memory V_RET_SALE. A preview of the data will appear on the right.

    Selection bar chart
  7. Check the box for this view in the tables list and also check the box for the product hierarchy view V_RET_PROD_HIER.

  8. Click on Add data.

  9. The data manager screen shows the selected views and associations. In the right hand pane there is a recommended association between the two views. Click on the association by product number “PRODNO” to see it visually and click Apply to confirm it.

    Selection bar chart
  10. Click on Edit the sheet in the pop-up.

  11. Let’s create a bar chart of sales by department.

    • Select Charts on left side bar and drag the bar chart to the grid in the middle.
    • Click on Add dimension and choose “DEPT_NAME”. Then Add measure and PRICE -> SUM(PRICE).
    • Edit the labels for these as “Department” and “Total sales” then change the chart title to “Sales by department”.
    • Add some colour by going to the Appearance and Colors and legend sub menus on the right. Toggle Auto color to be off and color by dimension.
    • Use “DEPT_NAME” as the color dimension if it’s not automatically selected and the color will be applied.
Selection bar chart

Creating visuals from data loaded via the load editor

  1. Create a new app and name it “Sales-retail2”.

  2. At the data loading screen, select the Script editor box.

  3. In the Main section, enter the connection string. This usually has the form:

    LIB CONNECT TO '<connection_name>'
    

    In this example we have:

    LIB CONNECT TO 'test_connection (ec2amaz-6vll591_administrator)';
    

    Note: your created connection strings can be found on the right in the load editor

    If you don’t have a connection then create a new one

    • Click on Create new connection.
    • Select ODBC
    • Select your DSN created earlier
    • Enter your Kognitio login credentials.
    • Name the connection and click Create.
  4. Below the connection string insert this SQL query:

    LOAD
            DEPT_NAME,
            SUM(TOTAL_SALES) as TOTAL_SALES
    GROUP BY
            DEPT_NAME;
    SQL
    SELECT ph.dept_name,SUM(price) total_sales
    FROM demo_ret.v_ret_sale sa
    JOIN demo_ret.v_ret_prod_hier ph ON sa.prodno=ph.prodno
    GROUP BY 1;
    

    This SQL query will emulate the functionality we created by selecting the association in the GUI section above. Here we are doing a SQL join in Kognitio rather than bringing the data into Qlik Sense and applying the association locally. This means that less data has to be transferred to the Qlik Sense server as it is already aggregated in Kognitio . However you are more restricted with what you can do with the data within Qlik Sense itself.

    If you prefer to do the association in Qlik Sense then you can load the two views separately in the same script and then associate them via the Data Manager as we did in the GUI section.

  1. Let’s create a bar chart of sales by department.

    • Load the data via the button in the top right.
    • Once it’s loaded, close the pop-up and use the navigation button in the top left and open the app overview.
    • Create a new sheet, name it “Sales by department” and open it.
    • Open edit mode and make a basic bar chart of sales with department as the dimension
    • Select Charts on left side bar and drag the bar chart to the grid in the middle.
    • Click on Add dimension and choose “DEPT_NAME”. Then Add measure and “TOTAL_SALES”.
    • Edit the labels for these as “Department” and “Total sales” then change the chart title to “Sales by department”.
    • Add some color by going to the Appearance and Colors and legend sub menus on the right. Toggle Auto color to be off and color by dimension.
    • Use “DEPT_NAME” as the color dimension if it’s not automatically selected and the color will be applied.
    Selection bar chart
  2. Learn More: