Using Kognitio to run Qlik Sense On Demand App Generation

This tutorial will show you how to create a simple Qlik Sense On Demand App Generation (ODAG) dashboard to access and visualize your big data from Kognitio.

The data used in this tutorial is the Kognitio retail data. For more information how to load this data into Kognitio see the getting started guide. or run the retail demo scripts

We assume that you have Qlik Sense server installed and you can already set-up a connection to Kognitio from Qlik Sense. For more information about Qlik Sense installation refer to the deployment section of Qlik’s documentation .

Kognitio and ODAG

On-demand app generation (ODAG) is Qlik Sense’s solution for visualising big data. The rationale is that users will only be interested in analyzing a subset of the data at a time. Therefore they need to make informed selections before drilling down to their chosen subset. To facilitate this a “selection” app is created. This shows an aggregated summary of all the data. The user then makes their own selections based on this summary information and creates a new “detail” app that uses their selection to filter the data. This new detail app is created “on demand” by querying the underlying data source with predicates based on the selections made.

ODAG isn’t limited to just one summary and one detail level.Several levels of app genration can be strung together. This allows Qlik developers to build a whole analysis journey for end users that can be driven by the user selections - on demand.

The “on demand” app generation is only as fast as the platform supporting it. This is where Kognitio performance comes in: super-fast SQL processing allows apps to be generated very quickly making Qlik Sense ODAG very responsive.

Additionally, with Kognitio empowering Qlik Sense ODAG analysis isn’t limited to SQL queries. Kognitio runs complex code (such as R or python) within a SQL wrapper (known as external scripts) meaning advanced analytics can be run as part of the Qlik Sense ODAG process.

Making the selection app

  1. In Qlik Sense go to the load editor and insert your connection string and this SQL aggregation query:

    LIB CONNECT TO 'test_connection (ec2amaz-6vll591_administrator)';
    
    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;
    

    Notice that we’re doing the aggregation in the SQL within Kognitio instead of Qlik Sense. This is so we can process large amounts of data that might be too big to pull into the memory allocated to Qlik Sense.

    Note

    Your connection strings can be found on the right in the load editor.

    Load editor and connections
  2. Load the data via the button in the top right. Close the pop-up.

  3. Use the navigation button in the top left to open the app overview.

  4. Create a new sheet named “summary” and open it.

  5. Open edit mode and make a basic bar chart of sales with department as the dimension:

    Selection bar chart

This Qlik Sense “summary” app will be the selection app for our ODAG process.

Making the details app

The detail app is similar to the selection app process but we don’t need a GROUP BY in our SQL query. In this tutorial we are going to look at sales in a specific department in the product hierarchy and our basic SQL query is:

SELECT ph.section_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 is a summary of sales by product section.

Go to the load editor and put this in like in the selection app. Above the load editor we’re going to define some functions which will take the user selections from the selection app and apply it to this one when it’s created and opened.

First is the SQL predicate building function, this creates the “where <column> in (<values>)” which we’re going to put into the SQL query:

SUB ExtendSQLWhere(Name, ValVarName)
  LET T = Name & '_COLNAME';
  LET ColName = $(T);
  LET Values = $(ValVarName);
  IF len(Values) > 0 THEN
        IF len(WHERE_PART) > 0 THEN
        LET WHERE_PART = '$(WHERE_PART) AND $(ColName) IN ( $(Values) )';
    ELSE
        LET WHERE_PART = ' WHERE $(ColName) IN ( $(Values) )';
    ENDIF
  ENDIF
END SUB;

Next is the value list. Here values are passed from the source field in the selection app and delimited but you can always hard code some values in here for testing purposes. Below that you define the column in the SQL you want to apply it to:

SUB BuildValueList(VarName, TableName, ColName, QuoteChrNum)
  IF ($(QuoteChrNum) = 0) THEN
    LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';
  ELSE
    LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';
    LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';
  ENDIF
  _TempTable:
  LOAD $(LOADEXPR) Resident $(TableName);
  Let vNoOfRows = NoOfRows('_TempTable');
  IF $(vNoOfRows)> 0 THEN
    LET $(VarName) = Peek('CombinedData',0,'_TempTable');
  ENDIF
  drop table _TempTable;
  drop table '$(TableName)';
END SUB;

SET DEPT_NAME='';
OdagBinding:
LOAD * INLINE [
VAL
$(ods_DEPT_NAME){"quote": "", "delimiter": ""}
];
SET DEPT_NAME_COLNAME='DEPT_NAME';
CALL BuildValueList('DEPT_NAME', 'OdagBinding', 'VAL', 39);

The “BuildValueList” concatenates the values together and delimits them. As SQL syntax is mostly comma delimited we’ll use that here. The prefix in the ODAG binding (“ods” in the above) defines how to link the fields between apps. There are different options for linking the columns:

ods - Selected values only e.g. where department = chocolate odso - Selected and associated values, this is for values on the same row e.g. choosing where price = 1 would apply all the other columns on the same row as filters too. odo - Associated values only odx - Excluded values only

You’ll mainly want to use are ods and odso.

To call the functions:

SET WHERE_PART = '';
FOR EACH fldname IN 'DEPT_NAME'
  LET vallist = $(fldname);
  WHEN (IsNull(vallist)) LET vallist = '';
  IF len(vallist) > 0 THEN
    CALL ExtendSQLWhere('$(fldname)','vallist');
  ENDIF
NEXT fldname

This iterates over the values, call the functions and builds the SQL predicate. Insert the WHERE_PART containing this into the SQL query:

SELECT ph.section_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
$(WHERE_PART)
GROUP BY 1;

In the end your detail app load editor should look like this:

SUB BuildValueList(VarName, TableName, ColName, QuoteChrNum)
  IF ($(QuoteChrNum) = 0) THEN
    LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';
  ELSE
    LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';
    LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';
  ENDIF
  _TempTable:
  LOAD $(LOADEXPR) Resident $(TableName);
  Let vNoOfRows = NoOfRows('_TempTable');
  IF $(vNoOfRows)> 0 THEN
    LET $(VarName) = Peek('CombinedData',0,'_TempTable');
  ENDIF
  drop table _TempTable;
  drop table '$(TableName)';
END SUB;

SUB ExtendSQLWhere(Name, ValVarName)
  LET T = Name & '_COLNAME';
  LET ColName = $(T);
  LET Values = $(ValVarName);
  IF len(Values) > 0 THEN
        IF len(WHERE_PART) > 0 THEN
        LET WHERE_PART = '$(WHERE_PART) AND $(ColName) IN ( $(Values) )';
    ELSE
        LET WHERE_PART = ' WHERE $(ColName) IN ( $(Values) )';
    ENDIF
  ENDIF
END SUB;

//Dynamic values
//SET DEPT_NAME='';
//OdagBinding:
//LOAD * INLINE [
//VAL
//$(ods_DEPT_NAME){"quote": "", "delimiter": ""}
//];
//SET DEPT_NAME_COLNAME='DEPT_NAME';Response Requested: Updated Contact Info
//CALL BuildValueList('DEPT_NAME', 'OdagBinding', 'VAL', 39);

//Fixed values
SET DEPT_NAME='';
OdagBinding:
LOAD * INLINE [
VAL
$(ods_DEPT_NAME)Ambient Grocery
];
SET DEPT_NAME_COLNAME='DEPT_NAME';
CALL BuildValueList('DEPT_NAME', 'OdagBinding', 'VAL', 39);

SET WHERE_PART = '';
FOR EACH fldname IN 'DEPT_NAME'
  LET vallist = $(fldname);
  WHEN (IsNull(vallist)) LET vallist = '';
  IF len(vallist) > 0 THEN
    CALL ExtendSQLWhere('$(fldname)','vallist');
  ENDIF
NEXT fldname

TRACE $(WHERE_PART);

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

LOAD
    SECTION_NAME,
    TOTAL_SALES
;
SQL
    SELECT ph.section_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
    $(WHERE_PART)
    GROUP BY 1;

We’re going to use a fixed value for our department to build some visuals first so we have something to work with and then swap it out with the dynamic one when we’re done.

Load the data, and go to the app overview screen using the navigation button again. Create a new sheet called “detail” and create a simple bar chart of sales by section:

Detail bar chart

For full details on creating a bar chart see Getting Started: using Qlik Sense with Kognitio

Now go back to the load editor, uncomment the dynamic value list and comment out the fixed one.

Linking them together

We now link the department(s) selected in the selection app to the department(s) used in the WHERE clause of the detail app.

  1. Open the selection app and edit the “selection” sheet. Click on navigation links app in the left side bar. Select create new.

  2. In the pop-up set the apps navigation link options:

    • Name: to “Sections in department”
    • Select your detail app as the Template app
    • Expression: to “count(DEPT_NAME)”
    • Maximum row count: to 1000. This is how you control the amount of data brought back to Qlik Sense memory. The end-user selection must meet this limit before the new detail app can be generated
    • Max number of apps to 5
    • Expiration to “Never Expires”.
    • Default view when opened to “Detail”

    Click Create.

    App navigation
  3. Right click on the “Sections in department” link. Click Add to App navigation.

    Add button
  4. Click Done to exit edit mode. The (green) App navigation button appears in the bottom left corner of your visual. You are now ready to use ODAG

  5. Make a selection by clicking on one of the bars of the graph. Click the App navigation button. Click “generate new app”.

  6. When the detail app is generated it will appear in the menu. Open it by clicking on the square and arrow icon on the right:

    Make new app
  7. Now we see our sales by product section bar chart but it’s been filtered to use only the one department selected; for example “household and pets”.

    New detail app
  8. Confirms ODAG is working. Go to the load editor to check the values used in the filtering.

    Selection detail app
  9. Learn More. In this tutorial we have shown you how to create a simpe link between one Selection and Detail App. To see the full potential of using Kognitio and Qlik together for big data analytics see our video on Using Kognitio to empower Qlik On Demand App Generation